简化 MySQL 嵌套 SELECT

Simplifing MySQL nested SELECT

提问by user1643156

id    title    slug    summary
------------------------------
1     title1   slug1   summary1
2     title2   slug2   summary2
3     title3   slug3   summary3
4     title4   slug4   summary4

I'm trying to select all fields, and in the meanwhile, select id, title and slug of prev/next row

我正在尝试选择所有字段，同时选择上一行/下一行的 id、title 和 slug

SELECT
    title, slug, summary, id as current_id,
    (SELECT id    FROM table WHERE id < current_id ORDER BY id DESC LIMIT 1) AS prev_id,
    (SELECT title FROM table WHERE id < current_id ORDER BY id DESC LIMIT 1) AS prev_title,
    (SELECT slug  FROM table WHERE id < current_id ORDER BY id DESC LIMIT 1) AS prev_slug,
    /*
    (SELECT id ...  ) AS next_id
    (SELECT title...) AS next_title
    ...
    and if there are more fields to select, I have to repeat this (SELECT...)
    */
FROM
    table
WHERE
    id IN (2,3,4);

The query works but apparently it's not the smart way to do so.

查询有效，但显然这不是这样做的聪明方法。

Can some please help with simplifing this? Thanks

有人可以帮助简化这个吗？谢谢

回答by user1643156

OK, I thought this would be easy. But after an hour without a working solution, I'm gonna answer my own question with the way I just figured out.

好吧，我以为这会很容易。但是在一个小时没有有效的解决方案之后，我将用我刚刚想出的方式回答我自己的问题。

using CONCAT_WS

使用CONCAT_WS

SELECT
    title, slug, summary, id as current_id,
    (
        SELECT
            CONCAT_WS(',' id, title, slug)
        FROM
            table
        WHERE
            id < current_id ORDER BY id DESC LIMIT 1)
    ) AS prev_data,
    (
        SELECT
            CONCAT_WS(',' id, title, slug)
        FROM
            table
        WHERE
            id > current_id ORDER BY id ASC LIMIT 1)
    ) AS next_data
FROM
    table
WHERE
    id IN (2,3,4);

and the result would be something like

结果会是这样的

id        => 2
title     => title2
slug      => slug2
summary   => summary2
prev_data => 1,title1,slug1
next_data => 3,title3,slug3

then I have to explode(PHP) prev_dataand next_datato get details.

然后我必须explode（PHP）prev_data并next_data获取详细信息。

I'm still looking for a (better) way to do this with MySQL only.

我仍在寻找一种（更好的）方法来仅使用 MySQL 来执行此操作。

回答by KaeL

Assuming the Idcolumn is auto_incrementand the values do not have gaps between id's(meaning they are incremented as 1, 2, 3, 4. No gaps in between like 1, 3, 4, 6), then you can try this one:

假设该Id列是auto_increment与这些值不必之间的间隙id的（这意味着它们被增值为1，2，3，4，在没有像之间1，3，4，6的间隙），则可以尝试这一个：

SELECT T.Id AS CurrentId
    , T.Title AS CurrentTitle
    , T.Slug AS CurrentSlug
    , T.Summary AS CurrentSummary
    , IFNULL(P.Id, -1) AS PreviousId
    , IFNULL(P.Title, '') AS PreviousTitle
    , IFNULL(P.Slug, '') AS PreviousSlug
    , IFNULL(P.Summary, '') AS PreviousSummary
    , IFNULL(N.Id, -1) AS NextId
    , IFNULL(N.Title, '') AS NextTitle
    , IFNULL(N.Slug, '') AS NextSlug
    , IFNULL(N.Summary, '') AS NextSummary
    FROM table T
    LEFT JOIN table P ON P.Id - 1 = T.Id
    LEFT JOIN table N ON N.Id + 1 = T.Id
    WHERE T.Id IN (2, 3, 4);

Otherwise, the answer you've posted is correct.

否则，您发布的答案是正确的。

回答by Rafal Kozlowski

Maybe something like this will work. I didn't test it so I'm not sure, but looks good :)

也许这样的事情会奏效。我没有测试所以我不确定，但看起来不错:)

SELECT
  current.title as current_title,
  current.slug as current_slug,
  current.summary as current_summary, 
  current.id as current_id,
  prev.title as prev_title,
  prev.slug as prev_slug,
  prev.summary as prev_summary,
  prev.id as prev_id,
  next.title as next_title,
  next.slug as next_slug,
  next.summary as next_summary,
  nexrt.id as next_id
FROM
  `table` current LEFT JOIN 
  `table` prev ON prev.id = current.id - 1 LEFT JOIN
  `table` next ON next.id = current.id + 1                    
WHERE
  current.id IN (2,3,4)

回答by im_chc

Sorry for necromancing a 6 yo question, but have to do so :)
LAG()and LEAD()to the rescue!
Pretty handy, available in Oracle and SQL Server too:

很抱歉对一个 6 岁的问题进行了死机处理，但必须这样做:)
LAG()并LEAD()进行救援！
非常方便，在 Oracle 和 SQL Server 中也可用：

select 
    title, lead (title) over (order by id) next_title, lag (title) over (order by id) prev_title,
    slug, lead (slug) over (order by id) next_slug, lag (slug) over (order by id) prev_slug,
    summary, lead (summary) over (order by id) next_summary, lag (summary) over (order by id) prev_summary,
    id, lead (id) over (order by id) next_id, lag (id) over (order by id) prev_id
from `table`
;
/*
create table `table` as
(select id, concat('title', id) title, concat('slug', id) slug, concat('summary', id) summary
from
(select 1 id union all
select 14 id union all
select 34 id union all
select 13 id union all
select 2 id union all
select 4 id ) as q)
*/

回答by Timothy

You could extract the columns from one subquery:

您可以从一个子查询中提取列：

SELECT
    title, slug, summary, id as current_id, prev.prev_id, prev.prev_title, prev.prev_slug, next.next_id, next.next_title, next.next_slug
FROM
    table,
    (SELECT id AS prev_id, title AS prev_title, slug AS prev_slug FROM table WHERE id < 2 ORDER BY id DESC LIMIT 1) AS prev,
    (SELECT id AS next_id, title AS next_title, slug AS next_slug FROM table WHERE id > 2 ORDER BY id DESC LIMIT 1) AS next
WHERE
    id = 2;

But this wouldn't work if you have to use an INclause in your where; you would need to run this query for each value of id...

但是，如果您必须IN在 where 中使用子句，这将不起作用；您需要为id...的每个值运行此查询