若t2有数据,t1没有,sql执行,也想返回数据 ,怎么修改
时间: 2024-02-27 19:51:24 浏览: 39
如果 t1 表中没有数据,那么 CROSS JOIN 之后的结果集就会为空,因此返回的结果也会是空。为了让有数据返回,可以将 CROSS JOIN 改成 RIGHT JOIN,这样即使 t1 表中没有数据,t2 表中的数据也会被返回。修改后的 SQL 查询语句如下:
```
SELECT t1.*, t2.*
FROM (
SELECT COUNT(1) AS order_quantity,
SUM(IFNULL(amounts_payable, 0)) AS order_amount,
(ROUND(SUM(IFNULL(amounts_payable, 0)) / COUNT(1), 2)) AS order_average_unit_price,
COUNT(IF(product_type = 1, 1, NULL)) AS experience_product_order,
SUM(IF(product_type = 1, IFNULL(amounts_payable, 0), NULL)) AS experience_order_amount,
COUNT(IF(product_type = 2, 1, NULL)) AS formal_product_order,
SUM(IF(product_type = 2, IFNULL(amounts_payable, 0), NULL)) AS formal_order_amount,
"1" AS param
FROM `order_management`
WHERE order_status = 1
AND DATE_FORMAT(create_time, '%Y-%m-%d') = DATE_FORMAT(NOW(), '%Y-%m-%d')
GROUP BY DATE_FORMAT(create_time, '%Y-%m-%d')
) t1
RIGHT JOIN (
SELECT COUNT(IF(student_type = 0, 1, NULL)) AS registered_student_num,
COUNT(IF(student_type = 1, 1, NULL)) AS experience_student_num,
COUNT(IF(student_type = 2, 1, NULL)) AS regular_student,
"1" AS param
FROM student_record
WHERE DATE_FORMAT(create_time, '%Y-%m-%d') = DATE_FORMAT(NOW(), '%Y-%m-%d')
GROUP BY DATE_FORMAT(create_time, '%Y-%m-%d')
) t2
ON t1.param = t2.param
```
这样修改后,即使 t1 表中没有数据,t2 表中的数据也会被返回,同时 t1 表中的列值都会被设置为 NULL。