table A
id insid name time
1 1 a 2011-5-20 14:55
2 2 a 2011-5-19 14:55
3 1 a 2011-5-18 14:55
4 2 a 2011-5-17 14:55
5 3 a 2011-5-16 14:55
6 4 a 2011-5-15 14:55
7 1 a 2011-5-14 14:55
8 5 a 2011-5-13 14:55
9 3 a 2011-5-12 14:55我想得到这样的结果
id insid name time
1 1 a 2011-5-20 14:55
2 2 a 2011-5-19 14:55
5 3 a 2011-5-16 14:55
6 4 a 2011-5-15 14:55
8 5 a 2011-5-13 14:55就是说insid不能重复,只能得到一个time要是最近的insid
id insid name time
1 1 a 2011-5-20 14:55
2 2 a 2011-5-19 14:55
3 1 a 2011-5-18 14:55
4 2 a 2011-5-17 14:55
5 3 a 2011-5-16 14:55
6 4 a 2011-5-15 14:55
7 1 a 2011-5-14 14:55
8 5 a 2011-5-13 14:55
9 3 a 2011-5-12 14:55我想得到这样的结果
id insid name time
1 1 a 2011-5-20 14:55
2 2 a 2011-5-19 14:55
5 3 a 2011-5-16 14:55
6 4 a 2011-5-15 14:55
8 5 a 2011-5-13 14:55就是说insid不能重复,只能得到一个time要是最近的insid
where [time ]=(select max([time]) from tb where insid=a.insid)
insert into tableA select 1,1,'a','2011-5-20 14:55'
insert into tableA select 2,2,'a','2011-5-19 14:55'
insert into tableA select 3,1,'a','2011-5-18 14:55'
insert into tableA select 4,2,'a','2011-5-17 14:55'
insert into tableA select 5,3,'a','2011-5-16 14:55'
insert into tableA select 6,4,'a','2011-5-15 14:55'
insert into tableA select 7,1,'a','2011-5-14 14:55'
insert into tableA select 8,5,'a','2011-5-13 14:55'
insert into tableA select 9,3,'a','2011-5-12 14:55'
go
select * from tableA a where not exists(select 1 from tableA where insid=a.insid and time>a.time)
/*
id insid name time
----------- ----------- ---------- -----------------------
1 1 a 2011-05-20 14:55:00.000
2 2 a 2011-05-19 14:55:00.000
5 3 a 2011-05-16 14:55:00.000
6 4 a 2011-05-15 14:55:00.000
8 5 a 2011-05-13 14:55:00.000(5 行受影响)*/
go
drop table tableA
not exists(select 1 from tableA where insid=a.insid and time>a.time)
--查询表a 条件是 不存在下面这个子查询里面
子查询自己和自己关联,查询的是 insid 相等的时候,time 较大的
连在一起也就是 不在子查询里面的insid 相等的时候,time 较大的
数据
insert into tableA select 1,1,'a','2011-5-20 14:55'
insert into tableA select 2,2,'a','2011-5-19 14:55'
insert into tableA select 3,1,'a','2011-5-18 14:55'
insert into tableA select 4,2,'a','2011-5-17 14:55'
insert into tableA select 5,3,'a','2011-5-16 14:55'
insert into tableA select 6,4,'a','2011-5-15 14:55'
insert into tableA select 7,1,'a','2011-5-14 14:55'
insert into tableA select 8,5,'a','2011-5-13 14:55'
insert into tableA select 9,3,'a','2011-5-12 14:55'
go
select * from tableA a
where time=(select max(time) from tableA b where b.insid=a.insid) order by id
/*
id insid name time
----------- ----------- ---------- -----------------------
1 1 a 2011-05-20 14:55:00.000
2 2 a 2011-05-19 14:55:00.000
5 3 a 2011-05-16 14:55:00.000
6 4 a 2011-05-15 14:55:00.000
8 5 a 2011-05-13 14:55:00.000(5 行受影响)*/
go
drop table tableA
select * from tableA a
where time=(select max(time) from tableA b where b.insid=a.insid) order by id这个比较好理解:
1、select max(time) from tableA b where b.insid=a.insid 字句先对insid分组找到时间最近的那条记录的时间。
2、然后从表中选出时间等于1中最近时间的记录,搞定。看看执行计划吧,比较清晰!
id insid name time
----------- ----------- ---------- -----------------------
1 1 a 2011-05-20 14:55:00.000
2 2 a 2011-05-19 14:55:00.000
5 3 a 2011-05-16 14:55:00.000
6 4 a 2011-05-15 14:55:00.000
8 5 a 2011-05-13 14:55:00.000
10 1 b 2011-05-16 14:55:00.000
11 2 b 2011-05-15 14:55:00.000
14 3 b 2011-05-12 14:55:00.000
(id int, insid int, name nvarchar(10), time datetime)
insert #A
select 1,1,'a','2011-5-20 14:55' union all
select 2,2,'a','2011-5-19 14:55' union all
select 3,1,'a','2011-5-18 14:55' union all
select 4,2,'a','2011-5-17 14:55' union all
select 5,3,'a','2011-5-16 14:55' union all
select 6,4,'a','2011-5-15 14:55' union all
select 7,1,'a','2011-5-14 14:55' union all
select 8,5,'a','2011-5-13 14:55' union all
select 9,3,'a','2011-5-12 14:55' union allselect 10,1,'b','2011-5-16 14:55' union all
select 11,2,'b','2011-5-15 14:55' union all
select 12,1,'b','2011-5-14 14:55' union all
select 13,2,'b','2011-5-13 14:55' union all
select 14,3,'b','2011-5-12 14:55' --select
select * from #A as a where
not exists(select 1 from #A as b where a.name=b.name and a.insid=b.insid and b.time>a.time)--id insid name time
------------- ----------- ---------- -----------------------
--1 1 a 2011-05-20 14:55:00.000
--2 2 a 2011-05-19 14:55:00.000
--5 3 a 2011-05-16 14:55:00.000
--6 4 a 2011-05-15 14:55:00.000
--8 5 a 2011-05-13 14:55:00.000
--10 1 b 2011-05-16 14:55:00.000
--11 2 b 2011-05-15 14:55:00.000
--14 3 b 2011-05-12 14:55:00.000
With cte AS
(
SELECT *,ROW_NUMBER() OVER (PARTITION BY insid ORDER BY time DESC) as rw FROM #A
)SELECT * FROM cte WHERE cte.rw=1
insert into @temp select 2,2,'a','2011-5-19 14:55'
insert into @temp select 3,1,'a','2011-5-18 14:55'
insert into @temp select 4,2,'a','2011-5-17 14:55'
insert into @temp select 5,3,'a','2011-5-16 14:55'
insert into @temp select 6,4,'a','2011-5-15 14:55'
insert into @temp select 7,1,'a','2011-5-14 14:55'
insert into @temp select 8,5,'a','2011-5-13 14:55'
insert into @temp select 9,3,'a','2011-5-12 14:55'
go;with orderby
as
(
select id,insid,[time],
row_number() over(partition by insid order by insid,[time] desc) as ros_id
from @temp
)select * from orderby
where ros_id = 1
order by insid-- Result
--1 1 2011-05-20 14:55:00.000 1
--2 2 2011-05-19 14:55:00.000 1
--5 3 2011-05-16 14:55:00.000 1
--6 4 2011-05-15 14:55:00.000 1
--8 5 2011-05-13 14:55:00.000 1
但是现在又有一个新问题 - -
table a就是上面的A表table b
insid name logo我要查出表B的name,logo,
条件是
insid in select * from tableA a where not exists(select 1 from tableA where insid=a.insid and time>a.time)order by time desc
但是顺序必须要和
select * from tableA a where not exists(select 1 from tableA where insid=a.insid and time>a.time)order by time desc
查出来的结果一致,请问这个该如何实现呢?
现在又有另一个问题了,希望你能帮我看看。table a就是上面的A表table b
insid name logo我要查出表B的name,logo,
条件是
SQL codeinsid in select * from tableA a where not exists(select 1 from tableA where insid=a.insid and time>a.time)order by time desc但是顺序必须要和
SQL codeselect * from tableA a where not exists(select 1 from tableA where insid=a.insid and time>a.time)order by time desc查出来的结果一致,请问这个该如何实现呢?
(
select row_number()over(partition by insid,order by time desc) as rownumber ,A.* from A
)
select * from test where rownumber =1
from tableA a,tableb b
where a.insid=b.insid and not exists(select 1 from tableA where insid=a.insid and time>a.time)
order by a.time desc