我想要的结果是1001(上次转科的dept_stayed ), A ,2011-01-03(第二条记录) 1002 ,A ,2011-01-04(第三条记录)
name,dept_stayed , dept_tranfer, date ---------------------------------------------------- A , 1001, 1002, 2011-01-02 A , 1002, 1001, 2011-01-03 A , 1001, 1002, 2011-01-04 不好意思,表数据第三条改成2011-01-04
需求是:取非第一次转科的时间date,以及上次转科记录的转出科室dept_stayed
试试这个:WITH TAB AS (SELECT 'A' AS NAME, '1001' AS DEPT_STAYED, '1002' AS DEPT_TRANFER, '2011-01-02' AS "date" FROM DUAL UNION SELECT 'A', '1002', '1001', '2011-01-03' FROM DUAL UNION SELECT 'A', '1001', '1002', '2011-01-04' FROM DUAL) SELECT DEPT_STAYED, NAME, "date" FROM (SELECT RN, LAG(DEPT_STAYED) OVER(PARTITION BY NAME ORDER BY RN) AS DEPT_STAYED, NAME, "date" FROM (SELECT TAB.*, ROW_NUMBER() OVER(ORDER BY "date") AS RN FROM TAB)) WHERE RN > 1
你的表设计有问题,假设一天之内转了多次,像下面这种情况,怎么区分两个红色的记录谁先谁后? 因此你的表中需要一个字段,存储一个自增的序列的值用来标识数据记录顺序,有了这个顺序,一切就会变的顺理成章,数据也可以很方便的统计了。 name,dept_stayed , dept_tranfer, date ---------------------------------------------------- A , 1001, 1002, 2011-01-03 A , 1002, 1001, 2011-01-03 A , 1001, 1002, 2011-01-03
非常感谢wallace_jjh提供的代码,不仅给了我另一种思考角度,学会了LAG( ) OVER(PARTITION BY ORDER BY ) 的用法,还学会了用with as创建临时视图。 因实际中可能有另一个人B的转科记录,我对wallace_jjh提供的代码稍作了修改,即可实现我想要的效果,如下: WITH TAB AS (SELECT 'A' AS NAME, '1001' AS DEPT_STAYED, '1002' AS DEPT_TRANFER, '2011-01-02' AS "date" FROM DUAL UNION SELECT 'A', '1002', '1001', '2011-01-03' FROM DUAL UNION SELECT 'A', '1001', '1002', '2011-01-05' FROM DUAL UNION SELECT 'B', '2001', '2002', '2011-01-01' FROM DUAL UNION SELECT 'B', '2002', '2001', '2011-01-04' FROM DUAL)
SELECT DEPT_STAYED, NAME, "date" FROM (SELECT RN, LAG(DEPT_STAYED) OVER(PARTITION BY NAME ORDER BY RN) AS DEPT_STAYED, NAME, "date" FROM (SELECT TAB.*, ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY "date") AS RN FROM TAB)) WHERE RN > 1
你的这个需求肯定是用 row_number()、lead()、lag()分析函数来做的。序列只是为了确定数据记录的先后顺序,因为row_number()、 lead()、lag()函数后面都是需要 order by 排序的。 但是从你的描述中看不出应该怎么排序。 不过既然高手从你有漏洞的数据中写出了正确的sql,而且你也学会了这个东西,就恭喜你一下吧。
1002 ,A ,2011-01-04(第三条记录)
----------------------------------------------------
A , 1001, 1002, 2011-01-02
A , 1002, 1001, 2011-01-03
A , 1001, 1002, 2011-01-04
不好意思,表数据第三条改成2011-01-04
(SELECT 'A' AS NAME,
'1001' AS DEPT_STAYED,
'1002' AS DEPT_TRANFER,
'2011-01-02' AS "date"
FROM DUAL
UNION
SELECT 'A', '1002', '1001', '2011-01-03'
FROM DUAL
UNION
SELECT 'A', '1001', '1002', '2011-01-04' FROM DUAL)
SELECT DEPT_STAYED, NAME, "date"
FROM (SELECT RN,
LAG(DEPT_STAYED) OVER(PARTITION BY NAME ORDER BY RN) AS DEPT_STAYED,
NAME,
"date"
FROM (SELECT TAB.*, ROW_NUMBER() OVER(ORDER BY "date") AS RN
FROM TAB))
WHERE RN > 1
因此你的表中需要一个字段,存储一个自增的序列的值用来标识数据记录顺序,有了这个顺序,一切就会变的顺理成章,数据也可以很方便的统计了。 name,dept_stayed , dept_tranfer, date
----------------------------------------------------
A , 1001, 1002, 2011-01-03
A , 1002, 1001, 2011-01-03
A , 1001, 1002, 2011-01-03
非常感谢wallace_jjh提供的代码,不仅给了我另一种思考角度,学会了LAG( ) OVER(PARTITION BY ORDER BY ) 的用法,还学会了用with as创建临时视图。
因实际中可能有另一个人B的转科记录,我对wallace_jjh提供的代码稍作了修改,即可实现我想要的效果,如下:
WITH TAB AS
(SELECT 'A' AS NAME,
'1001' AS DEPT_STAYED,
'1002' AS DEPT_TRANFER,
'2011-01-02' AS "date"
FROM DUAL
UNION
SELECT 'A', '1002', '1001', '2011-01-03'
FROM DUAL
UNION
SELECT 'A', '1001', '1002', '2011-01-05' FROM DUAL
UNION
SELECT 'B', '2001', '2002', '2011-01-01' FROM DUAL
UNION
SELECT 'B', '2002', '2001', '2011-01-04' FROM DUAL)
SELECT DEPT_STAYED, NAME, "date"
FROM (SELECT RN,
LAG(DEPT_STAYED) OVER(PARTITION BY NAME ORDER BY RN) AS DEPT_STAYED,
NAME,
"date"
FROM (SELECT TAB.*, ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY "date") AS RN
FROM TAB))
WHERE RN > 1
但是从你的描述中看不出应该怎么排序。 不过既然高手从你有漏洞的数据中写出了正确的sql,而且你也学会了这个东西,就恭喜你一下吧。