php - 我可以在 MySQL 中使用 Distinct 和 Union All 吗？

Question

我使用 UNION ALL 从不同的表中获取值。我的代码如下。

SELECT DISTINCT datei, amount FROM
(
    SELECT datei, amount, 1 AS identification FROM income
    UNION ALL
    SELECT datee, amount, 2 AS identification FROM expense
    UNION ALL
    SELECT date, amount, 3 AS identification FROM others
) t
ORDER BY `datei` ASC

但我希望它在日期上不同。那我该怎么做呢？提前致谢。

Answer 1

要获取日期的单个金额，您可以使用以下查询

SELECT  datei,SUBSTRING_INDEX(GROUP_CONCAT(amount),',',1) amount
FROM (
    SELECT datei,amount, 1 AS identification FROM income
    UNION ALL
    SELECT datee,amount, 2 AS identification FROM expense
    UNION ALL
    SELECT DATE,amount, 3 AS identification FROM others
) t
GROUP BY datei
ORDER BY `datei` ASC