求助: 一对多的两个表的关联问题。

[蝈蝈俊]

有这样两个表，

CREATE TABLE [dbo].[Topics](
[TopicID] [uniqueidentifier] NOT NULL,
[Subject] [nvarchar](256) NOT NULL,
}

CREATE TABLE [dbo].[Topics_log](
[LogID] [bigint] IDENTITY(1,1) NOT NULL,
[TopicID] [uniqueidentifier] NOT NULL,
[UserName] [nvarchar](20) NOT NULL,
[CreateDate] [datetime] NOT NULL,
[ModerateAction] [smallint] NOT NULL,
}


一个是内容数据表，一个是内容变更日志表。他们两个是 1对多 的关系， 通过 [TopicID] 关联


其中 [Topics_log] 表的 [ModerateAction] 字段记录的是是那种变更类型。
其中这个查询相关的是：
10 推荐标志，
11 取消推荐标志。
当然还有其它数字对应不同类型的变更。



我想写的查询是：

获得当前最新的20个推荐，
即[ModerateAction] 的最后一次出现 10 或者 11 时，出现的是 10 的情况。并按照这时候 [Topics_log] 表的 [CreateDate] 值排序的内容。

注意：可以反复的设置 推荐，不推荐，推荐，不推荐。


由于这两个表的数量级都是数百万级别的， 普通的写法性能会很差。 我想找一个性能高的写法。

这个帖子最终结分给查询时间最短的解决方案。

[-晴天]

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

[Herb2]

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

[蝈蝈俊]

to Herb2,qianjin036a,happyflystone

你们的最后回复都没问题，都可以查出需要的数据。

汗颜的是， 我自己把一个需求忘了说， Topic 还另外还有一个论坛编号字段。
查询的最终结果是只取某些论坛的， 某些论坛的不出现。

这时候 qianjin036a 的方案中把 Top 20 放到一个内部查询中，可能就会有问题。

Herb2 和 happyflystone 的方案则不存在这个问题

[蝈蝈俊]

to qianjin036a

子查询的中 Top 50, 外部查询再取 top 20,
万一子查询中获得数据正好不是我想要的那几个论坛的数据。 这时候外部的 top 20 就取不来数据了。

[zheninchangjiang]

传统写法你是怎么写的？

[蝈蝈俊]

to happyflystone

刚刚细看你的代码时候，发现你代码有一种情况有问题。

比如如果有下面的数据，则这条数据你的代码查询不出来这个应该出来的数据

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 

[Herb2]

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
*/

[蝈蝈俊]

现在 满足需求的就只剩下 Herb2 的方案。 暂时还没有看到更好的方案。

明天可以给帖子加分后，给这个帖子再加 100 分，然后揭帖。

Herb2 的方案 如下：

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

[Herb2]

感觉还是52楼的好些，对TOPICS表的条件便于扩展，而且性能应该是最好的。
从你的数据特征来看，对于每个TOPICID的记录数应该在1000以内,建议如下索引：
topics :topicid
topics_log:createdate,topicid

[-狙击手-]

[Quote=引用 56 楼 ghj1976 的回复:]
to happyflystone

刚刚细看你的代码时候，发现你代码有一种情况有问题。

比如如果有下面的数据，则这条数据你的代码查询不出来这个应该出来的数据



SQL code

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','200…
[/Quote]

外出有事归来，我看看

[-狙击手-]

国国，未发现问题呀，

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 行）
*/

[-狙击手-]

把二等草的代码 和我的这段放在查询分析中比较
同等条件下，比较查询成本似乎我的低点呀

[-晴天]

[Quote=引用 54 楼 ghj1976 的回复:]
to qianjin036a

子查询的中 Top 50, 外部查询再取 top 20,
万一子查询中获得数据正好不是我想要的那几个论坛的数据。 这时候外部的 top 20 就取不来数据了。
[/Quote]
把你的另一个条件写到子查询里不就行了?

[-晴天]

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 max(logid) from topics_log
where moderateaction=10 or moderateaction=11
group by topicid)
and b.moderateaction=10
order by b.logid desc

[fengyunzi_007]

学学 看看

[蝈蝈俊]

to happyflystone

汗颜, 昨天我不知道那里糊涂了，今天看是没有问题的。


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)

这段语句理解起来比较不容易理解

应该理解成
-- 每一个 Topic 最后一次出现 10，11 时开始，随后出现的内容。 含最后一次 出现 10或者11 这条记录。

[蝈蝈俊]

[Quote=引用 63 楼 qianjin036a 的回复:]
把你的另一个条件写到子查询里不就行了?
[/Quote]

如果这样，子查询就要关联表了。而且关联一个比较大的表。性能上划不来

[蝈蝈俊]

开始揭帖给分。 现在帖子是 200 分。

考虑到中间我问题描述不清楚，让大家有歧义， 而 Herb2 又是第一个写出满足我需求的答案。

采用近似平均给分的方式

Herb2 80 分


qianjin036a,happyflystone 每人 60 分

[空空空]

留个名

[RiboseYim]

好像越来越复杂了