SELECT * FROM (SELECT NAME,(CASE WHEN mon=1 THEN 2 ELSE '' END ) AS WEEKDAY FROM aaa1 GROUP BY NAME UNION SELECT NAME,(CASE WHEN tues=1 THEN 3 ELSE '' END ) AS WEEKDAY FROM aaa1 GROUP BY NAME UNION SELECT NAME,(CASE WHEN wend=1 THEN 4 ELSE '' END ) AS WEEKDAY FROM aaa1 GROUP BY NAME UNION SELECT NAME,(CASE WHEN thu=1 THEN 5 ELSE '' END ) AS WEEKDAY FROM aaa1 GROUP BY NAME UNION SELECT NAME,(CASE WHEN fri=1 THEN 6 ELSE '' END ) AS WEEKDAY FROM aaa1 GROUP BY NAME UNION SELECT NAME,(CASE WHEN sat=1 THEN 7 ELSE '' END ) AS WEEKDAY FROM aaa1 GROUP BY NAME UNION SELECT NAME,(CASE WHEN sun=1 THEN 1 ELSE '' END ) AS WEEKDAY FROM aaa1 GROUP BY NAME) AS bb WHERE bb.weekday <>'' ORDER BY bb.name 不知道如何合并。。
不知道3.51是否有WEEKDAY函数 select *,weekday(aweek)+2 from ( select name,mon as aweek from tt union select name,tues from tt union select name,wend from tt union select name,thu from tt union select name,fri from tt union select name,sat from tt union select name,sun from tt) a where aweek<>0
ELSE '' END
) AS WEEKDAY FROM aaa1 GROUP BY NAME
UNION
SELECT NAME,(CASE WHEN tues=1 THEN 3
ELSE '' END
) AS WEEKDAY FROM aaa1 GROUP BY NAME
UNION
SELECT NAME,(CASE WHEN wend=1 THEN 4
ELSE '' END
) AS WEEKDAY FROM aaa1 GROUP BY NAME
UNION
SELECT NAME,(CASE WHEN thu=1 THEN 5
ELSE '' END
) AS WEEKDAY FROM aaa1 GROUP BY NAME
UNION
SELECT NAME,(CASE WHEN fri=1 THEN 6
ELSE '' END
) AS WEEKDAY FROM aaa1 GROUP BY NAME
UNION
SELECT NAME,(CASE WHEN sat=1 THEN 7
ELSE '' END
) AS WEEKDAY FROM aaa1 GROUP BY NAME
UNION
SELECT NAME,(CASE WHEN sun=1 THEN 1
ELSE '' END
) AS WEEKDAY FROM aaa1 GROUP BY NAME) AS bb WHERE bb.weekday <>''
ORDER BY bb.name
不知道如何合并。。
select *,weekday(aweek)+2 from (
select name,mon as aweek from tt
union
select name,tues from tt
union
select name,wend from tt
union
select name,thu from tt
union
select name,fri from tt
union
select name,sat from tt
union
select name,sun from tt) a where aweek<>0