一种SQL统计的不同写法

drop table T_order

Create Table T_order
(
OrderId nvarchar(50) ,      --订单ID
ProductId nvarchar(50),     --产品ID
Price float,                --产品价格
OrderTime datetime,         --订单时间
AuthFlag char(1)            --是否已处理 0未处理 1已处理
)

Insert Into T_order
Select 's000001','P00001',10.00,getdate(),'1' union all
Select 's000002','P00001',10.00,getdate(),'1' union all
Select 's000003','P00001',10.00,getdate(),'0' union all
Select 's000004','P00002',20.00,getdate(),'1' union all
Select 's000005','P00002',20.00,getdate(),'0' union all
Select 's000006','P00002',20.00,getdate(),'1' union all
Select 's000007','P00001',10.00,getdate(),'0' union all
Select 's000008','P00003',14.00,getdate(),'1' union all
Select 's000009','P00003',14.00,getdate(),'0'

 

--要求按ProductID统计，按ProductID排序
--统计列为：产品ID、订单数量、已处理的订单总数量、已处理订单的总金额
--结果

P00001 4 2 20.0
P00002 3 2 40.0
P00003 2 1 14.0

写法一:
SELECT     ProductId,
                          (SELECT     COUNT(0)
                            FROM          T_order b
                            WHERE      b.ProductId = a.ProductId) AS Expr1,
                          (SELECT     COUNT(0)
                            FROM          T_order c
                            WHERE      c.ProductId = a.ProductId AND c.AuthFlag = 1) AS Expr2,
                          (SELECT     SUM(Price)
                            FROM          T_order d
                            WHERE      d.ProductId = a.ProductId AND d.AuthFlag = 1) AS Expr3
FROM         T_order a
GROUP BY ProductId

 

写法二:
select Productid as 'ProductId',
count(*) as 'Expr1',
sum(case authflag when 1 then 1 else 0 end) as 'Expr2',
sum(case authflag when 1 then Price else 0 end) as 'Expr3'
from T_order
group by Productid
order by Productid

 

写法三:
SELECT  distinct   ProductId,
                          (SELECT     COUNT(0)
                            FROM          T_order b
                            WHERE      b.ProductId = a.ProductId) AS Expr1,
                          (SELECT     COUNT(0)
                            FROM          T_order c
                            WHERE      c.ProductId = a.ProductId AND c.AuthFlag = 1) AS Expr2,
                          (SELECT     SUM(Price)
                            FROM          T_order d
                            WHERE      d.ProductId = a.ProductId AND d.AuthFlag = 1) AS Expr3
FROM         T_order a