如何重写UNION sql查询，使其以SELECT开头

I have an annoying issue that the this sql I need to write without UNION

我有一个恼人的问题,我需要在没有UNION的情况下编写这个sql

(SELECT entry_id, url_title, channel_id, entry_date, title 
 FROM exp_channel_titles 
 WHERE exp_channel_titles.channel_id = 5 
   AND status = 'open' 
 ORDER BY entry_date asc 
 LIMIT 2)
UNION
(SELECT entry_id, url_title, channel_id, entry_date, title 
 FROM exp_channel_titles 
 WHERE exp_channel_titles.channel_id = 6 
   AND status = 'open'
   AND entry_date > ((unix_timestamp(Now()))+(2*86400))
 ORDER BY entry_date asc LIMIT 1)
UNION
    (SELECT entry_id, url_title, channel_id, entry_date, title
    FROM exp_channel_titles
    WHERE exp_channel_titles.channel_id = 5
    AND status = 'open'
    ORDER BY entry_date asc LIMIT 1,2)
    LIMIT 3;

So what it does is select 2 news stories then an event in the future "story" then if no event story add another news story.

所以它做的是选择2个新闻故事,然后选择未来“故事”中的事件,然后如果没有事件故事添加另一个新闻故事。

So two categories, but one has a different select, so weighted there is always 3 but if possible an event.

所以有两个类别,但是一个有不同的选择,所以加权总是有3个,但如果可能的话就是一个事件。

I need to think of a way to do this with joins, or starting without the () brackets, to make it simpler. Where I load it does not like UNION.

我需要考虑使用连接执行此操作的方法,或者在没有()括号的情况下启动,以使其更简单。我加载的地方不喜欢UNION。

2 个解决方案

#1

This is the correct syntax:

这是正确的语法:

select * from (SELECT entry_id, url_title, channel_id, entry_date, title, 1 as comesFrom 
 FROM exp_channel_titles 
 WHERE exp_channel_titles.channel_id = 5 
   AND status = 'open' 
 ORDER BY entry_date asc 
 LIMIT 2) AS part1
UNION
select * from (SELECT entry_id, url_title, channel_id, entry_date, title, 2 as comesFrom
 FROM exp_channel_titles 
 WHERE exp_channel_titles.channel_id = 6 
   AND status = 'open'
   AND entry_date > ((unix_timestamp(Now()))+(2*86400))
 ORDER BY entry_date asc LIMIT 1) as part2
UNION
select * from  (SELECT entry_id, url_title, channel_id, entry_date, title, 3 as comesFrom
    FROM exp_channel_titles
    WHERE exp_channel_titles.channel_id = 5
    AND status = 'open'
    ORDER BY entry_date asc LIMIT 1,2) as part3
-- may be you want an order by here also
LIMIT 3;

I did also add a comesFrom field as a flag to know in the final recordset where the particular record comes from. It is useful in some cases.

我还添加了一个comeFrom字段作为标志,以便在最终记录集中知道特定记录的来源。在某些情况下它很有用。

#2

The answer in the end needed extra SELECT and AS alias to each subquery

答案最终需要为每个子查询提供额外的SELECT和AS别名

SELECT * FROM ( 
    SELECT * FROM (SELECT entry_id, url_title, channel_id, entry_date, title
        FROM exp_channel_titles
        WHERE channel_id = 5
        AND status = 'open'
        ORDER BY entry_date asc LIMIT 2) 
    AS news
UNION
    SELECT * FROM (SELECT entry_id, url_title, channel_id, entry_date, title
        FROM exp_channel_titles
        WHERE channel_id = 6
        AND status = 'open'
        AND entry_date > ((unix_timestamp(Now()))+(2*86400))
        ORDER BY entry_date asc LIMIT 1) 
    AS gallery
UNION 
    SELECT * FROM (SELECT entry_id, url_title, channel_id, entry_date, title
        FROM exp_channel_titles
        WHERE channel_id = 5
        AND status = 'open'
        ORDER BY entry_date asc LIMIT 1,2) 
    AS more_news
) AS articles LIMIT 3;

Seems MySQL when you wrap () with a SELECT * it now needs to join them differently so each () needs to now be SELECT * FROM () AS alias , which is a bit overkill, but makes sense as it wants to make all cols available.

当你用SELECT *换行时它似乎是MySQL,它现在需要以不同方式连接它们,所以每个()现在需要SELECT * FROM()AS别名,这有点矫枉过正,但是因为它想要制作所有的cols是有意义的可用。

I would still like a lighter solution as this is a lot of steps, but it works, so good with me.

我仍然想要一个更轻松的解决方案,因为这是很多步骤,但它有效,对我很好。

#1