22,300
社区成员




insert Topics(TopicID,Subject) values ('00008800-7BBC-4976-8377-00015BE16F83','TExt 2510?')
insert Topics_log([TopicID] ,[UserName] ,[CreateDate] ,[ModerateAction] )
values ('00008800-7BBC-4976-8377-00015BE16F83','ghj1976','2008-04-08',10);
go
select top 10 b.subject,b.topicid,a.UserName,a.CreateDate,a.TopicID
from
(
select * from Topics_log c
where not exists(
select 1 from Topics_log where topicid = c.topicid and logid > c.logid and ModerateAction between 10 and 11)
) a left join Topics b on a.topicid = b.topicid
where a.ModerateAction = 10 order by CreateDate desc
/*
subject topicid UserName CreateDate TopicID
-------------------------------------------------- ------------------------------------ -------------------- ------------------------------------------------------ ------------------------------------
急 唯一值问题 8796C07B-5798-4110-94CF-000161D677AE AA123ssAA 2008-04-08 00:00:00.000 8796C07B-5798-4110-94CF-000161D677AE
TExt 2510? 00008800-7BBC-4976-8377-00015BE16F83 ghj1976 2008-04-08 00:00:00.000 00008800-7BBC-4976-8377-00015BE16F83
SQL2005能不能把N台服务器组成一个组,然后加快查询速度? 6CB2CEA2-7BBC-4976-8377-00015BE16F83 AA234AA 2008-04-05 00:00:00.000 6CB2CEA2-7BBC-4976-8377-00015BE16F83
求一个图像重绘的详细的教程 C66BE975-70BE-43C3-AC0B-0000857EE65B AAccAA 2008-04-05 00:00:00.000 C66BE975-70BE-43C3-AC0B-0000857EE65B
(所影响的行数为 4 行)
*/
select top 20 b.*,a.username,a.createdate from topics_log a,topics b
where logid = (select top 1 logid from topics_log where topicid = a.topicid and ModerateAction in(10,11) order by logid desc)
and ModerateAction = 10
and a.TopicID=b.TopicID
order by createdate desc
select * from topics a,(
select top 20 topicid,username,createdate from topics_log a
where logid = (select max(logid) from topics_log where topicid = a.topicid and (ModerateAction =10 or ModerateAction =11))
and ModerateAction = 10
order by createdate desc
)b
where a.TopicID=b.TopicID
/*
TopicID Subject topicid username createdate
------------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------ -------------------- ------------------------------------------------------
C66BE975-70BE-43C3-AC0B-0000857EE65B 求一个图像重绘的详细的教程 C66BE975-70BE-43C3-AC0B-0000857EE65B AAccAA 2008-04-05 00:00:00.000
8796C07B-5798-4110-94CF-000161D677AE 急 唯一值问题 8796C07B-5798-4110-94CF-000161D677AE AA123ssAA 2008-04-08 00:00:00.000
6CB2CEA2-7BBC-4976-8377-00015BE16F83 SQL2005能不能把N台服务器组成一个组,然后加快查询速度? 6CB2CEA2-7BBC-4976-8377-00015BE16F83 AA234AA 2008-04-05 00:00:00.000
*/
insert Topics(TopicID,Subject) values ('00008800-7BBC-4976-8377-00015BE16F83','TExt 2510?')
insert Topics_log([TopicID] ,[UserName] ,[CreateDate] ,[ModerateAction] )
values ('00008800-7BBC-4976-8377-00015BE16F83','ghj1976','2008-04-08',10);
-- happyflystone
select top 10 b.subject,b.topicid,a.UserName,a.CreateDate,a.TopicID
from
(
select * from Topics_log c
where not exists(
select 1 from Topics_log where topicid = c.topicid and logid > c.logid and ModerateAction between 10 and 11)
) a left join Topics b on a.topicid = b.topicid
where a.ModerateAction = 10 order by CreateDate desc
select top 20 b.*,a.username,a.createdate from topics_log a,topics b
where logid = (select top 1 logid from topics_log where topicid = a.topicid and ModerateAction in(10,11) order by logid desc)
and ModerateAction = 10
and a.TopicID=b.TopicID
order by createdate desc
select top 20 a.topicid,a.subject,b.userName,b.createdate,b.logid
from topics a inner join topics_log b on a.topicid=b.topicid
where b.logid in
(select top 50 max(logid) from topics_log
where moderateaction=10 or moderateaction=11
group by topicid order by max(logid) desc) and b.moderateaction=10
order by b.logid desc
go