복붙노트

[SQL] 어떻게 날짜의 범위와 테이블을 채우는?

SQL

어떻게 날짜의 범위와 테이블을 채우는?

나는 2011-01-01과 2011-12-31 사이의 날짜를 모두 개최하는 MySQL의 테이블이 필요합니다. 나는 하나의 열 이름 "_date"를 입력 한 날짜와 테이블을 만들었습니다.

어떤 쿼리와 함께 내가 (대신 손으로 입력해야하는) 모든 원하는 날짜와 테이블을 채울 수 있습니다?

해결법

  1. ==============================

    1.이 시도:

    이 시도:

    DROP PROCEDURE IF EXISTS filldates;
DELIMITER |
CREATE PROCEDURE filldates(dateStart DATE, dateEnd DATE)
BEGIN
  WHILE dateStart <= dateEnd DO
    INSERT INTO tablename (_date) VALUES (dateStart);
    SET dateStart = date_add(dateStart, INTERVAL 1 DAY);
  END WHILE;
END;
|
DELIMITER ;
CALL filldates('2011-01-01','2011-12-31');

    여기에 플레이에 SQL 바이올린입니다 : http://sqlfiddle.com/#!2/65d13/1

    앤드류 폭스 요청으로 EDIT (날짜가 이미 존재 여부를 확인하기 위해).

    CREATE PROCEDURE filldates(dateStart DATE, dateEnd DATE)

BEGIN

DECLARE adate date;

    WHILE dateStart <= dateEnd DO

        SET adate = (SELECT mydate FROM MyDates WHERE mydate = dateStart);

        IF adate IS NULL THEN BEGIN

            INSERT INTO MyDates (mydate) VALUES (dateStart);

        END; END IF;

        SET dateStart = date_add(dateStart, INTERVAL 1 DAY);

    END WHILE;

END;//

    여기에 플레이에 SQL 바이올린입니다 : http://sqlfiddle.com/#!2/66f86/1

  2. ==============================

    2.내 SQL 쿼리가 외부 의존성이 필요하지 않았다 (달력 테이블이 필요, 등, 날짜와 임시 테이블을 채우기위한 절차)이 쿼리에 대한 원래의 아이디어는 http://jeffgarretson.wordpress.com/2012에서 온 / 5월 4일 / 생성-A-범위 - 중 - 날짜 -에 - mysql을 / 나는 약간 선명도와 사용 편의성에 최적화했다.

    내 SQL 쿼리가 외부 의존성이 필요하지 않았다 (달력 테이블이 필요, 등, 날짜와 임시 테이블을 채우기위한 절차)이 쿼리에 대한 원래의 아이디어는 http://jeffgarretson.wordpress.com/2012에서 온 / 5월 4일 / 생성-A-범위 - 중 - 날짜 -에 - mysql을 / 나는 약간 선명도와 사용 편의성에 최적화했다.

    SELECT (CURDATE() - INTERVAL c.number DAY) AS date
FROM (SELECT singles + tens + hundreds number FROM 
( SELECT 0 singles
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
) singles JOIN 
(SELECT 0 tens
UNION ALL SELECT  10 UNION ALL SELECT  20 UNION ALL SELECT  30
UNION ALL SELECT  40 UNION ALL SELECT  50 UNION ALL SELECT  60
UNION ALL SELECT  70 UNION ALL SELECT  80 UNION ALL SELECT  90
) tens  JOIN 
(SELECT 0 hundreds
UNION ALL SELECT  100 UNION ALL SELECT  200 UNION ALL SELECT  300
UNION ALL SELECT  400 UNION ALL SELECT  500 UNION ALL SELECT  600
UNION ALL SELECT  700 UNION ALL SELECT  800 UNION ALL SELECT  900
) hundreds
ORDER BY number DESC) c  
WHERE c.number BETWEEN 0 and 364

    최적화하고 다른 용도로이 표를 확장 할 간단합니다. 당신은 단지 하나의 데이터 주 필요한 경우 쉽게 수십 수백 개의 테이블을 제거 얻을 수 있습니다.

    당신이 숫자의 더 큰 집합을해야하는 경우는 수천 개의 테이블을 쉽게 추가 할 수 있습니다. 당신은 복사 수백 표를 붙여 9 개 숫자에 0을 추가해야합니다.

  3. ==============================

    3.당신이 절차를 금지하고 SQL 사용자 따라서 삽입 할 수 없습니다, 삽입에 대한 권한이없는 나 같은 상황에있어,하지만 당신은 특정 기간의 날짜의 목록을 생성 할 경우, 올해 일부를 수행하는 말 집계, 이것을 사용

    당신이 절차를 금지하고 SQL 사용자 따라서 삽입 할 수 없습니다, 삽입에 대한 권한이없는 나 같은 상황에있어,하지만 당신은 특정 기간의 날짜의 목록을 생성 할 경우, 올해 일부를 수행하는 말 집계, 이것을 사용

    select * from 
(select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) gen_date from
 (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
 (select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
 (select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
 (select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
 (select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where gen_date between '2017-01-01' and '2017-12-31'
  4. ==============================

    4.나는이 붙여 넣기 - 및 - 이동의 변형 작업을 발견 :

    나는이 붙여 넣기 - 및 - 이동의 변형 작업을 발견 :

    DROP PROCEDURE IF EXISTS FillCalendar;
DROP TABLE IF EXISTS calendar;
CREATE TABLE IF NOT EXISTS calendar(calendar_date DATE NOT NULL PRIMARY KEY);

DELIMITER $$
    CREATE PROCEDURE FillCalendar(start_date DATE, end_date DATE)
    BEGIN
    DECLARE crt_date DATE;
    SET crt_date = start_date;
    WHILE crt_date <= end_date DO
        INSERT IGNORE INTO calendar VALUES(crt_date);
        SET crt_date = ADDDATE(crt_date, INTERVAL 1 DAY);
    END WHILE;
    END$$
DELIMITER ;

CALL FillCalendar('2013-01-01', '2013-01-03');
CALL FillCalendar('2013-01-01', '2013-01-07');
  5. ==============================

    5.나는 최근에 다음과 같은 calendar_date 테이블을 생성 할 필요가 있었다 :

    나는 최근에 다음과 같은 calendar_date 테이블을 생성 할 필요가 있었다 :

    CREATE TABLE `calendar_date` (
    `date`    DATE NOT NULL      -- A calendar date.
    , `day`   SMALLINT NOT NULL  -- The day of the year for the date, 1-366.
    , `month` TINYINT NOT NULL   -- The month number, 1-12.
    , `year`  SMALLINT NOT NULL  -- The year.
    , PRIMARY KEY (`id`));

    나는 다음 쿼리 아래를 사용하여 2001년 1월 1일 년 12 월 31 일 2100 (모두 포함) 사이의 모든 가능한 날짜와 함께 인구 :

    INSERT INTO `calendar_date` (`date`
    , `day`
    , `month`
    , `year`)
SELECT
    DATE
    , INCREMENT + 1
    , MONTH(DATE)
    , YEAR(DATE)
FROM
    -- Generate all possible dates for every year from 2001 to 2100.
    (SELECT
        DATE_ADD(CONCAT(YEAR, '-01-01'), INTERVAL INCREMENT DAY) DATE
        , INCREMENT
    FROM
        (SELECT
            (UNITS + TENS + HUNDREDS) INCREMENT
        FROM
            (SELECT 0 UNITS UNION
            SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION
            SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION
            SELECT 7 UNION SELECT 8 UNION SELECT 9) UNITS
        CROSS JOIN
            (SELECT 0 TENS UNION
            SELECT 10 UNION SELECT 20 UNION SELECT 30 UNION
            SELECT 40 UNION SELECT 50 UNION SELECT 60 UNION
            SELECT 70 UNION SELECT 80 UNION SELECT 90) TENS
        CROSS JOIN
            (SELECT 0 HUNDREDS UNION
            SELECT 100 UNION SELECT 200 UNION SELECT 300 UNION
            SELECT 400 UNION SELECT 500 UNION SELECT 600 UNION
            SELECT 700 UNION SELECT 800 UNION SELECT 900) HUNDREDS
        ) INCREMENT
        -- For every year from 2001 to 2100, find the number of days in the year.
        , (SELECT
            YEAR
            , DAYOFYEAR(CONCAT(YEAR, '-12-31')) - DAYOFYEAR(CONCAT(YEAR, '-01-01')) + 1 DAYS
        FROM
            -- Generate years from 2001 to 2100.
            (SELECT
                (2000 + UNITS + TENS) YEAR
            FROM
                (SELECT 0 UNITS UNION
                SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION
                SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION
                SELECT 7 UNION SELECT 8 UNION SELECT 9) UNITS
            CROSS JOIN
                (SELECT 0 TENS UNION
                SELECT 10 UNION SELECT 20 UNION SELECT 30 UNION
                SELECT 40 UNION SELECT 50 UNION SELECT 60 UNION
                SELECT 70 UNION SELECT 80 UNION SELECT 90) TENS
            ) YEAR
        WHERE
            YEAR BETWEEN 2001 AND 2100
        ) YEAR
      WHERE
          INCREMENT BETWEEN 0 AND DAYS - 1
      ORDER BY
          YEAR
          , INCREMENT) DATE;

    내 로컬 MySQL 데이터베이스에서 INSERT 쿼리는 단 몇 초 걸렸습니다. 이 사람을 도움이되기를 바랍니다.

  6. ==============================

    6.당신은 당신이 사용할 수있는 ID의 충분히 큰 연속 세트 테이블이있는 경우 -

    당신은 당신이 사용할 수있는 ID의 충분히 큰 연속 세트 테이블이있는 경우 -

    INSERT INTO tablename (_date)
SELECT '2011-01-01' + INTERVAL (id - 1) DAY
FROM some_table_with_lots_of_ids
WHERE id BETWEEN 1 AND 365

    주의 :하지만이 도약 년 동안 곤경에 당신을 얻을 수 있음을 인식 (366일 필요)

  7. ==============================

    7.내가하고 감사합니다. 난 당신이 지정된 달력 테이블을 만들 수있는 더 나은 솔루션을 가지고있다. 내가 2014-04의 테이블을 만들려고 해요 예를 들어, 그것은 다음과 같습니다 :

    내가하고 감사합니다. 난 당신이 지정된 달력 테이블을 만들 수있는 더 나은 솔루션을 가지고있다. 내가 2014-04의 테이블을 만들려고 해요 예를 들어, 그것은 다음과 같습니다 :

    SELECT (CURDATE() - INTERVAL c.number DAY) AS DATE
FROM 
(
    SELECT singles + tens + hundreds number FROM 
    ( 
        SELECT 0 singles
        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
    ) singles JOIN 
    (
        SELECT 0 tens
        UNION ALL SELECT  10 UNION ALL SELECT  20 UNION ALL SELECT  30
        UNION ALL SELECT  40 UNION ALL SELECT  50 UNION ALL SELECT  60
        UNION ALL SELECT  70 UNION ALL SELECT  80 UNION ALL SELECT  90
    ) tens  JOIN 
    (
        SELECT 0 hundreds
        UNION ALL SELECT  100 UNION ALL SELECT  200 UNION ALL SELECT  300
        UNION ALL SELECT  400 UNION ALL SELECT  500 UNION ALL SELECT  600
        UNION ALL SELECT  700 UNION ALL SELECT  800 UNION ALL SELECT  900
    ) hundreds
    ORDER BY number DESC
) c  
WHERE c.number BETWEEN 
DAYOFYEAR(NOW()) - DAYOFYEAR('2014-04-01')-  DAY(LAST_DAY('2014-04-01')) +1
AND 
DAYOFYEAR(NOW()) - DAYOFYEAR('2014-04-01')
  8. ==============================

    8.이것은 루프 간단한 PHP를 사용하여 달성 될 수있다. 그것을 할 수있는 방법이 몇 가지 있습니다. 한 가지 방법은 예를 들어, 각 루프에 일일를 추가하여 매일 그것을 통해 변수에 원래 날짜를 놓고 루프 실행하도록하는 것입니다, 당신은 01/01/2011에 시작합니다 다음 루프는 것이다 일일 I $ 변수 등등 등 이일이어서 다음과 0을 처음 추가. 그런 다음 일 인쇄하거나이 데이터베이스에 추가 할 수 있습니다. 이 경우 내가 시작 지점 인 0으로 카운터를 나타낼 것입니다 $에서 <= 365 당신이 일 수 또는 이하 동일 통해 가서 내가 ++ $ 내가 변수에 +1을 추가 $하려면 얼마나 많은 루프입니다 각 루프에.

    이것은 루프 간단한 PHP를 사용하여 달성 될 수있다. 그것을 할 수있는 방법이 몇 가지 있습니다. 한 가지 방법은 예를 들어, 각 루프에 일일를 추가하여 매일 그것을 통해 변수에 원래 날짜를 놓고 루프 실행하도록하는 것입니다, 당신은 01/01/2011에 시작합니다 다음 루프는 것이다 일일 I $ 변수 등등 등 이일이어서 다음과 0을 처음 추가. 그런 다음 일 인쇄하거나이 데이터베이스에 추가 할 수 있습니다. 이 경우 내가 시작 지점 인 0으로 카운터를 나타낼 것입니다 $에서 <= 365 당신이 일 수 또는 이하 동일 통해 가서 내가 ++ $ 내가 변수에 +1을 추가 $하려면 얼마나 많은 루프입니다 각 루프에.

    날짜 ( 'Ymd 일수'변환 YYYY-MM-DD로 날짜입니다. Y는 당신에게 올해의 마지막 2 개 자리를 줄 것이다 소문자 y로 사용하는 반면 당신에게 전체 4 자리 연도를 제공하는 자본을 사용하여이. 당신이 그것을 유지하려는 MySQL의에서 날짜 필드에 추가하는 주문.

    한 strtotime (유닉스 타임 스탬프로 $ originalDate 파싱 날짜와. "+". 난을 $. "일")는 기본적으로 지금까지 일 년에 $ i 값을 추가합니다.

    마지막으로 mysqli 쿼리가있다. $ DB는 데이터베이스 연결 변수를 나타내며, 이것은 당신이 연결을 위해 설정 한 어떤 변수를 변경해야합니다. 이것은 실제 쿼리옵니다. 그냥 날짜 행 이름에 값 전에 테이블 이름에 대한 단어 테이블과 날짜를 교환하고 당신은 갈 준비가 된 것입니다.

    다음은 예입니다 :

    <?php
for($i=0;$i<=365;$i++){ 
$originalDate = "01/01/2011";
$date = date('Y-m-d',strtotime($originalDate . "+".$i." day"));
mysqli_query($db, "INSERT INTO table (date)VALUES('$date')");
}

    또 다른 방법은 함수에 대한이 코드의 더 짧은 조각 카운터 변수에 반대하는 등의 작업에 대한에 직접 strtotime으로 날짜를 포함하는 것입니다 사용하여이를 달성하기 위해. , 시작 하루 포인트와 $ I = 0 (시작 카운터 포인트)를 교체보다 적은 것을 따라 나에 배치하여 플러스 한 첫 번째 문에와 마지막으로 다음 종료일 포인트 (루프의 수)에 동일 사용할 준비가 변수입니다.

    마지막으로, 데이터베이스에 배치 할 준비가 Y-m-D 형식으로 날짜를 변환하고 쿼리를 실행합니다.

    다시, 첫 번째 예에서와 같이,이 인쇄 할 수 있습니다 또는 데이터베이스에 직접 배치.

    다음은 예입니다 :

    <?php
for ($startdate = strtotime("2011-01-01"); $startdate <= strtotime("2011-12-31"); $startdate = strtotime("+1 day", $startdate)) {
$date= date("Y-m-d", $startdate);
mysqli_query($db, "INSERT INTO tracking (date)VALUES('$date')");
}

    나는 아마 더 그것보다 혼란 소리를했다,하지만 적어도 당신이 어떻게 작동하는지에 대한 아이디어를 줄 것이다 희망했습니다.

  9. ==============================

    9.IvanD`s에서 영감을 많은 수의 난이 올 조인

    IvanD`s에서 영감을 많은 수의 난이 올 조인

    SELECT DATE_ADD('2015-10-21', INTERVAL c.number DAY)    AS DATE
FROM 
(
    SELECT singles + tens + hundreds+thousands number FROM 
    ( 
        SELECT 0 singles
        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
    ) singles JOIN 
    (
        SELECT 0 tens
        UNION ALL SELECT  10 UNION ALL SELECT  20 UNION ALL SELECT  30
        UNION ALL SELECT  40 UNION ALL SELECT  50 UNION ALL SELECT  60
        UNION ALL SELECT  70 UNION ALL SELECT  80 UNION ALL SELECT  90
    ) tens  JOIN 
    (
        SELECT 0 hundreds
        UNION ALL SELECT  100 UNION ALL SELECT  200 UNION ALL SELECT  300
        UNION ALL SELECT  400 UNION ALL SELECT  500 UNION ALL SELECT  600
        UNION ALL SELECT  700 UNION ALL SELECT  800 UNION ALL SELECT  900
    ) hundreds
     JOIN 
    (
        SELECT 0 thousands
        UNION ALL SELECT  1000 UNION ALL SELECT  2000 UNION ALL SELECT  3000
        UNION ALL SELECT  4000 UNION ALL SELECT  5000 UNION ALL SELECT  6000
        UNION ALL SELECT  7000 UNION ALL SELECT  8000 UNION ALL SELECT  9000
    ) thousands
    ORDER BY number DESC
) c  
WHERE c.number BETWEEN 
0 
AND
DATEDIFF('2016-10-08', '2015-10-21')
  10. ==============================

    10.

    INSERT INTO my_dates (\`_date\`) SELECT DATE_ADD('2011-01-01', INTERVAL @_tmp:=@_tmp+1 day) \`_date\`
FROM (SELECT @_tmp:=-1 d UNION SELECT 1 UNION SELECT 2 
      UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6
      UNION SELECT 7 UNION SELECT 8  UNION SELECT 9) a /\*10^1\*/
JOIN (SELECT 0 UNION SELECT 1 UNION SELECT 2 
      UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6
      UNION SELECT 7  UNION SELECT 8  UNION SELECT 9) b /\*10^2\*/
JOIN (SELECT 0 UNION SELECT 1 UNION SELECT 2
      UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6
      UNION SELECT 7  UNION SELECT 8  UNION SELECT 9) c /\*10^3\*/
WHERE @_tmp+1 BETWEEN 0 AND DATEDIFF('2011-12-31', '2011-01-01');

    11. from https://stackoverflow.com/questions/10132024/how-to-populate-a-table-with-a-range-of-dates by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] SQL가 null = NULL [중복]  (0) 2020.03.10
[SQL] 내부는 SQL에 LINQ에서 조인 구문은 무엇입니까?  (0) 2020.03.10
[SQL] 왜 NULL = NULL은 SQL 서버에서 false로 평가 않습니다  (0) 2020.03.10
[SQL] MySQL의 LIKE IN ()?  (0) 2020.03.10
[SQL] SQL에서 100 년 달력 테이블을 만드는 방법 [마감]  (0) 2020.03.10
,

티스토리툴바