MySQL查询以计算植物的拾取数字数量

Question

我正在尝试通过植物和皮卡登录年份获得独特的拾取数字

输入

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

我希望到达下面的桌子，以获取植物和TimePeriod的独特拾取数字，

Time period     Seattle Irving  Mini chih   Kettle
Jan-22              2     2        1           1
Feb-22              0     2        1           1

请找到DDL的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')

Answer 1

有条件聚合：

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;

请参阅