自己写过的sql
SELECT
td.JobNumber AS 'ScanJobName' ,
tb.Sn2 AS 'PcbLot_PCBAlot',
ta.扫描Sn AS 'Sensor_Serial',
tb.CreateDate AS 'CreateDate_CollectSensorSN',
ta.PairJobName,
ta.扫描时间 AS 'Scan_time',
ta.彩盒Sn AS 'Kit_SN',
ta.包装箱号 AS 'master_carton_No' ,
ta.包装时间 AS 'Packing_time',
(SELECT Sn1 + ',' FROM ScanLog where BoxId = ta.BoxID FOR XML PATH(''))
--关键在这里
FROM
(SELECT
tt1.包装箱号,
tt1.包装时间,
tt5.JobNumber AS 'PairJobName',
tt3.Sn2 AS Lot ,
tt1.彩盒Sn,
tt3.sn1 AS '扫描Sn' ,
tt3.CreateDate AS '扫描时间',
tt2.Id AS 'BoxID'
FROM
(SELECT t1.FullBoxSn AS '包装箱号', t4.CreateDate AS '包装时间' ,t4.Sn1 AS '彩盒Sn'
FROM dbo.BoxLog AS t1,
dbo.PositionInfo AS t2,
dbo.Job AS t3,
dbo.ScanLog AS t4
WHERE t1.PositionId = t2.SortNum
AND t1.JobId = t3.Id
AND t1.Id = t4.BoxId
AND t2.PostionName='Pack'
AND t3.JobNumber='LE33914-01'
AND t1.IsComplete=1
AND t3.DisabledDate IS NULL
AND t4.DisabledDate IS NULL) AS tt1,
dbo.BoxLog AS tt2,
dbo.ScanLog AS tt3,
dbo.PositionInfo AS tt4,
dbo.Job AS tt5
WHERE tt1.彩盒Sn=tt2.FullBoxSn
AND tt2.Id=tt3.BoxId
AND tt3.PostionId=tt4.SortNum
AND tt5.Id=tt3.JobId
AND tt4.PostionName='Pair'
AND tt2.IsComplete=1
AND tt3.DisabledDate IS NULL) AS ta,
dbo.ScanLog AS tb,
dbo.PositionInfo AS tc,
dbo.Job AS td
WHERE ta.扫描Sn=tb.Sn1
AND tb.PostionId=tc.SortNum
AND tb.JobId=td.Id
AND tc.PostionName='Scan'
AND tb.DisabledDate IS NULL
AND tb.IsComplete=1
ORDER BY ta.包装箱号 ,ta.彩盒Sn