mysql - 根据其他行值从 SQL 表查询数据

Question

标题听起来有点复杂，但如果没有例子很难解释我的意思。

所以我有一个带有树表的 SQL 数据库:

照片:

+------------+------------------+------+-----+-------------------+-----------------------------+
| Field      | Type             | Null | Key | Default           | Extra                       |
+------------+------------------+------+-----+-------------------+-----------------------------+
| id         | int(10) unsigned | NO   | PRI | NULL              | auto_increment              |
| hash       | varchar(256)     | NO   | MUL | NULL              |                             |
| path       | varchar(1024)    | NO   | MUL | NULL              |                             |
| store_date | timestamp        | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+------------+------------------+------+-----+-------------------+-----------------------------+

标签名称:

+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(64)      | NO   | UNI | NULL    |                |
| type  | int(11)          | NO   |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+

标签:

+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id       | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| value    | varchar(2048)    | YES  |     | NULL    |                |
| name_id  | int(10) unsigned | NO   | MUL | NULL    |                |
| photo_id | int(10) unsigned | NO   | MUL | NULL    |                |
+----------+------------------+------+-----+---------+----------------+

Photos 是一个包含照片数据的表(ID、照片路径和 SHA 哈希值)。

Tag_names 收集有关可能标签的信息(ID、标签名称及其类型，我在程序中将其映射到正确的类型，如字符串、数字、日期等)

Tags 包含有关哪张照片包含哪些带有标签值的标签的信息:name_id 是对 tag_names.id 的引用，photo_id 是对 photos.id 的引用，value 是标签的值。

我遇到的问题是我想以树形形式可视化数据库中的数据。一个示例:根节点将具有名称为日期的子节点。 tag_names 表中将有一个“Date”标签，并且标签表中将有引用该名称(通过tags.name_id)和不同值的条目。

可视化:

select * from tag_names;
+----+------+------+
| id | name | type |
+----+------+------+
|  1 | Date |    1 |
+----+------+------+

select * from tags;
+----+------------+---------+----------+
| id | value      | name_id | photo_id |
+----+------------+---------+----------+
|  1 | 2011.05.19 |       1 |        1 |
|  3 | 2011.05.20 |       1 |        2 |
+----+------------+---------+----------+

会是这样一棵树:

root
|
+--- 2011.05.19
     |
     +---- photo 1
     |
     2011.05.20
     |
     +---- photo 2

只需对标签进行适当的查询，我就可以非常轻松地实现它。不过，我希望能够在树中创建两个(或更多)级别。

假设我想在树中显示日期和时间。我仍然可以从第一级收集数据(查找名称为“日期”的标签的所有可能值)，但随后我想查找“时间”标签的所有可能值，但仅限于具有特定“日期”标签的照片。

因此，我需要在 tags 上进行 select，其中标签名称等于“Time”，并且具有相关 photo_id 的照片也包含标签“日期”，具有一个特定值。

我的表格布局可以吗？我总是可以通过缓存一些数据来做到这一点，这是我最后的手段，但是我很好奇是否可以直接使用 SQL 来解决这样的问题。

编辑:

下面是照片查询的示例(在评论中询问):

'1', 'cda6bfe0fd7a588704e1ebc81f8ef4b3c884895afadf0d0c97892db8f6d9cc91', '/DSCF9529.JPG', '2014-03-23 16:19:26'
'2', 'cda6bfe0fd7a588704e1ebc81f8ef4b3c884895afadf0d0c97892db8f6d9cc91', '/DSCF9529.JPG', '2014-03-23 16:19:26'
'3', 'e213df3f22276173c2e07a8c4ec9e83aee73605196d4e2aa529fbb34ceb6f86d', '/DSCF9532.JPG', '2014-03-23 16:19:26'
'4', '681a7c723ce16908c0fc73ed819de9a1af7c19cbd6fbcb7bf1c238a9d0378c2f', '/DSCF9531.JPG', '2014-03-23 16:19:26'
'5', 'd586eaae7d0fd625ec6282a51d12625db341c72d9395efd9e142850e457272ca', '/DSCF9537.JPG', '2014-03-23 16:19:26'
'6', 'dc1c40ce690c42f9fa9edc0f5020e01ca8e0c59694108d49f942b79b0167ef10', '/DSCF9222.JPG', '2014-03-23 16:19:26'
'7', '518884037e9d67ccbee98f6805cf7bb5ccf4c6f2e7aa35efe3c834e7c7ad3c32', '/DSCF9534.JPG', '2014-03-23 16:19:26'
'8', '21d33a2c5a25515689d68885d7d485aa89b96b7e5929a86ef658e53b61c7266b', '/DSCF9221.JPG', '2014-03-23 16:19:26'
'9', 'ff7ea451ce772b9d18c706dc9b989a1a318491e5d0f095575bf5dd6cc6448ab0', '/DSCF9530.JPG', '2014-03-23 16:19:26'

Answer 1

此查询选择所有带有标签名称“DATE”的照片，然后选择具有相同 ID 且标签名称为“TIME”的照片，这是您想要的吗？请参阅FIDDLE对于工作示例

SELECT 
    * 
FROM
(
    SELECT p.id AS p_id FROM photo p
    JOIN tag t ON t.photo_id = p.id
    JOIN tag_name tn ON tn.id = t.name_id
    WHERE tn.name = 'Date'
) AS ti
JOIN tag t1 ON t1.photo_id = p_id
JOIN tag_name tn1 ON tn1.id = t1.name_id
WHERE tn1.name = 'Time'