mysql - 按首选值列表对SQL进行分组

首先:我不太确定要在问题标题中加上什么，我不知道如何调用这样的查询，也许这就是为什么我找不到任何答案的原因。

我有一张电台表和一张流表。每个 radio 台可以具有多个流，用于不同的格式，比特率等。
我想获取所有工作站的列表，并以给定应用程序的首选格式显示流。

现在这很棘手，我希望首选格式是列表，并且我的数据库应首先返回合适的流。

所以我可能会有一个这样的列表:(“MP3”，“AAC”，“OGG”)

然后，我希望MySQL为每个工作站返回类型为'MP3'的流，但是如果不存在，则应为该工作站返回'AAC'流，依此类推。
如果找不到合适的流，则不应返回al。

示例:

CREATE TABLE `stations` (
  `id` INT(11),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE `streams` (
  `id` INT(11),
  `station` INT(11),
  `media_type` ENUM('MP3', 'OGG', 'AAC', 'Flash'),
  PRIMARY KEY (`id`),
  KEY (`station`),
  CONSTRAINT `fk_1` FOREIGN KEY (`station`) REFERENCES `stations` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

INSERT INTO `stations` (`id`) VALUES (1), (2), (3);
INSERT INTO `streams` (`id`, `station`, `media_type`) VALUES (1, 1, 'MP3'), (2, 1, 'AAC'), (3, 2, 'Flash'), (4, 2, 'AAC'), (5, 3, 'Flash');

我做了一个SQLFiddle here

如果首选媒体类型列表是('MP3', 'AAC')，那么使用上面的示例数据的期望结果应该是:

station stream  type
1       1       MP3
2       4       AAC

站1应该具有MP3类型的流1(也支持AAC，但比AAC首选MP3)

站2应该具有AAC类型的流4(站2不提供MP3，但AAC是)

Station 3不应出现在结果中，因为它仅通过Flash提供流

我尝试了这个:

SELECT
    st.id AS station_id,
    str.id AS stream_id,
    str.media_type,
    FIELD(str.media_type, 'MP3', 'AAC') AS preference
FROM
    stations st
LEFT JOIN
    streams str ON str.station = st.id
GROUP BY
    st.id
HAVING
    MIN(preference)

但这仅返回1或0条记录，具体取决于流表中的第一条记录是首选的媒体类型，我不明白为什么。

我能找到的唯一解决方案是使用子查询对流进行排序，然后按station_id进行分组，如下所示:

SELECT sub.* FROM
    (SELECT
        st.id AS station_id,
        str.id AS stream_id,
        str.media_type
    FROM
        stations st
    LEFT JOIN
        streams str ON str.station = st.id
    WHERE
        str.media_type IN ('MP3', 'AAC')
    ORDER BY
        FIELD(str.media_type, 'MP3', 'AAC')
    ) AS sub
GROUP BY sub.station_id

但这将导致由子查询创建的临时表的全表扫描，性能是 Not Acceptable 。由于我们无法限制内部查询(因为尚未将其分组)，因此临时表将变得非常大。

B.T.W.，我正在运行MySQL 5.6

那么，应该使用哪种查询来处理首选属性列表？

最佳答案

如果只想返回存在'MP3'或'AAC'的行，则不需要外部联接。

这是一个标准SQL解决方案，可以在mysql中按原样工作，请参见fiddle:

SELECT
   st.id AS station_id,
   COALESCE(MAX(CASE WHEN str.media_type = 'MP3' THEN str.id END)
           ,MAX(CASE WHEN str.media_type = 'AAC' THEN str.id END)
           ) AS stream_id,
   COALESCE(MAX(CASE WHEN str.media_type = 'MP3' THEN str.media_type END)
           ,MAX(CASE WHEN str.media_type = 'AAC' THEN str.media_type END)
           ) AS media_type
FROM stations st
JOIN streams str
  ON str.station = st.id
WHERE -- only stations with the requested media types
   str.media_type IN ('MP3', 'AAC')
GROUP BY st.id

添加更多的媒体类型很容易，主要是剪切和粘贴。
COALESCE根据CASE的顺序返回第一个匹配的媒体类型。

关于mysql - 按首选值列表对SQL进行分组，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/32080957/