将两个查询结果合并到一个表中，但合并到两列中

6kkfgxo0 于 2021-07-24 发布在 Java

关注(0)|答案(3)|浏览(392)

小提琴

CREATE TABLE sales (
    id int auto_increment primary key,
    orderID VARCHAR(255),
    event_date DATE,
    sold_quantity INT,
    return_quantity INT
);

INSERT INTO sales
(orderID, event_date, sold_quantity, return_quantity
)
VALUES 

("Order_01", "2020-01-25", "500", "0"),
("Order_02", "2020-01-25", "700", "0"),
("Order_03", "2020-03-24", "800", "0"),

("Order_01", "2020-02-10", "0", "400"),
("Order_02", "2020-04-09", "0", "300"),
("Order_03", "2020-04-09", "0", "100");

预期结果：

event_date         sold_orders       returned_orders
2020-01-25            2                   0
2020-03-24            1                   0
2020-02-10            0                   1
2020-04-09            0                   2

在上表中我有 sales 以及相应的 returns .
现在，我想 COUNT 这个 sales 以及 returns 每 event_date 并在两列中显示它们。
因此，我尝试使用以下查询：

SELECT
event_date,
COUNT(DISTINCT OrderID) AS sold_orders
FROM sales
GROUP BY 1

UNION ALL

SELECT
event_date,
COUNT(DISTINCT OrderID) AS returned_orders
FROM sales
GROUP BY 1;

已经有了 merges 它们在一个表中，但如何修改查询，使其显示在两个不同的列中？

sql mysql

来源：https://stackoverflow.com/questions/63033673/merge-two-query-results-in-one-table-but-in-two-columns

3条答案

按热度按时间

ryoqjall1#

看起来您想对没有值的行进行计数 0 . 所以：

select event_date, sum(sold_quantity > 0) as num_sales, sum(return_quantity > 0) as num_returns
from sales
group by event_date;

这是小提琴。

赞(0）回复(0）举报 2021-07-24

5fjcxozz2#

如果两个查询需要分开，您可以使用以下查询：
小提琴

SELECT
event_date,
(CASE WHEN quantity_type = 'sold' THEN quantity ELSE 0 END) AS sold_quantity,
(CASE WHEN quantity_type = 'returns' THEN quantity ELSE 0 END) AS return_quantity

FROM

  (SELECT
  'sold' AS quantity_type,
  event_date,
  COUNT(DISTINCT OrderID) AS quantity
  FROM sales
  WHERE sold_quantity > 0
  GROUP BY 1,2

  UNION ALL

  SELECT
  'returns' AS quantity_type,
  event_date,
  COUNT(DISTINCT OrderID) AS quantity
  FROM sales
  WHERE return_quantity >0
  GROUP BY 1,2) t1

GROUP BY 1;

赞(0）回复(0）举报 2021-07-24

2sbarzqh3#

如果订单在特定日期售出或退回？通过销售数量和退货数量栏来知道。然而，我们不想求数量的总和，而是求每一个事件的总和，按日期分组。因此，我首先分别基于当前值是否大于0将列标记为0或1。然后对导出的值进行简单求和，就会得到结果

select event_date, sum(if(sold_quantity > 0, 1, 0)) as sold_orders, 
sum(if(return_quantity > 0, 1, 0)) as returned_orders
from sales
group by 1;

赞(0）回复(0）举报 2021-07-24

我来回答

将两个查询结果合并到一个表中，但合并到两列中

3条答案

相关问题

热门标签

最新问答