求SQL语句

[舞台中央的我]

现有一个表
优先度 店铺CD 通过仓库CD
1 3 5
2 3 206
1 4 65
2 4 206
3 4 998

优先度数字越大 优先度越高
要 抽出 每个店铺优先度最高的 通过仓库的 list
想要的结果
优先度 店铺CD 通过仓库CD
2 3 206
3 4 998

谢谢

[-小蕾-]

引用 2 楼 roy_88 的回复:

use Tempdb
go
--> --> 
 
if not object_id(N'Tempdb..#1') is null
	drop table #1
Go
Create table #1([优先度] int,[店铺CD] int,[通过仓库CD] int)
Insert #1
select 1,3,5 union all
select 2,3,206 union all
select 1,4,65 union all
select 2,4,206 union all
select 3,4,998
Go
SELECT [优先度],[店铺CD],[通过仓库CD] FROM (Select *,ROW_NUMBER()OVER(PARTITION BY [店铺CD] ORDER BY [优先度] DESC) AS RN from #1) AS a WHERE RN=1
/*
优先度	店铺CD	通过仓库CD
2	3	206

3	4	998*/

+1,建议用ROW_NUMBER()OVER来实现。

[唐诗三百首]

create table #t (优先度 int,店铺CD int,通过仓库CD int)

insert into #t
  select 1,3,5 union all
  select 2,3,206 union all
  select 1,4,65 union all
  select 2,4,206 union all
  select 3,4,998


select *
  from #t a
  where not exists(select 1 from #t b where b.店铺CD=a.店铺CD and b.优先度>a.优先度)

/*
优先度         店铺CD        通过仓库CD
----------- ----------- -----------
2           3           206
3           4           998

(2 row(s) affected)
*/

[Scorpius246]

use tempdb ; go create table t1(id int , shopCD int, whouseCD int); go insert into t1 values (1,3,5), (2,3,206), (1,4,65), (2,4,206), (3,4,998); select * from t1 a where id in (select MAX(id) from t1 b where a.shopCD=b.shopCD)

[转身@未来]

#2 支持你，用ROW_NUMBER()OVER来实现。

[kingtiy]

select a.店铺CD,a.优先度.a.通过仓库CD from tb a as join (
select 店铺CD,max(优先度) 优先度 from tb group by 店铺CD
) b on a.店铺CD=b.店铺CD and a.优先度=b.优先度

[中国风]

use Tempdb
go
--> --> 
 
if not object_id(N'Tempdb..#1') is null
	drop table #1
Go
Create table #1([优先度] int,[店铺CD] int,[通过仓库CD] int)
Insert #1
select 1,3,5 union all
select 2,3,206 union all
select 1,4,65 union all
select 2,4,206 union all
select 3,4,998
Go
SELECT [优先度],[店铺CD],[通过仓库CD] FROM (Select *,ROW_NUMBER()OVER(PARTITION BY [店铺CD] ORDER BY [优先度] DESC) AS RN from #1) AS a WHERE RN=1
/*
优先度	店铺CD	通过仓库CD
2	3	206

3	4	998*/

[中国风]

参照其它方法：http://bbs.csdn.net/topics/240034273

[舞台中央的我]

谢谢 各位 d

[卖水果的net]

2# 的方法，只扫描一次表，效率上要高很多；

[Ginnnnnnnn]

select * from table a where not exists(select * from table where 店铺CD = a.店铺CD and 优先级 > a.优先级)