티스토리 뷰
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
- Oracle
- server
- 서버
- tomcat
- apache
- MySQL
- user
- table
- 리눅스
- select
- 테이블
- DATABASE
- 데이터
- java
- 윈도우
- Linux
- Windows
- Shell
- IP
- 파일
- 설정
- Toad
- eclipse
- sql
- mssql
- 백업
- 자동차
- 오라클
- delete
- DB
일 | 월 | 화 | 수 | 목 | 금 | 토 |
---|---|---|---|---|---|---|
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 |