当我执行"从表"中选择 *时,我得到了下面的结果:
ID Date Time Type
----------------------------------
60 03/03/2013 8:55:00 AM 1
60 03/03/2013 2:10:00 PM 2
110 17/03/2013 9:15:00 AM 1
67 24/03/2013 9:00:00 AM 1
67 24/03/2013 3:05:00 PM 2
您看到每个ID在同一日期具有1型交易类型1和2除了ID 110只有类型1
所以我怎么能像这样获得结果:
ID Date Time Type
----------------------------------
110 17/03/2013 9:15:00 AM 1
因为从第一个结果返回了一个记录
根据您的需要更改分区定义(partition by id,date
)
select *
from (select t.*
,count(*) over (partition by id,date) as cnt
from mytable t
) t
where t.cnt = 1
;
您可以使用以下方式:
select * from my_table t
where exists (
select 1 from my_table
where id = t.id
group by id
having count(*) = 1
)
如果仅想要类型1,请比较最小值和最大值。我更喜欢窗口功能:
select t.*
from (select t.*, min(type) over (partition by id) as mintype,
max(type) over (partition by id) as maxtype
from t
) t
where mintype = maxtype and mintype = 1;
如果仅想要相同类型的记录(而不是专门的type = 1
),请删除该条件。
如果您只想在同一天记录,则在partition by
中包括日期。
在某些情况下,not exists
可以更快:
select t.*
from t
where not exists (select 1 from t t2 where t2.id = t.id and t2.type <> 1);