具有优先级查询的SQLite

我需要帮助来解决我的问题与DB查询.在我的数据库里面，我有一些不同的电视台.这些电台的大多数有不同的分辨率提供.即. 4K，FHD，HD，SD，.现在我想为每个站选择不同.这很容易，但我总是想得到这个电视台的最高分辨率行.
示例：

|DE|ZDF
|DE|ZDF HD
|DE|RTL
|DE|RTL FHD
|DE|RTL HD
|DE|SAT1

输出应为：

|DE|ZDF HD
|DE|RTL FHD
|DE|SAT1

你能帮我一下吗？
这并不像预期的那样工作：

SELECT 
    sender,
    MAX(quality) AS quality
FROM (
    SELECT 
        SUBSTR(tvg_name, 1, INSTR(tvg_name, '|') - 1) AS sender,
        CASE 
            WHEN tvg_name LIKE '%4K' THEN '4K'
            WHEN tvg_name LIKE '%FHD' THEN 'FHD'
            WHEN tvg_name LIKE '%HD' THEN 'HD'
            ELSE 'SD' 
        END AS quality
    FROM derek 
    WHERE tvg_name LIKE '|DE%'
) AS temp
GROUP BY sender;

注意以下几点（特别是评论）：

DROP TABLE IF EXISTS derek;
CREATE TABLE IF NOT EXISTS derek (tvg_name);
INSERT INTO derek VALUES
    ('|DE|ZDF'),
    ('|DE|ZDF HD'),
    ('|DE|RTL'),
    ('|DE|RTL FHD'),
    ('|DE|RTL HD'),
    ('|DE|SAT1')
;

SELECT 
    tvg_name, /* result data wanted */
    sender, /* ????? see results  and also country_and_station */
    quality, /* see output not sortable (maxable according to resolution) without custom sort/handling as F < S and also less than H */
    MAX(resolution) AS resolution, /* sortable/maxable i.e. converted to resolution */
    country_and_station
FROM (
    SELECT
        tvg_name,
        SUBSTR(tvg_name, 1, INSTR(tvg_name, '|') - 1) AS sender /*????*/,
        CASE 
            WHEN tvg_name LIKE '%4K' THEN '4K'
            WHEN tvg_name LIKE '%FHD' THEN 'FHD'
            WHEN tvg_name LIKE '%HD' THEN 'HD'
            ELSE 'SD' 
        END AS quality,
        /* Added to add column sortable according to screen resolution aka quality */
        CASE 
            WHEN tvg_name LIKE '%4K' THEN 3840 * 2160
            WHEN tvg_name LIKE '%FHD' THEN 1920 * 1080
            WHEN tvg_name LIKE '%HD' THEN 1270 * 720
            ELSE 640 * 480 
        END AS resolution,
        /* better handling of sender i.e. strip quality if quality exists so same for all station quality (!!!!!WARNING!!!!! ASSUMES SPACE BEFORE quality) */
        CASE 
            WHEN instr(tvg_name,' ') THEN substr(tvg_name,1,instr(tvg_name,' ')-1)
            ELSE tvg_name
        END AS country_and_station
    FROM derek
    WHERE tvg_name LIKE '|DE%'
) AS temp
GROUP BY country_and_station
ORDER BY resolution DESC
;
DROP TABLE IF EXISTS derek;

上面的结果（它有额外的列来显示中间结果/列），使用您所示的数据，是：

也就是说，根据你所说的输出应该是（第1列）。

当然，由于涉及的复杂性，对多个数据使用一个列是不可取的。每个数据（国家、站点、质量）一个列将大大简化上述操作。
例如：假设你有：

CREATE TABLE IF NOT EXISTS derek2 (country,station,quality);
INSERT INTO derek2 VALUES
    ('DE','ZDF',''),
    ('DE','ZDF','HD'),
    ('DE','RTL',''),
    ('DE','RTL','FHD'),
    ('DE','RTL','HD'),
    ('DE','SAT1','')
;

即3列数据
甚至更简单，如果你说转换的质量到一个排序版本的质量，如分辨率。

然后你可以使用，更简单：

SELECT '|'||country||'|'||station||' '||quality AS tvg_name, 
    MAX( CASE 
        WHEN quality = '4K'  THEN 3840 * 2160
        WHEN quality = 'FHD' THEN 1920 * 1080
        WHEN quality = 'HD' THEN 1270 * 720
        ELSE 640 * 480
    END) AS resolution
FROM derek2
WHERE country = 'DE'
GROUP BY  station
ORDER BY resolution DESC
;

然后产生：

具有优先级查询的SQLite

1条答案

相关问题

热门标签

最新问答