我有一个表配置文件包含该配置文件的状态,即活动和非活动注意一个csr\u id有多个非活动状态记录,但肯定只有一个或没有状态为活动的记录
您的tablereference:-
id | status | csr_id |
---+-----------+---------
1 | inactive | 1
2 | inactive | 1
3 | inactive | 1
4 | inactive | 1
5 | inactive | 2
6 | inactive | 2
7 | inactive | 2
8 | inactive | 2
9 | active | 2
查询reference:-
(select * from profile where csr_id IN (2,1) AND status = 'active')
UNION
(select DISTINCT ON (csr_id) *from profile where csr_id IN (2,1) AND status = 'inactive') order by status
result:-
id | status | csr_id |
-----+----------+---------------+--------
9 | active | 2
4 | inactive | 1
8 | inactive | 2
预期result:-
id | status | csr_id |
-----+----------+---------
9 | active | 2
4 | inactive | 1
因为csr\u id 2是活动的,所以忽略csr\u id 2的非活动条目
有什么解决办法吗?
1条答案
按热度按时间7z5jn7bk1#
你可以这样做
distinct on
仅限于:这个
order by
升序从句active
将活动记录放在第一位,然后优先考虑具有最大id
.db小提琴演示: