mysql - 带有where子句的子查询mysql

Question

Hello, Im stuck with mysql subquery, this is the table I have

table detail_order
==============================
id_detail | id_order | id_toko
1         | 1        | 1
2         | 1        | 2
3         | 1        | 3
4         | 1        | 4

table ket_detail
==================================
id_ket | id_detail | id_size | qty
1      | 1         | 7       | 3
2      | 1         | 9       | 1
3      | 1         | 5       | 2
4      | 2         | 7       | 8

table size
=================================
id_size | size | id_color | stock
7       | 40   | 6        | 30
9       | 42   | 6        | 20
5       | 39   | 5        | 30

table color
==========================
id_color | color
6        | green
5        | red

我尝试在子查询中使用 where 子句显示表 ket_detail 上的数量，但是当我尝试它时子查询返回多行。这是我的查询

SELECT dt.id_detail, 
       SUM(tk.qty) AS tot_order, 
       COUNT(dm.color) AS tot_color,
      (SELECT ket.qty FROM ket_detail AS ket, t_size AS u 
       WHERE u.id_size=ket.id_size AND u.size = 40) AS size_40
FROM detail_order AS dt 
LEFT JOIN ket_detail AS tk ON tk.id_detail=dt.id_detail
LEFT JOIN t_size AS u ON u.id_size = tk.id_size
LEFT JOIN t_color AS dm ON dm.id_color=u.id_color
WHERE dt.id_order = 1
GROUP BY dt.id_detail

但是当我将大小更改为 39 时，数据是这样的

id_detail | tot_order | tot_color | size_40
============================================
1         | 6         | 2         | 2
2         | 8         | 1         | 2
3         | NULL      | 0         | 2
4         | NULL      | 0         | 2

我要的是这样的数据

id_detail | tot_order | tot_color | size_40
============================================
1         | 6         | 2         | 3
2         | 8         | 1         | 8
3         | NULL      | 0         | NULL
4         | NULL      | 0         | NULL

Answer 1

您不需要子查询来获取 size = 39 或 size = 40 数据。您可以改用条件聚合:

SELECT dt.id_detail, 
       SUM(tk.qty) AS tot_order, 
       COUNT(dm.color) AS tot_color,
       SUM(CASE 
              WHEN u.size = 39 THEN tk.qty
              ELSE 0
           END)  AS size_39,       
       SUM(CASE 
              WHEN u.size = 40 THEN tk.qty
              ELSE 0
           END)  AS size_40        
FROM detail_order AS dt 
LEFT JOIN ket_detail AS tk ON tk.id_detail=dt.id_detail
LEFT JOIN t_size AS u ON u.id_size = tk.id_size
LEFT JOIN t_color AS dm ON dm.id_color=u.id_color
WHERE dt.id_order = 1
GROUP BY dt.id_detail;

Demo here

使用子查询的正确方法是:

SELECT dt.id_detail, 
       SUM(tk.qty) AS tot_order, 
       COUNT(dm.color) AS tot_color,
      (SELECT SUM(ket.qty) 
       FROM ket_detail AS ket
       JOIN t_size AS u ON u.id_size=ket.id_size
       WHERE ket.id_detail = dt.id_detail AND u.size = 40) AS size_40
FROM detail_order AS dt 
LEFT JOIN ket_detail AS tk ON tk.id_detail=dt.id_detail
LEFT JOIN t_size AS u ON u.id_size = tk.id_size
LEFT JOIN t_color AS dm ON dm.id_color=u.id_color
WHERE dt.id_order = 1
GROUP BY dt.id_detail;

Demo here

如果您需要为多个尺码选择总数量，则必须为每个所需尺码重复子查询。因此，我认为第一个查询提供了一种更简洁、更易于扩展且更高效的解决方案。