一种SQL统计的不同写法

最新推荐文章于 2024-03-09 09:46:12 发布

no_mIss

最新推荐文章于 2024-03-09 09:46:12 发布

阅读量1.5k

点赞数

分类专栏： Ms sql server 文章标签： sql 产品 table insert float c

本文链接：https://blog.csdn.net/no_mIss/article/details/1268575

版权

Ms sql server 专栏收录该内容

31 篇文章 0 订阅

订阅专栏

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