mysql - 选择不同条件的 2 列

Question

我需要创建一个查询，以每周分组形式提供 2019 年已完成且未付款的交易。我已经创建了查询来生成已完成的交易，但是当尝试将未付款交易查询合并到已完成的交易查询中时，我卡住了

这是已完成交易的查询

SELECT WEEK(A.plat_create_time, 1) AS 'Week Create Time',
       COUNT(t1.lp_sign_time) AS 'Completed Order'
FROM deli_order A
LEFT JOIN
(
    SELECT order_code, code, lp_sign_time
    FROM pg_send_package 
    UNION
    SELECT D.order_code, D.oms_code, C.cm_sign_time
    FROM pg_package C
    INNER JOIN pg_order D ON C.pg_order_id = D.id
) t1 ON t1.order_code = A.order_code
        AND t1.code = A.code
        AND YEAR(A.plat_create_time) = 2019
WHERE (YEAR(A.plat_create_time) = 2019) AND A.status = 6 AND t1.lp_sign_time IS NOT NULL
GROUP BY WEEK(A.plat_create_time, 1);   

它会生成这样的东西

Week | Completed Order
1         886
2         734
3         868
4         1000

这是我已经尝试生成已完成和未付款交易的查询

SELECT WEEK(A.plat_create_time, 1) AS 'Week Create Time',
       COUNT(t1.lp_sign_time) AS 'Completed Order',
       COUNT(t2.plat_create_time) AS 'Unpaid Order'
FROM deli_order A
LEFT JOIN
(
    SELECT order_code, code, lp_sign_time
    FROM pg_send_package 
    UNION
    SELECT D.order_code, D.oms_code, C.cm_sign_time
    FROM pg_package C
    INNER JOIN pg_order D ON C.pg_order_id = D.id
) t1 ON t1.order_code = A.order_code
        AND t1.code = A.code
        AND YEAR(A.plat_create_time) = 2019
LEFT JOIN
(   
    SELECT order_code, WEEK(plat_create_time,1) AS 'Create Time'
    FROM deli_order
    WHERE pay_state = 0 AND (YEAR(plat_create_time) = 2019)
) t2 ON t2.order_code = A.order_code
WHERE (YEAR(A.plat_create_time) = 2019) AND A.status = 6 AND t1.lp_sign_time IS NOT NULL
GROUP BY WEEK(A.plat_create_time, 1);

但是当我执行它时，MySQL总是给出错误消息“字段列表中未知列't2.plat_create_time'”。我的预期结果是这样的

Week | Completed Order | Unpaid Order
1         886              51
2         734              42
3         868              40
4         1000             31

我应该在查询中更改什么？

Answer 1

更改:

SELECT order_code, WEEK(plat_create_time,1) AS 'Create Time'

至:

SELECT order_code, WEEK(plat_create_time,1) AS 'Create Time', plat_create_time