时间戳

首页 > 解决方案 > 将日期时间与另一个表 date_time 进行比较

sql - 将日期时间与另一个表 date_time 进行比较

问题描述

select uid , max(price) keep(dense_rank last order by usage_date ASC) amount 
from HISTORY group by uid" ;

将 HISTORY 与 ADJUSTMENT 进行比较 如果最新的 usage_date（每个 id ）早于（sen_date） 我想添加 ADJUSTMENT 的价格 我该怎么做？

*如果usage_date早于ADJUSTMENT，我想将ADJUSTMENT的价格添加到max（price），在这种情况下uid = 5应该添加200

ADJUSTMENT
uid,price,sen_date
1,100,2020-10-23
2,200,2020-10-23
2,200,2020-09-22
2,200,2020-08-23
2,200,2020-10-22
3,300,2020-10-20
5,200,2020-10-20


HISTORY
uid,price,usage_date
1,1000,2020-10-23
1,1000,2020-10-19
1,1000,2020-10-03
2,1000,2020-10-23
3,1000,2020-10-23
3,1000,2020-10-23
3,1000,2020-10-23
4,1000,2020-10-20
4,1000,2020-10-23
4,1000,2020-10-19
4,1000,2020-10-23
4,1000,2020-10-23
5,1000,2020-10-02
5,1000,2020-10-03
5,1000,2020-10-04
6,1000,2020-10-23
7,1000,2020-10-23

标签： sqloracleoracle11ggreatest-n-per-group

解决方案

将您的查询和它带到与以后日期相关的表LEFT OUTER JOIN的类似查询中，然后用于组合日期：ADJUSTMENTuidCOALESCE

SELECT h."UID",
       h.usage_date,
       a.sen_date,
       h.price AS base_price,
       COALESCE( a.price, 0 ) AS price_adjustment,
       h.price + COALESCE( a.price, 0 ) AS price
FROM   (
         SELECT "UID",
                MAX( usage_date ) AS usage_date,
                MAX(price) KEEP ( DENSE_RANK LAST ORDER BY usage_date ASC) AS price
         FROM   HISTORY
         GROUP BY "UID"
       ) h
       LEFT OUTER JOIN
       (
         SELECT "UID",
                MAX( sen_date ) AS sen_date,
                MAX(price) KEEP ( DENSE_RANK LAST ORDER BY sen_date ASC) AS price
         FROM   ADJUSTMENT
         GROUP BY "UID"
       ) a
       ON ( h."UID" = a."UID" AND h.usage_date < a.sen_date )
ORDER BY "UID"

其中，对于样本数据：

CREATE TABLE adjustment ( "UID",price,sen_date ) AS
SELECT 1,100,DATE '2020-10-23' FROM DUAL UNION ALL
SELECT 2,200,DATE '2020-10-23' FROM DUAL UNION ALL
SELECT 2,200,DATE '2020-09-22' FROM DUAL UNION ALL
SELECT 2,200,DATE '2020-08-23' FROM DUAL UNION ALL
SELECT 2,200,DATE '2020-10-22' FROM DUAL UNION ALL
SELECT 3,300,DATE '2020-10-20' FROM DUAL UNION ALL
SELECT 5,200,DATE '2020-10-20' FROM DUAL;

CREATE TABLE HISTORY ( "UID",price,usage_date) AS
SELECT 1,1000,DATE '2020-10-23' FROM DUAL UNION ALL
SELECT 1,1000,DATE '2020-10-19' FROM DUAL UNION ALL
SELECT 1,1000,DATE '2020-10-03' FROM DUAL UNION ALL
SELECT 2,1000,DATE '2020-10-23' FROM DUAL UNION ALL
SELECT 3,1000,DATE '2020-10-23' FROM DUAL UNION ALL
SELECT 3,1000,DATE '2020-10-23' FROM DUAL UNION ALL
SELECT 3,1000,DATE '2020-10-23' FROM DUAL UNION ALL
SELECT 4,1000,DATE '2020-10-20' FROM DUAL UNION ALL
SELECT 4,1000,DATE '2020-10-23' FROM DUAL UNION ALL
SELECT 4,1000,DATE '2020-10-19' FROM DUAL UNION ALL
SELECT 4,1000,DATE '2020-10-23' FROM DUAL UNION ALL
SELECT 4,1000,DATE '2020-10-23' FROM DUAL UNION ALL
SELECT 5,1000,DATE '2020-10-02' FROM DUAL UNION ALL
SELECT 5,1000,DATE '2020-10-03' FROM DUAL UNION ALL
SELECT 5,1000,DATE '2020-10-04' FROM DUAL UNION ALL
SELECT 6,1000,DATE '2020-10-23' FROM DUAL UNION ALL
SELECT 7,1000,DATE '2020-10-23' FROM DUAL;

输出：

识别码 | 使用日期 | 发送日期 | 基本价格 | 价格调整 | 价格
--: | :--------- | :-------- | ---------: | ---------------: | ----:
  1 | 20 年 10 月 23 日 | 空      | 1000 | 0 | 1000
  2 | 20 年 10 月 23 日 | 空      | 1000 | 0 | 1000
  3 | 20 年 10 月 23 日 | 空      | 1000 | 0 | 1000
  4 | 20 年 10 月 23 日 | 空      | 1000 | 0 | 1000
  5 | 04-OCT-20 | 20-OCT-20 | 1000 | 200 | 1200
  6 | 20 年 10 月 23 日 | 空      | 1000 | 0 | 1000
  7 | 20 年 10 月 23 日 | 空      | 1000 | 0 | 1000

db<>在这里摆弄

推荐阅读