php - SQL使用是或否获取两列

我有一个查询，在这里我选择所有sendIntakes并将其按正式ID和年份分组。

sendResponse列是否已响应，0为否，1为是。

                     SELECT officiantId, COUNT(*) as ct, sendResponse,
                     Year(created_at) as yr
                     FROM sendIntakes
                     Where sendResponse is not null

                     GROUP BY officiantId, sendResponse, YEAR(created_at)
                     ORDER BY officiantId, yr
                     DESC;

我进行了计数，但是如果计数为0/1，它将进行新的一行

officiant id - 404 | ct - 2 | sendResponse 1 | yr 2017
officiant id - 404 | ct - 1 | sendResponse 0 | yr 2017
officiant id - 547 | ct - 1 | sendResponse 1 | yr 2017

有没有办法摆脱sendResponse，并将其作为

officiant id - 404 | ct - 2 | Yes - 1 | No - 1 | yr 2017

最佳答案

您可以通过删除GROUP BY中的sendResponse来计数每次出现的是和否（1或0）

SELECT officiantId, COUNT(*) as ct,
SUM(CASE WHEN sendResponse = 1 THEN 1 ELSE 0 END) as Yes,
SUM(CASE WHEN sendResponse = 0 THEN 1 ELSE 0 END) as No,
Year(created_at) as yr
FROM sendIntakes
Where sendResponse is not null

GROUP BY officiantId, YEAR(created_at)
ORDER BY officiantId, yr
DESC;

关于php - SQL使用是或否获取两列，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/47820161/