sql 多表查询玩家排名

[喜洋洋呵呵]

表 A
player_id 战斗力
1             100
2             98
3             100

表 B
player_id  荣誉
1             32
2              91
3             12

给玩家player_id 获取对应玩家排名,如果战斗力一样，查看荣誉，荣誉高的拍在前面。请问SQL语句怎么写，所以玩家 1 拍第一名 玩家 3排名 第二 玩家2排名第三

[哥眼神纯洁不]

select a.* from a left join b on a.player_id=b.player_id order b 战斗力 desc,荣誉 desc

[搬砖的码农]

select a.player_id,b.荣誉,a.战斗力 from a inner join b on a.player_id=b.player_id order by b.荣誉,a.战斗力 desc

[喜洋洋呵呵]

引用 楼主 hujunlong123 的回复:

SQL code?1234567891011表 Aplayer_id 战斗力1 1002 983 100 表 Bplayer_id 荣誉1 322 913 12 给玩家player_id 获取对应玩家排名,如果战斗力一样，查看……

我只想想给表玩家ID 然后 返回玩家排第几名就可以，其他什么都不需要。能不能在帮我看看

[上善若水_厚德载物]

--表A
IF(OBJECT_ID('A') IS NOT NULL) DROP TABLE A
CREATE TABLE A (player_id INT,zhandouli INT)
INSERT INTO A  
SELECT  1,100 UNION ALL
SELECT 2,98 UNION ALL
SELECT 3,100
--表B
IF(OBJECT_ID('B') IS NOT NULL) DROP TABLE B
CREATE TABLE B (player_id INT,rongyu INT)
INSERT INTO B
SELECT  1,32 UNION ALL
SELECT 2,91 UNION ALL
SELECT 3,12

select ID=ROW_NUMBER() over(order by zhandouli desc,rongyu  desc),a.player_id 
from A  join B  on a.player_id = b.player_id

[Kim_Du]

两表联查，两个字段排序

[骑着蜗牛去爬山]

引用 3 楼 hujunlong123 的回复:

引用 楼主 hujunlong123 的回复:SQL code?1234567891011表 Aplayer_id 战斗力1 1002 983 100 表 Bplayer_id 荣誉1 322 913 12 给玩家player……

select row from(select a.player_id,row=row_number()over( order by 战斗力 desc,荣誉 desc) from tba a join tbb b on a.player_id=b.player_id)t where player_id=@id

[learningcoder]

--表A
IF(OBJECT_ID('TA') IS NOT NULL) DROP TABLE TA
CREATE TABLE TA (player_id INT,zhandouli INT)
INSERT INTO TA  
SELECT  1,100 UNION ALL
SELECT 2,98 UNION ALL
SELECT 3,100
--表B
IF(OBJECT_ID('TB') IS NOT NULL) DROP TABLE TB
CREATE TABLE TB (player_id INT,rongyu INT)
INSERT INTO TB
SELECT  1,32 UNION ALL
SELECT 2,91 UNION ALL
SELECT 3,12
--结果集 
SELECT ROW_NUMBER() OVER (ORDER BY TA.zhandouli DESC,TB.rongyu DESC) AS ID,TA.player_id
  FROM TA JOIN TB ON (TA.player_id=TB.player_id)
/*
ID                   player_id
-------------------- -----------
1                    1
2                    3
3                    2

(3 行受影响)
*/ 	 

[豁燃开朗]

--表A
IF(OBJECT_ID('A') IS NOT NULL) DROP TABLE A
CREATE TABLE A (player_id INT,zhandouli INT)
INSERT INTO A  
SELECT  1,100 UNION ALL
SELECT 2,98 UNION ALL
SELECT 3,100
--表B
IF(OBJECT_ID('B') IS NOT NULL) DROP TABLE B
CREATE TABLE B (player_id INT,rongyu INT)
INSERT INTO B
SELECT  1,32 UNION ALL
SELECT 2,91 UNION ALL
SELECT 3,12
 
select ID=rank() over(order by zhandouli desc,rongyu  desc),a.player_id 
from A  join B  on a.player_id = b.player_id