mysql - 从具有不同值的单个表中多次选择一列

Question

您好，我有一个问题，因为我遇到了一个 sql 查询。我写了这个 sql 查询:

SELECT DISTINCT Date(createddate) as Date,
       (SELECT maxage FROM wob_stock_age 
        WHERE (maxage = a.maxage) AND (totetype='B') 
        GROUP BY Date)AS Blue,

       (SELECT maxage FROM wob_stock_age 
        WHERE (maxage = a.maxage) AND (totetype='V') 
        GROUP BY Date)AS Yellow,

       (SELECT maxage FROM wob_stock_age 
        WHERE (maxage = a.maxage) AND (totetype='N') 
        GROUP BY Date)AS Pink
FROM wob_stock_age as a

如您所见，我在不同的别名下多次选择同一列。然而，结果并不是我所期望的，因为我得到了 Null 值。

更新: 实际上，我想为每个日期获取一行，并希望为一个日期获取一行中的所有“totetype”粉红色、蓝色和黄色。而不是一个日期的 3 行。

那么结果就是

    |Date       |Blue|Yellow|Pink|
    ------------------------------
    |2016-02-16 |153 |27    |40  |
    ------------------------------
    |2016-02-17 |152 |26    |40  |

Answer 1

您可以使用条件聚合来做到这一点。您的问题不清楚，但我对您尝试做的事情的最佳猜测是:

SELECT maxage,
       SUM(CASE WHEN totetype = 'B' THEN 1 ELSE 0 END) as Blue,
       SUM(CASE WHEN totetype = 'V' THEN 1 ELSE 0 END) as Yellow,
       SUM(CASE WHEN totetype = 'N' THEN 1 ELSE 0 END) as Pink
FROM wob_stock_age as a
GROUP BY maxage
ORDER BY maxage ;