SQL Query for When Then and Count

Question

我有一个表

ID |Code   |Start      |End
1  | SKX007| 2020-06-01|2020-06-11
2  | SRP120| 2020-05-01|2020-05-01
3  | SKX454| 2020-05-01|2020-06-21
4  | SKX471| 2020-06-11|2020-06-15

我试图得到我打印代码的前3个字母和它的计数开始或结束在六月。

Code |"Count Code in June"
SKX  |  3

目前我有这个

SELECT 
CASE 
WHEN Code LIKE 'SKX%'
THEN 'SKX'
WHEN Code LIKE 'SRP%'
THEN 'SRP'
END AS 'Start Code'
SELECT COUNT(Code) AS "Count Code in June" from Loan
WHERE MONTH(Start) = 6 or MONTH(End) = 6;

如何合并这两个语句?

Answer 1

合并语句最简单的方法是

SELECT COUNT(CASE 
WHEN Code LIKE 'SKX%'
THEN 'SKX'
WHEN Code LIKE 'SRP%'
THEN 'SRP'
END ) AS "Count Code in June", CASE 
WHEN Code LIKE 'SKX%'
THEN 'SKX'
WHEN Code LIKE 'SRP%'
THEN 'SRP'
END AS 'Start Code' from Loan
WHERE MONTH(Start) = 6 or MONTH(End) = 6
group by CASE 
WHEN Code LIKE 'SKX%'
THEN 'SKX'
WHEN Code LIKE 'SRP%'
THEN 'SRP'
END 

Answer 2

我试图得到我打印代码的前3个字母和它的计数开始或结束在六月。

我建议:

select left(code, 3) as code_3, count(*)
from loan
where month(start) = 6 or month(end) = 6
group by code_3;

注意:想要月份没有年份是非常非常罕见的。如果你想要2020年6月，那么你需要:

select left(code, 3) as code_3, count(*)
from loan
where (start >= '2020-06-01' and start < '2020-07-01') or
(end >= '2020-06-01' and end < '2020-07-01')
group by code_3;

那么这将不包括在6月活动的但在之前开始并在之后结束的行。所以你可能真的想要一些重叠:

select left(code, 3) as code_3, count(*)
from loan
where start < '2020-07-01' and
end >= '2020-06-01'
group by code_3;