postgresql:额外的WHERE子句隐藏行
我的SQL请求加入2个表,结果是支付金额,按付款方式分组。付款方式存在于payment_history
表中:BlueMedia,Transferuj,BankPayment和ChangeSum。另外payment_history.payment_method
列可以包含空字符串或NULL
- 在这种情况下,我想将其作为“其他”值进行检索。postgresql:额外的WHERE子句隐藏行
SQL查询是:
SELECT
CASE
WHEN COALESCE(payment_history.payment_method, '') = '' THEN 'other'
ELSE payment_history.payment_method
END as payment_method,
CASE
WHEN SUM(paid_balance) IS NULL THEN 0.00
ELSE SUM(paid_balance)
END as paid_balance,
CASE
WHEN SUM(sum_without_commission) IS NULL THEN 0.00
ELSE SUM(sum_without_commission)
END as sum_without_commission
FROM
payment_history
LEFT JOIN
participants_list ON payment_history.registration_id = participants_list.registration_id
WHERE
payment_history.client_id = 258 AND
participants_list.deleted = 0 AND
participants_list.is_reserved = 0
GROUP BY payment_history.payment_method
结果(以付款方式Transferuj没有记录,这是好的,因为它是缺席了当前客户端id
= 258):
other
表示该记录存在于payment_history
中,其中payment_method
是NULL
或空字符串。
我不想检索记录,其中付款方式为ChangeSum
。在查询所以,我已经添加了相应的条件:
...
WHERE payment_history.client_id = 258
AND payment_history.payment_method != 'ChangeSum'
...
但在结果集也other
是空的:
为什么会出现这种问题,我能做些什么来获得所需要的数据方法(包括other
,当payment_method
列包含NULL
||空字符串;但没有ChangeStatus
)?
除了什么jarlh已经expalined约left join
和where
条款,你可以用coalesce()
简化您的查询,您可以使用coalesce()
围绕一个空值比较,所以他们不会返回null
。
select
coalesce(payment_history.payment_method, 'other') as payment_method
, coalesce(sum(paid_balance),0.00) as paid_balance
, coalesce(sum(sum_without_commission),0.00) as sum_without_commission
from payment_history
left join participants_list
on payment_history.registration_id = participants_list.registration_id
and participants_list.deleted = 0
and participants_list.is_reserved = 0
where payment_history.client_id = 258
and coalesce(payment_history.payment_method,'')!='ChangeSum'
group by payment_history.payment_method
当比较payment_method
为null,null != 'ChangeSum'
将返回null
,事实并非如此。 Comparison Operators
扩大对null
比较问题:
Null
被视为unknown
,所以比较null
总是unknown
,所以如果你在null != 'ChangeSum'
筛选结果不是true
(这是null
),和您的where
只包括您的条件为true
的结果。https://www.postgresql.org/docs/9.1/static/functions-comparison.html
使用coalesce(payment_history.payment_method,'')!='ChangeSum'
返回true
当payment_method
是null
,因为3210与''
用于比较和'' != 'ChangeSum'
取代null
值是true
。
当LEFT JOIN
,在ON
条款中放置右侧表条件。 (当WHERE
,你会得到定期INNER JOIN
结果。)
SELECT
CASE
WHEN COALESCE(payment_history.payment_method, '') = '' THEN 'other'
ELSE payment_history.payment_method
END as payment_method,
CASE
WHEN SUM(paid_balance) IS NULL THEN 0.00
ELSE SUM(paid_balance)
END as paid_balance,
CASE
WHEN SUM(sum_without_commission) IS NULL THEN 0.00
ELSE SUM(sum_without_commission)
END as sum_without_commission
FROM payment_history
LEFT JOIN participants_list
ON payment_history.registration_id = participants_list.registration_id
AND participants_list.deleted = 0
AND participants_list.is_reserved = 0
WHERE payment_history.client_id = 258
GROUP BY payment_history.payment_method
感谢的答案,但它不能对我有所帮助:** 1)**你是对的,我需要的正是'INNER JOIN'结果,这样我就可以”把右边的表格条件放在'ON'子句中(但是我会写'INNER'而不是'LEFT'); ** 2)**不回答我的问题:如何让'other'行,当我添加条件'AND payment_history.payment_method =“ChangeSum''。 –
感谢@SqlZim和@jarlh我已经改变了我的查询。下面有有效的变体:
SELECT
COALESCE(NULLIF(payment_history.payment_method, ''), 'other') AS payment_method,
COALESCE(SUM(paid_balance), 0.00) AS paid_balance,
COALESCE(SUM(sum_without_commission), 0.00) AS sum_without_commission
FROM payment_history
INNER JOIN participants_list
ON payment_history.registration_id = participants_list.registration_id
WHERE payment_history.client_id = 258
AND participants_list.deleted = 0
AND participants_list.is_reserved = 0
AND COALESCE(payment_history.payment_method, '') != 'ChangeSum'
GROUP BY payment_history.payment_method
也有可能写,而不是
AND payment_history.payment_method IS DISTINCT FROM 'ChangeSum'
AND COALESCE(payment_history.payment_method, '') != 'ChangeSum'
,作为@Clodoaldo内托提议。
and payment_history.payment_method is distinct from 'ChangeSum'
谢谢,它的工作原理呢! –
谢谢你,它的工作原理!我会后我的答案(我需要使用'NULLIF'太多,因为它可以为空字符串也,不仅'NULL'),但究竟你的回答将被接受。 –
@Boolean_Type你说得对,我忽略了'nullif()'的需要。虽然乐意帮忙! – SqlZim
不幸的是,我不是SQL中的profi。可以ü,请解释一下,为什么'COALESCE(payment_history.payment_method, '')!='ChangeSum''让我看到'other'?我明白,这个问题是万一'payment_method'包含'NULL',但不明白,它是如何阻止检索行'other' ...也就是说,当payment_method包含NULL时结果如何呢?和'空!=“ChangeSum''返回'null'?.. –