mysql - 在多个连接表上使用 MySQL GROUP_CONCAT 或 PIVOT

Question

问题

我正在为一所学校开发一个内容管理系统，我希望能够创建一个包含多个连接表的 View 。除了连接表之外，我还需要执行 PIVOT 或 GROUP_CONCAT(或其他一些函数)以便从每个表中获取多行并将它们用作 View 中的列。为了帮助形象化，这里简化了我正在使用的内容:

设置

结构:

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','');

一些失败的尝试

通过执行以下操作，我能够很好地将表格连接在一起:

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 的原因，尽管我也没有取得太大成功:

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;

我能够用表中的相应值填充 View 中的每一列，但日期和 bigText 列正在根据它们连接的原始行数(7 次)进行复制。

(此外，作为另一个增加的复杂程度......我希望拥有它，以便不同的教师也能找到进入自己专栏的方式:Teacher_01，Teacher_02，.. Teacher_05，我会我猜这可能涉及对我现有内容的重大重组。)

谢谢!

...更新

因此，我能够生成一个查询，它输出的内容更接近我正在寻找的内容，但我不能在 View 中使用它，因为 FROM 中有一个子查询> 子句:

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 列外，这基本上就是我正在寻找的内容(但以某种方式生成，我可以在 View 中使用查询)。

此外，正如我上面提到的，我希望能够将同一 inquiry_name 的多个值提取到单独的列中。所以最终期望的输出看起来像:

期望的结果

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

谢谢!

Answer 1

这是一个模拟 PIVOT 应用 rownum 给教师:

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 Fiddle with demo

编辑 #1，根据您提供的附加字段 col_order，您可以使用它来确定教师，而无需使用 rownum 变量:

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

参见SQL Fiddle with Demo