作者热门文章
- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
我试图为一个查询连接多个表,但我从数据库中得到不一致的结果,我相信我的查询采用的是所有用户的笛卡尔积,而我只需要 DirectConversation 中的用户。
供引用的架构:
查询是(其中 $id 代表变量 User.id):
SELECT c.*, count(dm.id),
u1.first_name, u1.last_name, u1.company, u1.picture,
u2.first_name, u2.last_name, u2.company, u2.picture
FROM "DirectConversation" as c, "DirectMessage" as dm, "Profile" as u1, "Profile" as u2
WHERE u1."id_User" = c."id_User1"
AND u2."id_User" = c."id_User2"
AND c.id = dm."id_DirectConversation"
AND dm.viewed = 'f' AND dm.deleted = 'f'
AND c."id_User1" = $id OR c."id_User2" = $id
GROUP BY c.id, u1.id, u2.id;
预期结果(用户id=1时的结果):
id | id_User1 | id_User2 | count | first_name | last_name | company | picture | first_name | last_name | company | picture
----+----------+----------+-------+------------+-----------+--------------------+-----------------------------------------------------------------------------+------------+-----------+----------------+--------------------------------------------------------------------------
1 | 1 | 2 | 3 | Albert | Einstein | alberts inventions | http://upload.wikimedia.org/wikipedia/commons/d/d3/Albert_Einstein_Head.jpg | Nikola | Tesla | Teslas Widgets | http://upload.wikimedia.org/wikipedia/commons/7/79/Tesla_circa_1890.jpeg
(1 row)
(END)
错误结果(用户id=2时的结果):
id | id_User1 | id_User2 | count | first_name | last_name | company | picture | first_name | last_name | company | picture
----+----------+----------+-------+------------+-----------+--------------------+----------------------------------------------------------------------------------------------------------------+------------+-----------+--------------------+----------------------------------------------------------------------------------------------------------------
1 | 1 | 2 | 4 | Albert | Einstein | alberts inventions | http://upload.wikimedia.org/wikipedia/commons/d/d3/Albert_Einstein_Head.jpg | Albert | Einstein | alberts inventions | http://upload.wikimedia.org/wikipedia/commons/d/d3/Albert_Einstein_Head.jpg
1 | 1 | 2 | 4 | Albert | Einstein | alberts inventions | http://upload.wikimedia.org/wikipedia/commons/d/d3/Albert_Einstein_Head.jpg | Nikola | Tesla | Teslas Widgets | http://upload.wikimedia.org/wikipedia/commons/7/79/Tesla_circa_1890.jpeg
1 | 1 | 2 | 4 | Albert | Einstein | alberts inventions | http://upload.wikimedia.org/wikipedia/commons/d/d3/Albert_Einstein_Head.jpg | Rosalind | Franklin | DNA R US | http://upload.wikimedia.org/wikipedia/en/9/97/Rosalind_Franklin.jpg
1 | 1 | 2 | 4 | Albert | Einstein | alberts inventions | http://upload.wikimedia.org/wikipedia/commons/d/d3/Albert_Einstein_Head.jpg | Charles | Babbage | Babbages Cabbages | http://upload.wikimedia.org/wikipedia/commons/6/6b/Charles_Babbage_-_1860.jpg
...请注意,为简洁起见,这被截断了。我相信这是所有用户的笛卡尔积,但我不知道为什么
我使用的 postgres 版本:
-----------------------------------------------------------------------------------------------------
PostgreSQL 9.3.6 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit
最佳答案
我只是将你用于连接的 where 子句移动到 ON 语句并进行了正确的连接,如果这不起作用我将设置一个 sqlfiddle 并查看此 sql 的问题是什么
SELECT c.*, count(dm.id),
u1.first_name, u1.last_name, u1.company, u1.picture,
u2.first_name, u2.last_name, u2.company, u2.picture
FROM "DirectConversation" c
JOIN "DirectMessage" dm ON c.id = dm."id_DirectConversation"
JOIN "Profile" u1 ON u1."id_User" = c."id_User1"
JOIN "Profile" u2 ON u2."id_User" = c."id_User2"
WHERE
dm.viewed = 'f' AND dm.deleted = 'f'
AND (c."id_User1" = $id OR c."id_User2" = $id)
GROUP BY c.id, u1.id, u2.id;
编辑:为了安全起见将 OR 子句分组
关于sql - Postgres 连接查询有时采用笛卡尔积,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/30183741/
我是一名优秀的程序员,十分优秀!