根据字段组合选择记录的分区(sql)

Question

请告诉我如何用sql写代码。如果在给定的集合中(表1)，假设&;clusterid&;均相同且"不为空"，并且还提供"已发出";和";operdate"都是一样的。线条更有独特的" public "显示组合。(表2)

表1
|旅店|publid|clusterid|issuedate|operdate|
|----|------|---------|----------|----------|
| 333 | - 1 - | - 12 - | 01-01-21 | 01-01-21 |
| 222 | - 1 - | - 12 - | 01-01-21 | 01-01-21 |
| 333 | - 2 - | - 12 - | 01-01-21 | 01-01-21 |
| 222 | - 2 - | - 12 - | 01-01-21 | 01-01-21 |
| 111 | - 2 - | - 12 - | 01-01-21 | 01-01-21 |

表2
|旅店|publid|clusterid|issuedate|operdate|
|----|------|---------|----------|----------|
| 333 | - 2 - | - 12 - | 01-01-21 | 01-01-21 |
| 222 | - 2 - | - 12 - | 01-01-21 | 01-01-21 |
| 111 | - 2 - | - 12 - | 01-01-21 | 01-01-21 |

必须通过窗口函数完成，我尝试了，但请求不起作用

SELECT a.*
FROM (SELECT m.*, RANK() OVER (PARTITION BY clusterid order by issuedate desc, operdate desc, count(inn) desc) AS rn
FROM table as m 
GROUP BY publid
WHERE clusterid is not null
) AS a
WHERE a.rn = 1

Answer 1

试试这个

Select * from (
Select *, rank() over (order by cnt desc) rn from (
(Select publid, clusterid, issuedate, operdate, count(distinct 
inn) as cnt
From table where clusterid is not null 
group by publid, clusterid, issuedate, operdate)
)
) where rn =1;