这个SQL语句怎么写。写了半天写不出来，急求

tiantian2324 2017-11-22 09:56:56
LOTID TESTCNT POWER
0001 1 370
0001 2 365
0001 3 370

0002 1 365
0002 2 365

0003 1 380

0004 1 390
0004 2 395

0005 1 NULL
0005 2 370

0006 1 NULL


--------------这种数据是不会出现的不用考虑-----------------------
0007 1 370
0007 2 NULL
0007 3 375
--------------这种数据是不会出现的不用考虑-----------------------

我想要个下面这个结果 ，就是检查最近的一次测试结果 和上一次的测试结果 不一致就列出来 编号
0001
0004

在线等能用马上结贴.
...全文
222 14 打赏 收藏 转发到动态 举报
写回复
用AI写文章
14 条回复
切换为时间正序
请发表友善的回复…
发表回复
砸死牛顿的苹果 2017-11-22
  • 打赏
  • 举报
 回复
又改了下，这样测试效率高一点 
declare @t table(LOTID varchar(10),TESTCNT int,POWER int)
insert @t
select '0001','1',370
union all select '0001','1',365
union all select '0001','2',370
union all select '0001','3',370
union all select '0002','1',365
union all select '0003','1',380
union all select '0004','1',390
union all select '0004','2',395
union all select '0005','1',NULL
union all select '0005','2',370
union all select '0006','1',NULL
union all select '0007','1',370
union all select '0007','2',NULL
union all select '0007','3',375
 
select * from @t t1 
where exists
    (select 1 
	from 
		(select top 1 * from @t t3 where t1.LOTID =t3.LOTID order by t3.TESTCNT desc
		)t2 
    where t1.LOTID =t2.lotid
        and t1.TESTCNT+1=t2.TESTCNT
		and t1.POWER !=t2.POWER 
    )
砸死牛顿的苹果 2017-11-22
  • 打赏
  • 举报
 回复
改下 
declare @t table(LOTID varchar(10),TESTCNT int,POWER int)
insert @t
select '0001','1',370
union all select '0001','1',365
union all select '0001','2',370
union all select '0001','3',370
union all select '0002','1',365
union all select '0003','1',380
union all select '0004','1',390
union all select '0004','2',395
union all select '0005','1',NULL
union all select '0005','2',370
union all select '0006','1',NULL
union all select '0007','1',370
union all select '0007','2',NULL
union all select '0007','3',375
 
select * from @t t1 
where exists
    (select 1 from @t t2 
    where t1.LOTID =t2.lotid
        and t1.POWER !=(select top 1 t3.POWER from @t t3 where t2.LOTID =t3.LOTID order by t3.TESTCNT desc)
    group by t2.lotid
    having  t1.TESTCNT+1=max(t2.TESTCNT)
    )
砸死牛顿的苹果 2017-11-22
  • 打赏
  • 举报
 回复 
declare @t table(LOTID varchar(10),TESTCNT int,POWER int)
insert @t
select '0001','1',370
union all select '0001','1',365
union all select '0001','2',370
union all select '0001','3',370
union all select '0002','1',365
union all select '0003','1',380
union all select '0004','1',390
union all select '0004','2',395
union all select '0005','1',NULL
union all select '0005','2',370
union all select '0006','1',NULL
union all select '0007','1',370
union all select '0007','2',NULL
union all select '0007','3',375

select lotid from @t t1 
where exists
	(select 1 from @t t2 
	where t1.LOTID =t2.lotid
		and t1.POWER !=t2.POWER 
	group by t2.lotid
	having  t1.TESTCNT+1=max(t2.TESTCNT)
	)
/*
lotid
0004
*/
tiantian2324 2017-11-22
  • 打赏
  • 举报
 回复
引用 10 楼 ayalicer 的回复:
[quote=引用 8 楼 tiantian2324 的回复:] [quote=引用 5 楼 ayalicer 的回复:] 
declare @t table(LOTID varchar(10),TESTCNT int,POWER int)
insert @t
select '0001','1',370
union all select '0001','2',365
union all select '0001','3',370
union all select '0002','1',365
union all select '0002','2',365
union all select '0003','1',380
union all select '0004','1',390
union all select '0004','2',395
union all select '0005','1',NULL
union all select '0005','2',370
union all select '0006','1',NULL
union all select '0007','1',370
union all select '0007','2',NULL
union all select '0007','3',375

select distinct lotid from @t t1 
where exists
	(select 1 from @t t2 
	where t1.LOTID =t2.lotid 
		and t1.TESTCNT =t2.testcnt+1
		and t1.POWER !=t2.power
	)
/*
lotid
0001
0004
*/
这个查询有点错. 如果 有条记录 是 0001 1 365 0001 2 360 0002 3 360 这种数据 是不用查出来的 [/quote] 0001 也不用查出来? 0002 本来就不会出来[/quote] 写错了 是 0001 1 365 0001 2 360 0001 3 360
砸死牛顿的苹果 2017-11-22
  • 打赏
  • 举报
 回复
引用 8 楼 tiantian2324 的回复:
[quote=引用 5 楼 ayalicer 的回复:] 
declare @t table(LOTID varchar(10),TESTCNT int,POWER int)
insert @t
select '0001','1',370
union all select '0001','2',365
union all select '0001','3',370
union all select '0002','1',365
union all select '0002','2',365
union all select '0003','1',380
union all select '0004','1',390
union all select '0004','2',395
union all select '0005','1',NULL
union all select '0005','2',370
union all select '0006','1',NULL
union all select '0007','1',370
union all select '0007','2',NULL
union all select '0007','3',375

select distinct lotid from @t t1 
where exists
	(select 1 from @t t2 
	where t1.LOTID =t2.lotid 
		and t1.TESTCNT =t2.testcnt+1
		and t1.POWER !=t2.power
	)
/*
lotid
0001
0004
*/
这个查询有点错. 如果 有条记录 是 0001 1 365 0001 2 360 0002 3 360 这种数据 是不用查出来的 [/quote] 0001 也不用查出来? 0002 本来就不会出来
文盲老顾 2017-11-22
  • 打赏
  • 举报
 回复 
declare @t table(lotid varchar(5),testcnt int,power int)
insert into @t(lotid,testcnt,power) values('0001',1,370),('0001',2,365),('0001',3,370),('0002',1,365),('0002',2,365),('0003',1,380),('0004',1,390),('0004',2,395),('0005',1,null),('0005',2,370),('0006',1,null),('0007',1,370),('0007',2,null),('0007',3,375),('0008',1,370),('0008',2,375),('0008',3,375)
select lotid from @t a where not exists(select 1 from @t where lotid=a.lotid and testcnt>a.testcnt) and exists(select 1 from @t where lotid=a.lotid and testcnt=a.testcnt-1 and power<>a.power)
不用row_number和group来判断是否最后一次测试
tiantian2324 2017-11-22
  • 打赏
  • 举报
 回复
引用 5 楼 ayalicer 的回复:
declare @t table(LOTID varchar(10),TESTCNT int,POWER int)
insert @t
select '0001','1',370
union all select '0001','2',365
union all select '0001','3',370
union all select '0002','1',365
union all select '0002','2',365
union all select '0003','1',380
union all select '0004','1',390
union all select '0004','2',395
union all select '0005','1',NULL
union all select '0005','2',370
union all select '0006','1',NULL
union all select '0007','1',370
union all select '0007','2',NULL
union all select '0007','3',375

select distinct lotid from @t t1 
where exists
	(select 1 from @t t2 
	where t1.LOTID =t2.lotid 
		and t1.TESTCNT =t2.testcnt+1
		and t1.POWER !=t2.power
	)
/*
lotid
0001
0004
*/
这个查询有点错. 如果 有条记录 是 0001 1 365 0001 2 360 0002 3 360 这种数据 是不用查出来的
文盲老顾 2017-11-22
  • 打赏
  • 举报
 回复 
declare @t table(lotid varchar(5),testcnt int,power int)
insert into @t(lotid,testcnt,power) values('0001',1,370),('0001',2,365),('0001',3,370),('0002',1,365),('0002',2,365),('0003',1,380),('0004',1,390),('0004',2,395),('0005',1,null),('0005',2,370),('0006',1,null),('0007',1,370),('0007',2,null),('0007',3,375),('0008',1,370),('0008',2,375),('0008',3,375)
select lotid from (select *,row_number() over(partition by lotid order by testcnt desc) as rowid from @t) a where rowid=1 and exists(select 1 from @t where lotid=a.lotid and testcnt=a.testcnt-1 and power<>a.power)
tiantian2324 2017-11-22
  • 打赏
  • 举报
 回复
引用 4 楼 sinat_28984567 的回复:
用 TESTCNT来确定最新一次吗？ 
--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([LOTID] nvarchar(24),[TESTCNT] int,[POWER] int)
Insert #T
select N'0001',1,365 union all
select N'0001',2,365 union all
select N'0001',3,370 union all
select N'0002',1,365 union all
select N'0002',2,365 union all
select N'0003',1,380 union all
select N'0004',1,390 union all
select N'0004',2,395 union all
select N'0005',1,null union all
select N'0005',2,370 union all
select N'0006',1,null
Go
--测试数据结束
;WITH ctea AS (
SELECT LOTID,MAX(TESTCNT) AS TESTCNT FROM #T GROUP BY LOTID HAVING MAX(TESTCNT)>1
)
SELECT  ctea.LOTID
FROM    ctea
        JOIN #T a ON a.LOTID = ctea.LOTID
                     AND a.TESTCNT = ctea.TESTCNT
        JOIN #T b ON b.LOTID = ctea.LOTID
                     AND b.TESTCNT = ctea.TESTCNT - 1
WHERE   a.POWER <> b.POWER
[/code]
是的 用 TESTCnt 确定最新一次
砸死牛顿的苹果 2017-11-22
  • 打赏
  • 举报
 回复 
declare @t table(LOTID varchar(10),TESTCNT int,POWER int)
insert @t
select '0001','1',370
union all select '0001','2',365
union all select '0001','3',370
union all select '0002','1',365
union all select '0002','2',365
union all select '0003','1',380
union all select '0004','1',390
union all select '0004','2',395
union all select '0005','1',NULL
union all select '0005','2',370
union all select '0006','1',NULL
union all select '0007','1',370
union all select '0007','2',NULL
union all select '0007','3',375

select distinct lotid from @t t1 
where exists
	(select 1 from @t t2 
	where t1.LOTID =t2.lotid 
		and t1.TESTCNT =t2.testcnt+1
		and t1.POWER !=t2.power
	)
/*
lotid
0001
0004
*/
二月十六 版主 2017-11-22
  • 打赏
  • 举报
 回复
用 TESTCNT来确定最新一次吗？

--测试数据

if not object_id(N'Tempdb..#T') is null

	drop table #T

Go

Create table #T([LOTID] nvarchar(24),[TESTCNT] int,[POWER] int)

Insert #T

select N'0001',1,365 union all

select N'0001',2,365 union all

select N'0001',3,370 union all

select N'0002',1,365 union all

select N'0002',2,365 union all

select N'0003',1,380 union all

select N'0004',1,390 union all

select N'0004',2,395 union all

select N'0005',1,null union all

select N'0005',2,370 union all

select N'0006',1,null

Go

--测试数据结束

;WITH ctea AS (

SELECT LOTID,MAX(TESTCNT) AS TESTCNT FROM #T GROUP BY LOTID HAVING MAX(TESTCNT)>1

)

SELECT  ctea.LOTID

FROM    ctea

        JOIN #T a ON a.LOTID = ctea.LOTID

                     AND a.TESTCNT = ctea.TESTCNT

        JOIN #T b ON b.LOTID = ctea.LOTID

                     AND b.TESTCNT = ctea.TESTCNT - 1

WHERE   a.POWER <> b.POWER


[/code]

文盲老顾 2017-11-22
  • 打赏
  • 举报
 回复
这个回复有错误，看另一个帖子
文盲老顾 2017-11-22
  • 打赏
  • 举报
 回复 
declare @t table(lotid varchar(5),testcnt int,power int)
insert into @t(lotid,testcnt,power) values('0001',1,370),('0001',2,365),('0001',3,370),('0002',1,365),('0002',2,365),('0003',1,380),('0004',1,390),('0004',2,395),('0005',1,null),('0005',2,370),('0006',1,null),('0007',1,370),('0007',2,null),('0007',3,375)
select lotid from @t a where exists(select top 1 1 from @t where lotid=a.lotid and testcnt<>a.testcnt and power<>a.power) and not exists(select top 1 1 from @t where lotid=a.lotid and power is null) group by lotid
tiantian2324 2017-11-22
  • 打赏
  • 举报
 回复
http://bbs.csdn.net/topics/392289345 这还50分 有人嘛
急求SQL语句解决报表问题
有表A：cz_t_czr.mz(民族）,cz_t_czr.xb（性别）,cz_t_czr.bs（标示），字典表B：d_mz.bm(编码),d_mz.mc(名称)和字典表C：d_xb.bm(编码),d_xb.mc(名称)要求实现报表格式：民族 汉 回 蒙古 藏 
navicat运行sql文件时错误
记录一个navicat工具运行sql文件时遇到的坑： 错误如下： [ERR] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near...
SQL自动计算工龄
SELECT DATEDIFF(year,'2008-12-29（写入入职时间）','2019-12-30（写日记算到哪年）')/365 AS DiffDate GETDATE() //取当前日期，可以将其填入记算到哪年，获得时时更新的工龄数据
oracle执行计划完全一样,急求解答:两个相同的数据库的执行计划差别怎么那么大，附带执行计划!!!...
最近工作中遇到一个很郁闷的问题:前提:我一个培训环境，一个测试环境，...一个sql语句在两个配置和表结构相同的数据库中的执行计划不同，而且差别太大,查询结果出来后一个是5秒,一个几分钟sql语句:select *from (se...
最热网友收藏：30条经典的SQL语句(2007年第12周)
求30条经典的SQL语句,或是T-SQL语句.随后再开贴放分.(138)，共176人收藏，yuewh491首先收藏 吐血奉献：C#获取硬盘编号、CPU编号及类型、内存大小及使用情况(120)，共122人收藏，jrl5365首先收藏 微软的面试题...
MS-SQL Server

34,576

社区成员

254,586

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章