mysql - 理解下面的MySQL逻辑

Question

这是一段 MySQL 代码，在其中一个网站的后台运行。

我试图理解 new 列的含义。任何拥有单个订单的客户都会被标记为新客户 = 1 吗？

代码如下:

select
a.id_order,
IF((
        SELECT so.id_order 
        FROM `orders` so 
        WHERE so.id_customer = a.id_customer 
        AND 
        so.id_order < a.id_order 
        LIMIT 1) > 0, 0, 1) as new               // new keyword

        FROM `orders` a 

        LEFT JOIN `customer` c ON 
       (c.`id_customer` = a.`id_customer`)

Answer 1

首先，让我假设 id_order 永远不会为负数或零。

如果客户的订单 ID 较小，则查询将返回“0”。目的是标记第一个订单。

如果客户只有一个订单，则其值始终为 1，因为子查询将返回 NULL。

我会写逻辑:

SELECT o.id_order,
       (NOT EXISTS (SELECT 1
                    FROM orders so
                    WHERE so.id_customer = o.id_customer AND
                          so.id_order < o.id_order
                   )
       ) as is_first_order
FROM orders o LEFT JOIN
     customer c
     ON c.id_customer = o.id_customer;

如果所有订单都有客户，则不需要 LEFT JOIN。事实上，JOIN根本没有必要，你可以只使用:

select o.id_order,
       (NOT EXISTS (SELECT 1
                    FROM orders so
                    WHERE so.id_customer = o.id_customer AND
                          so.id_order < o.id_order
                   )
       ) as is_first_order
FROM orders o ;