一个复杂的SQL语句问题请帮忙

[yxl2000]

SELECT dbo.ZJpart.id, dbo.ZJpart.partname, dbo.ZJpart.spec,
dbo.ZJpart.number * dbo.XPlan.JhCount AS number, dbo.ZJpart.danwei,
dbo.ZJpart.alarm, dbo.ZJpart.remark, dbo.ZJpart.ZJid into shiyan
FROM dbo.ZJpart INNER JOIN
dbo.XPlan ON dbo.ZJpart.ZJid = dbo.XPlan.JhCode
WHERE (dbo.ZJpart.id NOT IN
(SELECT XQid
FROM XQKind)) AND (dbo.ZJpart.id NOT IN
(SELECT DNBid
FROM DNBKind)and(dbo.XPlan.GQsign = 'F'))
union all
SELECT dbo.DNBpart.id, dbo.DNBpart.partname, dbo.DNBpart.spec,
dbo.DNBpart.number * dbo.ZJpart.number * dbo.XPlan.JhCount AS number,
dbo.DNBpart.danwei, dbo.DNBpart.alarm, dbo.DNBpart.remark, dbo.ZJpart.ZJid
FROM dbo.XPlan INNER JOIN
dbo.ZJpart ON dbo.XPlan.JhCode = dbo.ZJpart.ZJid INNER JOIN
dbo.DNBpart ON dbo.ZJpart.id = dbo.DNBpart.DNBid
WHERE (dbo.XPlan.GQsign = 'F')

请问我怎么把上面的查询结果当作下面的查询的数据源啊
就是把shiyan去掉直接写成SQL语句
select Storage.xycount,sum(shiyan.number)+shiyan.alarm-Storage.xycount as number,
shiyan.id,shiyan.partname,shiyan.spec,shiyan.danwei,shiyan.remark from shiyan,
Storage where Storage.id=shiyan.id group by shiyan.id,shiyan.partname,shiyan.spec,
shiyan.danwei,shiyan.remark,shiyan.alarm,Storage.xycount
union all
select Storage.xycount,sum(shiyan.number)+shiyan.alarm as number,
shiyan.id,shiyan.partname,shiyan.spec,shiyan.danwei,shiyan.remark from shiyan,
Storage where shiyan.id not in(select id from Storage ) group by shiyan.id,
shiyan.partname,shiyan.spec,shiyan.danwei,shiyan.remark,shiyan.alarm,Storage.xycount

[leeboyan]

晕，帮顶吧！

[leeboyan]

存入临时表，再查可以么
select ... into #t from table where...
select ... from #t where ...
drop table #t

[yxl2000]

to leeboyan(宝宝)
我不想用临时表
还有其他办法吗?

[zjcxc]

select Storage.xycount,sum(shiyan.number)+shiyan.alarm-Storage.xycount as number,
shiyan.id,shiyan.partname,shiyan.spec,shiyan.danwei,shiyan.remark
from (SELECT dbo.ZJpart.id, dbo.ZJpart.partname, dbo.ZJpart.spec,
dbo.ZJpart.number * dbo.XPlan.JhCount AS number, dbo.ZJpart.danwei,
dbo.ZJpart.alarm, dbo.ZJpart.remark, dbo.ZJpart.ZJid
FROM dbo.ZJpart INNER JOIN
dbo.XPlan ON dbo.ZJpart.ZJid = dbo.XPlan.JhCode
WHERE (dbo.ZJpart.id NOT IN
(SELECT XQid
FROM XQKind)) AND (dbo.ZJpart.id NOT IN
(SELECT DNBid
FROM DNBKind)and(dbo.XPlan.GQsign = 'F'))
union all
SELECT dbo.DNBpart.id, dbo.DNBpart.partname, dbo.DNBpart.spec,
dbo.DNBpart.number * dbo.ZJpart.number * dbo.XPlan.JhCount AS number,
dbo.DNBpart.danwei, dbo.DNBpart.alarm, dbo.DNBpart.remark, dbo.ZJpart.ZJid
FROM dbo.XPlan INNER JOIN
dbo.ZJpart ON dbo.XPlan.JhCode = dbo.ZJpart.ZJid INNER JOIN
dbo.DNBpart ON dbo.ZJpart.id = dbo.DNBpart.DNBid
WHERE (dbo.XPlan.GQsign = 'F'))shiyan,
Storage where Storage.id=shiyan.id group by shiyan.id,shiyan.partname,shiyan.spec,
shiyan.danwei,shiyan.remark,shiyan.alarm,Storage.xycount
union all
select Storage.xycount,sum(shiyan.number)+shiyan.alarm as number,
shiyan.id,shiyan.partname,shiyan.spec,shiyan.danwei,shiyan.remark
from (SELECT dbo.ZJpart.id, dbo.ZJpart.partname, dbo.ZJpart.spec,
dbo.ZJpart.number * dbo.XPlan.JhCount AS number, dbo.ZJpart.danwei,
dbo.ZJpart.alarm, dbo.ZJpart.remark, dbo.ZJpart.ZJid
FROM dbo.ZJpart INNER JOIN
dbo.XPlan ON dbo.ZJpart.ZJid = dbo.XPlan.JhCode
WHERE (dbo.ZJpart.id NOT IN
(SELECT XQid
FROM XQKind)) AND (dbo.ZJpart.id NOT IN
(SELECT DNBid
FROM DNBKind)and(dbo.XPlan.GQsign = 'F'))
union all
SELECT dbo.DNBpart.id, dbo.DNBpart.partname, dbo.DNBpart.spec,
dbo.DNBpart.number * dbo.ZJpart.number * dbo.XPlan.JhCount AS number,
dbo.DNBpart.danwei, dbo.DNBpart.alarm, dbo.DNBpart.remark, dbo.ZJpart.ZJid
FROM dbo.XPlan INNER JOIN
dbo.ZJpart ON dbo.XPlan.JhCode = dbo.ZJpart.ZJid INNER JOIN
dbo.DNBpart ON dbo.ZJpart.id = dbo.DNBpart.DNBid
WHERE (dbo.XPlan.GQsign = 'F'))shiyan,
Storage where shiyan.id not in(select id from Storage ) group by shiyan.id,
shiyan.partname,shiyan.spec,shiyan.danwei,shiyan.remark,shiyan.alarm,Storage.xycount