这个查询是我尝试过的最复杂的查询。
我的表:(我删除了不相关的数据)
mysql> describe Posts;
+-----------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| Time | int(11) | YES | MUL | NULL | |
+-----------+---------+------+-----+---------+----------------+
mysql> describe Containers;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| pid | int(11) | YES | MUL | NULL | |
| iid | int(11) | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+
mysql> describe Images;
+--------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| LocalPath | varchar(100) | YES | | NULL | |
| Category | varchar(20) | YES | | NULL | |
+--------------+--------------+------+-----+---------+----------------+
关系:
Posts.id>容器.pid
Containers.iid>图像.id
我想:
获取对应图像所在的所有文章。类别是value1、value2或value3之一。按职位排序。时间。
伪代码:从Images.Category IN('value1','value2,'value3')按Posts.Time DESC.顺序获取所有数据。
尝试:
SELECT * FROM Images I
INNER JOIN Containers C ON C.iid = I.id
INNER JOIN Posts P ON C.pid = P.id
WHERE I.Category IN('value1','value2','value3');
结果:可以,但不能按Posts.Time排序。
SELECT * FROM Posts P
INNER JOIN Containers C ON P.id = C.pid
RIGHT OUTER JOIN Images I ON C.iid = I.id
WHERE I.Category IN('value1','value2','value3')
ORDER BY P.Time DESC;
结果:为不包含Images.Category的帖子获取行。
我希望我的问题格式正确,谢谢你抽出时间。
最佳答案
试试这个:
SELECT * FROM Images I
INNER JOIN Containers C ON C.iid = I.id
INNER JOIN Posts P ON C.pid = P.id
WHERE I.Category IN('value1','value2','value3')
ORDER BY P.Time DESC;