SELECT * FROM company_disposition WHERE company_id = $1 AND campaign_uuid = 'null' OR campaign_uuid != $2
这是我的帖子请求..我不想which company_id = $1 AND campaign_uuid = 'null' OR campaign_uuid != $2
得到所有值..怎么做?您看到的请求不起作用//
{
"company_id": "be1b5ee1-51e7-422b-b469-592dbd62bec9",
"disposition": "string",
"disposition_uuid": "8f66c72a-9a68-440a-bd7f-3a0f49ef4973",
"disposition_order": 2323,
"campaign_uuid": "fd80a1fa-947d-4b09-a755-cee5951b6c85"
},
{
"company_id": "be1b5ee1-51e7-422b-b469-592dbd62bec9",
"disposition": "strinsdsdg",
"disposition_uuid": "4c8e9601-1894-4e69-b22c-f2b83d9d80bb",
"disposition_order": 2323,
"campaign_uuid": null
}
我想得到这个:
{
"company_id": "be1b5ee1-51e7-422b-b469-592dbd62bec9",
"disposition": "strinsdsdg",
"disposition_uuid": "4c8e9601-1894-4e69-b22c-f2b83d9d80bb",
"disposition_order": 2323,
"campaign_uuid": null
}
"campaign_uuid"
- 可以是值或空
您的查询有两个问题(如果我没看错的话):
-
您可以使用
=
运算符与 NULL 进行比较。SQL 对比较运算符使用三值逻辑,因此将 NULL(= 未知)与任何内容进行比较既不是
TRUE
也不是FALSE
,而是 NULL。这意味着条件
something = NULL
永远不会TRUE
(它始终为 NULL)。您必须使用
IS NULL
运算符来测试值是否为 NULL。 -
你忘记了
AND
的优先级高于OR
,所以你必须使用括号。
我想你的意思如下:
SELECT * FROM company_disposition
WHERE company_id = $1
AND (campaign_uuid IS NULL OR campaign_uuid <> $2);
如果您从不提供 $2 的 NULL 值,更简单的方法是
SELECT * FROM company_disposition
WHERE company_id = $1
AND campaign_uuid IS DISTINCT FROM $2;
SELECT * FROM company_disposition WHERE company_id = $1 AND campaign_uuid is null OR campaign_uuid <> $2 AND company_id = $1
尝试:
SELECT * FROM company_disposition WHERE company_id = $1 AND (campaign_uuid is null OR campaign_uuid <> $2)
此查询可以返回几行,所有 company_id = $ 1 且 campaign_uuid != $ 2 的行,(条件 campaign_uuid != $2 将包括 campaign_uuid = null 的行),如果只想为每个company_id获取一行,则需要使用"DISTINCT"
例:
SELECT DISTINCT ON (company_id)
company_id,
disposition,
disposition_uuid,
disposition_order,
campaign_uuid
FROM
company_disposition
WHERE
company_id = $1 AND
(campaign_uuid is null OR campaign_uuid <> $2)
ORDER BY company_id