大家好。正如您在下面看到的，我想用不同的c.config_field_id 和 relation_type_id來計算c.key_value。所以我只想加入這兩個選擇陳述句。因此，應該顯示 3 列，它們是：
| parent_id | 計數（c.key_value）| 計數（c.key_value（與另一個 config_field_id，relation_type_id））|
請幫我解決這個問題。提前致謝
select parent_id, count(c.key_value) from relation as r
left join config_value_number as c on r.child_id = c.key_value
where c.config_field_id = 100 and relation_type_id = 150
group by parent_id
select parent_id, count(c.key_value) from relation as r
left join config_value_number as c on r.child_id = c.key_value
where c.config_field_id = 101 and relation_type_id = 151
group by parent_id
uj5u.com熱心網友回復：
您可以簡單地使用條件聚合：
select parent_id,
count(case when c.config_field_id=100 then c.key_value end) as key_value_150,
count(case when c.config_field_id=101 then c.key_value end) as key_value_151
from relation r
left join config_value_number c on r.child_id = c.key_value
where (c.config_field_id =100 and relation_type_id =150)
or (c.config_field_id =101 and relation_type_id =151)
group by parent_id
uj5u.com熱心網友回復：
您只需要加入這兩個選擇陳述句而不是實際的表作為子查詢。
SELECT parent_id, left_count, right_count
FROM (select parent_id, count(c.key_value) as left_count
from relation as r
left join config_value_number as c
on r.child_id = c.key_value
where c.config_field_id = 100 and relation_type_id = 150 group by parent_id) a
JOIN (select parent_id, count(c.key_value) as right_count
from relation as r
left join config_value_number as c
on r.child_id = c.key_value
where c.config_field_id = 101 and relation_type_id = 151 group by parent_id) b
ON a.parent_id = b.parent_id;
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/432936.html