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

qq_33079425 2017-11-24 04:32:59


相同编号下,计算出最早与最后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')
...全文
385 9 打赏 收藏 举报
写回复
9 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
yilanwuyu123 2017-11-24
Min Max Group by
  • 打赏
  • 举报
回复
顺势而为1 2017-11-24


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

  • 打赏
  • 举报
回复
吉普赛的歌 2017-11-24
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 没什么关系。
  • 打赏
  • 举报
回复
qq_33079425 2017-11-24

谢谢各位的解答。。关键是不能用group by 啊,不能改变原有的数据结构。因为中间还有一列,没列出来,不好意思。
  • 打赏
  • 举报
回复
RINK_1 2017-11-24

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

  • 打赏
  • 举报
回复
吉普赛的歌 2017-11-24
;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期
*/
  • 打赏
  • 举报
回复
二月十六 2017-11-24
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


  • 打赏
  • 举报
回复
吉普赛的歌 2017-11-24
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
*/
  • 打赏
  • 举报
回复
顺势而为1 2017-11-24


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)

  • 打赏
  • 举报
回复
相关推荐
发帖
疑难问题

2.2w+

社区成员

MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
加入社区
帖子事件
创建了帖子
2017-11-24 04:32
社区公告
暂无公告