34,576
社区成员
SQL2008 数据表如下:
生产单号 类型 下单数量 成品入库数量 成品发货数量
001 新单 100 80 80
001H1 回修 15 15 15
001H2 回修 5 5 5
002 新单 100 90 90
002H1 回修 10 10 10
说明:生产单号后缀有H的表示是回修改,H后面的数字表示回修次数。
求:统计类型为新单的生产单号的下单数量、成品入库数量和成品发货数量,格式如下:
生产单号 类型 下单数量 成品入库数量 成品发货数量
001 新单 100 100 100
002 新单 100 100 100
上边按个有点问题
用这个
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([生产单号] nvarchar(25),[类型] nvarchar(22),[下单数量] int,[成品入库数量] int,[成品发货数量] int)
Insert #T
select N'001',N'新单',100,80,80 union all
select N'001H1',N'回修',15,15,15 union all
select N'001H2',N'回修',5,5,5 union all
select N'002',N'新单',100,90,90 union all
select N'002H1',N'回修',10,10,10
Go
--测试数据结束
SELECT SUBSTRING(生产单号, 0, 4) AS 生产单号,
'新单' AS 类型,
SUM(下单数量) 下单数量,
SUM(成品入库数量) 成品入库数量,
SUM(成品发货数量) 成品发货数量
FROM #T t WHERE EXISTS(SELECT * FROM #T WHERE #T.类型='新单' AND 生产单号 LIKE '%'+SUBSTRING(t.生产单号, 0, 4)+'%')
GROUP BY SUBSTRING(生产单号, 0, 4);
上边那个有点问题
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([生产单号] nvarchar(25),[类型] nvarchar(22),[下单数量] int,[成品入库数量] int,[成品发货数量] int)
Insert #T
select N'001',N'新单',100,80,80 union all
select N'001H1',N'回修',15,15,15 union all
select N'001H2',N'回修',5,5,5 union all
select N'002',N'新单',100,90,90 union all
select N'002H1',N'回修',10,10,10
Go
--测试数据结束
SELECT SUBSTRING(生产单号, 0, 4) AS 生产单号,
'新单' AS 类型,
SUM(下单数量) 下单数量,
SUM(成品入库数量) 成品入库数量,
SUM(成品发货数量) 成品发货数量
FROM #T t WHERE EXISTS(SELECT * FROM #T WHERE #T.类型='新单' AND 生产单号 LIKE '%'+SUBSTRING(t.生产单号, 0, 4)+'%')
GROUP BY SUBSTRING(生产单号, 0, 4);
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([生产单号] nvarchar(25),[类型] nvarchar(22),[下单数量] int,[成品入库数量] int,[成品发货数量] int)
Insert #T
select N'001',N'新单',100,80,80 union all
select N'001H1',N'回修',15,15,15 union all
select N'001H2',N'回修',5,5,5 union all
select N'002',N'新单',100,90,90 union all
select N'002H1',N'回修',10,10,10
Go
--测试数据结束
SELECT SUBSTRING(生产单号, 0, 4) AS 生产单号,
'新单' AS 类型,
SUM(下单数量) 下单数量,
SUM(成品入库数量) 成品入库数量,
SUM(成品发货数量) 成品发货数量
FROM #T t WHERE EXISTS(SELECT * FROM #T WHERE 类型='新单' AND 生产单号 LIKE '%'+t.生产单号+'%')
GROUP BY SUBSTRING(生产单号, 0, 4);
使用 case 表达式就可以了,然后以单号前三个字符为分组
select left(单号, 3) 单号, '新单' 类型,
sum(case when charindex('H', 单号) = 0 then 下单数量 else 0 end) 下单数量,
sum(成品入库数量) 成品入库数量,
sum(成品发货数量) 成品发货数量
from table_name
group by left(单号, 3)
with A as(
select '001' 生产单号,'新单' 类型,100 下单数量,80 成品入库数量,80 成品发货数量 union all
select '001H1' 生产单号,'回修' 类型,15 下单数量,15 成品入库数量,15 成品发货数量 union all
select '001H2' 生产单号,'回修' 类型,5 下单数量,5 成品入库数量,5 成品发货数量 union all
select '002' 生产单号,'新单' 类型,100 下单数量,90 成品入库数量,90 成品发货数量 union all
select '002H1' 生产单号,'回修' 类型,10 下单数量,10 成品入库数量,10 成品发货数量
)
select
*
from
(
select case when charIndex('H',生产单号)>0 then left(生产单号,charIndex('H',生产单号)) else 生产单号 end id,
生产单号,
类型,
下单数量,
sum(成品入库数量) over(partition by case when charIndex('H',生产单号)>0 then left(生产单号,charIndex('H',生产单号)-1) else 生产单号 end) 成品入库数量,
sum(成品发货数量) over(partition by case when charIndex('H',生产单号)>0 then left(生产单号,charIndex('H',生产单号)-1) else 生产单号 end) 成品发货数量
from A
) A where 类型='新单'