主题：【求助】实现送花功能的SQL语法求助，回复者花 -- 卷心菜
家园 【求助】实现送花功能的SQL语法求助，回复者花 最近想给某个论坛实现类似河里送花的功能，遇上一个难题。
先简单地说一下论坛资料库结构：
ipf_posts---记录所有post的表格（所有主题帖与回复帖的具体发言、状态，包括所属的主题id，发言时间，发言者名称和id等等）。
ibf_topics---记录所有topic的表格（主题id、名称、发表时间、主题发起者的名称和id等等，但不包含任何具体发言内容）。
下面这段code是在特定论坛版面显示该版主题列表的mysql query：
$query = "SELECT DISTINCT(ibf_posts.author_id), ibf_topics.* FROM ibf_topicsLEFT JOIN ibf_posts ON
(ibf_topics.tid=ibf_posts.topic_id AND ibf_posts.author_id=".$ibforums->member['id'].")
WHERE ibf_topics.forum_id=".$this->forum['id']."
这段源码的效果是从ibf_topics中找出应该在本版显示的topic。其本质就是SELECT ibf_topics.* WHERE ibf_topics.forum_id=".$this->forum['id']。那些SELECT DISTINCT和LEFT JOIN ibf_posts只是为了把使用者参予过的主题在主题列表中标示出来。
现在在（ibf_posts）中有rep_inc（花）和rep_dec（草）这两栏，记录每帖收花、收草的数量。我打算在显示主题列表时把主题帖（该主题第一个发言）的收花/草数也显示在主题列表上，如同西西河一样。可是怎样在显示主题列表的query当中把记录在ibf_posts的花草数量也一并查出来呢？
ibf_topics中有主题发起者的id和发表时间，ibf_posts中同样有发表者id和发表时间，通过比较两者就可以从ibf_posts找到相应的row并挖出该主题帖的rep_inc（花）和rep_dec（草）。问题是query要怎样写？
如果分开来做，比如说先执行topic query然后根据每行的结果再去执行ibf_posts的query找出花草数量，理论上会比较简单。但是根据该论坛的排版，一个主题列表会有好几十行结果，变相每次显示主题列表要额外执行好几十次query，效率恐怕会大打折扣，所以我不打算使用这一方法。
我不常接触SQL，想了好久还是想不出什么，请大家帮帮忙吧。
===============================
更直观地说：
ibf_topics
外链图片需谨慎，可能会被源头改ibf_posts
外链图片需谨慎，可能会被源头改其实我想做的就是在
SELECT * from ibf_topics WHERE forum_id=".$this->forum['id']."
之余把两个表中的id和date（橙色部份）比较一下，把ibf_posts里面match的那个row当中的rep_inc和rep_dec栏合并到ibf_topics被SELECT的row上面。
关键词(Tags): #SQL，家园 这种写法效率太低 如果你使用Query的方式来计算,不管怎么样效率都太低,帖子数一多性能马上急剧下降,最好是改用另外一种方式就是增加新的表用来记录花草的数量,显示时直接引用比你每次都全表扫描来的好.计算积分可以用DTS job来计算,我相信CCthere也是用类似的方式来做的.有兴趣可以具体再聊
- 复 这种写法效率太低
家园 显示时确实是直接引用 那个Query并不是扫描全表来计算花草数量，Query是为了找出跟某主题相关的花草数字究竟储存在另一个表中的哪一行，然后把它join过来。就算新增一个表专门记录花草数量，也还是需要类似的query吧？当然一个更直接的办法就是在开帖时于ibf_topics表中记录下首发帖的id，这样就可以直接知道要拿另一个表中的哪一行。
至于计算积分，我在member表中有两栏是花/草数量，每次有人送花/扔草就一并写入该人相关的栏，显示积分时就直接引用。
家园 重复计算就是浪费 从你的Query来看,每次需要显示板块列表时,你都需要运行这个Query,对吧?同样的计算系统需要重复做多次,为什么不优化一下,例如在ibf_topics表中加字段保存首发贴的花草数,或者单独加一个表以免影响原有表结构,只有当首发贴有送花草时才重新计算,这样不更好吗? 你的Query就可以简化成
select ibf_topics.*, ibf_topics_extend.rep_inc,ibf_topics_extend.rep_dec from ibf_topics,ibf_topics_extend on ibf_topics.topic_id = ibf_topics_extend.topic_id
如果你不想做上面的修改的话,回到你现在的Query,也有可以优化的地方,那就是不要使用Left Join,这会让系统先将ibf_topics和ibf_posts中的全部记录进行外键匹配后再根据forum_id做过滤,这就是我前面帖子说的全表扫描,如果千万级甚至更多的记录数,left join对资源的消耗就会大到你不能忽视的地步,这时候我们可以用函数来代替left join,你可以在性能监视器中观察两种方法的优劣.
Sample function fn_Get_rep_inc
(
@topic_id INT,
@author_id INT,
@post_date INT
)
returns @ref_inc INT
as
begin
select @ref_inc = ibf_posts.rep_inc from ibf_posts where toic_id=@topic_id and author_id=@author_id and post_date = @post_date
return
End
这样你的Query可以改成
select ibf_topics.*, fn_Get_rep_inc(ibf_topics.topic_id,ibf_topics.starter_id, ibf_topics.starter_date), fn_Get_rep_dec(ibf_topics.topic_id,ibf_topics.starter_id, ibf_topics.starter_date) from ibf_topics WHERE ibf_topics.forum_id=".$this->forum['id']."
家园 你的要求是有难度的 你的原始查询是列出某个发言者参与过的所有主题（不管他是不是这个主题的发起人）。对于一个主题，当他只是跟帖者的时候，ibf_posts中对应author_id记录的row并不是你想输出rep_inc（花）和rep_dec（草）的row，这才是问题的关键。所以，如果你不想分两步做的话，就要一点技巧，我这里用“别名”（Alias）。
$query = "SELECT DISTINCT(post1.author_id), ibf_topics.*, post2.rep_inc, post2.rep_dec
FROM ibf_topics
LEFT JOIN ibf_posts AS post1 ON
(ibf_topics.tid=post1.topic_id AND post1.author_id=".$ibforums->member['id'].")
LEFT JOIN ibf_posts AS post2 ON
(ibf_topics.starter_id=post2.author_id AND ibf_topics.start_date=post2.post_date)
WHERE ibf_topics.forum_id=".$this->forum['id']."
家园 哈，这正是我需要的 用这个应该可以同时满足两个功能的要求。
家园 问题已经解决，感谢留言的朋友 最后写成这样了，透过比较发帖id和时间辨认哪个是首发帖，放弃原本‘标示使用者参予帖’的功能，不然没法拿LEFT JOIN一物两用。
$query = "SELECT ibf_posts.author_id, ibf_posts.post_date, ibf_posts.rep_inc, ibf_posts.rep_dec, ibf_topics.* FROM ibf_topicsLEFT JOIN ibf_posts ON
(ibf_topics.starter_id=ibf_posts.author_id AND ibf_topics.start_date=ibf_posts.post_date)
WHERE ibf_topics.forum_id=".$this->forum['id']."
家园 我觉得应该适当更改一下表结构 在ibf_posts表中加一个字段作为标志位，比如primary_post,标明是否是首发贴，处理起来会简单得多，这时SQL可以这样写：
$query = "SELECT DISTINCT(ibf_posts.author_id), ibf_topics.* FROM ibf_topics
LEFT JOIN ibf_posts ON
(ibf_topics.tid=ibf_posts.topic_id AND ibf_posts.primary_post = true)
WHERE ibf_topics.forum_id=".$this->forum['id']."
家园 羽羊盖个歪楼，提些自己粗浅的想法 也许没有必要区分主题贴和跟帖，一个表记录所有帖子，内容倒是可以放到子表当中
就不画图了，写下来，应该不难懂
有三张表
建立post表，记录帖子标题、相关信息，有parent_id和记录id字段建立关联，可以很方便建树，
建立post_content表，记录帖子内容，有字段 post_id作为外键和post表建立关系，
建立comment表，记录送的花花草草和相关信息，有post_id作为外键和post表建立关系
这样在平面显示帖子列表，象西西河这样每个帖子显示局部讨论串，以及书展都很方便
羽羊胡言乱语，各位大牛拍砖
家园 是这样的 其实讨论版核心用的是现成的程序，需要什么功能里面没有自己再改，这样省心很多。
当然，基本的资料库架构我就保留原样了，主题帖和跟帖并没有分开（内容都在ibf_posts表里边），只是关于主题标题等资讯另有一个表，前者类似于你说的post_content表，后者类似于你说的post表。花花草草我整合在发帖表里面，反正也只是两栏。
除了这些，还有很多其他大大小小的表（会员资料、短信、讨论版、分类、等级设定、管理记录......）好几十个。
要是全部自己写，不知要写到什么时候，呵呵。