22,300
社区成员




with minCTE as (
Select XM,RQ
From #t1 a
Where RQ=(Select min(Rq) From #t1 b where b.XM=a.XM)
),
maxCTE as (
Select XM,RQ
From #t1 a
Where RQ=(Select max(Rq) From #t1 b where b.XM=a.XM)
)
Select *,'First' as Status From minCTE
UNION
Select *,'Normal' as Status
From #t1 a
Where not EXISTS (Select 1 From (Select * From minCTE union Select * From maxCTE ) b Where a.XM=b.XM AND a.rq=b.rq)
union
Select *,'LAST' as Status From maxCTE
IF OBJECT_ID('tempdb..#T1') IS NOT NULL DROP TABLE #T1
CREATE TABLE #T1(XM VARCHAR(20),RQ DATE, otherCol INT IDENTITY(1,1))
INSERT INTO #T1 VALUES
('10001','2016/6/8'),
('10001','2016/7/5'),
('10001','2016/8/5'),
('10001','2016/9/5'),
('10001','2016/10/5'),
('10001','2016/11/5'),
('10002','2016/12/5'),
('10002','2017/1/5'),
('10002','2017/2/5'),
('10002','2017/3/5'),
('10002','2017/4/5'),
('10002','2017/5/5'),
('10002','2017/6/5'),
('10002','2017/7/5'),
('10002','2017/8/5'),
('10002','2017/9/5'),
('10002','2017/10/5'),
('10002','2017/11/5'),
('10002','2017/12/5'),
('10002','2017/12/5')
select *,
case when RQ=MIN(RQ) over (partition by XM) then '最早一期'
when RQ=MAX(RQ) over (partition by XM) then '最后一期'
else '正常'
end as ZT
from #T1
;WITH cte AS (
SELECT
XM
,MIN(RQ) AS minDate
,MAX(RQ) AS maxDate
FROM #T1 t
GROUP BY XM
)
SELECT
a.*
,CASE WHEN a.RQ=b.minDate THEN '最早1期'
WHEN a.RQ=b.maxDate THEN '最后1期'
ELSE '正常' END AS [flag]
FROM #t1 a LEFT JOIN cte b ON a.XM=b.XM
测试数据加了一列, 楼上几种写法都是可以的。跟加不加 group by 没什么关系。
select *,
case when RQ=MIN(RQ) over (partition by XM) then '最早一期'
when RQ=MAX(RQ) over (partition by XM) then '最后一期'
else '正常'
end as ZT
from #T1
;WITH cte AS (
SELECT
XM
,MIN(RQ) AS minDate
,MAX(RQ) AS maxDate
FROM #T1 t
GROUP BY XM
)
SELECT
a.*
,CASE WHEN a.RQ=b.minDate THEN '最早1期'
WHEN a.RQ=b.maxDate THEN '最后1期'
ELSE '正常' END AS [flag]
FROM #t1 a LEFT JOIN cte b ON a.XM=b.XM
/*
XM RQ flag
-------------------- ---------- -------
10001 2016-06-08 最早1期
10001 2016-07-05 正常
10001 2016-08-05 正常
10001 2016-09-05 正常
10001 2016-10-05 正常
10001 2016-11-05 最后1期
10002 2016-12-05 最早1期
10002 2017-01-05 正常
10002 2017-02-05 正常
10002 2017-03-05 正常
10002 2017-04-05 正常
10002 2017-05-05 正常
10002 2017-06-05 正常
10002 2017-07-05 正常
10002 2017-08-05 正常
10002 2017-09-05 正常
10002 2017-10-05 正常
10002 2017-11-05 正常
10002 2017-12-05 最后1期
10002 2017-12-05 最后1期
*/
SELECT #T1.* ,
CASE WHEN RQ = t.maxrq THEN '最后1期'
WHEN RQ = t.minrq THEN '最早1期'
ELSE '正常'
END AS ZT
FROM #T1
LEFT JOIN ( SELECT XM ,
MAX(RQ) AS maxrq ,
MIN(RQ) AS minrq
FROM #T1
GROUP BY XM
) t ON t.XM = #T1.XM
IF OBJECT_ID('tempdb..#T1') IS NOT NULL DROP TABLE #T1
CREATE TABLE #T1(XM VARCHAR(20),RQ DATE)
INSERT INTO #T1 VALUES
('10001','2016/6/8'),
('10001','2016/7/5'),
('10001','2016/8/5'),
('10001','2016/9/5'),
('10001','2016/10/5'),
('10001','2016/11/5'),
('10002','2016/12/5'),
('10002','2017/1/5'),
('10002','2017/2/5'),
('10002','2017/3/5'),
('10002','2017/4/5'),
('10002','2017/5/5'),
('10002','2017/6/5'),
('10002','2017/7/5'),
('10002','2017/8/5'),
('10002','2017/9/5'),
('10002','2017/10/5'),
('10002','2017/11/5'),
('10002','2017/12/5'),
('10002','2017/12/5')
SELECT
XM
,MIN(RQ) AS minDate
,MAX(RQ) AS maxDate
FROM #T1 t
GROUP BY XM
/*
XM minDate maxDate
-------------------- ---------- ----------
10001 2016-06-08 2016-11-05
10002 2016-12-05 2017-12-05
*/
Select *
From #t1 a
Where RQ=(Select min(Rq) From #t1 b where b.XM=a.XM)
union
Select *
From #t1 a
Where RQ=(Select max(Rq) From #t1 b where b.XM=a.XM)