mysql语句优化

[huaiping]

centos 5.4 + mysql 5.1.48 + php 5.5





选课统计，物理wl 化学hx 生物sw 政治zz 历史ls 地理dl，现在需要分别取出选择各科的总人数，然后用php输出
SELECT count(id) AS wl FROM courses WHERE wl IS NOT NULL;
SELECT count(id) AS hx FROM courses WHERE hx IS NOT NULL;
SELECT count(id) AS sw FROM courses WHERE sw IS NOT NULL;
SELECT count(id) AS zz FROM courses WHERE zz IS NOT NULL;
SELECT count(id) AS ls FROM courses WHERE ls IS NOT NULL;
SELECT count(id) AS dl FROM courses WHERE dl IS NOT NULL;

如果用以上语句的话，需要查询6次数据库，不知道能优化成一个语句吗？

[huaiping]

/*------- CREATE SQL---------*/ CREATE TABLE `swan_course` ( `id` int(10) NOT NULL AUTO_INCREMENT, `xh` varchar(10) NOT NULL, `xm` varchar(12) NOT NULL, `wl` varchar(3) NOT NULL, `hx` varchar(3) DEFAULT NULL, `sw` varchar(3) DEFAULT NULL, `zz` varchar(3) DEFAULT NULL, `ls` varchar(3) DEFAULT NULL, `dl` varchar(3) DEFAULT NULL, `courses` varchar(30) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `xh` (`xh`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

[吉普赛的歌]

select 
sum(case when wl IS NOT NULL then 1 else 0 end) as wl
,sum(case when hx IS NOT NULL then 1 else 0 end) as hx
,sum(case when sw IS NOT NULL then 1 else 0 end) as sw
,sum(case when zz IS NOT NULL then 1 else 0 end) as zz
,sum(case when ls IS NOT NULL then 1 else 0 end) as ls
,sum(case when dl IS NOT NULL then 1 else 0 end) as dl
from `swan_course`;