php - 需要有关复杂嵌套查询的专家建议

Question

我有 3 个问题。有人告诉我他们可能效率低下，所以我想知道是否有经验的人可以提出任何建议。 逻辑有点复杂，请耐心等待。

我有两个表:shoutbox 和 topic。 Topic 存储有关已创建主题的所有信息，而 shoutbox 存储与每个主题相关的所有评论。每个评论都带有一个标记为 reply_chunk_id 的组。最早的时间戳是第一条评论，而任何具有相同 reply_chunk_id 和较晚时间戳的后续评论都是回复。我想找到用户发起的每个组的最新评论(第一个评论)，如果最新评论是本月发表的，则显示它。

我所写的实现了一个问题:所有最新的评论都以随机顺序显示。我想组织这些小组/最新评论。我非常感谢任何建议

Shoutbox

Field        Type
-------------------
id              int(5)           
timestamp       int(11)              
user            varchar(25)      
message         varchar(2000)    
topic_id        varchar(35)     
reply_chunk_id  varchar(35)

主题

id                    mediumint(8)       
topic_id              varchar(35)            
subject_id            mediumint(8)           
file_name             varchar(35)            
topic_title           varchar(255)           
creator               varchar(25)            
topic_host            varchar(255)           
timestamp             int(11)            
color                 varchar(10)            
mp3                   varchar(75)        
custom_background     varchar(55)            
description           mediumtext                     
content_type          tinyint(1)

查询

$sql="SELECT reply_chunk_id FROM shoutbox 
GROUP BY reply_chunk_id 
HAVING count(*) > 1 
ORDER BY timestamp DESC ";
$stmt16 = $conn->prepare($sql);
$result=$stmt16->execute();
while($row = $stmt16->fetch(PDO::FETCH_ASSOC)){


$sql="SELECT user,reply_chunk_id, MIN(timestamp) AS grp_timestamp
FROM shoutbox WHERE reply_chunk_id=? AND user=?";
$stmt17 = $conn->prepare($sql);
$result=$stmt17->execute(array($row['reply_chunk_id'],$user));
while($row2 = $stmt17->fetch(PDO::FETCH_ASSOC)){


$sql="SELECT t.topic_title, t.content_type, t.subject_id, 
    t.creator, t.description, t.topic_host,
    c1.message, c1.topic_id, c1.user, c1.timestamp AS max
FROM shoutbox c1 
JOIN topic t ON (t.topic_id = c1.topic_id)
WHERE reply_chunk_id = ? AND c1.timestamp > ?
ORDER BY c1.timestamp DESC, c1.id
LIMIT 1";
$stmt18 = $conn->prepare($sql);
$result=$stmt18->execute(array($row2['reply_chunk_id'],$month));
while($row3 = $stmt18->fetch(PDO::FETCH_ASSOC)){

Answer 1

进行第一个查询:

SELECT reply_chunk_id FROM shoutbox 
GROUP BY reply_chunk_id 
HAVING count(*) > 1 
ORDER BY timestamp DESC

这也是一样的，但是速度更快。确保您在 reply_chunk_id 上有索引.

第二个查询:

SELECT user,reply_chunk_id, MIN(timestamp) AS grp_timestamp
FROM shoutbox WHERE reply_chunk_id=? AND user=?

GROUP BY不需要，因为只返回一行，因为 MIN()和相等性测试。

第三个查询:

SELECT t.topic_title, t.content_type, t.subject_id, 
    t.creator, t.description, t.topic_host,
    c1.message, c1.topic_id, c1.user, c1.timestamp AS max
FROM shoutbox c1 
JOIN topic t ON (t.topic_id = c1.topic_id)
WHERE reply_chunk_id = ? AND c1.timestamp > ?
ORDER BY c1.timestamp DESC, c1.id
LIMIT 1

在一个查询中完成所有操作:

SELECT 
    t.user,t.reply_chunk_id, MIN(t.timestamp) AS grp_timestamp,
    t.topic_title, t.content_type, t.subject_id, 
    t.creator, t.description, t.topic_host,
    c1.message, c1.topic_id, c1.user, c1.timestamp AS max
FROM shoutbox c1 
INNER JOIN topic t ON (t.topic_id = c1.topic_id)
LEFT JOIN shoutbox c2 ON (c1.id = c2.id and c1.timestamp < c2.timestamp)
WHERE c2.timestamp IS NULL AND t.user = ?
GROUP BY t.reply_chunk_id
HAVING count(*) > 1
ORDER BY t.reply_chunk_id

或等效的

SELECT 
    t.user,t.reply_chunk_id, MIN(t.timestamp) AS grp_timestamp,
    t.topic_title, t.content_type, t.subject_id, 
    t.creator, t.description, t.topic_host,
    c1.message, c1.topic_id, c1.user, c1.timestamp AS max
FROM shoutbox c1 
INNER JOIN topic t ON (t.topic_id = c1.topic_id)
WHERE c1.timestamp = (SELECT max(timestamp) FROM shoutbox c2 
                      WHERE c2.reply_chunk_id = c1.reply_chunk_id)
  AND t.user = ?
GROUP BY t.reply_chunk_id
HAVING count(*) > 1
ORDER BY t.reply_chunk_id

这是如何工作的？

1. 分组依据为每个 topic.reply_chunk_id 选择一个条目
2. 左边加入(c1.id = c2.id and c1.`timestamp` < c2.`timestamp`) + WHERE c2.`timestamp` IS NULL仅从 shoutbox 中选择具有最高时间戳的那些项目。这是有效的，因为 MySQL 不断增加 c1.timestamp 以使 c2.timestamp 成为 null一旦为真，它的 c1.timestamp 将达到其最大值，并将在可能的行中选择该行以供选择。

如果你不明白第2点，请看:http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html

请注意，PDO 正在使用反引号自动转义字段