我在SQL中有一个这样的表:
| assetId | price | volume | netVolume | date | transactionDateTime |
|---|---|---|---|---|---|
| 1 | 200.00 | 100 | 100 | 2023-05-12 | 2023-05-12 10:32:10.000 |
| 1 | 200.00 | 100 | 200 | 2023-05-12 | 2023-05-12 10:50:10.000 |
| 3 | 300.00 | 100 | 100 | 2023-05-15 | 2023-05-15 11:40:46.000 |
| 2 | 200.00 | 100 | 500 | 2023-05-15 | 2023-05-15 12:18:01.000 |
| 3 | 300.00 | 100 | 100 | 2023-05-16 | 2023-05-16 11:50:31.000 |
| 2 | 200.00 | 100 | 600 | 2023-05-16 | 2023-05-16 12:20:18.000 |
正如您在某些天看到的，我们可能有多行具有相同的assets资源 ID(2023-05-12)，或者我们可能没有任何东西. 我要创建一个CTE以获取具有以下规则的新表:
- 从@startDate开始，每天转到@endDate
- 如果有多行相同的assets资源 ID，只需 Select 最后一行(基于TransactionDateTime)
- 如果对于特定日期没有assets资源 ID的行，则添加最后一个现有的行，但将日期更改为当前日期，所有其他数据必须保持未链接状态.如果找不到任何以前的数据，请不要添加行. 我设法使用下面的CTE完成了第一部分，但我找不到一种方法来完成第二部分:
with CTE AS (
SELECT assetId, rowNumber, price, volume, netVolume, date, transactionDateTime,
ROW_NUMBER()OVER(PARTITION BY td.assetId,td.date order by td.transactionDateTime desc) AS RN
FROM transactiondData td
)
SELECT assetId, rowNumber, price, volume, netVolume, date, transactionDateTime
FROM CTE
WHERE RN = 1 AND date >= @startDate AND date <= @endDate
ORDER BY transactionDateTime;
最后，我必须有一个这样的表，想想@startDate=2023-05-13和@endDate=2023-05-17:
| assetId | price | volume | netVolume | date | transactionDateTime |
|---|---|---|---|---|---|
| 1 | 200.00 | 100 | 200 | 2023-05-13 | 2023-05-15 10:50:10.000 |
| 1 | 200.00 | 100 | 200 | 2023-05-14 | 2023-05-15 10:50:10.000 |
| 1 | 200.00 | 100 | 200 | 2023-05-15 | 2023-05-15 10:50:10.000 |
| 3 | 300.00 | 100 | 100 | 2023-05-15 | 2023-05-15 11:40:46.000 |
| 2 | 200.00 | 100 | 500 | 2023-05-15 | 2023-05-15 12:18:01.000 |
| 3 | 300.00 | 100 | 100 | 2023-05-16 | 2023-05-16 11:50:31.000 |
| 1 | 200.00 | 100 | 200 | 2023-05-16 | 2023-05-15 10:50:10.000 |
| 2 | 200.00 | 100 | 600 | 2023-05-16 | 2023-05-16 12:20:18.000 |
| 3 | 300.00 | 100 | 100 | 2023-05-17 | 2023-05-16 11:50:31.000 |
| 1 | 200.00 | 100 | 200 | 2023-05-17 | 2023-05-15 10:50:10.000 |
| 2 | 200.00 | 100 | 600 | 2023-05-17 | 2023-05-16 12:20:18.000 |
我怎么能这样做呢？