我有一个表,可以有多个记录用于ip_address
。但是,我想返回canceled=0
是否存在多个ip_address
记录的行,否则将获得第一行canceled=1
id ip_address username canceled
0 10.10.10.1 john 0
1 10.10.10.1 johnny 1
2 10.10.10.1 quincy 1
3 10.10.10.2 bob 1
4 10.10.10.3 kristin 0
示例返回的结果集为 SELECT ip_address,username ....
:
10.10.10.1 john
10.10.10.2 bob
10.10.10.3 kristin
使用 SQLAlchemy/Sqlite 如果这有任何相关性。
SELECT
b.id,
a.ip_address,
b.username,
b.canceled
FROM (
select distinct(ip_address) from mytable order by canceled) a,
mytable b
WHERE
b.ip_address=a.ip_address;
但这返回了"ip_address"的多个定义
正确的查询似乎是:
SELECT b.id,a.ip_address,b.username,b.canceled FROM (select distinct(ip_address) from mytable order by canceled) a, mytable b WHERE b.ip_address=a.ip_address GROUP BY b.ip_address
假设取消是一个整数,这应该可以工作。
select *from #temp order by canceled
with cte as
(
select ip_address,username,canceled,ROW_NUMBER() over(partition by ip_address order by canceled) as rn from #temp
)
select *from cte where rn=1