1select * from tab tmp
where (select count(*) from tab where class=tmp.class and chengji>tmp.chengji)<22select 号码,max(发送时间) from tab group by convert(varchar(10),发送时间)
where (select count(*) from tab where class=tmp.class and chengji>tmp.chengji)<22select 号码,max(发送时间) from tab group by convert(varchar(10),发送时间)
Select * From 表1 A Where Not Exists
(Select 1 From 表1 Where Class=A.Class And Chengji>A.Chengji)
2、
Select * From @表2 A Where Not Exists
(Select 1 From @表2 Where 号码=A.号码 And
Convert(Varchar(10),发送时间,120)=Convert(Varchar(10),A.发送时间,120) And
发送时间>A.发送时间)
Order By 号码,发送时间
Declare @表1 Table(name Varchar(6),chengji Numeric(5,1),class int)
Insert @表1 Select '张三', 87.0, 1
Union All Select '李四', 43.0, 1
Union All Select '啊啊', '65.0', 1
Union All Select '等待', '66.0', 2
Union All Select '哈哈', '98.0', 2
Union All Select '就就', '78.0', 2
---查询结果
Select * From @表1 A Where Not Exists
(Select 1 From @表1 Where Class=A.Class And Chengji>A.Chengji)
/*
name chengji class
------ ------- -----------
张三 87.0 1
哈哈 98.0 2(所影响的行数为 2 行)
*/---2、创建测试数据
Declare @表2 Table(号码 int,发送时间 Datetime)
Insert @表2 Select 1381,'2007-08-02 17:51:00.000'
Union ALl Select 1381,'2007-08-02 18:26:00.000'
Union ALl Select 1382,'2007-08-02 19:31:00.000'
Union ALl Select 1381,'2007-08-03 11:21:00.000'
---查询结果
Select * From @表2 A Where Not Exists
(Select 1 From @表2 Where 号码=A.号码 And
Convert(Varchar(10),发送时间,120)=Convert(Varchar(10),A.发送时间,120) And
发送时间>A.发送时间)
Order By 号码,发送时间
/*
号码 发送时间
----------- -------------------------
1381 2007-08-02 18:26:00.000
1381 2007-08-03 11:21:00.000
1382 2007-08-02 19:31:00.000(所影响的行数为 3 行)
*/
select name,chengji
from classTable as a
where exists
(select top 2 * from classTable where chengji<a.chengji and class=a.class)
select *
from test as a
where not exists
(select 1 from test where 发送时间>a.发送时间 and 号码=a.号码)
select *
from test as a
where not exists
(select 1 from test where 发送时间>a.发送时间 and 号码=a.号码)
-----
LS你这个是错误的,请看清题目(求每个号码每天发送时间最晚的记录)
"每天发送"