将两个查询合二为一 [英] Combining two queries into one

问题描述

我有两种类型的消息，一种是私人消息，另一种是信用消息，它们被插入到两个不同的表中.现在我正在尝试获取数据.

I have two type of message, one is private message and another is credit message are inserting into two different table. Now I'm trying to fetch the data.

SELECT * ,(SELECT COUNT(*) 
           FROM votes 
           WHERE message_id = m.message_id
             AND vote_type = 'like') AS likes,
          (SELECT COUNT(*) 
           FROM votes
           WHERE message_id = m.message_id 
           AND vote_type = 'dislike') AS dislikes 
FROM messages m
WHERE 1 #and hidden is null
  and recipient_id = 1
ORDER BY datetime DESC

和

SELECT * ,(SELECT COUNT(*) 
           FROM votes 
           WHERE message_id = m.message_id
             AND vote_type = 'like') AS likes,
          (SELECT COUNT(*)
           FROM votes
           WHERE message_id = m.message_id  
             AND vote_type = 'dislike') AS dislikes 
FROM private_messages m 
WHERE 1 #and hidden is null 
  and recipient_id = 1 
ORDER BY datetime DESC

现在想要将它们合并为一个查询，一个额外的参数将显示其私人消息或信用消息.

Now want to merge them into one query one extra parameter will show its private message or credit message.

推荐答案

如果现有查询满足您的要求/需要，UNION 将使组合它们变得非常简单，例如；

If the existing queries do what you want/need, UNION will make it pretty simple to combine them, something like;

SELECT * FROM (
    SELECT is_private 0, <field1>,<field2>,<field3>, ... ,(SELECT COUNT(*) 
               FROM votes 
               WHERE message_id = m.message_id
                 AND vote_type = 'like') AS likes,
              (SELECT COUNT(*) 
               FROM votes
               WHERE message_id = m.message_id 
               AND vote_type = 'dislike') AS dislikes 
    FROM messages m
    WHERE 1 #and hidden is null
      and recipient_id = 1
    UNION ALL
    SELECT 1, <field1>, <field2>, <field3>, ... ,(SELECT COUNT(*) 
               FROM votes 
               WHERE message_id = m.message_id
                 AND vote_type = 'like') AS likes,
              (SELECT COUNT(*)
               FROM votes
               WHERE message_id = m.message_id  
                 AND vote_type = 'dislike') AS dislikes 
    FROM private_messages m 
    WHERE 1 #and hidden is null 
      and recipient_id = 1 
)
ORDER BY datetime DESC

请注意，您需要从两个查询中选择相同数量/顺序的列才能使联合工作.SELECT * 很难验证是否是这种情况.如果

Note that you need to select the same number/order of columns from both queries for the union to work. SELECT * makes it hard to verify if/that that is the case. If

这篇关于将两个查询合二为一的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！