sql - 没有join怎么写SQL查询？

Question

最近在面试的时候有人问我一个问题:如果我有一个像下面这样的表:

要求是:每天有多少订单和多少发货(基于日期列)——输出需要是这样的:

我写了下面的代码，但是面试官让我写一个没有JOIN和UNION的SQL查询，实现相同的输出。

SELECT 
    COALESCE(a.order_date, b.ship_date), orders, shipments 
FROM
    (SELECT 
         order_date, COUNT(1) AS orders 
     FROM 
         table 
     GROUP BY 1) a
FULL JOIN 
    (SELECT
         ship_date, COUNT(1) AS shipments 
     FROM table) b ON a.order_date = b.ship_date

这可能吗？各位大大可以指点一下吗？

Answer 1

您可以使用 UNION 和 GROUP BY 进行条件聚合，如下所示:

SELECT DATE_, 
       COUNT(CASE WHEN FLAG = 'ORDER' THEN 1 END) AS ORDERS,
       COUNT(CASE WHEN FLAG = 'SHIP' THEN 1 END) AS SHIPMENTS
 FROM (SELECT ORDER_DATE AS DATE_, 'ORDER' AS FLAG FROM YOUR_TABLE
       UNION ALL
       SELECT SHIP_DATE AS DATE_, 'SHIP' AS FLAG FROM YOUR_TABLE) T