gpt4 book ai didi

sqlite - 无法在 sqlite 中连接两个表

转载 作者:行者123 更新时间:2023-12-01 16:23:48 25 4
gpt4 key购买 nike

我有两个表:

CREATE TABLE "object_comment" 
("object_id" INTEGER PRIMARY KEY NOT NULL,
"object_comment_text" TEXT,
"object_comment_title" TEXT,
"object_comment_date" DATETIME)

CREATE TABLE "object_comment_mark" 
("object_id" INTEGER PRIMARY KEY DEFAULT null,
"object_comment_mark_value" FLOAT DEFAULT null,
"object_comment_mark_date" DATETIME DEFAULT null)

我需要将它们与 object_id 字段连接起来,但唯一的行也应该出现在结果中。 (有一些相同的 object_id 值,我需要将其加入一行,并且一些 object_id 值不同,但它们应该在结果表中)

现在我有这个选择查询:

SELECT *
FROM object_comment
LEFT OUTER JOIN object_comment_mark ON object_comment.object_id = object_comment_mark.object_id

但在这种情况下,我没有第二个表中 object_id 具有唯一值的行。有什么帮助吗?

编辑:我需要什么

对象评论

1 | bla-bla | first | 2013
2 | be-be | sec | 2014

对象注释标记

1 | 5 | 2013
4 | 3 | 2013

结果

1 |bla-bla | first| 2013 | 5 | 2013
2 | be-be | sec | 2014 | |
4 | | | | 3 | 2013

最佳答案

你想要的是完全外连接,SQLite不支持它。相反,您可以组合左连接和右连接的不匹配(NULL)记录。也不支持右联接,因此请使用左联接并交换两个表:

SELECT oc.*, ocm.*
FROM object_comment AS oc
LEFT JOIN object_comment_mark AS ocm ON oc.object_id = ocm.object_id
UNION ALL
SELECT oc.*, ocm.*
FROM object_comment_mark AS ocm
LEFT JOIN object_comment AS oc ON oc.object_id = ocm.object_id
WHERE oc.object_id IS NULL

或者,手动搜索不匹配的记录:

SELECT oc.*, ocm.*
FROM object_comment AS oc
LEFT JOIN object_comment_mark AS ocm ON oc.object_id = ocm.object_id
UNION ALL
SELECT NULL, NULL, NULL, NULL, *
FROM object_comment_mark
WHERE object_id NOT IN (SELECT object_id
FROM object_comment)

关于sqlite - 无法在 sqlite 中连接两个表,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/19563533/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com