表LC_LeaveWord
LeavewordID
1 0 0 0 1
2 0 0 0 2
5 0 0 0 5
8 0 0 0 8
9 0 0 0 9
10 0 0 0 10
11 0 0 0 11
12 0 0 0 12表LC_LeaveWordReply
ReplyID LeavewordID ReplyUser ReplyContent RTime
1 1 Simon uuu 2008-03-11 00:00:00.000
2 1 zy yyy 2008-05-15 00:00:00.000
3 1 yyy yyy 2008-04-14 00:00:00.000
4 2 SS 331 2008-05-14 00:00:00.000
11 5 5 NULL
12 8 8 NULL
13 9 9 NULL
14 10 10 NULL
15 11 11 NULL
16 12 12 NULL用LeavewordID把两个表连起来 在表LC_LeaveWordReply中取时间最新的求个sql语句
LeavewordID
1 0 0 0 1
2 0 0 0 2
5 0 0 0 5
8 0 0 0 8
9 0 0 0 9
10 0 0 0 10
11 0 0 0 11
12 0 0 0 12表LC_LeaveWordReply
ReplyID LeavewordID ReplyUser ReplyContent RTime
1 1 Simon uuu 2008-03-11 00:00:00.000
2 1 zy yyy 2008-05-15 00:00:00.000
3 1 yyy yyy 2008-04-14 00:00:00.000
4 2 SS 331 2008-05-14 00:00:00.000
11 5 5 NULL
12 8 8 NULL
13 9 9 NULL
14 10 10 NULL
15 11 11 NULL
16 12 12 NULL用LeavewordID把两个表连起来 在表LC_LeaveWordReply中取时间最新的求个sql语句
LeavewordID
---------------
怎么是一串数字,如何理解
--------------------------两个表分组后取第一条---------declare @a table(date int ,ID int)
insert into @a select 100,5
insert into @a select 175,5
insert into @a select 175,6
insert into @a select 220,6declare @b table(ID int)
insert into @b select 5
insert into @b select 6
--最小
select a.id,min(date)
from @a a inner join @b b on a.id=b.id
group by a.id
--最大
select a.id,max(date)
from @a a inner join @b b on a.id=b.id
group by a.id
--第一条
select c.*
from (select a.* from @a a where date=(select top 1 date from @a b where ID=a.ID )) c
inner join @b b on c.id=b.id
--随机一条
select c.*
from (select a.* from @a a where date=(select top 1 date from @a b where ID=a.ID order by newid())) c
inner join @b b on c.id=b.id
--------------------------去除重复的记录,有一条后主键不重复---------
--按记录顺序取第一条4 种取法
select a.* from t a where d=(select top 1 d from t where a=a.a and b=a.b and c=a.c)--取最小
select a.* from t a where d=(select min(d) from t where a=a.a and b=a.b and c=a.c)--取最大
select a.* from t a where d=(select max(d) from t where a=a.a and b=a.b and c=a.c)--随机取
select a.* from t a where d=(select top 1 d from t where a=a.a and b=a.b and c=a.c order by newid())--取最大##
select a.* from T a where not exists(select 1 from T where C2=a.C2 and C3=a.C3 and C1 <a.C1)
-------------得到ID相同记录date最大的3中方法--------------------
--1
select a.* from @t a,(select id, max(date) date from @t
group by id) b where a.id=b.id and a.date=b.date
--2
select * from @t a
where [date]=(select max(date) from @t where a.id=id )
--3
select * from @t a where not exists (
select 1 from @t where id=a.id and date>a.Date)