[sql] 날짜 범위에서 일을 생성

같은 쿼리를 실행하고 싶습니다

select ... as days where `date` is between '2010-01-20' and '2010-01-24'

그리고 다음과 같은 데이터를 반환하십시오.

일
----------
2010-01-20
2010-01-21
2010-01-22
2010-01-23
2010-01-24

답변

이 솔루션은 루프, 프로 시저 또는 임시 테이블을 사용 하지 않습니다 . 하위 쿼리는 지난 10,000 일 동안 날짜를 생성하며 원하는만큼 뒤로 또는 앞으로 확장 될 수 있습니다.

select a.Date 
from (
    select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a) + (1000 * d.a) ) DAY as Date
    from (select 0 as a 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) as a
    cross join (select 0 as a 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) as b
    cross join (select 0 as a 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) as c
    cross join (select 0 as a 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) as d
) a
where a.Date between '2010-01-20' and '2010-01-24'

산출:

Date
----------
2010-01-24
2010-01-23
2010-01-22
2010-01-21
2010-01-20

성능에 대한 참고 사항

여기서 테스트 하면 성능이 놀랍게 좋습니다. 위의 쿼리는 0.0009 초가 걸립니다.

하위 쿼리를 확장하여 약. 100,000 개의 숫자 (따라서 약 274 년의 날짜)는 0.0458 초로 실행됩니다.

또한 이것은 약간의 조정만으로 대부분의 데이터베이스에서 작동하는 매우 이식성이 뛰어난 기술입니다.

1,000 일을 반환하는 SQL Fiddle 예제

답변

뷰를 사용하는 또 다른 변형은 다음과 같습니다.

CREATE VIEW digits AS
  SELECT 0 AS digit 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;

CREATE VIEW numbers AS
  SELECT
    ones.digit + tens.digit * 10 + hundreds.digit * 100 + thousands.digit * 1000 AS number
  FROM
    digits as ones,
    digits as tens,
    digits as hundreds,
    digits as thousands;

CREATE VIEW dates AS
  SELECT
    SUBDATE(CURRENT_DATE(), number) AS date
  FROM
    numbers;

그런 다음 간단하게 수행 할 수 있습니다 (얼마나 우아합니까?).

SELECT
  date
FROM
  dates
WHERE
  date BETWEEN '2010-01-20' AND '2010-01-24'
ORDER BY
  date

최신 정보

현재 날짜부터 시작 하여 과거 날짜 만 생성 할 수 있습니다 . 모든 종류의 날짜 범위 (과거, 미래 및 그 사이)를 생성하려면 대신이보기를 사용해야합니다.

CREATE VIEW dates AS
  SELECT
    SUBDATE(CURRENT_DATE(), number) AS date
  FROM
    numbers
  UNION ALL
  SELECT
    ADDDATE(CURRENT_DATE(), number + 1) AS date
  FROM
    numbers;

답변

수락 된 답변이 PostgreSQL에서 작동하지 않았습니다 ( “a”또는 그 근처의 구문 오류).

PostgreSQL에서이를 수행하는 방법은 다음과 같은 generate_series기능 을 사용하는 것입니다.

SELECT day::date
FROM generate_series('2010-01-20', '2010-01-24', INTERVAL '1 day') day;

    day
------------
 2010-01-20
 2010-01-21
 2010-01-22
 2010-01-23
 2010-01-24
(5 rows)

답변

재귀 공통 테이블 표현식 (CTE)을 사용하여 날짜 목록을 생성 한 후 선택할 수 있습니다. 분명히 당신은 일반적으로 3 백만 날짜를 만들고 싶지 않을 것이므로 이것은 가능성을 보여줍니다. CTE 내부의 날짜 범위를 간단히 제한하고 CTE를 사용하여 select 문에서 where 절을 생략 할 수 있습니다.

with [dates] as (
    select convert(datetime, '1753-01-01') as [date] --start
    union all
    select dateadd(day, 1, [date])
    from [dates]
    where [date] < '9999-12-31' --end
)
select [date]
from [dates]
where [date] between '2013-01-01' and '2013-12-31'
option (maxrecursion 0)

Microsoft SQL Server 2005에서 가능한 모든 날짜의 CTE 목록을 생성하는 데는 1:08이 걸렸습니다. 백 년을 생성하는 데 1 초도 걸리지 않았습니다.

답변

MSSQL 쿼리

select datetable.Date
from (
    select DATEADD(day,-(a.a + (10 * b.a) + (100 * c.a)),getdate()) AS Date
    from (select 0 as a 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) as a

    cross join (select 0 as a 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) as b

    cross join (select 0 as a 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) as c
) datetable
where datetable.Date between '2014-01-20' and '2014-01-24'
order by datetable.Date DESC

산출

Date
-----
2014-01-23 12:35:25.250
2014-01-22 12:35:25.250
2014-01-21 12:35:25.250
2014-01-20 12:35:25.250

답변

루프 / 커서없이이 작업을 수행하는 구식 솔루션은 NUMBERS값이 1부터 시작하는 단일 정수 열이있는 테이블 을 만드는 것 입니다.

CREATE TABLE  `example`.`numbers` (
  `id` int(10) unsigned NOT NULL auto_increment,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

필요에 맞는 충분한 레코드로 테이블을 채워야합니다.

INSERT INTO NUMBERS (id) VALUES (NULL);

당신은 일단 NUMBERS테이블을, 당신은 사용할 수 있습니다 :

SELECT x.start_date + INTERVAL n.id-1 DAY
  FROM NUMBERS n
  JOIN (SELECT STR_TO_DATE('2010-01-20', '%Y-%m-%d') AS start_date
          FROM DUAL) x
 WHERE x.start_date + INTERVAL n.id-1 DAY <= '2010-01-24'

절대적인 최첨단 솔루션은 다음과 같습니다.

SELECT STR_TO_DATE('2010-01-20', '%Y-%m-%d')
 FROM DUAL
UNION ALL
SELECT STR_TO_DATE('2010-01-21', '%Y-%m-%d')
 FROM DUAL
UNION ALL
SELECT STR_TO_DATE('2010-01-22', '%Y-%m-%d')
 FROM DUAL
UNION ALL
SELECT STR_TO_DATE('2010-01-23', '%Y-%m-%d')
 FROM DUAL
UNION ALL
SELECT STR_TO_DATE('2010-01-24', '%Y-%m-%d')
 FROM DUAL

무엇을 위해 사용 하시겠습니까?

왼쪽에 참여하기 위해 날짜 또는 숫자 목록을 생성합니다. 시퀀셜 데이터 목록에 왼쪽으로 참여하기 때문에 데이터에 차이가있는 곳을 확인하기 위해이 작업을 수행합니다. null 값은 간격이 존재하는 곳을 분명하게합니다.

답변

Access 2010의 경우 여러 단계가 필요합니다. 위에 게시 된 것과 같은 패턴을 따랐지만 Access의 누군가를 도울 수 있다고 생각했습니다. 나를 위해 훌륭하게 일 했으므로 씨앗 날짜 테이블을 유지할 필요가 없었습니다.

DUAL이라는 테이블을 만듭니다 (Oracle DUAL 테이블의 작동 방식과 유사)

아이디 (식 번호)
DummyColumn (텍스트)
하나의 행 값 추가 (1, “DummyRow”)

“ZeroThru9Q”라는 쿼리를 작성하십시오. 다음 구문을 수동으로 입력하십시오.

SELECT 0 AS a
FROM dual
UNION ALL
SELECT 1
FROM dual
UNION ALL
SELECT 2
FROM dual
UNION ALL
SELECT 3
FROM dual
UNION ALL
SELECT 4
FROM dual
UNION ALL
SELECT 5
FROM dual
UNION ALL
SELECT 6
FROM dual
UNION ALL
SELECT 7
FROM dual
UNION ALL
SELECT 8
FROM dual
UNION ALL
SELECT 9
FROM dual;

“TodayMinus1KQ”라는 이름의 쿼리를 작성하십시오 (오늘 이전 날짜의 경우). 다음 구문을 수동으로 입력하십시오.

SELECT date() - (a.a + (10 * b.a) + (100 * c.a)) AS MyDate
FROM
  (SELECT *
   FROM ZeroThru9Q) AS a,

  (SELECT *
   FROM ZeroThru9Q) AS b,

  (SELECT *
   FROM ZeroThru9Q) AS c

“TodayPlus1KQ”라는 이름의 쿼리를 작성하십시오 (오늘 이후 날짜). 다음 구문을 수동으로 입력하십시오.

SELECT date() + (a.a + (10 * b.a) + (100 * c.a)) AS MyDate
FROM
  (SELECT *
   FROM ZeroThru9Q) AS a,

  (SELECT *
   FROM ZeroThru9Q) AS b,

  (SELECT *
   FROM ZeroThru9Q) AS c;

“TodayPlusMinus1KQ”라는 이름의 통합 쿼리를 만듭니다 (날짜 +/- 1000 일).

SELECT MyDate
FROM TodayMinus1KQ
UNION
SELECT MyDate
FROM TodayPlus1KQ;

이제 쿼리를 사용할 수 있습니다 :

SELECT MyDate
FROM TodayPlusMinus1KQ
WHERE MyDate BETWEEN #05/01/2014# and #05/30/2014#