MS SQL SERVER
表A
id int
names nvarchar
times datetime现求2条SQL语句
1,names字段相同的记录只取每天的0点到24点的一条记录(最早的一条)。
2,取names字段相同的记录,要求每条记录的times字段的间隔大于24小时。要求效率尽可能高,不知道一句能不能写出来,如果不行求存储过程。
表A
id int
names nvarchar
times datetime现求2条SQL语句
1,names字段相同的记录只取每天的0点到24点的一条记录(最早的一条)。
2,取names字段相同的记录,要求每条记录的times字段的间隔大于24小时。要求效率尽可能高,不知道一句能不能写出来,如果不行求存储过程。
用两条语句吧:
1、Select a.* From [表A] a inner join (Select names,Min(times) As tm From [表A] Group by names) b on a.names=b.names And a.times=b.times2、没有理解透彻,讲清楚一点问题也许知道
sql="select distinct names from A order by times"2.
sql="select distinct times from A"
楼上的完全不对
1 x 2006-11-8 12:00:00
2 x 2006-11-9 1:00:00
3 x 2006-11-9 12:00:00
4 x 2006-11-9 18:00:00
5 x 2006-11-10 12:00:00
6 x 2006-11-10 14:00:00
7 x 2006-11-10 19:00:00现在只取id 1 4 7这样
a.times=b.tm
drop table tab
gocreate table tab
(
id int,
names varchar(10),
times datetime
)insert into tab(id,names,times) values(1,'A','2006-11-01 00:00:00')
insert into tab(id,names,times) values(2,'A','2006-11-01 01:00:00')
insert into tab(id,names,times) values(3,'A','2006-11-01 02:00:00')
insert into tab(id,names,times) values(4,'A','2006-11-02 01:00:00')
insert into tab(id,names,times) values(5,'A','2006-11-02 03:00:00')
insert into tab(id,names,times) values(6,'A','2006-11-04 01:00:00')
insert into tab(id,names,times) values(7,'A','2006-11-06 01:00:00')
insert into tab(id,names,times) values(8,'A','2006-11-06 02:00:00')select a.* from tab a,
(
select names , convert(varchar(10),times,120) as dates , min(times) as times
from tab
group by names,convert(varchar(10),times,120)
) b
where a.names = b.names and a.times = b.timesdrop table tabid names times
----------- ---------- ------------------------------------------------------
1 A 2006-11-01 00:00:00.000
4 A 2006-11-02 01:00:00.000
6 A 2006-11-04 01:00:00.000
7 A 2006-11-06 01:00:00.000(所影响的行数为 4 行)
declare @t table(id int, names nvarchar(10), times datetime)
insert @t
select 1, 'x', '2006-11-8 12:00:00' union all
select 2, 'x', '2006-11-9 1:00:00' union all
select 3, 'x', '2006-11-9 12:00:00' union all
select 4, 'x', '2006-11-9 18:00:00' union all
select 5, 'x', '2006-11-10 12:00:00' union all
select 6, 'x', '2006-11-10 14:00:00' union all
select 7, 'x', '2006-11-10 19:00:00'----查询1:
select * from @t as a
where not exists(select 1 from @t where names = a.names and datediff(day,times,a.times) = 0 and times > a.times)----查询2:请楼主写出希望的结果/*结果1
id names times
---------------------------------------
1 x 2006-11-08 12:00:00.000
4 x 2006-11-09 18:00:00.000
7 x 2006-11-10 19:00:00.000
*/
drop table tab
gocreate table tab
(
id int,
names varchar(10),
times datetime
)insert into tab(id,names,times) values(1,'x','2006-11-8 12:00:00')
insert into tab(id,names,times) values(2,'x','2006-11-9 1:00:00')
insert into tab(id,names,times) values(3,'x','2006-11-9 12:00:00')
insert into tab(id,names,times) values(4,'x','2006-11-9 18:00:00')
insert into tab(id,names,times) values(5,'x','2006-11-10 12:00:00')
insert into tab(id,names,times) values(6,'x','2006-11-10 14:00:00')
insert into tab(id,names,times) values(7,'x','2006-11-10 19:00:00')if object_id('pus..test') is not null
drop table test
goselect id1=identity(int,1,1) , * into test from tabdeclare @cnt as int
select @cnt = count(*) from test
declare @i as int
set @i = 1
declare @dt1 as datetime
declare @dt2 as datetime
set @dt1 = '1990-01-01'
while @i <= @cnt
begin
select @dt2 = times from test where id1 = @i
if datediff(hour,@dt1,@dt2) > 24
begin
set @dt1 = @dt2
print convert(varchar(20),@dt1,120)
end
set @i = @i + 1
enddrop table tab
drop table test--
2006-11-08 12:00:00
2006-11-09 18:00:00
2006-11-10 19:00:00
没有判断NAMES是否相等if object_id('pubs..tab') is not null
drop table tab
go
这个只能做测试用,实际应用怎么能把我的表drop掉,
而不drop掉的话“select id1=identity(int,1,1) , * into test from tab”这行就会提示已经有标识。
Select a.* From test a inner join (Select names,Min(times) As times From test Group by convert(varchar,times,2),names) b on a.names=b.names And a.times=b.times