mysql - 将count(distinct)与复杂的选择条件mysql结合起来

我想结合使用两个有效的mysql select语句：

SELECT
    t2.*,
    t1.Lang,
    Filmname,
    ColFI
FROM Timetable t2, Contenttable t1
WHERE DATE_ADD(STR_TO_DATE(CONCAT('201825',' Thursday'), '%x%v %W'), INTERVAL 0 DAY) = DateSZ
    AND RoomSZ =1 AND t2.idFI = t1.id
    AND deleteSZ = false
ORDER BY TimeSZ

在特定的一天给我（当然还有其他一些不相关的专栏）：

+----------+-------+------+------+------------+
| Filmname | time  | idFI | Lang | DateSZ     |
+----------+-------+------+------+------------+
| firstfi  | 12:00 | 22   | eng  | 2018-06-29 |
+----------+-------+------+------+------------+
| sencofi  | 15:00 | 44   | fra  | 2018-06-29 |

第二：

SELECT
    idFI,
    COUNT(DISTINCT (case when Yweek < 201825 then Yweek end)) AS Week
FROM `Timetable`
GROUP BY idFI

给我

+-------+------+
| Week  | idFI |
+-------+------+
| 2     | 22   |
+-------+------+
| 1     | 44   |

我的时间表表看起来像这样（加上更多列）：

+----------+-------+------+--------+------------+
| Filmname | time  | idFI | Yweek  | DateSZ     |
+----------+-------+------+--------+------------+
| firstfi  | 12:00 | 22   | 201825 | 2018-06-29 |
+----------+-------+------+--------+------------+
| firstfi  | 18:00 | 22   | 201824 | 2018-06-21 |
+----------+-------+------+--------+------------+
| firstfi  | 13:00 | 22   | 201823 | 2018-06-12 |
+----------+-------+------+--------+------------+
| sencofi  | 15:00 | 44   | 201825 | 2018-06-29 |
+----------+-------+------+--------+------------+
| sencofi  | 18:00 | 44   | 201823 | 2018-06-12 |
+----------+-------+------+--------+------------+
| sencofi  | 10:00 | 44   | 201823 | 2018-06-13 |

我的问题是，如果我将count（distinct）插入到第一个select语句中，那么它将无法正常工作，因为它仅对满足语句nr中所有where子句的行进行计数。 1.如何合并这些陈述？

编辑：
来自@MKhalidJunaid的答案，但不适用于我的格式：

SELECT t2.*, t1.Lang, Filmname, ColFI, t3.Week DATE_FORMAT(DateSZ, '%d.%m.%y') AS DateSZ, TIME_FORMAT(TimeSZ, '%H:%i') AS TimeSZ
FROM Timetable t2
JOIN Contenttable t1 ON t2.idFI = t1.id
JOIN (
    SELECT idFI, COUNT(DISTINCT (case when Yweek < 201825 then Yweek end)) AS Week
    FROM Timetable
    GROUP BY idFI
) t3 ON t2.idFI = t3.idFI
WHERE DATE_ADD(STR_TO_DATE(CONCAT('201825',' Thursday'), '%x%v %W'), INTERVAL 0 DAY) = DateSZ
AND RoomSZ =1
AND deleteSZ = false
ORDER BY TimeSZ

最佳答案

您可以将派生子选择用于计数查询，然后将第一个查询作为

SELECT t2.*, t1.Lang, Filmname, ColFI , t3.Week
FROM Timetable t2
JOIN Contenttable t1 ON t2.idFI = t1.id
JOIN (
    SELECT idFI, COUNT(DISTINCT (case when Yweek < 201825 then Yweek end)) AS Week
    FROM Timetable
    GROUP BY idFI
) t3 ON t2.idFI = t3.idFI
WHERE DATE_ADD(STR_TO_DATE(CONCAT('201825',' Thursday'), '%x%v %W'), INTERVAL 0 DAY) = DateSZ
AND RoomSZ =1
AND deleteSZ = false
ORDER BY TimeSZ

也不要使用旧语法来联接表，使用join关键字使用显式语法