sql - 当 PIVOT 在 SQL Server CE 中不可用时的 SQL 等效项
问题描述
除了在 Microsoft SQL Server CE 上(不支持 PIVOT)之外,我对正常运行的 SQL 查询有疑问。查询如下:
SELECT
*, [1] as IMGN1, [2] as IMGN2, [3] as IMGN3,
[4] as IMGN4, [5] as IMGN5, [6] as IMGN6,
[7] as IMGN7, [8] as IMGN8, [9] as IMGN9,
[10] as IMGN10
FROM
(SELECT
area.CoilId as CID, area.DEFECTID,
(SELECT SUM(s2.endposmd - s2.startposmd)
FROM sections s2
WHERE s2.OutCoilID = 999999
AND s2.InCoilId <= area.coilid) AS POSITIONMD,
d1.DNO as CAMERADEFECTNO, d1.IMAGE_NO as IMAGE_NO,
area.MERGEDTO as MERGEDTO
FROM
(OutCoils AS oc
INNER JOIN
sections AS s ON oc.OutCoilId = s.OutCoilId
INNER JOIN
defects AS area ON area.coilid = s.InCoilId
AND area.PositionMD >= s.StartPosMD
AND area.PositionMD <= s.EndPosMD
INNER JOIN
defects AS d1 ON d1.CoilId = area.CoilId
AND d1.MergedTo = area.DEFECTID)
WHERE
oc.OutCoilID = 999999 AND area.MergedTo = -2) AS SourceTable
PIVOT
(MIN([CAMERADEFECTNO]) FOR [IMAGE_NO]
IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10])
) AS PivotTable
ORDER BY
PositionMD;
如何将其转换为非 PIVOT 版本的有效 SQL 查询?
我使用 CASE 尝试了一些东西,但是我用来获取 POSITIONMD 的聚合函数中的子查询有问题,其余的已经正常工作。知道如何获得 POSITIONMD 吗?
SELECT
area.DEFECTID as DEFECTID,
min(CASE when d1.MERGEDTO = area.DEFECTID then area.COILID end) CID,
min(CASE when d1.MERGEDTO = area.DEFECTID then area.MERGEDTO end) MERGEDTO,
min(CASE when d1.MERGEDTO = area.DEFECTID then (select sum(s2.endposmd - s2.startposmd) from sections s2 where s2.OutCoilID=999999 and s2.InCoilId<=area.coilid) end) POSITIONMD,
sum(CASE when d1.IMAGE_NO = 1 then (d1.DNO) end) IMGN1,
sum(CASE when d1.IMAGE_NO = 2 then (d1.DNO) end) IMGN2,
sum(CASE when d1.IMAGE_NO = 3 then (d1.DNO) end) IMGN3,
sum(CASE when d1.IMAGE_NO = 4 then (d1.DNO) end) IMGN4,
sum(CASE when d1.IMAGE_NO = 5 then (d1.DNO) end) IMGN5,
sum(CASE when d1.IMAGE_NO = 6 then (d1.DNO) end) IMGN6,
sum(CASE when d1.IMAGE_NO = 7 then (d1.DNO) end) IMGN7,
sum(CASE when d1.IMAGE_NO = 8 then (d1.DNO) end) IMGN8,
sum(CASE when d1.IMAGE_NO = 9 then (d1.DNO) end) IMGN9,
sum(CASE when d1.IMAGE_NO = 10 then (d1.DNO) end) IMGN10
FROM ( steinb.OutCoils AS oc
INNER JOIN steinb.sections AS s ON oc.OutCoilId=s.OutCoilId
INNER JOIN steinb.defects AS area ON area.coilid=s.InCoilId AND area.PositionMD>=s.StartPosMD AND area.PositionMD<=s.EndPosMD
INNER JOIN steinb.defects AS d1 ON d1.CoilId=area.CoilId AND d1.MergedTo=area.DEFECTID AND d1.IMAGE_NO!=0)
WHERE oc.OutCoilID=999999 GROUP BY area.DEFECTID ORDER BY PositionMD;
十分感谢。
解决方案
明白了。我相信你只需要删除 MIN:
SELECT
DEFECTID = area.DEFECTID
, CID = MIN(CASE when d1.MERGEDTO = area.DEFECTID then area.COILID end)
, MERGEDTO = MIN(CASE when d1.MERGEDTO = area.DEFECTID then area.MERGEDTO end)
, POSITIONMD = CASE when d1.MERGEDTO = area.DEFECTID then (select sum(s2.endposmd - s2.startposmd) from sections s2 where s2.OutCoilID=999999 and s2.InCoilId<=area.coilid) END
, IMGN1 = sum(CASE when d1.IMAGE_NO = 1 then (d1.DNO) END)
, IMGN2 = sum(CASE when d1.IMAGE_NO = 2 then (d1.DNO) end)
, IMGN3 = sum(CASE when d1.IMAGE_NO = 3 then (d1.DNO) end)
, IMGN4 = sum(CASE when d1.IMAGE_NO = 4 then (d1.DNO) end)
, IMGN5 = sum(CASE when d1.IMAGE_NO = 5 then (d1.DNO) end)
, IMGN6 = sum(CASE when d1.IMAGE_NO = 6 then (d1.DNO) end)
, IMGN7 = sum(CASE when d1.IMAGE_NO = 7 then (d1.DNO) end)
, IMGN8 = sum(CASE when d1.IMAGE_NO = 8 then (d1.DNO) end)
, IMGN9 = sum(CASE when d1.IMAGE_NO = 9 then (d1.DNO) end)
, IMGN10 = sum(CASE when d1.IMAGE_NO = 10 then (d1.DNO) end)
FROM ( steinb.OutCoils AS oc
INNER JOIN steinb.sections AS s ON oc.OutCoilId=s.OutCoilId
INNER JOIN steinb.defects AS area ON area.coilid=s.InCoilId AND area.PositionMD>=s.StartPosMD AND area.PositionMD<=s.EndPosMD
INNER JOIN steinb.defects AS d1 ON d1.CoilId=area.CoilId AND d1.MergedTo=area.DEFECTID AND d1.IMAGE_NO!=0)
WHERE oc.OutCoilID=999999 GROUP BY area.DEFECTID ORDER BY PositionMD;
推荐阅读
- sql-server - 选择所有不是任何其他文件 SQL 的父文件的文件
- filenames - Inno Setup,从文件名中获取字符串
- asp.net-core-mvc - .net core 3状态码405在使用httpClient和Fiddler时响应?
- permissions - 在 Fedora 31 中,如何为 Podman 容器中运行的 nginx 设置权限?
- python - 计算大小时numpy数组中的索引太多
- javascript - 保存数据的多个变量 - 哪个具有最高值?
- mysql - 尽管使用外键约束,低索引基数和有太多其他索引是否需要从表中删除索引?
- python - 在循环中获取数据的问题(Scrapy)
- javascript - 在角度js中动态构造具有键和值作为列表的对象
- php - 在 div 类中自动插入图像