大家都来挑战这个SQL难题

3tzjq 2014-05-26 11:36:42
调试数据如下(for SQL2005+)：


--预订表(编号，产品型号，预订日期，预订数量)

CREATE TABLE Forecast(ID varchar(25), PartNumber varchar(25), BOM varchar(25), BeginDate smalldatetime, QtyCurrent decimal(14,4));

INSERT INTO Forecast VALUES('FO01','Part01','BOM01','2014-7-1',0);

INSERT INTO Forecast VALUES('FO01','Part01','BOM01','2014-8-1',1000);

INSERT INTO Forecast VALUES('FO01','Part01','BOM01','2014-9-1',2000);

INSERT INTO Forecast VALUES('FO01','Part01','BOM01','2014-10-1',3500);

INSERT INTO Forecast VALUES('FO01','Part02','BOM02','2014-7-1',1200);

INSERT INTO Forecast VALUES('FO01','Part02','BOM02','2014-8-1',1800);

INSERT INTO Forecast VALUES('FO01','Part02','BOM02','2014-9-1',2500);

INSERT INTO Forecast VALUES('FO01','Part02','BOM02','2014-10-1',0);

GO



--预订区间数据表(编号，预订日期，显示标题)

CREATE TABLE ForecastOrderArea(ID varchar(25), BeginDate smalldatetime, Caption varchar(20));

INSERT INTO ForecastOrderArea VALUES('FO01','2014-7-1','JAL');

INSERT INTO ForecastOrderArea VALUES('FO01','2014-8-1','AUG');

INSERT INTO ForecastOrderArea VALUES('FO01','2014-9-1','SEP');

INSERT INTO ForecastOrderArea VALUES('FO01','2014-10-1','OCT');

INSERT INTO ForecastOrderArea VALUES('FO02','2014-7-1','JAL');

INSERT INTO ForecastOrderArea VALUES('FO02','2014-8-1','AUG');

INSERT INTO ForecastOrderArea VALUES('FO02','2014-9-1','SEP');

INSERT INTO ForecastOrderArea VALUES('FO02','2014-10-1','OCT');

GO



--预订BOM(编号，产品型号)

CREATE TABLE PartBOMMaster(ID varchar(25), PartNumber varchar(25));

INSERT INTO PartBOMMaster VALUES('BOM01','Part01');

INSERT INTO PartBOMMaster VALUES('BOM02','Part02');



GO

--预订BOM明细(编号，元件编码,数量)

CREATE TABLE PartBOMDetail(ID varchar(25), ComponentId varchar(25), Qty decimal(6,3));

INSERT INTO PartBOMDetail VALUES('BOM01','CAP01',3);

INSERT INTO PartBOMDetail VALUES('BOM01','CAP02',1);

INSERT INTO PartBOMDetail VALUES('BOM01','RES01',2);

INSERT INTO PartBOMDetail VALUES('BOM01','RES02',1);

INSERT INTO PartBOMDetail VALUES('BOM02','DIO01',3);

INSERT INTO PartBOMDetail VALUES('BOM02','CAP02',1);

INSERT INTO PartBOMDetail VALUES('BOM02','RES02',1);

INSERT INTO PartBOMDetail VALUES('BOM02','CAP01',1);



GO


需要统计出以下主从数据表：
--1. Master
ID PartNumber BOM JAL AUG SEP OCT
FO01 Part01 BOM01 0 3000 6000 3500
FO02 Part02 BOM02 1200 1800 2500 0

--2. Detail(单个)
ID Component JAL AUG SEP OCT
FO01 CAP01 0 3000 6000 10500
FO01 CAP02 0 1000 2000 3500
FO01 RES01 0 2400 4000 7000
FO01 RES02 0 1000 2000 3500
FO02 DIO01 3600 5400 7500 0
FO02 CAP01 1200 1800 2500 0
FO02 CAP02 1200 1800 2500 0
FO02 RES02 1200 1800 2500 0

--3. Detail(多个统计: FO01, FO02)
S_Id Component JAL AUG SEP OCT
1 CAP01 1200 4800 8500 10500
2 CAP02 1200 2800 4500 3500
3 DIO01 3600 5400 7500 0
4 RES01 0 2400 4000 7000
5 RES02 1200 2800 4500 3500

难点：
需按ForecastOrderArea的Caption 将Forecast表中的QtyCurrent横向显示出来；
BOM数据也需要向每月的需求数量横向显示出来(PartBOMDetail.Qty * Forecast.QtyCurrent)
...全文
382 23 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
23 条回复
切换为时间正序
请发表友善的回复…
发表回复
3tzjq 2014-06-03
  • 打赏
  • 举报
 回复
搞定了！感谢 AlphaQCode 提供的思路，学到了PIVOT这个透视函数，正需要啊！
最后分张一下此贴的成果：
sytwz 2014-05-28
  • 打赏
  • 举报
 回复
declare @sql varchar(8000) set @sql='select A.ID,A.PartNumber,A.BOM ' select @sql=@sql+',max(case when B.Caption ='''+Caption+''' then A.QtyCurrent else 0 end) ['+Caption+']' from (select distinct Caption from ForecastOrderArea) a set @sql=@sql+' from dbo.Forecast A left join dbo.ForecastOrderArea B on A.BeginDate=B.BeginDate left join PartBOMMaster C on A.BOM=C.ID group by A.ID,A.PartNumber,A.BOM' exec (@sql)
3tzjq 2014-05-28
  • 打赏
  • 举报
 回复
引用 13 楼 AlphaQCode 的回复:
你自己添加所有月份即可,另外你自己列的结果和你的数据有点不同 
--master
select a.ID,a.PartNumber,a.BOM,b.Caption,sum(QtyCurrent) as QtyCurrent
into temp_fo_master
from Forecast a
	inner join ForecastOrderArea b on a.ID = b.ID and a.BeginDate = b.BeginDate
group by a.ID,a.PartNumber,a.BOM,b.Caption

select ID,PartNumber,BOM,[JAL],[AUG],[SEP],[OCT]
from temp_fo_master
pivot 
(
sum(QtyCurrent) for Caption in ([JAL],[AUG],[SEP],[OCT])
) as pvt

--detail
select a.BOM,b.ComponentId,a.Caption,a.QtyCurrent * b.Qty as Qty
into temp_fo_detail
from temp_fo_master a
	inner join PartBOMDetail b on a.BOM = b.ID


--detail_dua
select BOM,ComponentId,[JAL],[AUG],[SEP],[OCT]
from temp_fo_detail
pivot 
(
sum(Qty) for Caption in ([JAL],[AUG],[SEP],[OCT])
) as pvt

select b.ComponentId,a.Caption,a.QtyCurrent * b.Qty as Qty
into temp_fo_detail_dua
from temp_fo_master a
	inner join PartBOMDetail b on a.BOM = b.ID

select row_number() over(order by ComponentId) as S_ID,ComponentId,[JAL],[AUG],[SEP],[OCT]
from temp_fo_detail_dua
pivot 
(
sum(Qty) for Caption in ([JAL],[AUG],[SEP],[OCT])
) as pvt
我改成了动态的： 

DECLARE @sql varchar(5000);
DECLARE @area varchar(100);
SELECT @sql = '
WITH t AS
(
SELECT
a.[ID],
a.[PartNumber],
a.[BOM],
b.[Caption],
SUM(ISNULL(a.[QtyCurrent],0)) as QtyCurrent
FROM Forecast a
	JOIN ForecastOrderArea b ON a.[ID] = b.[ID] AND a.[BeginDate] = b.[BeginDate]
GROUP BY 
a.[ID],
a.[PartNumber],
a.[BOM],
b.[Caption]
)
SELECT
[ID],
[PartNumber],
[BOM],';
SELECT @area = '';
SELECT @area = @area + '['+ [Caption] + '],' FROM dbo.ForecastOrderArea;
SELECT @area = LEFT(@area,LEN(@area) - 1);
print @area;
SELECT @sql = @sql + @area + ' FROM t PIVOT(SUM([QtyCurrent]) FOR [Caption] IN(' + @area + ')) as pvt';

print @sql;
EXEC (@sql);
唐诗三百首 2014-05-27
  • 打赏
  • 举报
 回复
请问master表中,JAL=0,AUG=3000,SEP=6000,OCT=3500是如何计算出来的? ID PartNumber BOM JAL AUG SEP OCT FO01 Part01 BOM01 0 3000 6000 3500
卖水果的net 2014-05-27
  • 打赏
  • 举报
 回复
先顶一下，现在有点忙，等中午了，回来看看。
3tzjq 2014-05-27
  • 打赏
  • 举报
 回复
顶，有点眉目了。高手快来吧！
3tzjq 2014-05-27
  • 打赏
  • 举报
 回复
引用 19 楼 shinger126 的回复:
月份只有12个月，没必要写动态的吧，动态的效率实在不行。按日期来动态行转列，你确定有这样的需求？
不只是月份，还可能按周/月/季度运算，所以必须动态。
shinger126 2014-05-27
  • 打赏
  • 举报
 回复
月份只有12个月，没必要写动态的吧，动态的效率实在不行。按日期来动态行转列，你确定有这样的需求？
3tzjq 2014-05-27
  • 打赏
  • 举报
 回复
引用 13 楼 AlphaQCode 的回复:
你自己添加所有月份即可,另外你自己列的结果和你的数据有点不同 
--master
select a.ID,a.PartNumber,a.BOM,b.Caption,sum(QtyCurrent) as QtyCurrent
into temp_fo_master
from Forecast a
	inner join ForecastOrderArea b on a.ID = b.ID and a.BeginDate = b.BeginDate
group by a.ID,a.PartNumber,a.BOM,b.Caption

select ID,PartNumber,BOM,[JAL],[AUG],[SEP],[OCT]
from temp_fo_master
pivot 
(
sum(QtyCurrent) for Caption in ([JAL],[AUG],[SEP],[OCT])
) as pvt

--detail
select a.BOM,b.ComponentId,a.Caption,a.QtyCurrent * b.Qty as Qty
into temp_fo_detail
from temp_fo_master a
	inner join PartBOMDetail b on a.BOM = b.ID


--detail_dua
select BOM,ComponentId,[JAL],[AUG],[SEP],[OCT]
from temp_fo_detail
pivot 
(
sum(Qty) for Caption in ([JAL],[AUG],[SEP],[OCT])
) as pvt

select b.ComponentId,a.Caption,a.QtyCurrent * b.Qty as Qty
into temp_fo_detail_dua
from temp_fo_master a
	inner join PartBOMDetail b on a.BOM = b.ID

select row_number() over(order by ComponentId) as S_ID,ComponentId,[JAL],[AUG],[SEP],[OCT]
from temp_fo_detail_dua
pivot 
(
sum(Qty) for Caption in ([JAL],[AUG],[SEP],[OCT])
) as pvt
谢谢！但你这个用的是静态的，我要用动态的. 意思是字段 BeginDate, Caption可能是 2014-11-1, NOV, 2014-12-1, DEC, 2015-1-1, JAN...
3tzjq 2014-05-27
  • 打赏
  • 举报
 回复
引用 15 楼 ap0405140 的回复:
[quote=引用 7 楼 3tzjq 的回复:] 感谢指正！手写的测试数据，应该为：
引用 12 楼 3tzjq 的回复:
不好意思！手误。
目测测试数据还是有所谓"手误"喔, 本想帮你写,但这测试数据和结果实在无从下手啊. [/quote] 大哥，因为我没有用SQL语句运算出来，只好手写了，错误难免，你理解意思就行了，不要在乎这个结果数据。
唐诗三百首 2014-05-27
  • 打赏
  • 举报
 回复
做技术的思维要严谨些才好喔.
唐诗三百首 2014-05-27
  • 打赏
  • 举报
 回复
引用 7 楼 3tzjq 的回复:
感谢指正！手写的测试数据，应该为：
引用 12 楼 3tzjq 的回复:
不好意思！手误。
目测测试数据还是有所谓"手误"喔, 本想帮你写,但这测试数据和结果实在无从下手啊.
AlphaQCode 2014-05-27
  • 打赏
  • 举报
 回复
哦..我注释注错地方 --detail_dua 应该在下一个语句处
AlphaQCode 2014-05-27
  • 打赏
  • 举报
 回复
你自己添加所有月份即可,另外你自己列的结果和你的数据有点不同 
--master
select a.ID,a.PartNumber,a.BOM,b.Caption,sum(QtyCurrent) as QtyCurrent
into temp_fo_master
from Forecast a
	inner join ForecastOrderArea b on a.ID = b.ID and a.BeginDate = b.BeginDate
group by a.ID,a.PartNumber,a.BOM,b.Caption

select ID,PartNumber,BOM,[JAL],[AUG],[SEP],[OCT]
from temp_fo_master
pivot 
(
sum(QtyCurrent) for Caption in ([JAL],[AUG],[SEP],[OCT])
) as pvt

--detail
select a.BOM,b.ComponentId,a.Caption,a.QtyCurrent * b.Qty as Qty
into temp_fo_detail
from temp_fo_master a
	inner join PartBOMDetail b on a.BOM = b.ID


--detail_dua
select BOM,ComponentId,[JAL],[AUG],[SEP],[OCT]
from temp_fo_detail
pivot 
(
sum(Qty) for Caption in ([JAL],[AUG],[SEP],[OCT])
) as pvt

select b.ComponentId,a.Caption,a.QtyCurrent * b.Qty as Qty
into temp_fo_detail_dua
from temp_fo_master a
	inner join PartBOMDetail b on a.BOM = b.ID

select row_number() over(order by ComponentId) as S_ID,ComponentId,[JAL],[AUG],[SEP],[OCT]
from temp_fo_detail_dua
pivot 
(
sum(Qty) for Caption in ([JAL],[AUG],[SEP],[OCT])
) as pvt
3tzjq 2014-05-27
  • 打赏
  • 举报
 回复
引用 10 楼 ap0405140 的回复:
请问master表中,JAL=0,AUG=3000,SEP=6000,OCT=3500是如何计算出来的?

ID PartNumber BOM JAL AUG SEP OCT
FO01 Part01 BOM01 0 3000 6000 3500


不好意思！手误。
3tzjq 2014-05-27
  • 打赏
  • 举报
 回复
引用 9 楼 wmxcn2000 的回复:
先顶一下，现在有点忙，等中午了，回来看看。
谢谢了！
3tzjq 2014-05-26
  • 打赏
  • 举报
 回复
引用 6 楼 ap0405140 的回复:
请问Forecast表中为何木有ID=FO02的记录? 不明白如何得出master表的喔..
感谢指正！手写的测试数据，应该为： 
CREATE TABLE Forecast(ID varchar(25), PartNumber varchar(25), BOM varchar(25), BeginDate smalldatetime, QtyCurrent decimal(14,4));
INSERT INTO Forecast VALUES('FO01','Part01','BOM01','2014-7-1',0);
INSERT INTO Forecast VALUES('FO01','Part01','BOM01','2014-8-1',1000);
INSERT INTO Forecast VALUES('FO01','Part01','BOM01','2014-9-1',2000);
INSERT INTO Forecast VALUES('FO01','Part01','BOM01','2014-10-1',3500);
INSERT INTO Forecast VALUES('FO02','Part02','BOM02','2014-7-1',1200);
INSERT INTO Forecast VALUES('FO02','Part02','BOM02','2014-8-1',1800);
INSERT INTO Forecast VALUES('FO02','Part02','BOM02','2014-9-1',2500);
INSERT INTO Forecast VALUES('FO02','Part02','BOM02','2014-10-1',0);
GO
唐诗三百首 2014-05-26
  • 打赏
  • 举报
 回复
请问Forecast表中为何木有ID=FO02的记录? 不明白如何得出master表的喔..
3tzjq 2014-05-26
  • 打赏
  • 举报
 回复
引用 3 楼 HEROWANG 的回复:
第三个就是对第二个进行了个分组统计，都比较简单，建议自己写写。写出来有什么问题，大家再帮你看
大神们帮忙指点指点，谢谢了！
3tzjq 2014-05-26
  • 打赏
  • 举报
 回复
我试了很久，还是弄不出来，网上的例子都是单表查询或静态查询，这个是跨表动态，没头绪了
加载更多回复（3）
面试经验+基础总结
总的来说，这份压缩包文件为准备IT面试提供了一个全面的学习框架，从个人技能的展现到技术知识的巩固，再到面试策略的探讨，都覆盖到了。通过深入研究和实践这些资料，可以显著提高面试成功的可能性。
P8级SQL难题，答对送精美大礼包！！！
今天姜老师准备了一道非常具有挑战的SQL题，难度堪称P8等级，喜欢挑战的同学可以来试试。 第一个答对的同学，可得到如下大礼包： IMG社区精美T恤； 无线移动充电宝； IMG高端群M悦会会员资格一年； ...
探秘数据查询的未来：NL2SQL挑战赛桂冠之作
探秘数据查询的未来：NL2SQL挑战赛桂冠之作 去发现同类优质开源项目:https://gitcode.com/ 在大数据与人工智能交融的今天，如何让非技术人员轻松地利用自然语言进行数据库查询，已成为了一项亟待解决的技术难题。在...
传说中 6 个月都未必能全解开的 3 道 SQL 题，来挑战下？
这 3 道巨难的题目，来自 itpub 的 SQL 数据库编程大赛。说起 itpub 就不得不说它与 Oracle 的渊源，多少大师都在这里诞生。想成为 SQL 大师，有个最快的方法，就是刷题。如果能刷遍这里的题，Oracle 工作，十拿九稳...
简单和大家聊聊nlp2sql 以及 SQL数据分析存在的问题，以及解决方案。
这个技术主要是改变交互，从人讲自然语言需求写SQL改成大模型来理解自然语言，写SQL。2. SQL内置大模型函数支持实现对传统数据分析能力的突破，这个去年应该是我们和 Databricks 搞的比较早，我们可能略早（纯瞎猜）...
应用实例

27,582

社区成员

68,544

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章