请教一个sql语句！

Question

两个表：tb1、tb2tb1：ID号   姓名
001   张三
002   李四
003   王五
004   赵六tb2：ID号  得分
002   40
003   30
002   20
001   10
001   10根据tb1、tb2得到一个记录集（将tb2按每个人的得分求和排出名次），如下：ID号  总分  名次
002   60    1
003   30    2
001   20    3
004    0    4这样select语句应如何写？
  

Answer 1

DECLARE @TA TABLE([ID号] VARCHAR(3), [得分] INT)
INSERT @TA 
SELECT '002', 40 UNION ALL 
SELECT '003', 30 UNION ALL 
SELECT '002', 20 UNION ALL 
SELECT '001', 10 UNION ALL 
SELECT '001', 10DECLARE @TB TABLE([ID号] VARCHAR(3), [姓名] NVARCHAR(2))
INSERT @TB 
SELECT '001', N'张三' UNION ALL 
SELECT '002', N'李四' UNION ALL 
SELECT '003', N'王五' UNION ALL 
SELECT '004', N'赵六'SELECT B.ID号,ISNULL(总分,0) AS 总分,名次=DENSE_RANK() OVER (ORDER BY 总分 DESC)
FROM (
SELECT [ID号],SUM([得分]) AS 总分
FROM @TA
GROUP BY  ID号) A
RIGHT JOIN @TB AS B
ON A.[ID号]=B.[ID号]
/*
ID号  总分          名次
---- ----------- --------------------
002  60          1
003  30          2
001  20          3
004  0           4
*/

Answer 2

declare @tb1 table(ID号 varchar(10),姓名 varchar(10))
insert into @tb1
select '001',  '张三' union all
select '002',  '李四' union all
select '003',  '王五' union all
select '004',  '赵六' declare @tb2 table(ID号 varchar(10),得分 int)
insert into @tb2
select '002',  40 union all
select '003',  30 union all
select '002',  20 union all
select '001',  10 union all
select '001',  10 select *,名次=row_number() over(order by 得分 desc) from 
(
select b.ID号 ,b.姓名,isnull(sum(得分),0) 得分 from @tb2 a right join @tb1 b 
on a.ID号 = b.ID号 
group by b.ID号,b.姓名
)a

Answer 3

IF OBJECT_ID('tb1') IS NOT NULL 
   DROP TABLE tb1 
IF OBJECT_ID('tb2') IS NOT NULL
   DROP TABLE tb2 
 
CREATE TABLE tb1 (ID号 varchar(20), 姓名 varchar(20))
insert into tb1(ID号,姓名) 
SELECT '001', '张三'
UNION ALL 
SELECT '002',  '李四'
UNION ALL
SELECT '003',  '王五'
UNION ALL 
SELECT '004', '赵六'  CREATE TABLE tb2 (ID号 varchar(20), 得分  INT)
insert into tb2(ID号,得分) 
SELECT '002' ,40
UNION ALL  
SELECT '003' ,30
UNION ALL  
SELECT '002' ,20
UNION ALL  
SELECT '001' ,10
UNION ALL  
SELECT '001' ,10--查询结果
SELECT ID号,得分,ROW_NUMBER() OVER(ORDER BY 得分) AS 名次
FROM
(
SELECT tb1.ID号, isnull(SUM(得分),0)  as 得分  
FROM tb1 left join TB2 on tb1.id号 = tb2.ID号  GROUP BY TB1.ID号) LO   
ORDER BY LO.得分 DESCDROP TABLE TB1
DROP TABLE TB2
 
 
--结果
ID号    得分      名次
002 60 4
003 30 3
001 20 2
004 0 1

Answer 4

楼上几位写的都是2005以上的，写个sql200的---测试数据---
if object_id('[tb1]') is not null drop table [tb1]
go
create table [tb1]([ID号] varchar(3),[姓名] varchar(4))
insert [tb1]
select '001','张三' union all
select '002','李四' union all
select '003','王五' union all
select '004','赵六'
if object_id('[tb2]') is not null drop table [tb2]
go
create table [tb2]([ID号] varchar(3),[得分] int)
insert [tb2]
select '002',40 union all
select '003',30 union all
select '002',20 union all
select '001',10 union all
select '001',10
 
---查询---
select
  b.ID号,
  b.总分,
  名次=(select count(1)+1 
        from (select tb1.ID号,isnull(sum(tb2.得分),0) as 总分 from tb1 left join tb2 on tb1.ID号=tb2.ID号 group by tb1.ID号) t 
        where 总分>b.总分
       )
from
 (select tb1.ID号,isnull(sum(tb2.得分),0) as 总分 from tb1 left join tb2 on tb1.ID号=tb2.ID号 group by tb1.ID号) b
---结果---
ID号  总分          名次          
---- ----------- ----------- 
001  20          3
002  60          1
003  30          2
004  0           4（所影响的行数为 4 行）

Answer 5

引：if object_id('[tb1]') is not null drop table [tb1]
go
create table [tb1]([ID号] varchar(3),[姓名] varchar(4))
insert [tb1]
select '001','张三' union all
select '002','李四' union all
select '003','王五' union all
select '004','赵六'
if object_id('[tb2]') is not null drop table [tb2]
go
create table [tb2]([ID号] varchar(3),[得分] int)
insert [tb2]
select '002',40 union all
select '003',30 union all
select '002',20 union all
select '001',10 union all
select '001',10
 
---查询---
select
  b.ID号,
  b.总分,
  名次=(select count(1)+1 
        from (select tb1.ID号,isnull(sum(tb2.得分),0) as 总分 from tb1 left join tb2 on tb1.ID号=tb2.ID号 group by tb1.ID号) t 
        where 总分>b.总分
       )
from
 (select tb1.ID号,isnull(sum(tb2.得分),0) as 总分 from tb1 left join tb2 on tb1.ID号=tb2.ID号 group by tb1.ID号) b order by 名次 asc

Answer 6

--> 测试数据: @tb1:
declare @tb1 table (ID号 varchar(3),姓名 varchar(4))
insert into @tb1 
select '001','张三' union all
select '002','李四' union all
select '003','王五' union all
select '004','赵六'
--> 测试数据: @tb2 
declare @tb2  table (ID号 varchar(3),得分 int)
insert into @tb2 
select '002',40 union all
select '003',30 union all
select '002',20 union all
select '001',10 union all
select '001',10 select ID号,max(姓名) as 姓名,isnull(sum(得分),0) as 总分,row_number() over(order by  ID号) as 名次 from
(select a.ID号,a.姓名,b.得分 from @tb1 a left join @tb2 b on a.ID号=b.ID号) c group by ID号--结果
/*
ID号 (无列名) (无列名) 名次
001 张三 20 1
002 李四 60 2
003 王五 30 3
004 赵六 0 4
*/
 

Answer 7

1楼的测试正确！
我如果只显示某个人的总分、名次应将条件添加在什么位置呢？比如：（只显示“ID号=003”的记录）ID号  总分  名次 
003  30     2下面语句如何更改：DECLARE @TA TABLE([ID号] VARCHAR(3), [得分] INT)
INSERT @TA 
SELECT '002', 40 UNION ALL 
SELECT '003', 30 UNION ALL 
SELECT '002', 20 UNION ALL 
SELECT '001', 10 UNION ALL 
SELECT '001', 10DECLARE @TB TABLE([ID号] VARCHAR(3), [姓名] NVARCHAR(2))
INSERT @TB 
SELECT '001', N'张三' UNION ALL 
SELECT '002', N'李四' UNION ALL 
SELECT '003', N'王五' UNION ALL 
SELECT '004', N'赵六'SELECT B.ID号,ISNULL(总分,0) AS 总分,名次=DENSE_RANK() OVER (ORDER BY 总分 DESC)
FROM (
SELECT [ID号],SUM([得分]) AS 总分
FROM @TA
GROUP BY  ID号) A
RIGHT JOIN @TB AS B
ON A.[ID号]=B.[ID号]

Answer 8

后边加条件， where b.id in(select b.id from b where b.name='xx')

Answer 9

加在最后:
WHERE B.ID号='003'

Answer 10

这样得到的记录是：ID号  总分  名次 
003  30    1 而不是
ID号  总分  名次 
003  30    2 

Answer 11

DECLARE @TA TABLE([ID号] VARCHAR(3), [得分] INT)
INSERT @TA 
SELECT '002', 40 UNION ALL 
SELECT '003', 30 UNION ALL 
SELECT '002', 20 UNION ALL 
SELECT '001', 10 UNION ALL 
SELECT '001', 10DECLARE @TB TABLE([ID号] VARCHAR(3), [姓名] NVARCHAR(2))
INSERT @TB 
SELECT '001', N'张三' UNION ALL 
SELECT '002', N'李四' UNION ALL 
SELECT '003', N'王五' UNION ALL 
SELECT '004', N'赵六'--SELECT * FROM @TA
--SELECT * FROM @TBSELECT  T3.总分,排名=IDENTITY(INT,1,1) INTO #TABLE FROM 
(SELECT TOP 4 ISNULL(T.ID号,T1.ID号)ID号,T1.姓名,ISNULL(SUM(得分),0)总分
   FROM @TA  T FULL JOIN @TB T1 ON T.ID号=T1.ID号 GROUP BY T.ID号,T1.ID号,姓名 ORDER  BY 总分 DESC )AS T3SELECT ID号,姓名,TAB.* FROM #TABLE TAB,
(SELECT ISNULL(T.ID号,T1.ID号)ID号,T1.姓名,ISNULL(SUM(得分),0)总分
   FROM @TA  T FULL JOIN @TB T1 ON T.ID号=T1.ID号 GROUP BY T.ID号,T1.ID号,姓名 )AS T4
WHERE  TAB.总分=T4.总分ID号  姓名   总分          排名          
---- ---- ----------- ----------- 
004  赵六   0           4
001  张三   20          3
002  李四   60          1
003  王五   30          2（所影响的行数为 4 行）