如果在PostgreSQL中没有行匹配，我如何选择null ?

Question

我有以下查询:

SELECT c.customer_id
FROM order o
JOIN customer c USING (customer_id)
WHERE c.time >= '2021-01-01 10:00:00' AND c.time < '2021-01-01 11:00:00'
AND (0 IN (22) OR o.product_id IN (22))
LIMIT 1

只要至少有一个匹配，将返回一行，但是如果没有匹配，则将不返回任何行。

是否有可能返回一行与空值，如果没有匹配?

Answer 1

一个解决方案是LEFT JOIN，但从客户表开始。然而，这有点复杂:

SELECT o.customer_id
FROM customer c LEFT JOIN
order o
ON c.customer_id = o.customer_id AND
o.time >= '2021-01-01 10:00:00' AND
o.time < '2021-01-01 11:00:00'
LIMIT 1;

注意，这是从orders表返回customer_id。如果没有匹配，这就是NULL。

上面的假设你至少有一个客户，这似乎是一个合理的假设。

另一种选择是UNION ALL:

WITH c as (
SELECT customer_id
FROM customer c JOIN
order o
USING (customer_id) 
WHERE o.time >= '2021-01-01 10:00:00' AND
o.time < '2021-01-01 11:00:00'
LIMIT 1
)
SELECT c.customer_id
FROM c
UNION ALL
SELECT NULL
WHERE NOT EXISTS (SELECT 1 FROM c);

第三种选择可能更简单，子查询:

select (select o.customer_id
from order o
where o.time >= '2021-01-01 10:00:00' AND
o.time < '2021-01-01 11:00:00'
limit 1
) as customer_id;

如果子查询没有返回任何行，则结果为NULL。

Answer 2

最简单的解决方案是勾选" left joined"表行

select 
case when c.customer_id is null then null else o.customer_id end 
from order as o 
left join customer_cte as c using (customer_id)

Answer 3

所以你有两个表Customers和Orders，并且客户和订单之间存在一对多的关系:每个客户有零个或多个订单，每个订单只属于一个客户，即外键CustomerId所指的客户。

每个客户都有一个属性Time。(您确定Time是Customer属性，而不是Order属性吗?)您有两个DateTime值:timeStart和timeEnd，并且对于每个在timeStart和timeEnd之间具有属性时间值的客户，您需要他的一个订单，无论哪个订单，或者如果该客户没有任何订单，则为空。

循序渐进:

DateTime timeStart = ...              // = '2021-01-01 10:00:00'
DateTime timeEnd = ...                // = '2021-01-01 11:00:00'
IQueryable<Order> allOrders = ...
IQueryable<Customer> allCUstomers = ...
IQueryable<Customer> customersWithinTimeRanges = allCustomers
.Where(customer => timeStart <= customer.Time and customer.Time < timeEnd);

换句话说:在所有客户中，只保留那些具有timeStart和timeEnd属性Time值的客户(使用property <= and <)

现在，从时间范围内的每个客户，您需要他的一个订单，如果客户没有任何订单，则为空。

当您有一对多关系时，例如拥有零个或多个学生的学校，拥有零个或多个居民的城市，或者在您的例子中拥有零个或多个订单的客户，以及来自每个"one"你想要一个或更多他的"许多"。项时，考虑使用Queryable重载之一。GroupJoin

我几乎总是需要带参数resultSelector的重载

var customersWithAnOrder = customersWithinTimeRanges.GroupJoin(
allOrders,
customer => customer.Id,        // from every Customer take the primary key
order => order.CustomerId,      // from every Order take the foreign key
// parameter resultSelector: from every Customer with his zero or more Orders
// make one new object:
(customer, ordersOfThisCustomer) => new
{
// select the Customer properties that you plan to use:
Id = customer.Id,
Name = customer.Name,
...
Order = ordersOfThisCustomer.Select(order => new
{
// select the Order properties that you plan to use
Id = order.Id,
Date = order.Date,
...
// not needed, you already got the value in property Id:
// CustomerId = order.CustomerId
})
// you don't need all Orders of this Customer, you only want one,
// don't care which one or null if this Customer doesn't have any Orders
.FirstOrDefault(),
});

有时数据库不允许你做FirstOrDefault如果你没有排序的序列。在这种情况下，在FirstOrDefault:

之前排序

Order = ordersOfOfThisCustomer.Select(order => new {...})
.OrderBy(order => order.Date) // or whatever you want to sort on
.FirstOrDefault(),