25
社区成员
发帖
与我相关
我的任务
分享
SELECT
type,
GROUP_CONCAT(周一 SEPARATOR ',') as '周一',
GROUP_CONCAT(周二 SEPARATOR ',') as '周二',
GROUP_CONCAT(周三 SEPARATOR ',') as '周三',
GROUP_CONCAT(周四 SEPARATOR ',') as '周四',
GROUP_CONCAT(周四 SEPARATOR ',') as '周四'
FROM(
SELECT
'下午' AS type,
CASE WHEN 星期='周一' AND 上午='有课' THEN 科目 ELSE NULL END AS '周一',
CASE WHEN 星期='周二' AND 上午='有课' THEN 科目 ELSE NULL END AS '周二',
CASE WHEN 星期='周三' AND 上午='有课' THEN 科目 ELSE NULL END AS '周三',
CASE WHEN 星期='周四' AND 上午='有课' THEN 科目 ELSE NULL END AS '周四',
CASE WHEN 星期='周五' AND 上午='有课' THEN 科目 ELSE NULL END AS '周五'
FROM T20191202) as 上午
GROUP BY type
UNION
SELECT
type,
GROUP_CONCAT(周一 SEPARATOR ',') as '周一',
GROUP_CONCAT(周二 SEPARATOR ',') as '周二',
GROUP_CONCAT(周三 SEPARATOR ',') as '周三',
GROUP_CONCAT(周四 SEPARATOR ',') as '周四',
GROUP_CONCAT(周四 SEPARATOR ',') as '周四'
FROM(
SELECT
'下午' AS type,
CASE WHEN 星期='周一' AND 下午='有课' THEN 科目 ELSE NULL END AS '周一',
CASE WHEN 星期='周二' AND 下午='有课' THEN 科目 ELSE NULL END AS '周二',
CASE WHEN 星期='周三' AND 下午='有课' THEN 科目 ELSE NULL END AS '周三',
CASE WHEN 星期='周四' AND 下午='有课' THEN 科目 ELSE NULL END AS '周四',
CASE WHEN 星期='周五' AND 下午='有课' THEN 科目 ELSE NULL END AS '周五'
FROM T20191202
) AS 下午
GROUP BY type
SELECT
时段,
COALESCE(周一, '无课') AS 周一,
COALESCE(周二, '无课') AS 周二,
COALESCE(周三, '无课') AS 周三,
COALESCE(周四, '无课') AS 周四,
COALESCE(周五, '无课') AS 周五
FROM
(
SELECT
CASE WHEN 上午 = '有课' THEN '上午' ELSE '下午' END AS 时段,
星期,
科目
FROM
T20191202
) AS SourceTable
PIVOT
(
MAX(科目)
FOR 星期 IN ([周一], [周二], [周三], [周四], [周五])
) AS PivotTable;
SELECT
'上午' AS TYPE,
MAX(CASE WHEN 星期 = '周一' AND 上午 = '有课' THEN 科目 ELSE '无课' END) AS 周一,
'无课' AS 周二,
MAX(CASE WHEN 星期 = '周三' AND 上午 = '有课' THEN 科目 ELSE '无课' END) AS 周三,
'无课' AS 周四,
'无课' AS 周五
FROM
T20191202
UNION ALL
SELECT
'下午' AS TYPE,
CONCAT_WS(',', MAX(CASE WHEN 星期 = '周一' AND 下午 = '有课' THEN 科目 ELSE NULL END), MAX(CASE WHEN 星期 = '周一' AND 下午 = '有课' THEN 科目 ELSE NULL END)) AS 周一,
'无课' AS 周二,
CONCAT_WS(',', MAX(CASE WHEN 星期 = '周三' AND 下午 = '有课' THEN 科目 ELSE NULL END), MAX(CASE WHEN 星期 = '周三' AND 下午 = '有课' THEN 科目 ELSE NULL END)) AS 周三,
'无课' AS 周四,
'无课' AS 周五
FROM
T20191202;
你这结果也不对吧,科目也是一个分类的字段,被归类的信息应该是:有课和空吧