首页 新闻 搜索 专区 学院

SQL优先取某行

0
[待解决问题]
no line val
1 V0 100
2 V0-1 108
1 V1 99
2 V2 96
4 C3 109
5 C4 101
4 2E3 110
5 2E4 121
6 V0 102

上面tab1,同一no的line列中有v1时保留V1所有行,没有V1保留VO所在行

 当line中有V2优先取V2所在行,没有V2取V0-1所有行

 当line中有C3时优先取C3所在行,没有C3时取2E3

最后要得出的结果如下:

no line val
1 V1 99
2 V2 96
4 C3 109
5 C4 101
5 2E4 121
6 V0 102

请问这个脚本要怎么写?

临时表脚本

SELECT * INTO #T1 FROM (
SELECT 1 no,'V0'line,100 val
UNION ALL
SELECT 2 no,'V0-1'line,108 val
UNION ALL
SELECT 1 no,'V1'line,99 val
UNION ALL
SELECT 2 no,'V2'line,96 val
UNION ALL
SELECT 4 no,'C3'line,109 val
UNION ALL
SELECT 5 no,'C4'line,101 val
UNION ALL
SELECT 4 no,'2E3'line,110 val
UNION ALL
SELECT 5 no,'2E4'line,121 val
UNION ALL
SELECT 6 no,'V0'line,102 val
UNION ALL
SELECT 1 no,'V0'line,80 val
)T;

SQL
攀迪的主页 攀迪 | 菜鸟二级 | 园豆:202
提问于:2017-09-08 19:58
< >
分享
所有回答(3)
0

select * from (select *,rank() over (partition by no order by no) as rank from tb) t where rank=1

Daniel Cai | 园豆:10374 (专家六级) | 2017-09-08 20:53

我试了,这样子不行哦,大侠,没有过滤到。

支持(0) 反对(0) 攀迪 | 园豆:202 (菜鸟二级) | 2017-09-09 08:14
0

select * from (select *,rank() over (partition by no order by no) as rank from tb) t where rank=1

fcyh | 园豆:568 (小虾三级) | 2017-09-08 20:54
0

这样说,no取唯一值,那么,你需要获得唯一的,然后根据你自己的规则,获取line和val,伪代码如下,

with cte as
(
SELECT 1 no,'V0'line,100 val
UNION ALL
SELECT 2 no,'V0-1'line,108 val
UNION ALL 
SELECT 1 no,'V1'line,99 val
UNION ALL
SELECT 2 no,'V2'line,96 val
UNION ALL
SELECT 4 no,'C3'line,109 val
UNION ALL
SELECT 5 no,'C4'line,101 val
UNION ALL
SELECT 4 no,'2E3'line,110 val
UNION ALL
SELECT 5 no,'2E4'line,121 val
UNION ALL
SELECT 6 no,'V0'line,102 val
UNION ALL
SELECT 1 no,'V0'line,80 val
)
,cte_unique as 
(
select distinct
    no
from cte
)

select no
    ,line=(select top 1 c.line from cte c where c.no=u.no)    -- base on your rule
    ,val=(select top 1  c.val from cte c where c.no=u.no)  --base on your rule
from cte_unique u
悦光阴 | 园豆:2239 (老鸟四级) | 2017-10-11 14:10
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册