我正在嘗試按工廠和pickupmonth 年份獲取不同的取貨數量
輸入
pickdate picknumber Plant
1/3/2022 L009803 Seattle
1/3/2022 L009803 Seattle
1/3/2022 L009803 Seattle
1/20/2022 L19033 Irving
1/21/2022 KL89231 Mini chih
1/23/2022 MY12341 kettle
1/23/2022 MY12341 kettle
1/25/2022 FD1211 Seattle
1/26/2022 HJ12W1 Irving
2/11/2022 K231245 Irving
2/11/2022 K231245 Irving
2/15/2022 N12ER1 Mini chih
2/21/2022 JS1234 Irving
2/25/2022 MK12E1 Kettle
我正在尋找下表，以按工廠和時間段獲取不同的取貨數量
Time period Seattle Irving Mini chih Kettle
Jan-22 2 2 1 1
Feb-22 0 2 1 1
請找到輸入表的 DDL..
create table input
(pickdate date,
picknumber varchar(40),
plant varchar(20))
insert into input values
('1/3/2022','L009803','Seattle'),
('1/3/2022','L009803','Seattle'),
('1/3/2022','L009803','Seattle'),
('1/20/2022','L19033','Irving'),
('1/21/2022','KL89231','Mini chih'),
('1/23/2022','MY12341','kettle'),
('1/23/2022','MY12341','kettle'),
('1/25/2022','FD1211','Seattle'),
('1/26/2022','HJ12W1','Irving'),
('2/11/2022','K231245','Irving'),
('2/11/2022','K231245','Irving'),
('2/15/2022','N12ER1','Mini chih'),
('2/21/2022','JS1234','Irving'),
('2/25/2022','MK12E1','Kettle')
uj5u.com熱心網友回復：
使用條件聚合：
SELECT DATE_FORMAT(pickdate, '%b-%y') Time_period,
COUNT(DISTINCT CASE WHEN plant = 'Seattle' THEN picknumber END) Seattle,
COUNT(DISTINCT CASE WHEN plant = 'Irving' THEN picknumber END) Irving,
COUNT(DISTINCT CASE WHEN plant = 'Mini chih' THEN picknumber END) Mini_chih,
COUNT(DISTINCT CASE WHEN plant = 'kettle' THEN picknumber END) kettle
FROM input
GROUP BY Time_period
ORDER BY STR_TO_DATE(CONCAT('01-', Time_period), '%d-%b-%y');
或者：
SELECT DATE_FORMAT(pickdate, '%Y-%m') Time_period,
COUNT(DISTINCT CASE WHEN plant = 'Seattle' THEN picknumber END) Seattle,
COUNT(DISTINCT CASE WHEN plant = 'Irving' THEN picknumber END) Irving,
COUNT(DISTINCT CASE WHEN plant = 'Mini chih' THEN picknumber END) Mini_chih,
COUNT(DISTINCT CASE WHEN plant = 'kettle' THEN picknumber END) kettle
FROM input
GROUP BY Time_period
ORDER BY Time_period;
請參閱演示。
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/461710.html
標籤：mysql 通过...分组 数数 清楚的 mysql-5.6
上一篇：顯示按特定列分隔的所有列