dhl：-Sql-表子查询

--表1：
SELECT  count(*) as Moneybag1,PingCoId  FROM
 OpenDoor.UserKey  where
 keyType=1  and issystem=1 
group by pingcoid order by Moneybag1 desc
--表2：
SELECT  count(*) as Moneybag2,PingCoId  FROM
 OpenDoor.UserKey  where
 keyType=2  and issystem=1 
group by pingcoid order by Moneybag2 desc

---表1*20 + 表2*2 后： 注意：字表中不能有order by ：
--，ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。

select pingcoid,sum(moneybag1) as moneybag
from (
select b.moneybag1*20 as moneybag1,pingcoid
from (
SELECT  count(*) as Moneybag1,PingCoId  FROM
 OpenDoor.UserKey  where
 keyType=1  and issystem=1 
group by pingcoid
) as b
union
select c.Moneybag2*3 as Moneybag2,pingcoid
 from (
SELECT  count(*) as Moneybag2,PingCoId  FROM
 OpenDoor.UserKey  where
 keyType=2  and issystem=1 
group by pingcoid) as c) as a
group by a.pingcoid  order by moneybag desc

 

转载于:https://www.cnblogs.com/dudu837/archive/2009/12/25/1632192.html