PHP SQL LEFT JOIN语句丢失了一列 | JOIN语句丢失了一列

本文介绍了PHP SQL LEFT JOIN语句丢失了一列的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我正在使用本文中建议的查询 https://stackoverflow.com/a/51816820/6822845 列出我的表内容.这真的很好，我得到了每个节的列表，其子节用逗号分隔在另一列中，因此我可以将其分解并将其转换为数组.我的问题是，我的sections表(在上面链接的其他文章下提到)有另一个名为"sorder"的列，用于保存显示顺序.我不知道为什么，但是我无法将列选择每次输出0时都将其输出.

I'm using the query suggested in this post https://stackoverflow.com/a/51816820/6822845 to list my table contents. This is working really fine, I get a list of every section with its subsections comma seperated in another column so I can explode it and convert it to an array. My problem is, that my sections table(mentioned under my other post linked above) has another column named "sorder" that holds the displaying order. I don't know why, but I'm not able to output it with the column selects as its every time 0.

SELECT sections.sorder as sorder ,section_titel as t1,
       GROUP_CONCAT(sub_section_titel) as t2
FROM sections
    LEFT JOIN sub_sections ON section_id = sId
GROUP BY t1
ORDER by sorder

每次运行时，顺序显示为"0".但这不是0.奇怪的是，我可以读取sub_section表中的"iorder"列.但是主表"sections"中的"sorder"列无法访问/每次为0.我正在使用Mysql:

Every time I run it sorder displays as "0". But it's not 0. The weird thing is, that I can read the "iorder" column which is in my sub_section table. But the "sorder"-column which is in the main-table "sections" isn't accessible / every time 0. I'm using Mysql:

推荐答案

我创建了 SQLFiddle ，希望它能代表您的数据.在该示例中，ORDER BY sorder正常工作.我还在GROUP_CONCAT中加入了ORDER BY，以便可以对小节标题进行排序.因此，使用原始查询:

I've created an SQLFiddle which I hope represents your data. In that example, ORDER BY sorder works fine. I've also included an ORDER BY in the GROUP_CONCAT so that subsection titles can be ordered to. So, with the original query:

SELECT section_titel as t1, GROUP_CONCAT(sub_section_titel) as t2
FROM sections LEFT JOIN sub_sections ON section_id = sId
GROUP BY t1

输出为

t1              t2
Section One     SubOne,SubTwo
Section Three   SubThree
Section Two     (null)

但是使用新查询:

SELECT sorder, section_titel as t1, GROUP_CONCAT(sub_section_titel ORDER BY iorder) as t2
FROM sections LEFT JOIN sub_sections ON section_id = sId
GROUP BY t1
ORDER BY sorder

输出为:

sorder  t1              t2
1       Section Three   SubThree
2       Section One     SubTwo,SubOne
3       Section Two     (null)

根据sorder和iorder的值，注意Section Three和Section One以及SubTwo和SubOne的重新排序.

Note reordering of Section Three and Section One and also SubTwo and SubOne based on the values of sorder and iorder.

这篇关于PHP SQL LEFT JOIN语句丢失了一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！