診療年月 |Day_1|Day_2|Day_3|Day_4|Day_5|Day_6|Day_7|Day_8|Day_9|Day_10 ... | Day_31 |
200904 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 1 | 0 | 0 ... | 0 |
200905 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 ... | 0 |
200906 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 ... | 0 |
200907 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 ... | 0 |
200908 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 ... | 1 |
... |... |... |... |... |... |... |... |... |... |... |... |化列为行 結果以下 診療年月日
20090405
20090408
20090502
20090710
20090831
200904 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 1 | 0 | 0 ... | 0 |
200905 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 ... | 0 |
200906 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 ... | 0 |
200907 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 ... | 0 |
200908 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 ... | 1 |
... |... |... |... |... |... |... |... |... |... |... |... |化列为行 結果以下 診療年月日
20090405
20090408
20090502
20090710
20090831
200904 1 0 1 0
200905 0 0 0 0 0 0
200906 1 0 0 0 0 0
200907 0 0 0 0 0 1
… … … … … … …结果如下:诊疗年月日
20090401
20090403
20090601
20090731
200904 1 0 1 0 ... 0
200905 0 0 0 0 ... 0
200906 1 0 0 0 ... 0
200907 0 0 0 0 ... 1
… … … … … … … 结果如下: 诊疗年月日
20090401
20090403
20090601
20090731
SELECT
CASE day_1 WHEN 1 THEN CONCAT(诊断年月,'01') END AS yes
FROM tabl
UNION
SELECT
CASE day_2 WHEN 1 THEN CONCAT(诊断年月,'02') END AS yes
FROM tabl
UNION
SELECT
CASE day_3 WHEN 1 THEN CONCAT(诊断年月,'03') END AS yes
FROM tabl
UNION
SELECT
CASE day_4 WHEN 1 THEN CONCAT(诊断年月,'04') END AS yes
FROM tabl) t
WHERE t.yes IS NOT NULL;
列导航要用union
例如:建一个临时表 RowCount=31 从1 到 31 然后 LEFT JOIN