在多个联接表上使用MySQL GROUP_CONCAT或PIVOT [英] Using MySQL GROUP_CONCAT or PIVOT on multiple joined tables
问题描述
问题
我正在为学校开发内容管理系统，并且希望能够创建多个联接表的视图.除了加入表之外，我还需要执行PIVOT或GROUP_CONCAT(或其他功能)，以便从每个表中获取多行并将其用作视图中的列.为了帮助可视化，这是我正在使用的东西的简化:
I'm working on a content management system for a school, and I'd like to be able to create a view of multiple joined tables. In addition to joining the tables, I need to perform a PIVOT or GROUP_CONCAT (or some other function) in order to take multiple rows from each table and use them as columns in the view. To help visualize, here is a simplification of what I'm working with:
设置
结构:
DROP TABLE IF EXISTS `date`;
CREATE TABLE `date` (
`col_pkid` int(11) NOT NULL AUTO_INCREMENT,
`col_guid` varchar(100) NOT NULL DEFAULT '',
`col_entry` varchar(100) NOT NULL,
`col_inquiry_name` varchar(100) NOT NULL,
`col_value` int(11) DEFAULT NULL,
PRIMARY KEY (`col_pkid`)
);
DROP TABLE IF EXISTS `bigText`;
CREATE TABLE `bigText` (
`col_pkid` int(11) NOT NULL AUTO_INCREMENT,
`col_guid` varchar(100) NOT NULL DEFAULT '',
`col_entry` varchar(100) NOT NULL,
`col_inquiry_name` varchar(100) NOT NULL,
`col_value` text,
PRIMARY KEY (`col_pkid`)
);
DROP TABLE IF EXISTS `smallText`;
CREATE TABLE `smallText` (
`col_pkid` int(11) NOT NULL AUTO_INCREMENT,
`col_guid` varchar(100) NOT NULL,
`col_entry` varchar(100) NOT NULL,
`col_inquiry_name` varchar(100) NOT NULL,
`col_value` varchar(100) DEFAULT '',
PRIMARY KEY (`col_pkid`)
);
数据:
INSERT INTO `date` (`col_pkid`, `col_guid`, `col_entry`, `col_inquiry_name`, `col_value`)
VALUES
(1,'d2kih1ho5z','1','Semester',1294834220),
(2,'d2kih1j99y','2','Semester',1327077210);
INSERT INTO `bigText` (`col_pkid`, `col_guid`, `col_entry`, `col_inquiry_name`, `col_value`)
VALUES
(16,'d2kih1kxwh','1','Description','Lorem ipsum dolor sit amet...'),
(17,'d2kih1mhnn','2','Description','Consectetur adipiscing elit...');
INSERT INTO `smallText` (`col_pkid`, `col_guid`, `col_entry`, `col_inquiry_name`, `col_value`)
VALUES
(1,'d2kih1njuj','1','Title','Addition and Subtraction'),
(2,'d2kih1ot0x','1','Course','Math'),
(16,'d2kj5osy71','1','Location','Building 7'),
(17,'d2kj5p0z8f','1','Teachers','John'),
(18,'d2kj5p5efi','1','Teachers','Jane'),
(19,'d2kj5p98xv','1','Teachers',''),
(20,'d2kj5pasi9','1','Teachers',''),
(21,'d2kj5pbqbg','1','Teachers',''),
(22,'d2kj5pdxdb','2','Title','Reading Mark Twain'),
(23,'d2kih198do','2','Course','Literature'),
(24,'d2kj5opcgo','2','Location','Building 4'),
(25,'d2hd00n5eb','2','Teachers','Billy'),
(26,'d2hea3jo74','2','Teachers','Bob'),
(27,'d2hec78m5e','2','Teachers',''),
(28,'d2hec7bnar','2','Teachers',''),
(29,'d2hec7cbrq','2','Teachers','');
一些失败的尝试
通过执行以下操作，我可以很好地将表连接在一起:
I am able to join the tables together reasonably well by doing the following:
DROP VIEW IF EXISTS entries;
CREATE VIEW entries AS
SELECT
st.col_entry,
if(st.col_inquiry_name = 'Title', st.col_value, NULL) AS 'Title',
if(st.col_inquiry_name = 'Course', st.col_value, NULL) AS 'Course',
if(bt.col_inquiry_name = 'Description', bt.col_value, NULL) AS 'Description',
if(d.col_inquiry_name = 'Semester', d.col_value, NULL) AS 'Semester',
if(st.col_inquiry_name = 'Location', st.col_value, NULL) AS 'Location',
if(st.col_inquiry_name = 'Teachers', st.col_value, NULL) AS 'Teachers'
FROM smallText st
INNER JOIN bigText bt
ON st.col_entry = bt.col_entry
INNER JOIN date d
ON bt.col_entry = d.col_entry
GROUP BY col_entry;
但是在这里，我仅从每个表中获取第一行的值.这就是为什么我尝试引入GROUP_CONCAT的原因，尽管我也没有成功:
but here I'm only getting the value of the first row from each table. This is why I tried introducing GROUP_CONCAT although I haven't had much success with that either:
DROP VIEW IF EXISTS entries;
CREATE VIEW entries AS
SELECT
st.col_entry,
GROUP_CONCAT(if(st.col_inquiry_name = 'Title', st.col_value, NULL)) AS 'Title',
GROUP_CONCAT(if(st.col_inquiry_name = 'Course', st.col_value, NULL)) AS 'Course',
GROUP_CONCAT(if(bt.col_inquiry_name = 'Description', bt.col_value, NULL)) AS 'Description',
GROUP_CONCAT(if(d.col_inquiry_name = 'Semester', d.col_value, NULL)) AS 'Semester',
GROUP_CONCAT(if(st.col_inquiry_name = 'Location', st.col_value, NULL)) AS 'Location',
GROUP_CONCAT(if(st.col_inquiry_name = 'Teachers', st.col_value, NULL)) AS 'Teachers'
FROM smallText st
INNER JOIN bigText bt
ON st.col_entry = bt.col_entry
INNER JOIN date d
ON bt.col_entry = d.col_entry
GROUP BY col_entry;
我能够从表中获取视图中的每一列，并填充相应的值，但是date和bigText列是根据它们连接的原始行计数(7次)进行复制的.
I'm able to get each column in the view populated with the corresponding values from the tables, but the date and bigText columns are being duplicated according to the orignal row count that they are joined on (7 times).
(另外，作为另一种增加的复杂性...我想拥有它，以便不同的Teachers也能找到自己的方式进入他们自己的列中:Teacher_01，Teacher_02，.. Teacher_05，我想猜猜这可能涉及对我所拥有的东西的重大重组.)
(Also, as another added level of complexity... I'd like to have it so that the different Teachers would find their way into their own columns as well: Teacher_01, Teacher_02, .. Teacher_05, and I'd guess this probably involves a significant restructuring of what I've got.)
谢谢！
...更新
因此，我能够生成一个查询，该查询输出的内容与我要查找的内容更接近，但是由于FROM子句中有一个子查询，所以我无法在视图中使用它:
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:
SELECT
st.col_entry,
Title,
Course,
Description,
Semester,
Location,
Teachers
FROM smallText st
JOIN (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) a
ON a.col_entry = st.col_entry
JOIN (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) b
ON b.col_entry = st.col_entry
JOIN (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) c
ON c.col_entry = st.col_entry
JOIN (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) d
ON d.col_entry = st.col_entry
JOIN (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) e
ON e.col_entry = st.col_entry
JOIN (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) f
ON f.col_entry = st.col_entry
GROUP BY st.col_entry
结果
此查询的输出如下:
Entry Title Course Description Semester Location Teachers
---------------------------------------------------------------------------------------------------------------------------------
1 Addition and Subtraction Math Lorem ipsum dolor sit amet... 1294834220 Building 7 NULL
2 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 NULL
2 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 s
left 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_entry
group by s.col_entry;
请参见带有演示的SQL提琴
编辑#1，根据您提供的其他字段col_order，您可以使用它来确定教师，而无需使用rownum变量:
see SQL Fiddle with demo
Edit #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 s
left 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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
