mysql 쿼리를 sqlite 쿼리로 변경, 변수사용, 날짜별 합계 구하기
mysql 쿼리를 sqlite 쿼리로 변경, 변수사용, 날짜별 합계 구하기
//-------------------------------------
-- mysql
SET @START1 = '2024-04-01';
SET @END1 = '2024-05-01';
SET @TERM1 = '%Y-%m-%d';
SELECT
DATE_FORMAT(created_at, @TERM1) AS date1,
COUNT(id) as count1
FROM table1
WHERE created_at >= @START1
AND created_at < @END1
GROUP BY DATE_FORMAT(created_at, @TERM1)
ORDER BY created_at ASC
LIMIT 100;
//-------------------------------------
-- sqlite
WITH vars(START1, END1, TERM1) AS (
SELECT '2024-04-01', '2024-05-01', '%Y-%m-%d'
)
SELECT
strftime((SELECT TERM1 FROM vars), created_at) AS date1,
COUNT(id) as count1
FROM table1
WHERE created_at >= (SELECT START1 FROM vars)
AND created_at < (SELECT END1 FROM vars)
GROUP BY strftime((SELECT TERM1 FROM vars), created_at)
ORDER BY created_at ASC
LIMIT 100;