34,591
社区成员
发帖
与我相关
我的任务
分享
/*
有一张表(大约200万条记录)。为方便测试,剔除无关信息,随机生成10000行记录,保留3列,记为:
test(usrid,value,u_type),其中usrid唯一,value在1000范围以内,u_type为‘Yes'或‘No’。
--要求:选择表中value值相同但u_type不同的记录,将其usrid成对找出来。
--例如:原始表为:
usrid value u_type
1 1 Yes
2 34 No
4 86 No
5 34 No
6 7 Yes
8 1 Yes
9 1 No
3 10 Yes
89 10 Yes
78 7 No
14 2 No
66 2 Yes
102 2 No
708 8 Yes
84 8 No
99 8 Yes
182 8 No
则,最终表为(只有1行):
Usrid
1
9
6
78
14
66
84
99
182
708
这里像value为1的记录,u_type有2个Yes,1个No。属于多对1,那么任意挑一个Yes和No的记录,找出其usrid(1和9)。
value为2的记录属于1对多,做类似处理。但是多对多的时候,要取Yes和No最小记录数,例如value为8时,
有2个Yes,2个No,那么都要取出,
即:对于每一个相同的value,取出的记录数是:2*min(Yes,No)。
*/
--随机生成数据
if OBJECT_ID('test2') is not null
drop table test2
go
create table test2(usrid int,value int,u_type varchar(5))
declare @i int
set @i=1
while @i<=10000
begin
insert into test2 values(@i,ABS(CHECKSUM(newid())%1000),ABS(CHECKSUM(newid()))%2)
set @i=@i+1
end
update test2
set u_type=case u_type when 1 then 'Yes'
when 0 then 'No' end from test2
--select count(1) from test2
select
value,
sum(case when u_type='Yes' then 1 else 0 end) Yes,
sum(case when u_type='No' then 1 else 0 end) No
into temp1
from test2
group by value
having count(distinct u_type)>1
select *
,case when Yes>=No then No else Yes end as min_num
into temp2
from temp1
select t.*
from
(
select
t1.usrid,
t1.value,
t1.u_type,
min_num,
row_number() over(partition by t1.value,t1.u_type order by t1.usrid) rin
from
test4 t1
inner join
temp2 t2
on t1.value=t2.value
) t
where t.rin<=min_num
;with cte as
(
select value,sum(case when u_type='Yes' then 1 else 0 end) Yes,sum(case when u_type='No' then 1 else 0 end) No from @test
group by value
having count(distinct u_type)>1
)
select t.usrid from
(
select a.usrid,a.value,a.u_type,row_number() over(partition by a.value,a.u_type order by newid()) rn from @test a,cte b
where a.value=b.value and b.Yes>b.No
) t
where t.rn=1
union all
select t.usrid from
(
select a.usrid,a.value,a.u_type,row_number() over(partition by a.value,a.u_type order by a.usrid) rn from @test a,cte b
where a.value=b.value and b.Yes<b.No
) t
where t.rn=1
union all
select t.usrid from
(
select a.usrid,a.value,a.u_type,row_number() over(partition by a.value,a.u_type order by a.value) rn from @test a,cte b
where a.value=b.value and b.Yes=b.No
) t
order by usrid