select Sheet1$.ID1,count(*)as'次数',COUNT(DISTINCT Sheet1$.编码) AS'编码数量'
into aa
from Sheet1$
where Sheet1$.金额>=50000 and sheet1$.编码='55'
group by 卡号 having count(*)>5 and COUNT(Sheet1$.编码)>5
select distinct Sheet1$.ID1,Sheet1$.ID2,Sheet1$.ID3,Sheet1$.ID4,all1.ID5,all1.ID6
from Sheet1$,aa,all1
where Sheet1$.ID1=aa.ID1
and Sheet1$.ID3=all1.ID3
我想做一个子查询
select distinct Sheet1$.ID1,Sheet1$.ID2,Sheet1$.ID3,Sheet1$.ID4,all1.ID5,all1.ID6
from Sheet1$,aa,all1
where Sheet1$.ID1=aa.ID1
and Sheet1$.ID3=all1.ID3
对于ID1应该怎么写表达式 就不用每次做新的查询就要把上一次查询处理过的aa删除了
ID1的子查询为
select Sheet1$.ID1,count(*)as'次数',COUNT(DISTINCT Sheet1$.编码) AS'编码数量'
from Sheet1$
where Sheet1$.金额>=50000 and sheet1$.编码='55'
group by 卡号 having count(*)>5 and COUNT(Sheet1$.编码)>5
备注：我用的是SQL SEVER 2008
------解决思路----------------------
不是很明白。你的意思是用子查询代替中间表aa？
select distinct Sheet1$.ID1,Sheet1$.ID2,Sheet1$.ID3,Sheet1$.ID4,all1.ID5,all1.ID6
from Sheet1$,
(
select Sheet1$.ID1,count(*)as'次数',COUNT(DISTINCT Sheet1$.编码) AS'编码数量'
from Sheet1$
where Sheet1$.金额>=50000 and sheet1$.编码='55'
group by 卡号 having count(*)>5 and COUNT(Sheet1$.编码)>5
)aa,
all1
where Sheet1$.ID1=aa.ID1
and Sheet1$.ID3=all1.ID3