下面的代码的一些条件时重复的,有什么办法优化?
SELECT SUM(A),SUM(B)
FROM (SELECT COUNT(*) AS A,0 AS B
FROM STUDENT WHERE AGE <16
AND BIRTHDAY LIKE '____-01-__'
UINON ALL
SELECT 0 AS A, COUNT(*) AS B
FROM STUDENT WHERE AGE<16
AND BIRTHDAY LIKE '____-02-__') TEMPunion all sql优化
SELECT SUM(A),SUM(B)
FROM (SELECT COUNT(*) AS A,0 AS B
FROM STUDENT WHERE AGE <16
AND BIRTHDAY LIKE '____-01-__'
UINON ALL
SELECT 0 AS A, COUNT(*) AS B
FROM STUDENT WHERE AGE<16
AND BIRTHDAY LIKE '____-02-__') TEMPunion all sql优化
FROM (SELECT COUNT(*) AS A,0 AS B
FROM STUDENT WHERE AGE <16
AND BIRTHDAY LIKE '____-01-__'
UNION
SELECT 0 AS A, COUNT(*) AS B
FROM STUDENT WHERE AGE<16
AND BIRTHDAY LIKE '____-02-__') TEMP
sum(case when BIRTHDAY LIKE '____-02-__' then 1 else 0 end) AS B
FROM STUDENT
WHERE AGE <16
SUM(Case when BIRTHDAY LIKE '____-01-__' then 1 Else 0 End) As A
,SUM(Case when BIRTHDAY LIKE '____-02-__' then 1 Else 0 End) As B
FROM STUDENT WHERE AGE <16
SELECT SUM(A),SUM(B)
FROM (
SELECT COUNT(*) AS A,0 AS B
FROM STUDENT
WHERE AGE <16
AND BIRTHDAY LIKE '____-01-__'
UnION
SELECT 0 AS A, COUNT(*) AS B
FROM STUDENT WHERE AGE<16
AND BIRTHDAY LIKE '____-02-__'
) TEMP
还有一点请注意,你的语句慢就慢在了 like ‘_____-01-____’这种条件上,请尽量使用like 'a%'这种,会更好使用索引。
BIRTHDAY LIKE '____-01-__'就是截取月份吧,直接用month(birthday)=1就行了
还有看不出楼主的代码为什么会有重复,union all不带distinct,效率应该比union高一点