MySQL:多行结果 VS 单行结果 [英] MySQL: multi rows result VS single row result

问题描述

我有这三个表，

page 表格

page_id    page_title
1          a

content 表格

content_id   content_text
1            text one
2            text two

内容结构表格

page_id     content_id     order_in_page
1           1              1
1           2              2

我的工作 sql，

SELECT 
    p.*,
    c.*,
    x.*

FROM pages AS p

LEFT JOIN pages_structures AS x
ON x.page_id = p.page_id

LEFT JOIN  pages_contents AS c
ON c.content_id = x.content_id

WHERE p.page_url = 'a'

结果，

page_id   page_title   content_text    order_in_page
1         a            text one        1    
1         a            text two        2

我追求的结果

page_id   page_title   content_1   content_2    content_3    content_4
1         a            text one    text two     null         null

如何将多行结果变成单行结果?

How can I make the multi-row result into a single row result?

或者多行结果比我想要的单行结果更好更快?

Or maybe the multi rows result is better and faster than a single row result that I want?

我想要单行结果的原因是我可以通过这样做调用 content_#，

the reason I want a single row result is that I can just call the content_# by doing this,

echo $page['content_1'];

就像我所说的标题，

echo $page['page_title'];

推荐答案

你可以很容易地使用 GROUP BY 和一个 IF 语句来完成你想要的每一列转置.当 MAX 计算时，它总是将非空值计算为大于 NULL.

You can do this quite easily using GROUP BY with an IF statement for each column you want to transpose. When MAX evaluates it will always evaluate a non-null value as greater than NULL.

SELECT
   page_id,
   page_title, 
   MAX(IF(order_in_page = 1, content_text, NULL)) AS content_1,
   MAX(IF(order_in_page = 2, content_text, NULL)) AS content_2,
   .
   .
   .
FROM 
   pages AS p LEFT JOIN 
   pages_structures AS x ON x.page_id = p.page_id LEFT JOIN  
   pages_contents AS c ON c.content_id = x.content_id
WHERE
   p.page_url = 'a'
GROUP BY page_id

这篇关于MySQL:多行结果 VS 单行结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！