select 教师号,sum(CASE 星期号 WHEN 1 THEN 1 ELSE 0 END) AS 星期号1, sum(CASE 星期号 WHEN 2 THEN 1 ELSE 0 END) AS 星期号2, sum(CASE 星期号 WHEN 3 THEN 1 ELSE 0 END) AS 星期号3 FROM 课程表 group by 教师号语句中的0改成“”不行吗?
oracle里有个decode函数,不过你的是SqlServer,那只能用union all
select name,sum(CASE week WHEN 1 THEN 1 ELSE null END) AS 星期号1, sum(CASE week WHEN 2 THEN 1 ELSE null END) AS 星期号2, sum(CASE week WHEN 3 THEN 1 ELSE null END) AS 星期号3 FROM testhu group by name
select 教师号,sum(CASE 星期号 WHEN 1 THEN 1 ELSE null END) AS 星期一, sum(CASE 星期号 WHEN 2 THEN 1 ELSE null END) AS 星期二, sum(CASE 星期号 WHEN 3 THEN 1 ELSE null END) AS 星期三 FROM testhu group by 教师号
如果是用mysql 这个肯定是你想要的结果 SELECT aa.teacher_id, IF(SUM(CASE aa.day_num WHEN 1 THEN 1 ELSE 0 END)=0,'',SUM(CASE aa.day_num WHEN 1 THEN 1 ELSE NULL END)) AS 星期1, IF(SUM(CASE aa.day_num WHEN 2 THEN 1 ELSE 0 END)=0,'',SUM(CASE aa.day_num WHEN 2 THEN 1 ELSE NULL END)) AS 星期2, IF(SUM(CASE aa.day_num WHEN 3 THEN 1 ELSE 0 END)=0,'',SUM(CASE aa.day_num WHEN 3 THEN 1 ELSE NULL END)) AS 星期3 FROM ( SELECT teacher_id, day_num, is_free FROM test WHERE is_free =1 ) AS aa GROUP BY aa.teacher_id
isnumeric(xxx)<>0 then xxxxx else ' ' 假如星期是动态的 不是固定 的怎么写。
sum(CASE 星期号 WHEN 2 THEN 1 ELSE 0 END) AS 星期号2,
sum(CASE 星期号 WHEN 3 THEN 1 ELSE 0 END) AS 星期号3
FROM 课程表 group by 教师号语句中的0改成“”不行吗?
sum(CASE week WHEN 2 THEN 1 ELSE null END) AS 星期号2,
sum(CASE week WHEN 3 THEN 1 ELSE null END) AS 星期号3
FROM testhu group by name
sum(CASE 星期号 WHEN 2 THEN 1 ELSE null END) AS 星期二,
sum(CASE 星期号 WHEN 3 THEN 1 ELSE null END) AS 星期三
FROM testhu group by 教师号
SELECT
aa.teacher_id,
IF(SUM(CASE aa.day_num WHEN 1 THEN 1 ELSE 0 END)=0,'',SUM(CASE aa.day_num WHEN 1 THEN 1 ELSE NULL END)) AS 星期1,
IF(SUM(CASE aa.day_num WHEN 2 THEN 1 ELSE 0 END)=0,'',SUM(CASE aa.day_num WHEN 2 THEN 1 ELSE NULL END)) AS 星期2,
IF(SUM(CASE aa.day_num WHEN 3 THEN 1 ELSE 0 END)=0,'',SUM(CASE aa.day_num WHEN 3 THEN 1 ELSE NULL END)) AS 星期3
FROM (
SELECT
teacher_id,
day_num,
is_free
FROM test
WHERE is_free =1
) AS aa GROUP BY aa.teacher_id