如何使用Oracle SQL
问题描述:
选择行的第一个连续组,我有以下数据:如何使用Oracle SQL
Date GroupID Value 1/01/2000 1 44 2/01/2000 1 55 3/01/2000 1 66 4/01/2000 2 77 5/01/2000 2 88 6/01/2000 1 99 7/01/2000 1 22
我正在寻找能与连续组ID选择记录的第一组,当我为了通过查询日期。即在这个例子中我会得到:
1/01/2000 1 44 2/01/2000 1 55 3/01/2000 1 66
随着组ID改变在下一行,我不会得到任何后续数据。
帮助将不胜感激
感谢
埃利
做
答
一个办法:
SQL> WITH DATA AS (
2 SELECT '1/01/2000' mydate, 1 GroupID, 44 Value FROM DUAL
3 UNION ALL SELECT '2/01/2000', 1, 55 FROM DUAL
4 UNION ALL SELECT '3/01/2000', 1, 66 FROM DUAL
5 UNION ALL SELECT '4/01/2000', 2, 77 FROM DUAL
6 UNION ALL SELECT '5/01/2000', 2, 88 FROM DUAL
7 UNION ALL SELECT '6/01/2000', 1, 99 FROM DUAL
8 UNION ALL SELECT '7/01/2000', 1, 22 FROM DUAL
9 )
10 SELECT mydate, groupid, VALUE
11 FROM (SELECT mydate, groupid, VALUE,
12 SUM(gap) over(ORDER BY mydate) contiguous_group
13 FROM (SELECT mydate, groupid, VALUE,
14 CASE
15 WHEN lag(groupid)
16 over(ORDER BY mydate) != groupid
17 THEN
18 1
19 ELSE
20 0
21 END gap
22 FROM DATA))
23 WHERE contiguous_group = 0;
MYDATE GROUPID VALUE
--------- ---------- ----------
1/01/2000 1 44
2/01/2000 1 55
3/01/2000 1 66
+0
谢谢你,非常有帮助 – Elie 2010-06-26 11:27:56
答
这是另一种方式如何做到这一点
WITH data AS
(SELECT '1/01/2000' mydate,
1 groupid,
44 VALUE
FROM dual
UNION ALL
SELECT '2/01/2000',
1,
55
FROM dual
UNION ALL
SELECT '3/01/2000',
1,
66
FROM dual
UNION ALL
SELECT '4/01/2000',
2,
77
FROM dual
UNION ALL
SELECT '5/01/2000',
2,
88
FROM dual
UNION ALL
SELECT '6/01/2000',
1,
99
FROM dual
UNION ALL
SELECT '7/01/2000',
1,
22
FROM dual)
SELECT *
FROM data
WHERE rownum <= (SELECT MAX(rwn)
FROM (SELECT COUNT(*) over(PARTITION BY groupid ORDER BY rownum) cnt,
rownum rwn
FROM data)
WHERE rwn = cnt);
P.S.查询文森特将无法工作,如果所有mydate值将相同
+0
谢谢你,非常有帮助 – Elie 2010-06-26 11:40:54
答
虽然已经回答,想分享它。它使用JOINs
代替
WITH DATA AS (
SELECT '1/01/2000' mydate, 1 GroupID, 44 v
UNION ALL SELECT '2/01/2000', 1, 55
UNION ALL SELECT '3/01/2000', 1, 66
UNION ALL SELECT '4/01/2000', 2, 77
UNION ALL SELECT '5/01/2000', 2, 88
UNION ALL SELECT '6/01/2000', 1, 99
UNION ALL SELECT '7/01/2000', 1, 22
)
SELECT
a.GroupID,
a.mydate
FROM
DATA a
JOIN DATA b ON (b.mydate <= a.mydate)
GROUP BY
a.GroupID,
a.mydate
HAVING
MAX(b.GroupID) = MIN (b.GroupID)
ORDER BY
a.mydate
是否保证每个日期只有一条记录? – 2010-06-24 11:04:36