跑马(行转列，列转行)
建表
1 CREATE TABLE RacingResults 2 ( 3 track_id CHAR(2) NOT NULL, --赛道名称 4 race_date DATE NOT NULL, --比赛日期 5 race_nbr INTEGER NOT NULL, --比赛编号 6 win_name CHAR(30) NOT NULL, --第一名马的名字 7 place_name CHAR(30) NOT NULL, --第二名------ 8 show_name CHAR(30) NOT NULL, --第三名------ 9 PRIMARY KEY(track_id,race_date,race_nbr) 10 )
如图
行转列
1 SELECT NAME,NUM, flag FROM ( 2 SELECT win_name AS NAME,COUNT(1) AS NUM, 1 AS flag FROM RacingResults a GROUP BY win_name 3 UNION ALL 4 SELECT place_name AS NAME,COUNT(1) AS NUM, 2 AS flag FROM RacingResults a GROUP BY place_name 5 UNION ALL 6 SELECT show_name AS NAME,COUNT(1) AS NUM, 3 AS flag FROM RacingResults a GROUP BY show_name
如图
列转行
1 SELECT NAME 姓名, 2 SUM(CASE WHEN flag=1 THEN NUM ELSE 0 END)第一名次数, 3 SUM(CASE WHEN flag=2 THEN NUM ELSE 0 END)第二名次数, 4 SUM(CASE WHEN flag=3 THEN NUM ELSE 0 END)第三名次数 5 FROM 6 ( 7 SELECT NAME,NUM, flag FROM ( 8 SELECT win_name AS NAME,COUNT(1) AS NUM, 1 AS flag FROM RacingResults a GROUP BY win_name 9 UNION ALL 10 SELECT place_name AS NAME,COUNT(1) AS NUM, 2 AS flag FROM RacingResults a GROUP BY place_name 11 UNION ALL 12 SELECT show_name AS NAME,COUNT(1) AS NUM, 3 AS flag FROM RacingResults a GROUP BY show_name 13 ) table1 14 ) GROUP BY NAME
如图
