34,594
社区成员
发帖
与我相关
我的任务
分享
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([订单] nvarchar(23),[供应商] nvarchar(21),[数量] int)
Insert #T
select N'P01',N'A',100 union all
select N'P02',N'A',200 union all
select N'P03',N'B',20 union all
select N'P04',N'A',40
GO
if not object_id(N'Tempdb..#T2') is null
drop table #T2
Go
Create table #T2([订单] nvarchar(24),[收退货] nvarchar(22),[数量] int)
Insert #T2
select N'P01',N'收货',30 union all
select N'P01',N'收货',50 union all
select N'P01',N'退货',20 union all
select N'P02',N'收货',90
Go
--测试数据结束
SELECT [供应商] ,
COUNT(DISTINCT 订单) AS 订单数量 ,
SUM(数量) AS 数量汇总,
SUM(收货次数) 收货次数,
SUM(收货数量) 收货数量,
SUM(退货次数) 退货次数,
SUM(退货数量) 退货数量
FROM ( SELECT #T.* ,
ISNULL(t.收货次数, 0) AS 收货次数 ,
ISNULL(t.收货数量, 0) AS 收货数量 ,
ISNULL(t.退货次数, 0) AS 退货次数 ,
ISNULL(t.退货数量, 0) AS 退货数量
FROM #T
LEFT JOIN ( SELECT 订单 ,
SUM(CASE WHEN 收退货 = '收货' THEN 1
ELSE 0
END) AS 收货次数 ,
SUM(CASE WHEN 收退货 = '收货' THEN 数量
ELSE 0
END) AS 收货数量 ,
SUM(CASE WHEN 收退货 = '退货' THEN 1
ELSE 0
END) AS 退货次数 ,
SUM(CASE WHEN 收退货 = '退货' THEN 数量
ELSE 0
END) AS 退货数量
FROM #T2
GROUP BY 订单
) t ON t.订单 = #T.订单
) t1
GROUP BY [供应商]
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([订单] nvarchar(23),[供应商] nvarchar(21),[数量] int)
Insert #T
select N'P01',N'A',100 union all
select N'P02',N'A',200 union all
select N'P03',N'B',20 union all
select N'P04',N'A',40
Go
--测试数据结束
SELECT [供应商] ,
COUNT(DISTINCT 订单) AS 订单数量 ,
SUM(数量) AS 数量汇总
FROM #T
GROUP BY [供应商]