mysql - 合并同一个表中的多行(一周中的几天)

Question

我有一个表，其中存储特定用户/过滤器/类型一天中每个小时的是/否值。每一天都是它自己的一行。因此，对于任何给定的用户/过滤器/类型组合，始终有 7 行。

我想要完成的是每个用户/过滤器/类型组合的一个结果，其中包含一周中每一天的所有时间。我认为我在这里需要的方法是使用别名和组进行自连接，但我尝试过的所有方法都失败了。我在 fiddle 设置了一个基本的 fiddle

如果有更简单和/或首选的方法在数据库端处理这个问题(我的直觉说有)，我也有能力为此更改数据库模式。也许是一个由 filter_id 链接的一周中每一天的表格？

INSERT INTO filters
 (`filter_id`, `user_id`, `filter`, `type`, `day`, `12a`, `1a`... and so on for each hour)
VALUES
(1, 1, 'filter1', 1, 1, 1, 1),
(2, 1, 'filter1', 1, 2, 1, 1),
(3, 1, 'filter1', 1, 3, 1, 1),
(4, 1, 'filter1', 1, 4, 1, 1),
(5, 1, 'filter1', 1, 5, 1, 1),
(6, 1, 'filter1', 1, 6, 1, 1),
(7, 1, 'filter1', 1, 7, 1, 1),
(8, 1, 'filter2', 1, 1, 0, 0),
(9, 1, 'filter2', 1, 2, 0, 0),
(10, 1, 'filter2', 1, 3, 0, 0),
(11, 1, 'filter2', 1, 4, 0, 0),
(12, 1, 'filter2', 1, 5, 0, 0),
(13, 1, 'filter2', 1, 6, 0, 0),
(14, 1, 'filter2', 1, 7, 0, 0),
(15, 1, 'filter3', 1, 1, 0, 0),
(16, 1, 'filter3', 1, 2, 0, 0),
(17, 1, 'filter3', 1, 3, 0, 0),
(18, 1, 'filter3', 1, 4, 0, 0),
(19, 1, 'filter3', 1, 5, 0, 0),
(20, 1, 'filter3', 1, 6, 0, 0),
(21, 1, 'filter3', 1, 7, 0, 0)
;

编辑:

我在这方面取得了一些进展，它在一个结果中显示了过滤器的全天时间...但是...当用户拥有多个过滤器时，它不起作用。我似乎无法正确分组和/或其他内容，因此结果仅显示唯一的用户/类型/过滤器组合...目前它只为每个用户显示一个过滤器结果。

这也只是加入星期一、星期二、星期三......必须有一个更简单的方法来做到这一点。就像我说的，我也完全愿意为此改变数据库的架构，但不确定除此之外最好的方法是什么。我当然无法在一张表中列出一整周的所有小时数(这将是 168 列小时数加上每行 172 列的额外小时数)。

$stmt = $db->prepare("
    SELECT users.user_id, users.username, c.computer_name, filters.user_id, filters.filter, filters.type,
    monday.12a as m12a,
    monday.1a as m1a,
    monday.2a as m2a,
    monday.3a as m3a,
    monday.4a as m4a,
    monday.5a as m5a,
    monday.6a as m6a,
    monday.7a as m7a,
    monday.8a as m8a,
    monday.9a as m9a,
    monday.10a as m10a,
    monday.11a as m11a,
    monday.12p as m12p,
    monday.1p as m1p,
    monday.2p as m2p,
    monday.3p as m3p,
    monday.4p as m4p,
    monday.5p as m5p,
    monday.6p as m6p,
    monday.7p as m8p,
    monday.9p as m9p,
    monday.10p as m10p,
    monday.11p as m11p,
    tuesday.12a as t12a,
    tuesday.1a as t1a,
    tuesday.2a as t2a,
    tuesday.3a as t3a,
    tuesday.4a as t4a,
    tuesday.5a as t5a,
    tuesday.6a as t6a,
    tuesday.7a as t7a,
    tuesday.8a as t8a,
    tuesday.9a as t9a,
    tuesday.10a as t10a,
    tuesday.11a as t11a,
    tuesday.12p as t12p,
    tuesday.1p as t1p,
    tuesday.2p as t2p,
    tuesday.3p as t3p,
    tuesday.4p as t4p,
    tuesday.5p as t5p,
    tuesday.6p as t6p,
    tuesday.7p as t8p,
    tuesday.9p as t9p,
    tuesday.10p as t10p,
    tuesday.11p as t11p,
    wednesday.12a as w12a,
    wednesday.1a as w1a,
    wednesday.2a as w2a,
    wednesday.3a as w3a,
    wednesday.4a as w4a,
    wednesday.5a as w5a,
    wednesday.6a as w6a,
    wednesday.7a as w7a,
    wednesday.8a as w8a,
    wednesday.9a as w9a,
    wednesday.10a as w10a,
    wednesday.11a as w11a,
    wednesday.12p as w12p,
    wednesday.1p as w1p,
    wednesday.2p as w2p,
    wednesday.3p as w3p,
    wednesday.4p as w4p,
    wednesday.5p as w5p,
    wednesday.6p as w6p,
    wednesday.7p as w8p,
    wednesday.9p as w9p,
    wednesday.10p as w10p,
    wednesday.11p as w11p   
    FROM
        ( SELECT account_id, computer_id, computer_name
            FROM computers
            WHERE account_id = 1
            ORDER BY computer_id ASC LIMIT 0, 5
        ) as c
        LEFT JOIN users
            on users.computer_id = c.computer_id

        LEFT JOIN filters
            on filters.user_id = users.user_id

        LEFT JOIN filters as monday
            on monday.user_id = filters.user_id and monday.filter = filters.filter and monday.day = 1

        LEFT JOIN filters as tuesday
            on tuesday.user_id = filters.user_id and tuesday.filter = filters.filter and tuesday.day = 2

        LEFT JOIN filters as wednesday
            on wednesday.user_id = filters.user_id and wednesday.filter = filters.filter and wednesday.day = 3


    WHERE filters.type = 1
    GROUP BY users.user_id
");

Answer 1

只要两个表中的列之间存在匹配，INNER JOIN 关键字就会选择两个表中的所有行。

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;

这里是 mysql 官方页面。 http://dev.mysql.com/doc/refman/5.0/en/join.html