PostgreSQL在当前查询中按日期过滤

wixjitnu 于 2023-03-08 发布在 PostgreSQL

关注(0)|答案(1)|浏览(212)

初始数据：base
有一个数据库查询：

SELECT accounts.number acc, counters.service serv, meter_pok.date, counters.tarif, meter_pok.value
    FROM stack.accounts
    LEFT JOIN stack.counters ON counters.acc_id = accounts.row_id
    LEFT JOIN stack.meter_pok ON meter_pok.acc_id = accounts.row_id AND meter_pok.counter_id = counters.row_id
    WHERE accounts.type = 3 AND counters.service = 100

在执行时返回以下内容：

"acc","serv","date","tarif","value"
111,100,"2023-01-30",1,100
111,100,"2023-02-25",1,100
122,100,"2023-01-25",1,100
122,100,"2023-02-27",1,50
133,100,"2023-01-25",1,100
133,100,"2023-02-27",1,900
144,100,"2023-02-27",1,0
301,100,"2023-01-25",1,200
301,100,"2023-02-27",1,40
301,100,"2023-02-27",1,-90
501,100,"2023-01-25",1,100
501,100,"2023-02-27",1,50
402,100,NULL,1,NULL
401,100,NULL,1,NULL
502,100,NULL,1,NULL

如何增加按最迟日期过滤？得到如下结果：

"acc","serv","date","tarif","value"
111,100,"2023-02-25",1,100
122,100,"2023-02-27",1,50
133,100,"2023-02-27",1,900
144,100,"2023-02-27",1,0
301,100,"2023-02-27",1,40
301,100,"2023-02-27",1,-90
501,100,"2023-02-27",1,50

尝试此查询，没有结果...

SELECT accounts.number acc, counters.service serv, meter_pok.date, counters.tarif, meter_pok.value
    FROM stack.accounts
    LEFT JOIN stack.counters ON counters.acc_id = accounts.row_id
    LEFT JOIN stack.meter_pok ON meter_pok.acc_id = accounts.row_id AND meter_pok.counter_id = counters.row_id
    WHERE accounts.type = 3 AND counters.service = 100 AND meter_pok.date = (SELECT MAX(meter_pok.date) FROM stack.meter_pok)

postgresql

来源：https://stackoverflow.com/questions/75597506/postgresql-filter-by-date-in-current-query

1条答案

按热度按时间

j5fpnvbx1#

您可以使用窗口函数来实现!更具体地说，使用DENSE_RANK函数。

SELECT * FROM (
SELECT
  accounts.number acc,
  counters.service serv,
  meter_pok.date,
  counters.tarif,
  meter_pok.value,
  DENSE_RANK () OVER (
    PARTITION BY accounts.number
    ORDER BY meter_pok.date DESC
  ) AS row_num
FROM stack.accounts
LEFT JOIN stack.counters ON counters.acc_id = accounts.row_id
LEFT JOIN stack.meter_pok ON meter_pok.acc_id = accounts.row_id AND meter_pok.counter_id = counters.row_id
WHERE accounts.type = 3
AND counters.service = 100
AND date IS NOT NULL
) AS tb
WHERE row_num = 1;

输出：
| 验收|伺服器|日期|塔里夫|价值|行数|
| - ------|- ------|- ------|- ------|- ------|- ------|
| 一百一十一|一百|二〇二三年二月二十五日|1个|一百|1个|
| 一百二十二|一百|二〇二三年二月二十七日|1个|五十|1个|
| 一百三十三|一百|二〇二三年二月二十七日|1个|九百|1个|
| 一百四十四|一百|二〇二三年二月二十七日|1个|无|1个|
| 三零一|一百|二〇二三年二月二十七日|1个|四十|1个|
| 三零一|一百|二〇二三年二月二十七日|1个|-90|1个|
| 小行星501|一百|二〇二三年二月二十七日|1个|五十|1个|
SQLize上的示例。

赞(0）回复(0）举报 2023-03-08

我来回答

PostgreSQL在当前查询中按日期过滤

1条答案

相关问题

热门标签

最新问答