mysql - MySQL:多个表的总和

我有下表：
表1：

CREATE TABLE table1
    (
     id int auto_increment primary key,
     person varchar(30),
     color_code varchar(30),
     item_id varchar(30),
     date_start DATE,
     date_complete DATE
    );

INSERT INTO table1
(person, color_code, item_id, date_start, date_complete)
VALUES
('Jackson', 'RED', '1', '2013-07-11 11:23:39', '2013-08-1 11:23:39'),
('Danny', 'BLUE', '2', '2013-07-20 11:23:39', '2013-08-1 11:23:39'),
('Jimmy', 'GREEN', '5', '2013-05-15 11:23:39', '2013-08-1 11:23:39'),
('Jackson', 'RED', '3', '2013-02-16 11:23:39', '2013-08-1 11:23:39'),
('Jimmy', 'BLUE', '4', '2013-03-13 11:23:39', '2013-08-1 11:23:39'),
('William', 'RED', '5', '2013-04-29 11:23:39', '2013-08-1 11:23:39'),
('William', 'BLUE', '1', '2013-05-9 11:23:39', '2013-08-1 11:23:39'),
('Danny', 'GREEN', '5', '2013-01-6 11:23:39', '2013-08-1 11:23:39'),
('Jackson', 'RED', '4', '2013-07-11 11:23:39', '2013-08-1 11:23:39'),
('Jackson', 'RED', '5', '2013-08-7 11:23:39', '2013-09-1 11:23:39');

表2：

CREATE TABLE table2
    (
     id int auto_increment primary key,
     item_code varchar(30),
     item_name varchar(30)
    );

INSERT INTO table2
(item_code, item_name)
VALUES
('1', 'APPLE'),
('2', 'BANANA'),
('3', 'PINEAPPLE'),
('4', 'WATERMELON'),
('5', 'GUAVA');

我的sql查询如下所示：

SELECT

    person AS 'Name',
    COUNT(*) AS 'Total Item Purchased',
    SUM(CASE WHEN (color_code='RED') THEN 1 ELSE 0 END) AS 'Total Red Color',
    SUM(CASE WHEN (color_code='BLUE') THEN 1 ELSE 0 END) AS 'Total Blue Color',
    SUM(CASE WHEN (color_code='GREEN') THEN 1 ELSE 0 END) AS 'Total Green Color',
    SUM(CASE WHEN DATEDIFF(date_complete, date_start) BETWEEN 1 AND 30 THEN 1 ELSE 0 END) AS 'Bought Between 1-30 Days'

FROM table1

    WHERE
    person LIKE '%Jackson%' OR
    person LIKE '%Danny%' OR
    person LIKE '%Jimmy%' OR
    person LIKE '%William%'
    GROUP BY person;

问题：如何将表2中的项目名显示到select查询中，并按人员显示所有行组？我在考虑再选择，在'购买1-30天'之后，有可能吗？
SQLFiddle:http://sqlfiddle.com/#!2/d59e0/4

最佳答案

您可以加入表格并使用GROUP_CONCAT()：

SELECT

    person AS 'Name',
    COUNT(*) AS 'Total Item Purchased',
    SUM(CASE WHEN (color_code='RED') THEN 1 ELSE 0 END) AS 'Total Red Color',
    SUM(CASE WHEN (color_code='BLUE') THEN 1 ELSE 0 END) AS 'Total Blue Color',
    SUM(CASE WHEN (color_code='GREEN') THEN 1 ELSE 0 END) AS 'Total Green Color',
    SUM(CASE WHEN DATEDIFF(date_complete, date_start) BETWEEN 1 AND 30 THEN 1 ELSE 0 END) AS 'Bought Between 1-30 Days',
    GROUP_CONCAT(item_name)

FROM table1 JOIN table2 ON table2.item_code = table1.item_id

    WHERE
    person LIKE '%Jackson%' OR
    person LIKE '%Danny%' OR
    person LIKE '%Jimmy%' OR
    person LIKE '%William%'
    GROUP BY person;

在sqlfiddle上查看。
请注意，有时最好只是连接表并按person对结果集排序，在应用程序的表示层中执行这样的聚合。

关于mysql - MySQL:多个表的总和，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/19630109/