sql - 使用 SQL 查询对计数进行排名

Question

我正在使用 DB Browser for SQLite。我有以下格式的表格:

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

我想对 town1 到 town5 出现在 search_town 下的次数进行COUNT ，然后根据各自的计数对城镇进行降序排列。到目前为止，我有以下查询:

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 |
+-------+-------+-------+-------+-------+

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

Answer 1

您正在使用 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

Online DEMO

另一种方法可以使用 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