双11狂欢大促

盲盒赢 定制羽绒服

开启惊喜盲盒，抽取你的专属好礼

— 惊喜奖品抢先看 —

超级年卡50元券

周边70元券

手机支架

一拖三数据线

雨伞

运动毛巾

电动牙刷

VIP月卡

机械键盘

定制羽绒服

超级年卡50元券

周边70元券

手机支架

一拖三数据线

雨伞

运动毛巾

电动牙刷

VIP月卡

机械键盘

定制羽绒服

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

2017-11-24 04:32:59 613 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

qq_33079425 墨衍会员 · AI 创作全网分发关注 + 加入社区

Cursor+Playwright MCP实战：如何用自然语言一键生成UI自动化测试脚本本文探讨了如何利用Cursor与Playwright MCP实现自然语言驱动的UI自动化测试脚本生成。通过智能IDE与浏览器自动化协议的结合，开发者可以高效创建稳定的测试脚本，显著提升测试效率与维护性。重点介绍了MCP架构的优势、项目结构最佳实践及团队协作规范，为自动化测试带来革命性改进。阅读详情

相同编号下，计算出最早与最后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')

...全文

GAN网络详解析介绍目前常见的GAN网络，以及GAN网络的基本组成阅读详情

qq_33079425

疑难问题 22,298 成员 121,727 内容

+ 关注 + 加入社区

切换为时间正序

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)

✦双11狂欢大促✦

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

相关推荐

双11狂欢大促