uinon all 多个count 怎样能优化语句

[ccneal]

下面的代码的一些条件时重复的，有什么办法优化？

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-__') TEMP

[發糞塗牆]

试试

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

[-Tracy-McGrady-]

引用 1 楼 DBA_Huangzj 的回复:

试试SQL code?12345678SELECT SUM(A),SUM(B) FROM (SELECT COUNT(*) AS A,0 AS B FROM STUDENT WHERE AGE <16 AND BIRTHDAY LIKE '____-01-__' UNIONSELECT 0 AS A, COUNT(*) AS B FROM STUDENT WHERE ……

少了点数据？

[昵称被占用了]

SELECT 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(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

[Paddy]

楼主，将UNION ALL换成union union 实际是union all + distinct,也就是自动对结果集去重

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%'这种，会更好使用索引。

[ccneal]

引用 6 楼 zc10151 的回复:

楼主，将UNION ALL换成union union 实际是union all + distinct,也就是自动对结果集去重 SQL code ? 1234567891011 SELECT SUM(A),SUM(B) FROM ( SELECT COUNT(*) AS A,0 AS B FROM STUDENT WHERE AGE <16 AND BIR……

意思是日期可以写成like‘%-01-%’？感谢提醒。

[szm341]

额。。 BIRTHDAY LIKE '____-01-__'就是截取月份吧，直接用month(birthday)=1就行了 还有看不出楼主的代码为什么会有重复，union all不带distinct，效率应该比union高一点