在多个联接表上使用MySQL GROUP_CONCAT或PIVOT

So, I was able to produce a query which outputs something a bit closer to what I'm looking for, but I can't use it in a view because there is a subquery in the FROM clause:SELECTst.col_entry,Title,Course,Description,Semester,Location,TeachersFROM smallText stJOIN (SELECT smallText.col_entry, GROUP_CONCAT(if(smallText.col_inquiry_name = 'Title', smallText.col_value, NULL)) AS 'Title' FROM smallText GROUP BY smallText.col_entry) aON a.col_entry = st.col_entryJOIN (SELECT smallText.col_entry, GROUP_CONCAT(if(smallText.col_inquiry_name = 'Course', smallText.col_value, NULL)) AS 'Course' FROM smallText GROUP BY smallText.col_entry) bON b.col_entry = st.col_entryJOIN (SELECT bigText.col_entry, GROUP_CONCAT(if(bigText.col_inquiry_name = 'Description', bigText.col_value, NULL)) AS 'Description' FROM bigText GROUP BY bigText.col_entry) cON c.col_entry = st.col_entryJOIN (SELECT date.col_entry, GROUP_CONCAT(if(date.col_inquiry_name = 'Semester', date.col_value, NULL)) AS 'Semester' FROM date GROUP BY date.col_entry) dON d.col_entry = st.col_entryJOIN (SELECT smallText.col_entry, GROUP_CONCAT(if(smallText.col_inquiry_name = 'Location', smallText.col_value, NULL)) AS 'Location' FROM smallText GROUP BY smallText.col_entry) eON e.col_entry = st.col_entryJOIN (SELECT smallText.col_entry, GROUP_CONCAT(if(smallText.col_inquiry_name = 'Teachers', smallText.col_value, NULL)) AS 'Teachers' FROM smallText GROUP BY smallText.col_entry) fON f.col_entry = st.col_entryGROUP BY st.col_entry 结果 此查询的输出如下:Entry Title Course Description Semester Location Teachers---------------------------------------------------------------------------------------------------------------------------------1 Addition and Subtraction Math Lorem ipsum dolor sit amet... 1294834220 Building 7 NULL2 Reading Mark Twain Literature Consectetur adipiscing elit... 1327077210 Building 4 NULL除了空的Teachers列外，基本上就是我要查找的内容(但以某种方式生成，可以在视图中使用查询).which, with the exception of the null Teachers column, is basically what I'm looking for (but generated in some way that I can use the query in a view).此外，如上所述，我希望能够将同一inquiry_name的多个值提取到各个列中.因此，最终所需的输出将如下所示:Additionally, as I mentioned above, I'd like to be able to pull multiple values for the same inquiry_name into individual columns. So the final desired output would look like: 所需结果 Entry Title Course Description Semester Location Teachers_01 Teachers_02 Teachers_03 Teachers_04 Teachers_05---------------------------------------------------------------------------------------------------------------------------------1 Addition and Subtraction Math Lorem ipsum dolor sit amet... 1294834220 Building 7 John Jane NULL NULL NULL2 Reading Mark Twain Literature Consectetur adipiscing elit... 1327077210 Building 4 Billy Bob NULL NULL NULL谢谢！推荐答案这是一个模拟PIVOT，将rownum应用于教师:Here is a mock-PIVOT applying a rownum to the teachers:select s.col_entry, max(case when s.col_inquiry_name = 'Title' then s.col_value end) AS 'Title', max(case when s.col_inquiry_name = 'Course' then s.col_value end) AS 'Course', max(case when b.col_inquiry_name = 'Description' then b.col_value end) AS 'Description', max(case when d.col_inquiry_name = 'Semester' then d.col_value end) AS 'Semester', max(case when s.col_inquiry_name = 'Location' then s.col_value end) AS 'Location', max(case when tch.grp = 'Teachers_01' then tch.col_value end) AS 'Teachers_01', max(case when tch.grp = 'Teachers_02' then tch.col_value end) AS 'Teachers_02', max(case when tch.grp = 'Teachers_03' then tch.col_value end) AS 'Teachers_03', max(case when tch.grp = 'Teachers_04' then tch.col_value end) AS 'Teachers_04', max(case when tch.grp = 'Teachers_05' then tch.col_value end) AS 'Teachers_05'from smallText sleft join bigText b on s.col_entry = b.col_entry left join date d on b.col_entry = d.col_entry left join( select col_entry, col_value, concat('Teachers_0', group_row_number) grp from ( select col_entry, col_value, @num := if(@col_entry = `col_entry`, @num + 1, 1) as group_row_number, @col_entry := `col_entry` as dummy from smallText , (SELECT @rn:=0) r where col_inquiry_name = 'Teachers' and col_value != '' ) x) tch on s.col_entry = tch.col_entrygroup by s.col_entry;请参见带有演示的SQL提琴 编辑#1，根据您提供的其他字段col_order，您可以使用它来确定教师，而无需使用rownum变量:see SQL Fiddle with demoEdit #1, based on the additional field you provided col_order you can use it to determine the teachers without using rownum variables:select s.col_entry, max(case when s.col_inquiry_name = 'Title' then s.col_value end) AS 'Title', max(case when s.col_inquiry_name = 'Course' then s.col_value end) AS 'Course', max(case when b.col_inquiry_name = 'Description' then b.col_value end) AS 'Description', max(case when d.col_inquiry_name = 'Semester' then d.col_value end) AS 'Semester', max(case when s.col_inquiry_name = 'Location' then s.col_value end) AS 'Location', max(case when s.col_inquiry_name = 'Teachers' and s.col_order = 1 then s.col_value end) AS 'Teachers_01', max(case when s.col_inquiry_name = 'Teachers' and s.col_order = 2 then s.col_value end) AS 'Teachers_02', max(case when s.col_inquiry_name = 'Teachers' and s.col_order = 3 then s.col_value end) AS 'Teachers_03', max(case when s.col_inquiry_name = 'Teachers' and s.col_order = 4 then s.col_value end) AS 'Teachers_04', max(case when s.col_inquiry_name = 'Teachers' and s.col_order = 5 then s.col_value end) AS 'Teachers_05'from smallText sleft join bigText b on s.col_entry = b.col_entry left join date d on b.col_entry = d.col_entry group by s.col_entry 这篇关于在多个联接表上使用MySQL GROUP_CONCAT或PIVOT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！