sql 求相同编号下最早与最晚日期

[qq_33079425]

相同编号下，计算出最早与最后1期。麻烦各位。


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')

[顺势而为1]

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)

• 打赏
• 举报

回复 赞

[吉普赛的歌]

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  #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

• 打赏
• 举报

回复 赞

[吉普赛的歌]

;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期
*/

• 打赏
• 举报

回复 赞

[RINK_1]

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

• 打赏
• 举报

回复 赞

[qq_33079425]

谢谢各位的解答。。关键是不能用group by 啊，不能改变原有的数据结构。因为中间还有一列，没列出来，不好意思。

• 打赏
• 举报

回复 赞

[吉普赛的歌]

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 没什么关系。

• 打赏
• 举报

回复 赞

[顺势而为1]

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

• 打赏
• 举报

回复 赞

[yilanwuyu123]

Min Max Group by

• 打赏
• 举报

回复 赞