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

Question

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

Answer 1

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

Answer 2

select Storage.xycount,sum(shiyan.number)+shiyan.alarm-Storage.xycount as number,
shiyan.id,shiyan.partname,shiyan.spec,shiyan.danwei,shiyan.re 
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.re, 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.re, 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.re,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.re 
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.re, 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.re, 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.re,shiyan.alarm,Storage.xycount