728x90
๋ฐ์ํ
๐ ์ผ
์ผ๋ณ ํต๊ณ๊ฐ ๊ถ๊ตผํ ๋๋ Group By ๋ก date_format ์ day ๊น์ง ์ค์ ํ๊ธฐ.
<!-- ์ผ ํต๊ณ -->
<select id = "getLabellingStatusByDay" ... >
SELECT COUNT(T1.seq) AS cnt
, date_format(T1.day, '%Y-%m-%d') AS finDate
FROM DEVEL_METADATA T1
GROUP BY date_format(T1.day, '%Y-%m-%d')
</select>
๐ ์ฃผ
์ฃผ๋ณ ํต๊ณ๊ฐ ๊ถ๊ตผํ ๋๋ Group By ์ WEEK ๋ฅผ ์ด์ฉํ๊ธฐ.
์ฃผ๋ณ ํํฉ์ด ๋์จ๋น.
<!-- ์ฃผ ํต๊ณ -->
<select id = "getLabellingStatusByWeek" ...>
SELECT COUNT(T1.doc_nm) AS cnt
, CONCAT( WEEK(T1.fin_date, 7), ' ์ฃผ') AS finDate
FROM DEVEL_METADATA T1
GROUP BY WEEK(T1.fin_date)
</select>
๐ ์
์ ํต๊ณ๋ ์ผ ํต๊ณ์ ๋ง์ฐฌ๊ฐ์ง๋ก date_format ์ ์ด์ฉํ๋ค.
m ์ ๊ธฐ์ค์ผ๋ก !
<!-- ์ ํต๊ณ -->
<select id = "getLabellingStatusByMonth" ... >
SELECT COUNT(T1.doc_nm) AS cnt
, CONCAT( date_format(T1.fin_date, '%Y-%m'), ' ์') AS finDate
FROM DEVEL_METADATA T1
GROUP BY date_format(T1.fin_date, '%Y-%m')
</select>
๐ ๋
๋ ํต๊ณ๋ ์ผ ํต๊ณ์ ๋ง์ฐฌ๊ฐ์ง๋ก date_format ์ ์ด์ฉํ๋ค.
y ์ ๊ธฐ์ค์ผ๋ก !
<!-- ๋
ํต๊ณ -->
<select id = "getLabellingStatusByYear" ... >
SELECT COUNT(T1.doc_nm) AS cnt
, CONCAT( date_format(T1.fin_date, '%Y'), ' ๋
') AS finDate
FROM DEVEL_METADATA T1
GROUP BY date_format(T1.fin_date, '%Y')
</select>
๐ ๋ถ๊ธฐ
๋ถ๊ธฐ๋ก ํต๊ณ๋ผ ๋, quarter ๋ฅผ ์ด์ฉํ์.
์ด๋ฐ๊ฑด ๋ชฐ๋๋๋ฐ ๋๋ฌด ์ข์๊ฒ ์๊ตฌ๋ง์ ! ๊ตฟ๊ตฟ
<!-- ๋ถ๊ธฐ ํต๊ณ -->
<select id = "getLabellingStatusByQuarter" ...>
SELECT COUNT(T1.doc_nm) AS cnt
, CONCAT( quarter(T1.fin_date), ' ๋ถ๊ธฐ') AS finDate
FROM DEVEL_METADATA T1
GROUP BY quarter(T1.fin_date)
</select>
728x90
๋ฐ์ํ
'๊ฐ๋ฐ์ค > sql' ์นดํ ๊ณ ๋ฆฌ์ ๋ค๋ฅธ ๊ธ
ํธ๋์ญ์ ์ด ๋ญ๋๋ฉด ๐จ ACID๊ฐ๋ญ๋๋ฉด ๐จ ํธ๋์ญ์ ์ ํ์ํ๊ฐ ? (0) | 2022.11.09 |
---|---|
MySQL Error 1093 : You can't specify target table 'tablename' for update in FROM clause (0) | 2022.11.02 |
[MYSQL] ํ์์์๋ค ๋ชจ๋ ๊ตฌํด์ฃผ๋ผ (0) | 2022.10.12 |
Mysql5 ์์ rownum ๊ณผ ๊ทธ๋ฃน๋ณ rownum (1) | 2022.09.02 |
MYSQL ์ฟผ๋ฆฌ ํ์ฉ FIND_IN_SET (0) | 2022.05.23 |