select in select mysql about子查询

Question

我有一个这样的查询，哪个是解决这个查询的最好方法？

SELECT month(createdat), year(createdat), sum(quantity) from (
SELECT 
    sum(omd.quantity), omd.createdat
FROM
    order_match_detail omd, order_match om
WHERE
   omd.order_match_id = om.id
   AND omd.product_id = 144
   and om.order_status_id in (4, 5, 6, 8)
        AND NOT EXISTS( SELECT 
            1
        FROM
            order_match_detail omd1,
            order_match om1
        WHERE
            omd1.order_match_id = om1.id
                AND om1.createdAt < om.createdAt
                AND omd1.product_id = 144
                AND om1.order_status_id in (4, 5, 6, 8))) z
group by month(createdat), year(createdat) order by 2 asc, 1 asc;

它告诉我，当我运行这个查询Error Code : 1054. Unknown column 'quantity' in 'field list'时，这是预期的结果(基于小提琴)，其中product_id = 144，order_status_id in (4，5，6，8)

    +--------+------+---------------+
    | Month  | Year | Sum(Quantity) |
    +--------+------+---------------+
    |      1 | 2019 |            50 |
    |      2 | 2019 |            34 |
    +--------+------+---------------+

样本数据集及其小提琴

CREATE TABLE order_match ( id INT(11), 
                                       createdAt datetime, 
                                       order_status_id INT(10)
                               );
INSERT INTO order_match  VALUES
(1,'2019-01-01',4),
(2,'2019-01-10',5),
(3,'2019-01-20',6),
(4,'2019-02-10',7),
(5,'2019-02-15',4),
(6,'2019-02-21',5),
(7,'2019-01-02',6),
(8,'2019-01-11',8),
(9,'2019-01-18',7),
(10,'2019-02-06',5);

CREATE TABLE order_match_detail (id int(11),
                                 order_match_id INT(11), quantity int(11), product_id int(11));
INSERT INTO order_match_detail VALUES 
(1, 1, 20, 144),
(2, 2, 30, 144), 
(3, 3, 24, 100), 
(4, 4, 34, 144), 
(5, 5, 43, 120), 
(6, 6, 34, 144), 
(7, 7, 23, 442), 
(8, 8, 23, 24), 
(9, 9, 24, 12), 
(10,10, 24, 24);

http://sqlfiddle.com/#!9/81e3aa/2

Answer 1

你真的是在大吃大喝...:-(

SELECT DATE_FORMAT(o.createdat,'%Y-%m') yearmonth
     , SUM(quantity) total 
  FROM order_match o 
  JOIN order_match_detail d 
    ON d.order_match_id = o.id 
 WHERE o.order_status_id IN (4,5,6,8)
   AND d.product_id = 144
 GROUP 
    BY yearmonth;
+-----------+-------+
| yearmonth | total |
+-----------+-------+
| 2019-01   |    50 |
| 2019-02   |    34 |
+-----------+-------+

Answer 2

我认为您的问题出在第一个子查询上。您的列的名称不是“数量”，而是"sum(omd.quantity)“。您必须为子查询中的列指定别名，如下所示：

SELECT month(createdat), year(createdat), sum(quantity) from (
SELECT 
    sum(omd.quantity) AS quantity, omd.createdat
FROM ...

your :有了它，我解决了你的问题：http://sqlfiddle.com/#!9/81e3aa/5/0

但是:尽管它创建了您想要的结果，但它没有考虑到这种情况：

om1.createdAt < om.createdAt

更改数据会导致其他值，我认为结果应该保持不变：http://sqlfiddle.com/#!9/88c2e1/1/0

你认为如何？