CREATE TABLE [dbo].[Web_Patient](
[ID] [int] NOT NULL,
[UserName] [nvarchar](255) COLLATE Chinese_PRC_CI_AS NULL,
[ALT] [nvarchar](255) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
INSERT INTO Web_Patient(ID,UserName,ALT) VALUES(1,'王三',45)
INSERT INTO Web_Patient(ID,UserName,ALT) VALUES(2,'王三',55)
INSERT INTO Web_Patient(ID,UserName,ALT) VALUES(3,'王三',65)
INSERT INTO Web_Patient(ID,UserName,ALT) VALUES(4,'李四',5)
INSERT INTO Web_Patient(ID,UserName,ALT) VALUES(5,'李四',35)
INSERT INTO Web_Patient(ID,UserName,ALT) VALUES(6,'李四',65)
INSERT INTO Web_Patient(ID,UserName,ALT) VALUES(7,'陈六',45)
INSERT INTO Web_Patient(ID,UserName,ALT) VALUES(8,'陈六',75)
INSERT INTO Web_Patient(ID,UserName,ALT) VALUES(9,'陈六',65)用這條語句select * from Web_Patient a
where ALT>(select top 1 ALT from Web_Patient where UserName=a.UserName and id<a.id order by id desc)
and
not exists(select 1 from Web_Patient where UserName=a.UserName and id>a.id),查詢出的數據是怎麼來的,
select top 1 ALT from Web_Patient where UserName=a.UserName and id<a.id order by id desc和select 1 from Web_Patient where UserName=a.UserName and id>a.id要怎麼解釋呢?
要單獨實現select top 1 ALT from Web_Patient where UserName=a.UserName and id<a.id order by id desc查詢出的結果要怎麼寫?
[ID] [int] NOT NULL,
[UserName] [nvarchar](255) COLLATE Chinese_PRC_CI_AS NULL,
[ALT] [nvarchar](255) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
INSERT INTO Web_Patient(ID,UserName,ALT) VALUES(1,'王三',45)
INSERT INTO Web_Patient(ID,UserName,ALT) VALUES(2,'王三',55)
INSERT INTO Web_Patient(ID,UserName,ALT) VALUES(3,'王三',65)
INSERT INTO Web_Patient(ID,UserName,ALT) VALUES(4,'李四',5)
INSERT INTO Web_Patient(ID,UserName,ALT) VALUES(5,'李四',35)
INSERT INTO Web_Patient(ID,UserName,ALT) VALUES(6,'李四',65)
INSERT INTO Web_Patient(ID,UserName,ALT) VALUES(7,'陈六',45)
INSERT INTO Web_Patient(ID,UserName,ALT) VALUES(8,'陈六',75)
INSERT INTO Web_Patient(ID,UserName,ALT) VALUES(9,'陈六',65)用這條語句select * from Web_Patient a
where ALT>(select top 1 ALT from Web_Patient where UserName=a.UserName and id<a.id order by id desc)
and
not exists(select 1 from Web_Patient where UserName=a.UserName and id>a.id),查詢出的數據是怎麼來的,
select top 1 ALT from Web_Patient where UserName=a.UserName and id<a.id order by id desc和select 1 from Web_Patient where UserName=a.UserName and id>a.id要怎麼解釋呢?
要單獨實現select top 1 ALT from Web_Patient where UserName=a.UserName and id<a.id order by id desc查詢出的結果要怎麼寫?
select * from Web_Patient t
where id=(select max(id) from Web_Patient where UserName=t.UserName)
/*
ID UserName
----------- ------------
3 王三
6 李四
9 陈六
*/
on a.UserName=b.UserName and a.id<b.id
where ALT>(select top 1 ALT from Web_Patient where UserName=a.UserName and id<a.id order by id desc)--这个条件定义同一个人最后一次插入的alt必须大于倒数第二次插入的数据
and
not exists(select 1 from Web_Patient where UserName=a.UserName and id>a.id)--这个条件查询出同一个人最后一次插入的数据
行 1: 'alt' 附近的語法不正確。
這個不行啊。
on a.UserName=b.UserName and a.id<b.id少了个逗号吧
也不行,提示服务器: 消息 209,级别 16,状态 1,行 1
列名 'ALT' 不明确。
--select * from tt t where not exists(select 1 from tt where 姓名=t.姓名 and 薪资>t.薪资 )
--
--对于表tt的记录进行扫描,
--对于其中任一条记录,查询相同表中相同姓名的记录,查找其中是否存在薪资>当前记录的薪资的记录,如存在,扫描到的记录即不符合条件,
--如不存在,即扫描到的记录符合where的条件,将作为查询结果.
--考虑最简单的情况
--姓名,薪资
--chen,1000 --此记录与全部chen记录1000-4000比较,2000以上>1000存在查询结果,此记录不符合where 条件
--chen,2000 --此记录与全部chen记录1000-4000比较,3000以上>2000存在查询结果,此记录不符合where 条件
--chen,3000 --此记录与全部chen记录1000-4000比较,4000>3000存在查询结果,此记录不符合where 条件
--chen,4000 --此记录与全部chen记录1000-4000比较,没有>4000的记录,此记录符合where 条件
--最后符合条件的记录为
--chen,4000
--条件就是查询相同姓名 没有比查询的记录的薪资高的记录
--实际结果就是薪资的最大值.
[ID] [int] NOT NULL,
[UserName] [nvarchar](255) COLLATE Chinese_PRC_CI_AS NULL,
[ALT] [nvarchar](255) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
INSERT INTO Web_Patient(ID,UserName,ALT) VALUES(1,'王三',5)
INSERT INTO Web_Patient(ID,UserName,ALT) VALUES(2,'王三',10)
INSERT INTO Web_Patient(ID,UserName,ALT) VALUES(3,'王三',45)
INSERT INTO Web_Patient(ID,UserName,ALT) VALUES(4,'李四',5)
INSERT INTO Web_Patient(ID,UserName,ALT) VALUES(5,'李四',35)
INSERT INTO Web_Patient(ID,UserName,ALT) VALUES(6,'李四',65)
INSERT INTO Web_Patient(ID,UserName,ALT) VALUES(7,'陈六',10)
INSERT INTO Web_Patient(ID,UserName,ALT) VALUES(8,'陈六',20)
INSERT INTO Web_Patient(ID,UserName,ALT) VALUES(9,'陈六',30)
go
--SQL:最近一次和上一次比较ALT恶化
select a.id, a.UserName, ALT = b.LastALT from
(select UserName, ID = MAX(id) from [Web_Patient] group by UserName) a --按姓名分组
cross apply
(select LastALT = ALT from [Web_Patient] where UserName = a.UserName and ID = a.ID) b --最后一条数据
cross apply
(select top(1) PriorALT = ALT from [Web_Patient] where UserName = a.UserName and ID < a.ID order by ID desc) C --倒数第二条数据
where LastALT NOT BETWEEN 10 AND 50 AND PriorALt BETWEEN 10 AND 50 --两次比较是否恶化
/*
id UserName ALT
6 李四 65
*/