我有下面的表結構
我希望能夠選擇以下組
- 種植 1 到 3 種商品的農民
- 種植 4 到 6 種商品的農民
- 種植超過 6 種商品的農民
我的結果查詢應如下所示
我完全不知道如何進行此查詢。我試過了
SELECT count(*) AS total,
(SELECT count(farmer_id) from farmer_commodities HAVING count(commodity_id) < 3) AS grow1_3,
(SELECT count(farmer_id) from farmer_commodities HAVING count(commodity_id) BETWEEN 4 AND 6) AS grow4_6,
(SELECT count(farmer_id) from farmer_commodities HAVING count(commodity_id) > 6) as grow_above_6
from farmer_commodities
uj5u.com熱心網友回復：
嘗試這個
With data as (
SELECT farmer_id,
count(commodity_id) cnt
From table group by
farmer_id)
Select
count(*),
Count(distinct case when cnt
< 3 then farmer_id end),
Count(distinct case when cnt
BETWEEN 4 AND 6 then farmer_id end),
Count(distinct case when cnt> 6 then farmer_id end) from
Data;
uj5u.com熱心網友回復：
你能做這個嗎：
select grow1_3_commodities, grow4_6_commodities, grow_above_6_commodities from (
(select farmer_id, count(commodity_id) as grow1_3_commodities from farmer_commodities group by farmer_id HAVING count(commodity_id) < 3) AS grow1_3
join (select farmer_id, count(commodity_id) as grow4_6_commodities from farmer_commodities group by farmer_id HAVING count(commodity_id) BETWEEN 4 AND 6) AS grow4_6
join (SELECT farmer_id, count(farmer_id) as grow_above_6_commodities from farmer_commodities group by farmer_id HAVING count(commodity_id) > 6) as grow_above_6)
where grow1_3.farmer_id = grow4_6.farmer_id and grow4_6.farmer_id = grow_above_6.farmer_id);
轉載請註明出處，本文鏈接：https://www.uj5u.com/qianduan/521028.html
標籤：mysqlsql