sql - 如果查询没有返回结果,则在同一个表/视图中选择一行
问题描述
我的 SQL 数据库中有以下视图,它从 Transaction 表和 Customer 表中选择数据:
+-------+-----------+---------------------+--------+
| RowNo | Name | Date | Amount |
+-------+-----------+---------------------+--------+
| 1 | Customer1 | 2018-11-10 01:00:00 | 55.49 |
| 2 | Customer2 | 2018-11-10 02:00:00 | 58.15 |
| 3 | Customer3 | 2018-11-10 03:00:00 | 79.15 |
| 4 | Customer1 | 2018-11-11 04:00:00 | 41.89 |
| 5 | Customer2 | 2018-11-11 05:00:00 | 5.15 |
| 6 | Customer3 | 2018-11-11 06:00:00 | 35.17 |
| 7 | Customer1 | 2018-11-12 07:00:00 | 43.78 |
| 8 | Customer1 | 2018-11-12 08:00:00 | 93.78 |
| 9 | Customer2 | 2018-11-12 09:00:00 | 80.74 |
+-------+-----------+---------------------+--------+
我需要一个 SQL 查询来返回给定日期的所有客户交易(很简单),但是如果客户在给定日期没有交易,则查询必须返回客户最近的交易。
编辑:
观点如下:
Create view vwReport as
Select c.Name, t.Date, t.Amount
from Transaction t
inner join Customer c on c.Id = t.CustomerId
然后要获取数据,我只需从视图中进行选择:
Select * from
vwReport r
where r.Date between '2018-11-10 00:00:00' and '2018-11-11 00:00:00'
因此,为了澄清,我需要一个查询来返回一天的所有客户交易,并且包含在该结果集中的是当天没有交易的任何客户的最后一笔交易。因此,在上表中,运行 2018-11-12 的查询,应该返回第 7、8 和 9 行,以及 12 日没有交易的 Customer3 的第 6 行。
解决方案
UNION ALL
对于在该范围内没有交易的每个人,请使用您现有的查询和“最近的交易查询”。
with found as
(
select c.Id, c.Name, t.Date, t.Amount
from Transaction t
inner join Customer c on c.Id = t.CustomerId
where Date between '2018-11-10 00:00:00' and '2018-11-11 00:00:00'
)
with unfound as
(
select c.Id, c.Name, t.Date, t.Amount, RANK() OVER (PARTITION BY Name ORDER BY CAST(Date AS DATE) DESC) AS row
from Transaction t
inner join Customer c on c.Id = t.CustomerId
WHERE Date < '2018-11-10 00:00:00'
)
select Name, Date, Amount
from found
union all
select Name, Date, Amount
from unfound
where Id not in ( select Id from found ) and row = 1