求教，一个关于筛选的有关问题，

求教，一个关于筛选的问题，在线等。
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
[其他解释]

引用:

SQL code?12SELECT DISTINCT product_id,yhFROM TB

假如每个PRODUCT_ID 有三个呢，这样写就不好办了，我感觉，得先去一下重，然后再选两条数据


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 &lt;= 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 &lt;=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&lt;3
 /*
Product_id,yh
123wuwu
123lili
456hehe
456xixi
789yiyi
789zizi
*/

[其他解释]
select product_id,yh from TB group by product_id,yh
[其他解释]

SELECT DISTINCT product_id,yh
FROM TB

[其他解释]
额外赠品？
[其他解释]
啊，谢谢各位，
赠品就是 http://video.sina.com.cn/v/b/90127369-2606112032.html (一段芭蕾送给你们。)

求教一个关于筛选的有关问题

热点推荐