22,209
社区成员
发帖
与我相关
我的任务
分享
...全文
请发表友善的回复…
发表回复
深度格式 2017-09-21
- 打赏
- 举报
行字段拼接可以用 for xml path（）函数，很好用的
二月十六 2017-09-19
- 打赏
- 举报
大概这样：
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([机器1日期] Date,[机器1班次] nvarchar(22),[机器1产量] int,[机器2日期] Date,[机器2班次] nvarchar(22),[机器2产量] int)
Insert #T
select '2017-9-17',N'白班',100,null,null,null union all
select '2017-9-18',N'夜班',200,'2017-9-17',N'白班',50
Go
--测试数据结束
SELECT a.[机器1日期] ,
a.[机器1班次] ,
a.[机器1产量] ,
b.[机器2日期] ,
b.[机器2班次] ,
b.[机器2产量]
FROM #T a
FULL JOIN #T b ON b.机器2日期 = a.机器1日期
WHERE a.机器1日期 IS NOT NULL
OR b.机器2日期 IS NOT NULL
听雨停了 2017-09-19
- 打赏
- 举报
你这原始数据在数据库就是以图1的形式存储的？
二月十六 2017-09-19
- 打赏
- 举报
机器1234在数据库中是怎么存储的？
acen_chen 2017-09-19
- 打赏
- 举报
表结构和数据整一下啊
RINK_1 2017-09-19
- 打赏
- 举报
select 机器1日期,
机器1班次,
机器1产量,
机器2日期,
机器2班次,
机器2产量,
机器3日期,
机器3班次,
机器3产量,
机器4日期,
机器4班次,
机器4产量
from
(select 机器1日期,
机器1班次,
机器1产量,
ROW_NUMBER() over (partition by 机器1日期,机器1班次 order by getdate()) as rn_1
from #T
where ISNULL(机器1日期,'')<>'') as A
full join
(select 机器2日期,
机器2班次,
机器2产量,
ROW_NUMBER() over (partition by 机器2日期,机器2班次 order by getdate()) as rn_2
from #T
where ISNULL(机器2日期,'')<>'') as B ON A.机器1日期=B.机器2日期 AND A.机器1班次=机器2班次 AND A.rn_1=B.rn_2
full join
(select 机器3日期,
机器3班次,
机器3产量,
ROW_NUMBER() over (partition by 机器3日期,机器3班次 order by getdate()) as rn_3
from #T
where ISNULL(机器3日期,'')<>'') as C ON ISNULL(A.机器1日期,B.机器2日期)=C.机器3日期 AND ISNULL(A.机器1班次,B.机器2班次)=C.机器3班次 AND ISNULL(A.rn_1,B.rn_2)=C.rn_3
full join
(select 机器4日期,
机器4班次,
机器4产量,
ROW_NUMBER() over (partition by 机器4日期,机器4班次 order by getdate()) as rn_4
from #T
where ISNULL(机器4日期,'')<>'') as D ON COALESCE(A.机器1日期,B.机器2日期,C.机器3日期)=D.机器4日期 AND COALESCE(机器1班次,B.机器2班次,机器3班次)=机器4班次 AND COALESCE(A.rn_1,B.rn_2,C.rn_3)=D.rn_4
order by COALESCE(A.机器1日期,B.机器2日期,C.机器3日期,D.机器4日期),
case when COALESCE(机器1班次,B.机器2班次,C.机器3班次,D.机器4班次)='白班' THEN 0 ELSE 1 END,
COALESCE(机器1班次,B.机器2班次,C.机器3班次,D.机器4班次),
COALESCE(A.rn_1,B.rn_2,C.rn_3,D.rn_4)
leo_lesley 2017-09-19
- 打赏
- 举报
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([机器1日期] Datetime,[机器1班次] nvarchar(22),[机器1产量] int,[机器2日期] Datetime,[机器2班次] nvarchar(22),[机器2产量] int,
[机器3日期] Datetime,[机器3班次] nvarchar(22),[机器3产量] int,[机器4日期] Datetime,[机器4班次] nvarchar(22),[机器4产量] int )
Insert #T
select '2017-9-17',N'白班',100,null,null,null,null,null,null,null,null,null union all
select '2017-9-17',N'白班',120,null,null,null,null,null,null,null,null,null union all
select '2017-9-17',N'夜班',200,null,null,null,null,null,null,null,null,null union all
select '2017-9-18',N'白班',150,null,null,null,null,null,null,null,null,null union all
select '2017-9-18',N'夜班',190,null,null,null,null,null,null,null,null,null union all
select '2017-9-19',N'夜班',420,null,null,null,null,null,null,null,null,null union all
select null,null,null,'2017-9-17',N'白班',210,null,null,null,null,null,null union all
select null,null,null,null,null,null,'2017-9-17',N'白班',50,null,null,null union all
select null,null,null,null,null,null,'2017-9-17',N'白班',60,null,null,null union all
select null,null,null,null,null,null,'2017-9-17',N'白班',80,null,null,null union all
select null,null,null,null,null,null,null,null,null,'2017-9-18',N'夜班',210 union all
select null,null,null,null,null,null,null,null,null,'2017-9-19',N'夜班',110 union all
select null,null,null,null,null,null,null,null,null,'2017-9-19',N'白班',160
;with cet as(
select 机器1日期,
机器1班次,
机器1产量,
ROW_NUMBER()over(partition by 机器1日期,机器1班次 order by 机器1日期,机器1班次) id1,
机器2日期,
机器2班次,
机器2产量,
ROW_NUMBER()over(partition by 机器2日期,机器2班次 order by 机器2日期,机器2班次) id2,
机器3日期,
机器3班次,
机器3产量,
ROW_NUMBER()over(partition by 机器3日期,机器3班次 order by 机器3日期,机器3班次) id3,
机器4日期,
机器4班次,
机器4产量,
ROW_NUMBER()over(partition by 机器4日期,机器4班次 order by 机器4日期,机器4班次) id4
from #t
)
--测试数据结束
SELECT a.机器1日期,
a.机器1班次,
a.机器1产量,
b.机器2日期,
b.机器2班次,
b.机器2产量,
c.机器3日期,
c.机器3班次,
c.机器3产量,
d.机器4日期,
d.机器4班次,
d.机器4产量
FROM cet a
FULL JOIN cet b ON b.机器2日期 = a.机器1日期 and a.机器1班次 = b.机器2班次 and a.id1 = b.id2
FULL JOIN cet c ON c.机器3日期 = a.机器1日期 and a.机器1班次 = c.机器3班次 and a.id1 = c.id3
FULL JOIN cet d ON d.机器4日期 = a.机器1日期 and a.机器1班次 = d.机器4班次 and a.id1 = d.id4
WHERE a.机器1日期 IS NOT NULL
OR b.机器2日期 IS NOT NULL
OR c.机器3日期 IS NOT NULL
OR d.机器4日期 IS NOT NULL
order by case when a.机器1日期 IS null then
case when b.机器2日期 is null then
case when c.机器3日期 IS null then
case when d.机器4日期 IS null then null else convert(char(10),d.机器4日期,112) + d.机器4班次 end
else convert(char(10),c.机器3日期,112) + c.机器3班次 end
else convert(char(10),b.机器2日期,112) + b.机器2班次 end
else convert(char(10),a.机器1日期,112) + a.机器1班次 end
/*
(13 row(s) affected)
机器1日期 机器1班次 机器1产量 机器2日期 机器2班次 机器2产量 机器3日期 机器3班次 机器3产量 机器4日期 机器4班次 机器4产量
----------------------- ---------------------- ----------- ----------------------- ---------------------- ----------- ----------------------- ---------------------- ----------- ----------------------- ---------------------- -----------
2017-09-17 00:00:00.000 夜班 200 NULL NULL NULL NULL NULL NULL NULL NULL NULL
2017-09-17 00:00:00.000 白班 100 2017-09-17 00:00:00.000 白班 210 2017-09-17 00:00:00.000 白班 50 NULL NULL NULL
2017-09-17 00:00:00.000 白班 120 NULL NULL NULL 2017-09-17 00:00:00.000 白班 60 NULL NULL NULL
NULL NULL NULL NULL NULL NULL 2017-09-17 00:00:00.000 白班 80 NULL NULL NULL
2017-09-18 00:00:00.000 夜班 190 NULL NULL NULL NULL NULL NULL 2017-09-18 00:00:00.000 夜班 210
2017-09-18 00:00:00.000 白班 150 NULL NULL NULL NULL NULL NULL NULL NULL NULL
2017-09-19 00:00:00.000 夜班 420 NULL NULL NULL NULL NULL NULL 2017-09-19 00:00:00.000 夜班 110
NULL NULL NULL NULL NULL NULL NULL NULL NULL 2017-09-19 00:00:00.000 白班 160
(8 row(s) affected)
*/
二月十六 2017-09-19
- 打赏
- 举报
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([机器1日期] Datetime,[机器1班次] nvarchar(22),[机器1产量] int,[机器2日期] Datetime,[机器2班次] nvarchar(22),[机器2产量] int,
[机器3日期] Datetime,[机器3班次] nvarchar(22),[机器3产量] int,[机器4日期] Datetime,[机器4班次] nvarchar(22),[机器4产量] int )
Insert #T
select '2017-9-17','白班',100,null,null,null,null,null,null,null,null,null union all
select '2017-9-17','白班',120,null,null,null,null,null,null,null,null,null union all
select '2017-9-17','夜班',200,null,null,null,null,null,null,null,null,null union all
select '2017-9-18','白班',150,null,null,null,null,null,null,null,null,null union all
select '2017-9-18','夜班',190,null,null,null,null,null,null,null,null,null union all
select null,null,null,'2017-9-17','白班',210,null,null,null,null,null,null union all
select null,null,null,null,null,null,'2017-9-17','白班',50,null,null,null union all
select null,null,null,null,null,null,'2017-9-17','白班',60,null,null,null union all
select null,null,null,null,null,null,'2017-9-17','白班',80,null,null,null union all
select null,null,null,null,null,null,null,null,null,'2017-9-18','夜班',210
GO
;WITH a AS (
SELECT [机器1日期] ,
[机器1班次] ,
[机器1产量] ,
ROW_NUMBER() OVER ( PARTITION BY 机器1日期, 机器1班次 ORDER BY GETDATE() ) AS num
FROM #T
),b AS (
SELECT [机器2日期] ,
[机器2班次] ,
[机器2产量] ,
ROW_NUMBER() OVER ( PARTITION BY 机器2日期, 机器2班次 ORDER BY GETDATE() ) AS num
FROM #T
),c AS (
SELECT [机器3日期] ,
[机器3班次] ,
[机器3产量] ,
ROW_NUMBER() OVER ( PARTITION BY 机器3日期, 机器3班次 ORDER BY GETDATE() ) AS num
FROM #T
),d AS (
SELECT [机器4日期] ,
[机器4班次] ,
[机器4产量] ,
ROW_NUMBER() OVER ( PARTITION BY 机器4日期, 机器4班次 ORDER BY GETDATE() ) AS num
FROM #T
)
SELECT a.[机器1日期] ,
a.[机器1班次] ,
a.[机器1产量] ,
b.[机器2日期] ,
b.[机器2班次] ,
b.[机器2产量] ,
c.[机器3日期] ,
c.[机器3班次] ,
c.[机器3产量] ,
d.[机器4日期] ,
d.[机器4班次] ,
d.[机器4产量]
FROM a
FULL JOIN b ON b.机器2日期 = a.机器1日期
AND a.机器1班次 = b.机器2班次
AND b.num = a.num
FULL JOIN c ON c.机器3日期 = a.机器1日期
AND a.机器1班次 = c.机器3班次
AND c.num = a.num
FULL JOIN d ON d.机器4日期 = a.机器1日期
AND a.机器1班次 = d.机器4班次
AND d.num = a.num
WHERE a.机器1日期 IS NOT NULL
OR b.机器2日期 IS NOT NULL
OR c.机器3日期 IS NOT NULL
OR d.机器4日期 IS NOT NULL
ORDER BY CASE WHEN ISNULL(机器1日期, '2018-01-01') < ISNULL(机器2日期, '2018-01-01')
AND ISNULL(机器1日期, '2018-01-01') < ISNULL(机器3日期,
'2018-01-01')
AND ISNULL(机器1日期, '2018-01-01') < ISNULL(机器4日期,
'2018-01-01')
THEN 机器1日期
WHEN ISNULL(机器2日期, '2018-01-01') < ISNULL(机器1日期, '2018-01-01')
AND ISNULL(机器2日期, '2018-01-01') < ISNULL(机器3日期,
'2018-01-01')
AND ISNULL(机器2日期, '2018-01-01') < ISNULL(机器4日期,
'2018-01-01')
THEN 机器2日期
WHEN ISNULL(机器3日期, '2018-01-01') < ISNULL(机器1日期, '2018-01-01')
AND ISNULL(机器3日期, '2018-01-01') < ISNULL(机器2日期,
'2018-01-01')
AND ISNULL(机器3日期, '2018-01-01') < ISNULL(机器4日期,
'2018-01-01')
THEN 机器3日期
WHEN ISNULL(机器4日期, '2018-01-01') < ISNULL(机器1日期, '2018-01-01')
AND ISNULL(机器4日期, '2018-01-01') < ISNULL(机器2日期,
'2018-01-01')
AND ISNULL(机器4日期, '2018-01-01') < ISNULL(机器3日期,
'2018-01-01')
THEN 机器4日期
ELSE ISNULL(机器1日期, '2018-01-01')
END ,
CASE WHEN ISNULL(机器1班次, '赢') < ISNULL(机器2班次, '赢')
AND ISNULL(机器1班次, '赢') < ISNULL(机器3班次, '赢')
AND ISNULL(机器1班次, '赢') < ISNULL(机器4班次, '赢') THEN 机器1班次
WHEN ISNULL(机器2班次, '赢') < ISNULL(机器1班次, '赢')
AND ISNULL(机器2班次, '赢') < ISNULL(机器3班次, '赢')
AND ISNULL(机器2班次, '赢') < ISNULL(机器4班次, '赢') THEN 机器2班次
WHEN ISNULL(机器3班次, '赢') < ISNULL(机器1班次, '赢')
AND ISNULL(机器3班次, '赢') < ISNULL(机器2班次, '赢')
AND ISNULL(机器3班次, '赢') < ISNULL(机器4班次, '赢') THEN 机器3班次
WHEN ISNULL(机器4班次, '赢') < ISNULL(机器1班次, '赢')
AND ISNULL(机器4班次, '赢') < ISNULL(机器2班次, '赢')
AND ISNULL(机器4班次, '赢') < ISNULL(机器3班次, '赢') THEN 机器4班次
ELSE ISNULL(机器1班次, '赢')
END
老满 2017-09-19
- 打赏
- 举报
这个也是业务需要才这么做的。 本来是每天有两个班次，但每个班次要根据客户的订货和紧迫程度，安排生产不同的产品。而各个机台又适合生产的产品不同，所以才这么复杂。
繁花尽流年 2017-09-19
- 打赏
- 举报
那么生产排版的人呢，这些班次都是事先安排好的，你至少应该提前知道每天最多有几个白班几个夜班，那么你这张excel就好做的多了。
如果这个信息都要自己每天从生产流水里提取，就有点感人了。
繁花尽流年 2017-09-19
- 打赏
- 举报
LZ你这个excel太随意了，正常的应该是一个日期加班次的固定2列，然后是机器1~4的生产数量。
你这个每天白天安排几次夜班几次都没有完整安排么，看机器随便做的？至少应该有个每天的班次序列吧。
老满 2017-09-19
- 打赏
- 举报
表结构和原始数据时这样的
用上面那位老兄的sql 好像不行哦！
我执行的SQL
得到以下结果
机器2的产量已经重复了，这还没有把机器3和机器4一起做上去呢
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([机器1日期] Datetime,[机器1班次] nvarchar(22),[机器1产量] int,[机器2日期] Datetime,[机器2班次] nvarchar(22),[机器2产量] int,
[机器3日期] Datetime,[机器3班次] nvarchar(22),[机器3产量] int,[机器4日期] Datetime,[机器4班次] nvarchar(22),[机器4产量] int )
Insert #T
select '2017-9-17','白班',100,null,null,null,null,null,null,null,null,null union all
select '2017-9-17','白班',120,null,null,null,null,null,null,null,null,null union all
select '2017-9-17','夜班',200,null,null,null,null,null,null,null,null,null union all
select '2017-9-18','白班',150,null,null,null,null,null,null,null,null,null union all
select '2017-9-18','夜班',190,null,null,null,null,null,null,null,null,null union all
select null,null,null,'2017-9-17','白班',210,null,null,null,null,null,null union all
select null,null,null,null,null,null,'2017-9-17','白班',50,null,null,null union all
select null,null,null,null,null,null,'2017-9-17','白班',60,null,null,null union all
select null,null,null,null,null,null,'2017-9-17','白班',80,null,null,null union all
select null,null,null,null,null,null,null,null,null,'2017-9-18','夜班',210
Go用上面那位老兄的sql 好像不行哦！
我执行的SQL
SELECT a.[机器1日期] ,
a.[机器1班次] ,
a.[机器1产量] ,
b.[机器2日期] ,
b.[机器2班次] ,
b.[机器2产量]
FROM #T a
FULL JOIN #T b ON b.机器2日期 = a.机器1日期 and a.机器1班次=b.机器2班次
WHERE a.机器1日期 IS NOT NULL
OR b.机器2日期 IS NOT NULL得到以下结果
机器2的产量已经重复了，这还没有把机器3和机器4一起做上去呢
