使用SQL查询进行排名计数

随机行者

我正在为SQLite使用数据库浏览器。我有一个以下格式的表：

+-----------+-------------------------------------+
| search_id |             search_town             |
+-----------+-------------------------------------+
|         1 | town1,town3                         |
|         2 | town2,town4,town5                   |
|         3 | town3,town5                         |
|         4 | town2,town5                         |
|         5 | town2,town3,town4                   |
+-----------+-------------------------------------+

我想COUNT对town1通过town5中出现的次数做一个search_town，然后根据城镇的数量按降序对城镇进行排名。到目前为止，我有以下查询：

SELECT SUM(CASE WHEN search_location LIKE '%town01%' THEN 1 ELSE 0 END) AS town01,
SUM(CASE WHEN search_location LIKE '%town02%' THEN 1 ELSE 0 END) AS town02,
SUM(CASE WHEN search_location LIKE '%town03%' THEN 1 ELSE 0 END) AS town03,
SUM(CASE WHEN search_location LIKE '%town04%' THEN 1 ELSE 0 END) AS town04,
SUM(CASE WHEN search_location LIKE '%town05%' THEN 1 ELSE 0 END) AS town05
FROM searches

...但是无法执行，ORDER BY因为城镇及其计数以这种格式输出为列而不是行

+-------+-------+-------+-------+-------+
| town1 | town2 | town3 | town4 | town5 |
+-------+-------+-------+-------+-------+
|    12 |    31 |    12 |    24 |    12 |
+-------+-------+-------+-------+-------+

还有其他方法吗？感谢任何评论。

PSK

您正在使用将输出CASE WHEN转换为一行，将其转换为多行，您可以尝试如下操作。

;WITH cte 
     AS (SELECT * 
         FROM   (VALUES ('Town1'), 
                        ('Town2'), 
                        ('Town3'), 
                        ('Town4'), 
                        ('Town5')) T(town)) 
SELECT Count(*) [Count], 
       C.town 
FROM   [TABLE_NAME] T 
       INNER JOIN cte C 
               ON T.search_location LIKE '%' + C.town + '%' 
GROUP  BY C.town 
ORDER BY Count(*) DESC

在线演示

可以使用另一种方法UNION ALL。

SELECT * 
FROM   (SELECT Count(*) s, 
               'Town1'  AS Col 
        FROM   tablename 
        WHERE  search_location LIKE '%town1%' 
        UNION ALL 
        SELECT Count(*) s, 
               'Town2'  AS Col 
        FROM   tablename 
        WHERE  search_location LIKE '%town2%' 
        UNION ALL 
        SELECT Count(*) s, 
               'Town3'  AS Col 
        FROM   tablename 
        WHERE  search_location LIKE '%town3%' 
        UNION ALL 
        SELECT Count(*) s, 
               'Town4'  AS Col 
        FROM   tablename 
        WHERE  search_location LIKE '%town4%' 
        UNION ALL 
        SELECT Count(*) s, 
               'Town5'  AS Col 
        FROM   tablename 
        WHERE  search_location LIKE '%town5%') t 
ORDER  BY s DESC

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。