SELECT ROW_NUMBER() OVER (ORDER BY
ISNULL(b.count_1,0) +
ISNULL(c.count_2,0) +
ISNULL(d.count_3,0) +
ISNULL(e.count_4,0) +
ISNULL(f.count_5,0) DESC) AS rank
,a.categories
,ISNULL(b.count_1,0) + ISNULL(c.count_2,0) + ISNULL(d.count_3,0) + ISNULL(e.count_4,0) + ISNULL(f.count_5,0) AS total_score FROM
(SELECT DISTINCT Growth1 AS categories FROM [TESTDB].[dbo].[testtable]
UNION SELECT DISTINCT Growth2 AS categories FROM [TESTDB].[dbo].[testtable]
UNION SELECT DISTINCT Growth3 AS categories FROM [TESTDB].[dbo].[testtable]
UNION SELECT DISTINCT Growth4 AS categories FROM [TESTDB].[dbo].[testtable]
UNION SELECT DISTINCT Growth5 AS categories FROM [TESTDB].[dbo].[testtable]) AS a
LEFT JOIN
(SELECT Growth1, COUNT(Growth1) * 5 AS count_1
FROM [TESTDB].[dbo].[testtable]
GROUP BY Growth1) AS b
ON a.categories = b.Growth1
LEFT JOIN
(SELECT Growth2, COUNT(Growth2) * 4 AS count_2
FROM [TESTDB].[dbo].[testtable]
GROUP BY Growth2) AS c
ON a.categories = c.Growth2
LEFT JOIN
(SELECT Growth3, COUNT(Growth3) * 3 AS count_3
FROM [TESTDB].[dbo].[testtable]
GROUP BY Growth3) AS d
ON a.categories = d.Growth3
LEFT JOIN
(SELECT Growth4, COUNT(Growth4) * 2 AS count_4
FROM [TESTDB].[dbo].[testtable]
GROUP BY Growth4) AS e
ON a.categories = e.Growth4
LEFT JOIN
(SELECT Growth5, COUNT(Growth5) * 1 AS count_5
FROM [TESTDB].[dbo].[testtable]
GROUP BY Growth5) AS f
ON a.categories = f.Growth5
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…