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

蝈蝈俊 2008-05-28 01:34:28

有这样两个表,

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] 值排序的内容。

注意:可以反复的设置 推荐,不推荐,推荐,不推荐。


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

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

...全文
325 70 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
70 条回复
切换为时间正序
请发表友善的回复…
发表回复
RiboseYim 2008-05-31
  • 打赏
  • 举报
回复
好像越来越复杂了
空空空 2008-05-29
  • 打赏
  • 举报
回复
留个名
蝈蝈俊 2008-05-29
  • 打赏
  • 举报
回复
开始揭帖给分。 现在帖子是 200 分。

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

采用近似平均给分的方式

Herb2 80 分


qianjin036a,happyflystone 每人 60 分
蝈蝈俊 2008-05-29
  • 打赏
  • 举报
回复
[Quote=引用 63 楼 qianjin036a 的回复:]
把你的另一个条件写到子查询里不就行了?
[/Quote]

如果这样,子查询就要关联表了。而且关联一个比较大的表。性能上划不来
蝈蝈俊 2008-05-29
  • 打赏
  • 举报
回复
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 这条记录。
fengyunzi_007 2008-05-29
  • 打赏
  • 举报
回复
学学 看看
-晴天 2008-05-28
  • 打赏
  • 举报
回复
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
-晴天 2008-05-28
  • 打赏
  • 举报
回复
[Quote=引用 54 楼 ghj1976 的回复:]
to qianjin036a

子查询的中 Top 50, 外部查询再取 top 20,
万一子查询中获得数据正好不是我想要的那几个论坛的数据。 这时候外部的 top 20 就取不来数据了。
[/Quote]
把你的另一个条件写到子查询里不就行了?
-狙击手- 2008-05-28
  • 打赏
  • 举报
回复
把二等草的代码 和我的这段放在查询分析中比较
同等条件下,比较查询成本似乎我的低点呀
-狙击手- 2008-05-28
  • 打赏
  • 举报
回复
国国,未发现问题呀,

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 行)
*/
-狙击手- 2008-05-28
  • 打赏
  • 举报
回复
[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]

外出有事归来,我看看
Herb2 2008-05-28
  • 打赏
  • 举报
回复
感觉还是52楼的好些,对TOPICS表的条件便于扩展,而且性能应该是最好的。
从你的数据特征来看,对于每个TOPICID的记录数应该在1000以内,建议如下索引:
topics :topicid
topics_log:createdate,topicid
蝈蝈俊 2008-05-28
  • 打赏
  • 举报
回复
现在 满足需求的就只剩下 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 2008-05-28
  • 打赏
  • 举报
回复
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
*/
蝈蝈俊 2008-05-28
  • 打赏
  • 举报
回复
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

zheninchangjiang 2008-05-28
  • 打赏
  • 举报
回复
传统写法你是怎么写的?
蝈蝈俊 2008-05-28
  • 打赏
  • 举报
回复
to qianjin036a

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

蝈蝈俊 2008-05-28
  • 打赏
  • 举报
回复
to Herb2,qianjin036a,happyflystone

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

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

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

Herb2 和 happyflystone 的方案则不存在这个问题
Herb2 2008-05-28
  • 打赏
  • 举报
回复
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
-晴天 2008-05-28
  • 打赏
  • 举报
回复
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
加载更多回复(50)

22,300

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧