SELECT 年级,姓名,COUNT(*) AS 学生数,
SUM(CASE 身高 WHEN '上等' THEN 1 ELSE 0 END) AS 身高上等学生数,
SUM(CASE 身高 WHEN '下等' THEN 1 ELSE 0 END) AS 身高下等学生数,
SUM(CASE WHEN ISNULL(课程A,'')='' OR ISNULL(课程B,'') = '' THEN 0 ELSE 1 END) AS 有课程学生数,
SUM(CASE ISNULL(课程A,'') WHEN '' THEN 0 ELSE 1 END) AS 有课程A学生数,
SUM(CASE ISNULL(课程B,'') WHEN '' THEN 0 ELSE 1 END) AS 有课程B学生数
FROM 表 GROUP BY 年级,姓名
SUM(CASE 身高 WHEN '上等' THEN 1 ELSE 0 END) AS 身高上等学生数,
SUM(CASE 身高 WHEN '下等' THEN 1 ELSE 0 END) AS 身高下等学生数,
SUM(CASE WHEN ISNULL(课程A,'')='' OR ISNULL(课程B,'') = '' THEN 0 ELSE 1 END) AS 有课程学生数,
SUM(CASE ISNULL(课程A,'') WHEN '' THEN 0 ELSE 1 END) AS 有课程A学生数,
SUM(CASE ISNULL(课程B,'') WHEN '' THEN 0 ELSE 1 END) AS 有课程B学生数
FROM 表 GROUP BY 年级,姓名
from 数据表
group by 年级, 性别
order by 年级, 性别
(select sum(1) from 数据表 where 年级 = A.年级 and 性别 = A.性别 and 身高 = '上等') 身高上等学生数,
(select sum(1) from 数据表 where 年级 = A.年级 and 性别 = A.性别 and 身高 = '下等') 身高下等学生数,
(select sum(1) from 数据表 where 年级 = A.年级 and 性别 = A.性别 and (课程A is not null or 课程B is not null)) 有课程学生数,
(select sum(1) from 数据表 where 年级 = A.年级 and 性别 = A.性别 and 课程A is not null) 有课程A学生数,
(select sum(1) from 数据表 where 年级 = A.年级 and 性别 = A.性别 and 课程B is not null) 有课程B学生数,
from 数据表 A
group by 年级,性别
在 access 数据库里,SUM(CASE 身高 WHEN '上等' THEN 1 ELSE 0 END) AS 身高上等学生数
这个语句好象不能用啊!
select 年级,姓名,col1,col2,col3,col4 from A,
(select count(*) as col1 from A where 身高='上等') (select count(*) from 身高='下等'),
(select count(*) as col2 from A where 课程A<>NULL or 课程B<>NULL),
(select conut(*) as col3 from A where 课程A<>NULL),
(select conut(*) as col4 from A where 课程B<>NULL)
group by 年级,性别
select 年级,姓名,col1,col2,col3,col4,col5 from A,
(select count(*) as col1 from A where 身高='上等'),
(select count(*) as col2 from 身高='下等'),
(select count(*) as col3 from A where 课程A<>NULL or 课程B<>NULL),
(select conut(*) as col4 from A where 课程A<>NULL),
(select conut(*) as col5 from A where 课程B<>NULL)
group by 年级,性别
(select count(*) as col1 from A where 身高='上等'),
(select count(*) as col2 from A where 身高='下等'),
(select count(*) as col3 from A where 课程A<>NULL or 课程B<>NULL),
(select conut(*) as col4 from A where 课程A<>NULL),
(select conut(*) as col5 from A where 课程B<>NULL)
group by 年级,性别
SUM(CASE 身高 WHEN '上等' THEN 1 ELSE 0 END) AS 身高上等学生数,
SUM(CASE 身高 WHEN '下等' THEN 1 ELSE 0 END) AS 身高下等学生数,
SUM(CASE WHEN ISNULL(课程A,'')='' OR ISNULL(课程B,'') = '' THEN 0 ELSE 1 END) AS 有课程学生数,
SUM(CASE ISNULL(课程A,'') WHEN '' THEN 0 ELSE 1 END) AS 有课程A学生数,
SUM(CASE ISNULL(课程B,'') WHEN '' THEN 0 ELSE 1 END) AS 有课程B学生数
FROM 表 GROUP BY 年级,性别
学生数=count(*),
身高上等学生数=sum(case 身高 when '上等' then 1 end),
身高下等学生数=sum(case 身高 when '下等' then 1 end),
有课程学生数=sum(case when 课程A is not null or 课程B is not null then 1 end),
有课程A学生数=sum(case when 课程A is not null then 1 end),
有课程B学生数=sum(case when 课程B is not null then 1 end)
from 数据表
group by 年级,性别