MySQL UNION 组合来自同一个表的数据

Question

所以我有一张产品销售表。

表：

• id
• 名称
• 价格
• 金额
• 日期

我试图获取每天商品的平均价格。如果我分别运行我的两个查询，它们就会工作，我一整天都在试图解决这个问题，但我似乎无法弄清楚。这是我的查询。

    SELECT id, date, name, AVG(price) as today FROM sales 
        WHERE DATE(FROM_UNIXTIME(date)) = (CURRENT_DATE()) AND id = 1 GROUP BY id
    UNION 
    SELECT id, date, name, AVG(price) as yesterday FROM sales 
        WHERE DATE(FROM_UNIXTIme(date)) = (CURRENT_DATE() - INTERVAL 1 DAY) AND id = 1 GROUP BY id) a group by id 

我需要它显示为，

[{"id":1,"name":"test","today":200, "yesterday": 100}]

我可以得到它，这样它两者都显示，但由于某种原因它会显示 [{"id":1,"date":1645881110,"name":"测试","今天":200},{"id":1,"date":1645881110,"name":" test","today":100}]

不确定为什么它显示“今天”，即使它获取了几天的正确数据。

如果有人能指出我正确的方向，那就太好了，非常感激。

Answer 1

您的 "today" 字符串是列名称。 UNION 操作生成的结果集中的列名由联合中的第一个查询设置。这就是为什么第二个查询中的 AVG(price) 值的名称为 “today”。

您似乎希望今天和昨天的平均价格显示在结果集的同一行上。如果这就是您想要的，则 UNION 不是执行此操作的方法：它们的目的是将行添加到结果集中，而您想要添加一列。

尝试这样的事情。

SELECT 
  id, date, name, 
  AVG(IF(DATE(FROM_UNIXTIME(date)) = (CURRENT_DATE(), price, NULL) ) as today,
  AVG(IF(DATE(FROM_UNIXTIME(date)) = (CURRENT_DATE() - INTERVAL 1 DAY, price, NULL) ) as yesterday
FROM sales 
WHERE DATE(FROM_UNIXTIME(date)) BETWEEN CURRENT_DATE() - INTERVAL 1 DAY
                                    AND CURRENT_DATE()
  AND id = 1
GROUP BY id;

当条件为真时，表达式 AVG(condition, value, NULL) 生成值的平均值。第一个条件表示“今天”，第二个条件表示“昨天”。

这称为 pivot 操作。正如您所看到的，这在 MySQL 中是一个相当大的混乱。

Answer 2

SQL UNION 需要相同数量的列并将它们混合到结果集中，但它使用第一个选择的列名，即使列名与其他列名不同，这就是为什么只有“今天”。

UNION 会将每个 SELECT 结果的所有行返回到同一结果集中，但不一定只返回到一行。要实现您想要的效果，您应该尝试以下操作：

SELECT
name, 
(select AVG(st.price) from sales st where st.date = (CURRENT_DATE()) and st.name = s.name group by st.name) as today, 
(select AVG(sy.price) from sales sy where sy.date = (CURRENT_DATE() - INTERVAL 1 DAY) and sy.name = s.name group by sy.name) as yesterday 
FROM sales s
group by s.name

随意在键入时添加 DATE(FROM_UNIXTIme()) 。

在这种情况下，通过聚合打印 id 和日期是没有意义的。

英语不是我的母语，如果您不明白什么，请再问我。

我建议您阅读以下内容： https://www.w3schools.com/sql/sql_union.asp