我有一个表,里面有大量的小时数条目。我正在尝试通过日期来获取基于某些特征所花费的总时数。我的查询将竞争这项任务,但如果在某个日期没有某组特征的值,则不会返回该日期的数据。如果我使用不同的连接类型连接表,我知道如何解决这个问题,但当连接在where语句中时,我不知道如何解决这个问题。
SELECT
t1.dates,
t1.hours1,
t2.hours2,
t3.hours3,
t4.hours4
FROM
(
SELECT
dates,
SUM(times) AS hours1
FROM mytable
WHERE
characterisics1
GROUP By dates
) t1,
(
SELECT
dates,
SUM(times) AS hours2
FROM mytable
WHERE
characterisics2
GROUP By dates
) t2,
(
SELECT
dates,
SUM(times) AS hours3
FROM mytable
WHERE
characterisics3
GROUP By dates
) t3,
(
SELECT
dates,
SUM(times) AS hours4
FROM mytable
WHERE
characterisics4
GROUP By dates
) t4
WHERE
t1.dates = t2.dates and t1.dates = t3.dates and t1.dates = t4.dates
ORDER BY
t1.dates DESC发布于 2017-07-25 03:59:41
使用条件聚合。。。简单得多:
SELECT dates,
SUM(CASE WHEN characterisics1 THEN times ELSE 0 END) AS hours1,
SUM(CASE WHEN characterisics2 THEN times ELSE 0 END) AS hours2,
SUM(CASE WHEN characterisics3 THEN times ELSE 0 END) AS hours3,
SUM(CASE WHEN characterisics4 THEN times ELSE 0 END) AS hours4,
SUM(CASE WHEN characterisics5 THEN times ELSE 0 END) AS hours5
FROM mytable
GROUP By dates;https://stackoverflow.com/questions/45288986
复制相似问题