SQL Server 题目：为用户投票，并统计投票结果

为用户投票，并统计投票结果

用户表:

uid 用户id
city 城市
name 姓名
type 类型 0:投票人 1:被投票人

投票记录表:

id
time 投票时间
uid 投票人id
t uid 被投票人id
score 得分整型 1-3分
checks 理由 得分为1时，必选理由，理由共11条，复选，此为拼接的序号，例: 2.10.11,1

请写出sql统出投票结果，数据库为sql server

结果表头:被投票人城市、被投票人姓名、1分的数量、2分的数量、3分的数量、理由1的数量、理由2的数
量...理由11的数量

答案

SELECT
  t.city as '被投票人城市',
  t.name as '被投票人姓名',
  (SELECT COUNT(1) FROM t_voting_record WHERE t_uid = t.uid AND score = 1) as '1分的数量',
  (SELECT COUNT(1) FROM t_voting_record WHERE t_uid = t.uid AND score = 2) as '2分的数量',
  (SELECT COUNT(1) FROM t_voting_record WHERE t_uid = t.uid AND score = 3) as '3分的数量',
  (SELECT COUNT(1) FROM (SELECT T.t_uid ,A.X.value ('.', 'VARCHAR(20)') as checks FROM (SELECT t_uid, checks = CONVERT(XML, '<CHECKS>' + REPLACE(checks, ',', '</CHECKS><CHECKS>' )+ '</CHECKS>') FROM t_voting_record ) AS T CROSS APPLY checks.nodes ('//CHECKS') AS A (X)) as tc WHERE tc.t_uid = t.uid AND tc.checks = 1 GROUP BY tc.checks) as '理由1的数量',
  (SELECT COUNT(1) FROM (SELECT T.t_uid ,A.X.value ('.', 'VARCHAR(20)') as checks FROM (SELECT t_uid, checks = CONVERT(XML, '<CHECKS>' + REPLACE(checks, ',', '</CHECKS><CHECKS>' )+ '</CHECKS>') FROM t_voting_record ) AS T CROSS APPLY checks.nodes ('//CHECKS') AS A (X)) as tc WHERE tc.t_uid = t.uid AND tc.checks = 2 GROUP BY tc.checks) as '理由2的数量',
  (SELECT COUNT(1) FROM (SELECT T.t_uid ,A.X.value ('.', 'VARCHAR(20)') as checks FROM (SELECT t_uid, checks = CONVERT(XML, '<CHECKS>' + REPLACE(checks, ',', '</CHECKS><CHECKS>' )+ '</CHECKS>') FROM t_voting_record ) AS T CROSS APPLY checks.nodes ('//CHECKS') AS A (X)) as tc WHERE tc.t_uid = t.uid AND tc.checks = 3 GROUP BY tc.checks) as '理由3的数量',
  (SELECT COUNT(1) FROM (SELECT T.t_uid ,A.X.value ('.', 'VARCHAR(20)') as checks FROM (SELECT t_uid, checks = CONVERT(XML, '<CHECKS>' + REPLACE(checks, ',', '</CHECKS><CHECKS>' )+ '</CHECKS>') FROM t_voting_record ) AS T CROSS APPLY checks.nodes ('//CHECKS') AS A (X)) as tc WHERE tc.t_uid = t.uid AND tc.checks = 4 GROUP BY tc.checks) as '理由4的数量',
  (SELECT COUNT(1) FROM (SELECT T.t_uid ,A.X.value ('.', 'VARCHAR(20)') as checks FROM (SELECT t_uid, checks = CONVERT(XML, '<CHECKS>' + REPLACE(checks, ',', '</CHECKS><CHECKS>' )+ '</CHECKS>') FROM t_voting_record ) AS T CROSS APPLY checks.nodes ('//CHECKS') AS A (X)) as tc WHERE tc.t_uid = t.uid AND tc.checks = 5 GROUP BY tc.checks) as '理由5的数量',
  (SELECT COUNT(1) FROM (SELECT T.t_uid ,A.X.value ('.', 'VARCHAR(20)') as checks FROM (SELECT t_uid, checks = CONVERT(XML, '<CHECKS>' + REPLACE(checks, ',', '</CHECKS><CHECKS>' )+ '</CHECKS>') FROM t_voting_record ) AS T CROSS APPLY checks.nodes ('//CHECKS') AS A (X)) as tc WHERE tc.t_uid = t.uid AND tc.checks = 6 GROUP BY tc.checks) as '理由6的数量',
  (SELECT COUNT(1) FROM (SELECT T.t_uid ,A.X.value ('.', 'VARCHAR(20)') as checks FROM (SELECT t_uid, checks = CONVERT(XML, '<CHECKS>' + REPLACE(checks, ',', '</CHECKS><CHECKS>' )+ '</CHECKS>') FROM t_voting_record ) AS T CROSS APPLY checks.nodes ('//CHECKS') AS A (X)) as tc WHERE tc.t_uid = t.uid AND tc.checks = 7 GROUP BY tc.checks) as '理由7的数量',
  (SELECT COUNT(1) FROM (SELECT T.t_uid ,A.X.value ('.', 'VARCHAR(20)') as checks FROM (SELECT t_uid, checks = CONVERT(XML, '<CHECKS>' + REPLACE(checks, ',', '</CHECKS><CHECKS>' )+ '</CHECKS>') FROM t_voting_record ) AS T CROSS APPLY checks.nodes ('//CHECKS') AS A (X)) as tc WHERE tc.t_uid = t.uid AND tc.checks = 8 GROUP BY tc.checks) as '理由8的数量',
  (SELECT COUNT(1) FROM (SELECT T.t_uid ,A.X.value ('.', 'VARCHAR(20)') as checks FROM (SELECT t_uid, checks = CONVERT(XML, '<CHECKS>' + REPLACE(checks, ',', '</CHECKS><CHECKS>' )+ '</CHECKS>') FROM t_voting_record ) AS T CROSS APPLY checks.nodes ('//CHECKS') AS A (X)) as tc WHERE tc.t_uid = t.uid AND tc.checks = 9 GROUP BY tc.checks) as '理由9的数量',
  (SELECT COUNT(1) FROM (SELECT T.t_uid ,A.X.value ('.', 'VARCHAR(20)') as checks FROM (SELECT t_uid, checks = CONVERT(XML, '<CHECKS>' + REPLACE(checks, ',', '</CHECKS><CHECKS>' )+ '</CHECKS>') FROM t_voting_record ) AS T CROSS APPLY checks.nodes ('//CHECKS') AS A (X)) as tc WHERE tc.t_uid = t.uid AND tc.checks = 10 GROUP BY tc.checks) as '理由10的数量',
  (SELECT COUNT(1) FROM (SELECT T.t_uid ,A.X.value ('.', 'VARCHAR(20)') as checks FROM (SELECT t_uid, checks = CONVERT(XML, '<CHECKS>' + REPLACE(checks, ',', '</CHECKS><CHECKS>' )+ '</CHECKS>') FROM t_voting_record ) AS T CROSS APPLY checks.nodes ('//CHECKS') AS A (X)) as tc WHERE tc.t_uid = t.uid AND tc.checks = 11 GROUP BY tc.checks) as '理由11的数量'
FROM
    t_user t
WHERE
    t.uid IN(
    SELECT
        tvr.t_uid
    FROM
        t_user tu
    LEFT JOIN t_voting_record tvr 
ON
        tu.uid = tvr.t_uid
    GROUP BY
        tvr.t_uid
    HAVING
        t_uid IS NOT NULL);