在单个sql查询中选择多个表

4 回答

• 1

  您需要加入：

  SELECT
       g.message,
       g.date,
       g.ip,
       g.name,
       g.website,
       g.id 
  FROM guestbook_message AS g
  LEFT JOIN m_votes AS v
  ON g.id = v.message_id
  WHERE g.deleted = 0 
  AND g.date > NOW() - INTERVAL 1 DAY
  GROUP BY g.id
  ORDER BY COUNT(v.message_id) DESC
  

  回复于 2024-05-02T17:23:14+08:00
• 5

  我是一个初学者，对我而言，这是一个非常难的查询，因为它需要在m_votes中找到'投票'的SUM并按此顺序排序 . 以及从其他查询中获取信息 . 这是获取有关消息的信息的查询：

  "SELECT message,  
           `date`,  
           ip,  
           name,  
           website,  
           id   
    FROM `guestbook_message`   
    WHERE deleted = 0   
       AND date > DATE_SUB(NOW(), INTERVAL 1 DAY)   
    ORDER BY `date` DESC";
  

  这是获取有关投票信息的查询：

  "SELECT SUM(votes) as votes FROM m_votes WHERE messageid = $mid"
  

  但我不知道我会将它们放入一个查询中，该查询将从第一个查询中收集所有信息，然后通过投票对它们进行排序 .

  回复于 2024-05-02T17:23:14+08:00
• 1

  您必须加入数据，也就是说，您需要为每个留言板留言投票 .

  让我们假设为了简单起见，您有以下表格：

  Message
  ----
  id INT
  messsageText VARCHAR(5000)
  

  和

  MessageVotes
  ------------
  messageId INT (references the `id` column in table Message)
  voteValue INT (suppose it can be +1 or -1, whatever)
  votingIp  VARCHAR(100)
  

  然后你可以做类似的事情

  SELECT
    m.id,
    m.messageText,
    SUM(mv.voteValue) AS votes
  FROM
    Message AS m,
    MessageVotes AS mv
  WHERE
    mv.messageId = m.id
  GROUP BY
    m.id, m.messageText /* here you need to place every field you `select` from Message */
  ORDER BY
    SUM(mv.voteValue) DESC
  

  甚至更好：

  SELECT
    m.id,
    m.messageText,
    SUM(mv.voteValue) AS votes
  FROM
    Message AS m
    LEFT JOIN MessageVotes AS mv ON mv.messageId = m.id
  GROUP BY
    m.id, m.messageText
  ORDER BY
    SUM(mv.voteValue) DESC
  

  ---

  看到：

  • mysql joins，

  • mysql left join

  • mysql group by - aggregate functions

  • Join Syntax

  回复于 2024-05-02T17:23:14+08:00
• 0

  请尝试下面的查询，你错过了group by子句 .

  SELECT g.message, 
          g.date, 
          g.ip, 
          g.name, 
          g.website, 
          g.id 
      FROM guestbook_message AS g 
      JOIN m_votes AS v ON g.id = v.vid 
      WHERE g.deleted = 0 
          AND v.messageid = $mid 
          AND g.date > DATE_SUB(NOW(), INTERVAL 1 DAY) 
      GROUP BY g.message, 
          g.date, 
          g.ip, 
          g.name, 
          g.website, 
          g.id
      ORDER BY SUM(v.votes) DESC;
  

  回复于 2024-05-02T17:23:14+08:00