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;
所有回答(3)
0
select * from (select *,rank() over (partition by no order by no) as rank from tb) t where rank=1
我试了，这样子不行哦，大侠，没有过滤到。
0
select * from (select *,rank() over (partition by no order by no) as rank from tb) t where rank=1
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
