如何根据另一列在单行中显示 3 个不同的表数据

Question

我正在使用谷歌图表可视化 API 开发一个页面来显示最近 10 次测试用例通过和失败状态。我必须从 3 个不同的表中获取并显示数据。这是 mysql 创建和插入查询。我也附上了输出。

CREATE TABLE `test`.`team` (
  `teamId` INT NOT NULL,
  `teamName` VARCHAR(45) NULL, PRIMARY KEY (`TeamId`));

INSERT INTO test.team values (1,'T1');
INSERT INTO test.team values (2,'T2');
INSERT INTO test.team values (3,'T3');
INSERT INTO test.team values (4,'T4');
CREATE TABLE `test`.`regression` (
  `teamId` INT NOT NULL,
  `rundate` VARCHAR(45) NULL, `runid` VARCHAR(45) NULL,PRIMARY KEY (`runid`));

INSERT INTO test.regression values (1,'2016-06-07','2016-06-07_13:24:07');
INSERT INTO test.regression values (1,'2016-06-07','2016-06-07_18:07:53');
INSERT INTO test.regression values (1,'2016-06-07','2016-06-07_18:16:19');
INSERT INTO test.regression values (1,'2016-06-07','2016-06-07_13:50:59');
INSERT INTO test.regression values (1,'2016-06-07','2016-06-07_14:16:44');
INSERT INTO test.regression values (1,'2016-06-07','2016-06-07_14:38:13');
INSERT INTO test.regression values (1,'2016-06-07','2016-06-07_14:56:46');
INSERT INTO test.regression values (1,'2016-06-07','2016-06-06_13:24:07');
INSERT INTO test.regression values (1,'2016-06-07','2016-06-06_18:07:53');
INSERT INTO test.regression values (1,'2016-06-07','2016-06-06_18:16:19');
INSERT INTO test.regression values (1,'2016-06-07','2016-06-06_13:50:59');
INSERT INTO test.regression values (1,'2016-06-07','2016-06-06_14:16:44');
INSERT INTO test.regression values (1,'2016-06-07','2016-06-06_14:38:13');
INSERT INTO test.regression values (1,'2016-06-07','2016-06-06_14:56:46');
CREATE TABLE `test`.`runs` (
  `runid` VARCHAR(45) NOT NULL,
  `name` VARCHAR(45) NULL, `status` VARCHAR(45) NULL, `elapsed` VARCHAR(45) NULL, PRIMARY KEY (`runid`));

INSERT INTO test.runs values ('2016-06-07_13:24:07','TestCase1','Passed','20');
INSERT INTO test.runs values ('2016-06-07_18:07:53','TestCase1','Passed','20');
INSERT INTO test.runs values ('2016-06-07_18:16:19','TestCase1','Failed','20');
INSERT INTO test.runs values ('2016-06-07_13:50:59','TestCase1','Passed','20');
INSERT INTO test.runs values ('2016-06-07_14:38:13','TestCase1','Failed','20');
INSERT INTO test.runs values ('2016-06-07_14:56:46','TestCase1','Passed','20');
INSERT INTO test.runs values ('2016-06-07_14:16:44','TestCase1','Failed','20');
INSERT INTO test.runs values ('2016-06-06_13:24:07','TestCase2','Failed','20');
INSERT INTO test.runs values ('2016-06-06_18:07:53','TestCase2','Passed','20');
INSERT INTO test.runs values ('2016-06-06_18:16:19','TestCase2','Failed','20');
INSERT INTO test.runs values ('2016-06-06_13:50:59','TestCase2','Passed','20');
INSERT INTO test.runs values ('2016-06-06_14:38:13','TestCase2','Failed','20');
INSERT INTO test.runs values ('2016-06-06_14:56:46','TestCase2','Passed','20');
INSERT INTO test.runs values ('2016-06-06_14:16:44','TestCase2','Passed','20');

输出

T1 TestCase1 2016-06-07 Passed Passed,Failed,Passed,Failed,Passed,Failed
T1 TestCase2 2016-06-07 Failed Passed,Failed,Passed,Failed,Passed,Passed

Answer 1

在 SELECT 查询中使用 LEFT JOIN。我无法粘贴任何链接，但您可以在互联网上搜索有关左加入的信息

Answer 2

从他的链接引用的解决方案将多个子行合并为一行 MYSQL

SELECT 
    team.application_name AS 'Project',
    testruns.testcase_name AS 'TestCaseName',
    GROUP_CONCAT(regression.execution_date,
        '-',
        testruns.testcase_status) AS 'Result'
FROM
    testruns,
    regression,
    team
WHERE
    testruns.testrun_id = regression.testrun_information_id
        AND regression.regression_application_id = team.application_id
        AND testruns.testcase_name != ''
GROUP BY testruns.testcase_name