基于if-else语句的postgres内部联接

Question

我遇到的情况是，如果一个标志为true，则内部联接一个表，否则不需要联接。

SELECT     ti.upc, 
ti.quantity, 
ti.return_type, 
ti.amount, 
ti.transaction_date, 
ti.transaction_time, 
ti.location_id
FROM       transaction_item ti 
IF _user = true THEN
INNER JOIN user_location ul 
ON         ul.location_id=ti.location_id 
AND      ul.user_id=_user_id 
AND        ul.location_id=_location_id
END IF; 
WHERE        ti.transaction_date BETWEEN _start_date::date AND  _end_date::date
AND        ti.upc IS NOT NULL as ti

Answer 1

您可以将inner join更改为left join，并将对变量的检查添加到on条件中。然后，where子句可以筛选出应该进行联接但没有成功的行：

SELECT     
ti.upc, 
ti.quantity, 
ti.return_type, 
ti.amount, 
ti.transaction_date, 
ti.transaction_time, 
ti.location_id
FROM transaction_item ti 
LEFT JOIN user_location ul 
ON  _user = true
AND ul.location_id=ti.location_id 
AND ul.user_id=_user_id 
AND ul.location_id=_location_id
WHERE    
ti.transaction_date BETWEEN _start_date::date AND  _end_date::date
AND ti.upc IS NOT NULL
AND NOT (_user = true AND ul.location_id IS NULL)

Answer 2

您没有使用来自user_location的任何数据，因此我建议：

SELECT ti.upc, ti.quantity, ti.return_type,  ti.amount, 
ti.transaction_date, ti.transaction_time, ti.location_id
FROM ransaction_item ti 
WHERE (_user <> true OR
EXISTS (SELECT 1
FROM user_location ul 
WHERE ul.location_id = ti.location_id AND
ul.user_id = _user_id AND
ul.location_id = _location_id
)
) AND
ti.transaction_date BETWEEN _start_date::date AND _end_date::date AND
ti.upc IS NOT NULL;

对我来说，"有条件加入"似乎很难遵循。但是，WHERE子句中的条件过滤器非常容易遵循。