英文:
Combine Customer and Purchase Date tables for latest purchase, but include nulls
问题
我有两个表，一个存储客户ID，另一个存储他们每次购买的日期。当查询所有客户的最大购买日期时，我遇到了一个问题，即使用MAX函数时，那些还没有购买日期的新客户不会显示，因为他们要么在PurchaseDates表中没有条目，要么他们的purchase_date字段为空。
我的SQL视图代码：
SELECT ct.CustomerID,
ct.Full_Name,
pd.Purchase_Date
FROM CustomerTable AS ct
LEFT OUTER JOIN PurchaseDates AS pd
ON ct.CustomerID = pd.CustomerID
WHERE EXISTS (SELECT 1
FROM PurchaseDates AS pd_latest
WHERE ( CustomerID= pd.CustomerID)
GROUP BY CustomerID
HAVING ( Max(Purchase_Date) = pd.Purchase_Date))
在上面的示例中，结果仅显示了客户ID为1的购买日期为11/21/2021的客户，但我还希望显示客户ID为2，其purchase_date为null。我不太确定如何继续，除了看到一些人选择用任意日期替换所有null值。
最终结果应如下所示：
| CustomerID | Full_Name | Purchase_Date |
|---|---|---|
| 1 | John Doe | 11/21/2021 |
| 2 | Jane Doe | NULL |
感谢帮助。
英文:
I've got two tables, one where customer ID is store and another that stores each date they had a purchase on. I am stuck on keeping all new customers that don't have a purchase date yet when querying for the max purchase date for all customers.
CustomerTable:
| CustomerID | Full_Name |
|---|---|
| 1 | John Doe |
| 2 | Jane Doe |
PurchaseDates:
| CustomerID | Purchase_Date |
|---|---|
| 1 | 11/21/2021 |
| 1 | 4/19/2003 |
I have set up a view in SQL that combines the two and queries for the MAX purchase date for each customer. The problem is that since I am using MAX, customers that have not purchased anything yet do not show up as they either do not have an entry in PurchaseDates table or their purchase_date field is blank.
My SQL View Code:
SELECT ct.CustomerID,
ct.Full_Name,
pd.Purchase_Date,
FROM CustomerTable AS ct
LEFT OUTER JOIN PurchaseDates AS pd
ON ct.CustomerID = pd.CustomerID
WHERE EXISTS (SELECT 1
FROM PurchaseDates AS pd_latest
WHERE ( CustomerID= pd.CustomerID)
GROUP BY CustomerID
HAVING ( Max(Purchase_Date) = pd.Purchase_Date))
The result in my example above yields only customerID 1 with the purchase date of 11/21/2021, but I'd like to also display CustomerID 2 with a null date for their purchase_date. Not really sure how to proceed apart from seeing that some have opted to replace all nulls with arbitrary days.
The end result should be
| CustomerID | Full_Name | Purchase_Date |
|---|---|---|
| 1 | John Doe | 11/21/2021 |
| 2 | Jane Doe |
Appreciate the help
答案1
得分: 2
以下是翻译好的部分：
你只需要从`PurchaseDates`表中获取一个单一值，所以你只需要一个简单的相关子查询：
select ct.CustomerID, ct.Full_Name,
(
select Max(pd.Purchase_Date)
from PurchaseDates pd
where pd.CustomerId = ct.CustomerId
) as Purchase_Date
from CustomerTable ct;
如果需要不止一个列，那么你可以*应用*适当的行：
select ct.CustomerID, ct.Full_Name, pd.*
from CustomerTable ct
outer apply (
select top(1) *
from PurchaseDates pd
where pd.CustomerId = ct.CustomerId
order by pd.Purchase_date desc
) pd;
英文:
You only need a single value from the PurchaseDates table so a simple correlated subquery is all you require:
select ct.CustomerID, ct.Full_Name,
(
select Max(pd.Purchase_Date)
from PurchaseDates pd
where pd.CustomerId = ct.CustomerId
) as Purchase_Date
from CustomerTable ct;
Should more than a single column be required then you could apply the appropriate row:
select ct.CustomerID, ct.Full_Name, pd.*
from CustomerTable ct
outer apply (
select top(1) *
from PurchaseDates pd
where pd.CustomerId = ct.CustomerId
order by pd.Purchase_date desc
)pd;
答案2
得分: 0
另一个相关子查询的版本：
select *
from (
(select Full_name,
your_date,
(select max(your_date) from PurchaseDates c where c.id=A.id ) as max_date
from CustomerTable A
LEFT JOIN PurchaseDates B ON A.ID =B.ID)) x
where (x.max_date = your_date) or your_date is null or max_date is null
英文:
Another version of the correlated subquery :
select *
from (
(select Full_name,
your_date,
(select max(your_date) from PurchaseDates c where c.id=A.id ) as max_date
from CustomerTable A
LEFT JOIN PurchaseDates B ON A.ID =B.ID)) x
where (x.max_date = your_date) or your_date is null or max_date is null
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论