SELECT COUNT(F.值)数量,F.差距,F.差值 FROM (SELECT E.值,E.差值,CASE WHEN E.与上限差距>0 THEN E.与上限差距 WHEN E.与下限差距>0 THEN E.与下限差距 END AS 差距 FROM (select D.值,D.差值,D.上限,D.下限,D.值-D.上限 as 与上限差距,D.下限-D.值 as 与下限差距 FROM(SELECT A.*, B.差值, B.上限, B.下限, B.SPCN, CONVERT ( VARCHAR ( 10 ), datepart(WK, A.time) ) + N'周' AS WEEK1, datepart(WK, A.time) AS WEEK2 FROM ( SELECT AA.*, ( cast( AA.设备 AS NVARCHAR ) + cast( AA.位置 AS NVARCHAR ) ) AS 设备位置, C.ITEM, C.供应商, CASE WHEN AA.设备 = '影像仪测试—正面' THEN C.影像仪正面时间 WHEN AA.设备 = '影像仪测试—反面' THEN C.影像仪反面时间 WHEN AA.设备 = '三坐标' THEN C.三坐标时间END as time FROM 测量室数据 AA LEFT JOIN 测量室主表 C ON AA.ID = C.ID WHERE isnull(C.ITEM,'')<>'' AND isnull(C.供应商,'')<>'' AND (AA.设备 = '影像仪测试—正面' OR AA.设备 = '影像仪测试—反面' OR AA.设备 = '三坐标' ) ) A LEFT JOIN ( SELECT ( cast( 设备 AS NVARCHAR ) + cast( SPC位置 AS NVARCHAR ) ) AS 设备位置, SPCID, SPC位置, SPCU - SPCL AS 差值, SPCN + SPCU AS 上限, SPCN + SPCL AS 下限,设备, ID, SPCN FROM 测量室SPC ) B ON A.设备位置 = B.设备位置 AND A.ITEM = B.ID )D where D.WEEK2>34 and D.WEEK2<40 and D.供应商= '烈炎' )E)F GROUP BY F.差值,F.差距-----------------------------------------------------------这个SQL的结果查出来是这样-----------------------------想达到这个透视的效果 行与列的分组是自己创建的
