求一条语句 需要用到循环

[tjcloving]

表 test:
ID PRODUCT NUM
1 A 10
1 B 10
1 C 10
2 A 20
2 B 20

查询输出后：
ID PRODUCT NUM
1 A 10
1 B
1 C
2 A 20
2 B

也就是说同一个ID不管PRODUCT 只要NUM有值 只出现一次 其余都为空
通过where exists()可以实现 但是如果数据量过大 速度太慢 所以我现在需要大家帮忙通过循环来实现这个功能 谢谢

[EastboyEastboy]

select a.id,a.product,b.num
from test1 a left join
(select distinct id,min(product) as product,num from test1 group by id,num) b on a.id = b.id and a.product = b.product

一楼group by 后面有误

[gohst001]

select a.ID,a.PRODUCT,num from test a left outer join
(select ID,min(PRODUCT) as PRODUCT from test where not num is null group by ID,PRODUCT) b
on a.ID=b.ID and a.PRODUCT=b.PRODUCT

[imzhu]

楼上的应该能满足楼主的要求

[gohst001]

上面的有问题 ,修改一下
select a.ID,a.PRODUCT,case when n is null then null else num end as num from test a left outer join
(select ID,min(PRODUCT) as PRODUCT,'1' as n from test where not num is null group by ID,PRODUCT) b
on a.ID=b.ID and a.PRODUCT=b.PRODUCT

[tjcloving]

谢谢你们的关注 我都说过了 如果数据量大的话 这样做速度太慢 我希望用变量和循环实现