我正在研究一個資料庫 oracle，我需要創建一個查詢，它回傳一個日期范圍。例如：
假設我有一個這樣的領域：
我需要得到這個日期并應用一系列年份來回傳類似的東西：
|'0-5'|'6-10'|'11-15'|...
| 10 | 35 | 20 |...
其中每個范圍包含該年齡范圍內的許多人。
我試著用 SELECT CASE...
SELECT CASE
WHEN DATE_BORN <= DATE_BORN 5 THEN '0 - 5
WHEN DATE_BORN >= DATE_BORN 6 AND DATE_BORN <= 10 THEN '6 - 10'
END AS AGE_RANGE,
COUNT(*)
FROM MY_TABLE
GROUP BY 1
所以我看到這種方式只改變了幾天而不是一年。
我該如何撰寫這個查詢？
uj5u.com熱心網友回復：
那是條件聚合：
SQL> with test (date_born) as
2 (select date '2000-05-12' from dual union all
3 select date '2001-05-12' from dual union all
4 select date '2012-05-12' from dual union all
5 select date '2013-05-12' from dual union all
6 select date '2004-05-12' from dual union all
7 select date '2008-05-12' from dual union all
8 select date '2009-05-12' from dual union all
9 select date '2001-05-12' from dual union all
10 select date '2012-05-12' from dual union all
11 select date '2001-05-12' from dual union all
12 select date '2004-05-12' from dual union all
13 select date '2005-05-12' from dual
14 )
15 select
16 sum(case when extract (year from date_born) between 2000 and 2005 then 1 else 0 end) as "2000 - 2005",
17 sum(case when extract (year from date_born) between 2006 and 2010 then 1 else 0 end) as "2006 - 2010",
18 sum(case when extract (year from date_born) between 2011 and 2015 then 1 else 0 end) as "2011 - 2015"
19 from test;
2000 - 2005 2006 - 2010 2011 - 2015
----------- ----------- -----------
7 2 3
SQL>
uj5u.com熱心網友回復：
這是執行此操作的動態方法（使用上面的示例表）
首先，我認為將范圍放在行而不是列中更容易，更容易擁有可能會更改的各種日期。
其次，您的第一個分組是 6 年，所以我將其更改為 5 年系列：
with test (date_born) as
(select date '2000-05-12' from dual union all
select date '2001-05-12' from dual union all
select date '2012-05-12' from dual union all
select date '2013-05-12' from dual union all
select date '2004-05-12' from dual union all
select date '2008-05-12' from dual union all
select date '2009-05-12' from dual union all
select date '2001-05-12' from dual union all
select date '2012-05-12' from dual union all
select date '2001-05-12' from dual union all
select date '2004-05-12' from dual union all
select date '2005-05-12' from dual
)
,mydata AS (
SELECT
(SELECT min(extract(YEAR FROM date_born)) FROM test) ((LEVEL-1)*5)dt1
,(SELECT min(extract(YEAR FROM date_born)) FROM test) ((LEVEL-1)*5) 4 dt2
FROM dual CONNECT BY LEVEL*5 <=
(SELECT max(extract(YEAR FROM date_born))-min(extract(YEAR FROM date_born)) FROM test) 5)
SELECT d.*, count(t.date_born) cnt FROM mydata d
LEFT JOIN test t ON extract(YEAR FROM date_born) BETWEEN d.dt1 AND d.dt2
GROUP BY dt1, dt2
ORDER BY dt1;
你得到這個為你的解決方案
DT1 DT2 CNT
2000 2004 6
2005 2009 3
2010 2014 3
解決方案基本上是從日期中提取年份，找到該資料集的最小值/最大值，connect用于獲取兩者之間的所有年份，然后加入以計算匹配記錄
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/406507.html
標籤：
上一篇：SQL對存在的十年記錄進行計數