MySQL 查询，带两个表，如果不存在，则从 NULL 筛选到 " " 并且如果联接表上的多个匹配项仅保留最新的

Question

所以我有一个名为"用户"；以及一个名为"；运输"；。我想创建一个查询；用户"；表信息和JOIN；运输"；将"；tracking_ number；以及"；tracking_ date"；仅列。我只想要最近的"；tracking_ date"；以按用户显示。

为了完成上述任务，我写了这篇文章(根据保罗的回答更新了Coalese，谢谢！(：

SELECT users.*
, shipping.tracking_number
, coalese(shipping.tracking_date, '') AS `tracking_date`
FROM users
LEFT 
JOIN shipping shipping
ON users.user_id =shipping.user_id

；运输"；表并不总是具有来自"；用户"；桌子没有匹配项的那些显示为NULL；tracking_ number；以及"；tracking_date">

1.我需要；tracking_ date"；列以显示"(空白(与NULL
2。如果一个"；user_id"；具有多个"；tracking_ numbers"&quot；tracking_ date"；与它们关联，那么我只需要显示最近的一行。

我试着使用Max((，但它只显示了Max"；tracking_ date"；来自所有记录(不是每个用户ID(

我需要在(PHP(之后对其进行JSON编码，以创建API来获取数据。

如有任何帮助，我们将不胜感激。

示例数据：表"；用户"；

user_id: 1 
name: john smith
address: 123 address road
user_id: 2
name: jane smith
address: 456 address road
user_id: 3
name: john doe
address 123 anon street
user_id: 4
name: jane doe
address: 456 anon street

表"；运输"；

user_id: 1
tracking_number: 1234567891
tracking_date: 2020-06-17 12:34:56
user_id: 2
tracking_number: 1234567892
tracking_date: 2020-07-10 12:34:56
user_id: 1
tracking_number: 1234567893
tracking_date: 2020-07-17 12:34:56
user_id: 4
tracking_number: 1234567894
tracking_date: 2020-08-11 12:34:56

当前结果：

user_id: 1 <-- user 1 shows twice
name: john smith
address: 123 address road
tracking_number: 1234567891
tracking_date: 2020-06-17 12:34:56
user_id: 1  <-- user 1 shows twice (only need most current tracking date)
name: john smith
address: 123 address road
tracking_number: 1234567893
tracking_date: 2020-07-17 12:34:56
user_id: 2
name: jane smith
address: 456 address road
tracking_number: 1234567892
tracking_date: 2020-07-10 12:34:56
user_id: 3
name: john doe
address 123 anon street
tracking_number: NULL
tracking_date: 
user_id: 4
name: jane doe
address: 456 anon street
tracking_number: 1234567894
tracking_date: 2020-08-11 12:34:56

想要的结果：

user_id: 1 
name: john smith
address: 123 address road
tracking_number: 1234567893
tracking_date: 2020-07-17 12:34:56
user_id: 2
name: jane smith
address: 456 address road
tracking_number: 1234567892
tracking_date: 2020-07-10 12:34:56
user_id: 3
name: john doe
address 123 anon street
tracking_number: NULL
tracking_date: 
user_id: 4
name: jane doe
address: 456 anon street
tracking_number: 1234567894
tracking_date: 2020-08-11 12:34:56

Answer 1

使用coalesce()，它通过从左到右的参数返回遇到的第一个非null值。即如果第一个参数为NULL，则返回第二个参数"在这种情况下。

SELECT users.*
, coalesce(shipping.tracking_number,'""') as tracking_number
, coalesce(shipping.tracking_date,'""') as tracking_date
FROM users
LEFT 
JOIN shipping shipping
ON users.user_id =shipping.user_id

有一个MySQL特定的函数ifnull()，但我认真建议您坚持使用符合ANSI标准的coalesce()。CCD_ 4被限制为2个参数CCD_ 5允许2个以上的参数。

https://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html#function_coalescehttps://dev.mysql.com/doc/refman/5.7/en/flow-control-functions.html#function_ifnull

对于只保留最新发货参考的问题，假设您有MySQL 8，那么我建议使用row_number() over()，它允许将数字1分配给每个用户的最新日期，如下所示：

SELECT users.*
, coalesce(shipping.tracking_number,'""') as tracking_number
, coalesce(shipping.tracking_date,'""') as tracking_date
FROM users
LEFT 
JOIN (
SELECT user_id, tracking_number, tracking_date
, ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY tracking_date DESC) AS rn
FROM shipping 
) AS shipping
ON users.user_id =shipping.user_id
AND shipping.rn = 1

此变体应适用于V8:之前的旧MySQL版本

SELECT users.*
, coalesce(shipping.tracking_number,'""') as tracking_number
, coalesce(shipping.tracking_date,'""') as tracking_date
FROM users
LEFT 
JOIN (
SELECT
@row_num :=IF( @prev_value = s.user_id ,@row_num + 1 ,1 )AS rn
, s.user_id, s.tracking_number, s.tracking_date
, @prev_value := user_id
FROM shipping As s
CROSS
JOIN (SELECT @row_num :=1,  @prev_value :='') vars
ORDER
BY s.user_id, s.tracking_date DESC
) AS shipping
ON users.user_id =shipping.user_id
AND shipping.rn = 1