MachineName PrintDate LabelDate Qty StartSN EndSN
5CS130XCC030000 2014-05-22 17:26 2014-04-29 5 1 5
5CS130XCC030000 2014-05-29 13:40 2014-04-29 3 11 13
5CS130XCC030000 2014-05-22 17:29 2014-04-30 1 6 6
5CS130XCC030000 2014-05-22 18:12 2014-04-30 4 7 10
查询出的结果如上..
列 Qty, StartSN,EndSN都是用聚合函数弄出来的..
但这个结果不是我想要的结果..
想要的结果是.. .
MachineName PrintDate LabelDate Qty StartSN EndSN
5CS130XCC030000 2014-05-22 17:26 2014-04-29 5 1 5
5CS130XCC030000 2014-05-29 13:40 2014-04-29 3 11 13
5CS130XCC030000 2014-05-22 17:29 2014-04-30 5 6 10
求一sql语句..
本人对于sql很弱很弱....
------解决方案--------------------
--> 测试数据[ProductPrintInfo]
if object_id('[ProductPrintInfo]') is not null drop table [ProductPrintInfo]
go
create table [ProductPrintInfo]([MachineName] nvarchar(30),[PrintDate] datetime,[LabelDate] date,[Qty] int,[StartSN] int,[EndSN] int)
insert [ProductPrintInfo]
select '5CS130XCC030000','2014-05-22 17:26','2014-04-29',5,1,5 union all
select '5CS130XCC030000','2014-05-29 13:40','2014-04-29',3,11,13 union all
select '5CS130XCC030000','2014-05-22 17:29','2014-04-30',1,6,6 union all
select '5CS130XCC030000','2014-05-22 18:12','2014-04-30',4,7,10
--------------生成数据--------------------------
;WITH cte AS (
select * ,ROW_NUMBER()OVER(PARTITION BY [MachineName],[LabelDate] ORDER BY startSN)id
from [ProductPrintInfo]),cte1 AS
(
SELECT * FROM cte WHERE id=1
UNION ALL
SELECT b.[MachineName],CASE WHEN b.startsn-1=a.endsn THEN a.[PrintDate] ELSE b.[PrintDate] END [PrintDate],b.[LabelDate],CASE WHEN b.startsn-1=a.endsn THEN a.qty+b.qty ELSE b.qty END qty,CASE WHEN b.startsn-1=a.endsn THEN a.endsn ELSE b.startsn END startsn,b.endsn,b.id
--CASE WHEN b.startsn-1=a.endsn THEN a.startsn ELSE b.endsn END startsn,CASE WHEN b.startsn-1=a.endsn THEN b.endsn ELSE a.startsn END endsn,b.id
FROM cte1 a INNER JOIN cte b ON a.id=b.id-1 AND a.[MachineName]=b.[MachineName] AND a.[LabelDate]=b.[LabelDate]
)
SELECT MachineName,PrintDate,LabelDate,MAX(Qty) AS qty,MIN(StartSN)StartSN,MAX(EndSN )EndSN
FROM cte1
GROUP BY MachineName,PrintDate,LabelDate
ORDER BY MachineName,LabelDate
----------------结果----------------------------
/*
MachineName PrintDate LabelDate qty StartSN EndSN
------------------------------ ----------------------- ---------- ----------- ----------- -----------
5CS130XCC030000 2014-05-22 17:26:00.000 2014-04-29 5 1 5
5CS130XCC030000 2014-05-29 13:40:00.000 2014-04-29 3 11 13