如何根据特定的标准对列中的字段进行分组
以下是查询：
SELECT REF.TOS AS [TOS Value]
,RSA_BIRMINGHAM_1941$.Percentage AS [Birmingham]
,RSA_CARDIFFREGUS_2911$.Percentage AS [Cardiff Regus]
,RSA_CASTLEMEAD_1941$.Percentage AS [Castlemead]
,RSA_CHELMSFORD_1941$.Percentage AS [Chelmsford]
FROM
( SELECT TOS FROM RSA_BIRMINGHAM_1941$ UNION
SELECT TOS FROM RSA_CARDIFFREGUS_2911$ UNION
SELECT TOS FROM RSA_CASTLEMEAD_1941$ UNION
SELECT TOS FROM RSA_CHELMSFORD_1941$
) AS REF
FULL OUTER JOIN RSA_BIRMINGHAM_1941$ ON REF.TOS =
RSA_BIRMINGHAM_1941$.TOS
FULL OUTER JOIN RSA_CARDIFFREGUS_2911$ ON REF.TOS =
RSA_CARDIFFREGUS_2911$.TOS
FULL OUTER JOIN RSA_CASTLEMEAD_1941$ ON REF.TOS =
RSA_CASTLEMEAD_1941$.TOS
FULL OUTER JOIN RSA_CHELMSFORD_1941$ ON REF.TOS =
RSA_CHELMSFORD_1941$.TOS这是输出：
TOS Value Birmingham CardiffRegus Castlemead Chelmsford
af11 (DSCP 10) 15.22% 4.63% 0.00% 6.16%
af12 (DSCP 12) 0.12% NULL NULL NULL
af21 (DSCP 18) 0.05% 0.05% 0.00% 0.02%
af31 (DSCP 26) 8.86% 13.85% 0.01% 5.59%
af32 (DSCP 28) NULL NULL NULL 0.02%
af33 (DSCP 30) 11.49% 15.44% NULL 7.33%
af41 (DSCP 34) 0.70% 0.03% 0.01% 0.05%
cs3 (DSCP 24) 0.06% 0.11% 0.01% 0.04%
cs4 (DSCP 32) 0.15% 0.20% NULL 0.10%
cs6 (DSCP 48) NULL 0.23% 0.00% NULL
defau(DSCP 0) 61.37% 61.74% 99.48% 79.78%
ef (DSCP 46) 1.91% 3.72% 0.49% 0.91%我需要将TOS值列的某些值分组到“父”组下，而不影响其他列，因为它们与TOS值列直接相关。
关于我需要完成什么的想法，请看下面的图片：
回答 1
Stack Overflow用户
发布于 2016-03-31 03:54:01
您可以进行长时间的自连接，但是由于这是Server，所以我们可以利用PIVOT表查询。
SELECT * FROM ( SELECT 'Birmingham' AS [SITE], REF.TOS, Percentage FROM ( SELECT TOS FROM RSA_BIRMINGHAM_1941$ UNION SELECT TOS FROM RSA_CARDIFFREGUS_2911$ UNION SELECT TOS FROM RSA_CASTLEMEAD_1941$ UNION SELECT TOS FROM RSA_CHELMSFORD_1941$ ) AS REF LEFT OUTER JOIN RSA_BIRMINGHAM_1941$ RSA ON RSA.TOS = REF.TOS ) BIRM PIVOT ( SUM(Percentage) FOR TOS in ( [ef (DSCP 46)], [af31 (DSCP 26)], [af32 (DSCP 28)], [af33 (DSCP 30)], [cs3 (DSCP 24)], [cs4 (DSCP 32)], [cs6 (DSCP 48)], [af41 (DSCP 34)], [af42 (DSCP 36)], [af43 (DSCP 38)], [af21 (DSCP 18)], [af22 (DSCP 20)], [af23 (DSCP 22)], [af11 (DSCP 10)], [af12 (DSCP 12)], [af13 (DSCP 14)], [defau(DSCP 0)]) ) PIV UNION ALL SELECT * FROM ( SELECT 'Cardiff Regus' AS [SITE], REF.TOS, Percentage FROM ( SELECT TOS FROM RSA_BIRMINGHAM_1941$ UNION SELECT TOS FROM RSA_CARDIFFREGUS_2911$ UNION SELECT TOS FROM RSA_CASTLEMEAD_1941$ UNION SELECT TOS FROM RSA_CHELMSFORD_1941$ ) AS REF LEFT OUTER JOIN RSA_CARDIFFREGUS_2911$ RSA ON RSA.TOS = REF.TOS ) CARD PIVOT ( SUM(Percentage) FOR TOS in ( [ef (DSCP 46)], [af31 (DSCP 26)], [af32 (DSCP 28)], [af33 (DSCP 30)], [cs3 (DSCP 24)], [cs4 (DSCP 32)], [cs6 (DSCP 48)], [af41 (DSCP 34)], [af42 (DSCP 36)], [af43 (DSCP 38)], [af21 (DSCP 18)], [af22 (DSCP 20)], [af23 (DSCP 22)], [af11 (DSCP 10)], [af12 (DSCP 12)], [af13 (DSCP 14)], [defau(DSCP 0)]) ) PIV UNION ALL SELECT * FROM ( SELECT 'Castlemead' AS [SITE], REF.TOS, Percentage FROM ( SELECT TOS FROM RSA_BIRMINGHAM_1941$ UNION SELECT TOS FROM RSA_CARDIFFREGUS_2911$ UNION SELECT TOS FROM RSA_CASTLEMEAD_1941$ UNION SELECT TOS FROM RSA_CHELMSFORD_1941$ ) AS REF LEFT OUTER JOIN RSA_CASTLEMEAD_1941$ RSA ON RSA.TOS = REF.TOS ) CASTLE PIVOT ( SUM(Percentage) FOR TOS in ( [ef (DSCP 46)], [af31 (DSCP 26)], [af32 (DSCP 28)], [af33 (DSCP 30)], [cs3 (DSCP 24)], [cs4 (DSCP 32)], [cs6 (DSCP 48)], [af41 (DSCP 34)], [af42 (DSCP 36)], [af43 (DSCP 38)], [af21 (DSCP 18)], [af22 (DSCP 20)], [af23 (DSCP 22)], [af11 (DSCP 10)], [af12 (DSCP 12)], [af13 (DSCP 14)], [defau(DSCP 0)]) ) PIV UNION ALL SELECT * FROM ( SELECT 'Chelmsford' AS [SITE], REF.TOS, Percentage FROM ( SELECT TOS FROM RSA_BIRMINGHAM_1941$ UNION SELECT TOS FROM RSA_CARDIFFREGUS_2911$ UNION SELECT TOS FROM RSA_CASTLEMEAD_1941$ UNION SELECT TOS FROM RSA_CHELMSFORD_1941$ ) AS REF LEFT OUTER JOIN RSA_CHELMSFORD_1941$ RSA ON RSA.TOS = REF.TOS ) CHEM PIVOT ( SUM(Percentage) FOR TOS in ( [ef (DSCP 46)], [af31 (DSCP 26)], [af32 (DSCP 28)], [af33 (DSCP 30)], [cs3 (DSCP 24)], [cs4 (DSCP 32)], [cs6 (DSCP 48)], [af41 (DSCP 34)], [af42 (DSCP 36)], [af43 (DSCP 38)], [af21 (DSCP 18)], [af22 (DSCP 20)], [af23 (DSCP 22)], [af11 (DSCP 10)], [af12 (DSCP 12)], [af13 (DSCP 14)], [defau(DSCP 0)]) ) PIV;
https://stackoverflow.com/questions/36286269
复制相似问题
腾讯云开发者
