求教，一个关于筛选的问题，在线等。
product_id yh
123 lili
123 wuwu
123 lili
456 xixi
456 hehe
789 zizi
789 yiyi
789 pipi
789 yiyi
可以看到product_id一共分为3类，而每类对应的yh有相同的也有不同的，想提取这样的结果：
product_id yh
123 lili
123 wuwu
456 xixi
456 hehe
789 zizi
789 yiyi
即：每个product_id取两行,而每一行对应的yh是不同的。。。。。。。。
谢谢，在线等，有额外赠品啊。。。
[最优解释]
修改下：
select product_id,yh
from (
select product_id,yh,row_number() over(order by product_id) rowid from TB group by product_id,yh) as a
where rowid<3
[其他解释]
SELECT?DISTINCT?product_id,yh into #temp
FROM?TB
SELECT *
FROM (SELECT *,
Row_number()OVER (partition BY product_id ORDER BY yh ) AS id
FROM #temp) a
WHERE id <= 1
SELECT product_id ,
yh
FROM ( SELECT product_id ,
yh ,
row_number() OVER (PARTITION BY product_id ORDER BY product_id ) rowid
FROM TB
GROUP BY product_id ,
yh
) AS a
WHERE rowid <=2
if not object_id('tempdb..#Testtb') is null
begin
drop table #Testtb
end
create table #Testtb
(
Product_id nvarchar(36),
yh nvarchar(36)
)
insert into #Testtb
select '123','lili' union all
select '123','wuwu' union all
select '123','lili' union all
select '456','xixi' union all
select '456','hehe' union all
select '789','zizi' union all
select '789','yiyi' union all
select '789','pipi' union all
select '789','yiyi'
select Product_id,yh from
(select Product_id,yh,row_number() over(partition by product_id order by(select 1)) number from
(select *,row_number() over(partition by yh order by(select 1))
number from #Testtb) A
where number=1
) A where number<3
/*
Product_id,yh
123wuwu
123lili
456hehe
456xixi
789yiyi
789zizi
*/
SELECT DISTINCT product_id,yh
FROM TB
