티스토리 뷰
SELECT ym,
MIN(CASE dw WHEN 1 THEN LPAD(d,2,'0') END) Sun,
MIN(CASE dw WHEN 2 THEN LPAD(d,2,'0') END) Mon,
MIN(CASE dw WHEN 3 THEN LPAD(d,2,'0') END) Tue,
MIN(CASE dw WHEN 4 THEN LPAD(d,2,'0') END) Wed,
MIN(CASE dw WHEN 5 THEN LPAD(d,2,'0') END) Thu,
MIN(CASE dw WHEN 6 THEN LPAD(d,2,'0') END) Fri,
MIN(CASE dw WHEN 7 THEN LPAD(d,2,'0') END) Sat
FROM (SELECT date_format(dt, '%Y%m') ym,
Week(dt) w,
Day(dt) d,
DayofWeek(dt) dw
FROM (SELECT CONCAT(y, '0101') + INTERVAL a * 100 + b * 10 + c DAY dt
FROM (SELECT 0 a
UNION ALL
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3) a,
(SELECT 0 b
UNION ALL
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5
UNION ALL
SELECT 6
UNION ALL
SELECT 7
UNION ALL
SELECT 8
UNION ALL
SELECT 9) b,
(SELECT 0 c
UNION ALL
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5
UNION ALL
SELECT 6
UNION ALL
SELECT 7
UNION ALL
SELECT 8
UNION ALL
SELECT 9) c,
(SELECT '2021' y) d
WHERE a * 100 + b * 10 + c < DayOfYear(CONCAT(y, '1231'))) a) a
GROUP BY ym, w
'Database' 카테고리의 다른 글
[MYSQL]월별 영업일(일정테이블) (0) | 2022.04.26 |
---|---|
[DB]Centos 7 MariaDB 설치 및 실행 (0) | 2022.02.21 |
[ORACLE]ORDER BY 순서 정렬 (0) | 2019.04.03 |
[ORACLE]ROW_NUMBER 순서 (0) | 2018.07.03 |
[Oracle] 누적접수건수 (0) | 2018.06.27 |
- Total
- Today
- Yesterday
- 설정
- Shell
- Toad
- Oracle
- 서버
- 오라클
- tomcat
- DATABASE
- user
- 테이블
- eclipse
- IP
- java
- Windows
- delete
- table
- MySQL
- DB
- 리눅스
- 자동차
- server
- 데이터
- sql
- 윈도우
- 백업
- apache
- select
- Linux
- 파일
- mssql
일 | 월 | 화 | 수 | 목 | 금 | 토 |
---|---|---|---|---|---|---|
1 | 2 | 3 | 4 | 5 | 6 | |
7 | 8 | 9 | 10 | 11 | 12 | 13 |
14 | 15 | 16 | 17 | 18 | 19 | 20 |
21 | 22 | 23 | 24 | 25 | 26 | 27 |
28 | 29 | 30 |