又碰到难题了？积分的计算排名

Question

数据----A表 
create table A
(
Id varchar(10),
UserName nvarchar(100)
) 
insert into A(Id,UserName)
(
select '1001','张三' union
select '1002','李四', union
select '1003','王五', union
select '1004','马六', union
select '1005','钱七', union
select '1006','赵八' 
)
--B表
create table B
(
Id varchar(10),
UserId varchar(10),
JF1 int,
JF2 int,
JF3 int
) 
insert into B(Id,UserId,JF1,JF2,JF3)
(
select '1100','1001',0,3,5 union
select '1101','1001',2,3,5 union
select '1102','1001',5,5,5 union
select '1103','1002',0,3,5 union
select '1005','1002',0,3,5 union
) 
计算积分排行榜：
积分：为B表中 sum(JF1) +sum(JF2) +sum(JF3)  where B userid=A.id 
 

Answer 1

----A表 
IF OBJECT_ID('A') IS NOT NULL DROP TABLE A
IF OBJECT_ID('B') IS NOT NULL DROP TABLE B
GO
create table A
(
Id varchar(10),
UserName nvarchar(100)
) 
insert into A(Id,UserName)
(
select '1001','张三' union
select '1002','李四' union
select '1003','王五' union
select '1004','马六' union
select '1005','钱七' union
select '1006','赵八' 
)
--B表
create table B
(
Id varchar(10),
UserId varchar(10),
JF1 int,
JF2 int,
JF3 int
) 
insert into B(Id,UserId,JF1,JF2,JF3)select '1100','1001',0,3,5 union
select '1101','1001',2,3,5 union
select '1102','1001',5,5,5 union
select '1103','1002',0,3,5 union
select '1005','1002',0,3,5 
SELECT UserId ,A.UserName ,SUM(JF1)+SUM(JF2)+SUM(JF3)
FROM B
INNER JOIN A ON B.UserId=A.ID
GROUP BY UserId ,A.UserName 
/*
1001 张三 33
1002 李四 16
*/

Answer 2

----A表 
create table A
(
Id varchar(10),
UserName nvarchar(100)
) 
insert into A(Id,UserName)
(
select '1001','张三' union
select '1002','李四' union
select '1003','王五' union
select '1004','马六' union
select '1005','钱七' union
select '1006','赵八' 
)
--B表
create table B
(
Id varchar(10),
UserId varchar(10),
JF1 int,
JF2 int,
JF3 int
) 
insert into B(Id,UserId,JF1,JF2,JF3)
(
select '1100','1001',0,3,5 union
select '1101','1001',2,3,5 union
select '1102','1001',5,5,5 union
select '1103','1002',0,3,5 union
select '1005','1002',0,3,5 
)select a.UserName,score=sum(JF1+JF2+JF3)from a,b where a.Id=b.UserId
group by a.UserName
order by score desc
/*
UserName                                                                                             score
---------------------------------------------------------------------------------------------------- -----------
张三                                                                                                   33
李四                                                                                                   16(2 行受影响)
*/

Answer 3

SELECT UserId ,
       A.UserName ,
       SUM(JF1)+SUM(JF2)+SUM(JF3)
FROM B,A 
WHERE B.UserId=A.ID
GROUP BY UserId ,A.UserName 

Answer 4

create table A
(
Id varchar(10),
UserName nvarchar(100)
) 
insert into A(Id,UserName)
select '1001','张三' union all 
select '1002','李四'union all
select '1003','王五' union all
select '1004','马六' union all
select '1005','钱七'union all
select '1006','赵八' --B表
create table B
(
Id varchar(10),
UserId varchar(10),
JF1 int,
JF2 int,
JF3 int
) 
insert into B(Id,UserId,JF1,JF2,JF3)
select '1100','1001',0,3,5 union
select '1101','1001',2,3,5 union
select '1102','1001',5,5,5 union
select '1103','1002',0,3,5 union
select '1005','1002',0,3,5 go
select username,dense_rank()over(order by sum_jf) as 排名
from (
select username,jf1+jf2+jf3 as sum_jf
from a join b on a.id=b.userid ) k
order by 排名
/*
username                                                                                             排名
---------------------------------------------------------------------------------------------------- --------------------
李四                                                                                                   1
张三                                                                                                   1
李四                                                                                                   1
张三                                                                                                   2
张三                                                                                                   3*/

Answer 5

select a.id,a.username,
sum(JF1) +sum(JF2) +sum(JF3) 积分,
rank() over(order by sum(JF1)+sum(JF2)+sum(JF3) desc)排名
from a,b
where B.userid=A.id 
group by a.id,a.username

Answer 6

--drop table a
----A表 
create table A
(
Id varchar(10),
UserName nvarchar(100)
) 
insert into A(Id,UserName)select '1001','张三' union
select '1002','李四' union
select '1003','王五' union
select '1004','马六' union
select '1005','钱七' union
select '1006','赵八' --B表
--drop table b
create table B
(
Id varchar(10),
UserId varchar(10),
JF1 int,
JF2 int,
JF3 int
) 
insert into B(Id,UserId,JF1,JF2,JF3)select '1100','1001',0,3,5 union
select '1101','1001',2,3,5 union
select '1102','1001',5,5,5 union
select '1103','1002',0,3,5 union
select '1005','1002',0,3,5 
select * from a ,
(select userid,num,(select count(1) from (select userid,sum(jf1+jf2+jf3) num from b group by userid) a where num<=t.num) rn from 
(select userid,sum(jf1+jf2+jf3) num from b group by userid) t
)
t where a.id=t.userid/*Id         UserName                                                                                             userid     num         rn          
---------- ---------------------------------------------------------------------------------------------------- ---------- ----------- ----------- 
1001       张三                                                                                                   1001       33          2
1002       李四                                                                                                   1002       16          1（所影响的行数为 2 行）

Answer 7

你的测试数据有问题吧
给你换下 create table A
(
Id varchar(10),
UserName nvarchar(100)
) 
insert into A(Id,UserName)
select '1001','张三' union all 
select '1002','李四'union all
select '1003','王五' union all
select '1004','马六' union all
select '1005','钱七'union all
select '1006','赵八' --B表
create table B
(
Id varchar(10),
UserId varchar(10),
JF1 int,
JF2 int,
JF3 int
) 
insert into B(Id,UserId,JF1,JF2,JF3)
select '1100','1001',0,3,5 union
select '1101','1002',2,3,5 union
select '1102','1003',5,5,5 union
select '1103','1004',0,3,5 union
select '1005','1005',0,3,5 
drop table b
go
select username,dense_rank()over(order by sum_jf) as 排名
from (
select username,jf1+jf2+jf3 as sum_jf
from a join b on a.id=b.userid ) k
order by 排名
/*
username                                                                                             排名
---------------------------------------------------------------------------------------------------- --------------------
钱七                                                                                                   1
张三                                                                                                   1
马六                                                                                                   1
李四                                                                                                   2
王五                                                                                                   3
*/

Answer 8

select a.id,a.username,
isnull(sum(JF1) +sum(JF2) +sum(JF3),0) 积分,
rank() over(order by sum(JF1)+sum(JF2)+sum(JF3) desc)排名
from a
left join b on B.userid=A.id 
group by a.id,a.username
/**
id         username             积分          排名
---------- -------------------- ----------- --------------------
1001       张三                   33          1
1002       李四                   16          2
1004       马六                   0           3
1005       钱七                   0           3
1003       王五                   0           3
1006       赵八                   0           3
警告: 聚合或其他 SET 操作消除了空值。(6 行受影响)
**/

Answer 9

漏掉一个条件 不要意思
B 表 附加一个字段 stuats 
当 status=0 时 B表统计的JF1 只能有2条参与 总和

Answer 10

create table B
(
Id varchar(10),
UserId varchar(10),
JF1 int,
JF2 int,
JF3 intcreate table B
(
Id varchar(10),
UserId varchar(10),
JF1 int,
JF2 int,
JF3 int,
Status int
) 
insert into B(Id,UserId,JF1,JF2,JF3)
select '1100','1001',2,3,5,0 union
select '1101','1002',2,3,5,0 union
select '1102','1003',2,5,5,0 union
select '1102','1003',2,6,5,1 union
select '1103','1004',2,3,5,0 union
select '1005','1005',2,3,5,1 
) 

Answer 11

B中的JF1相同
B 表的所有数据参与统计 
但是的 status=0 的时候只能有2条 如果原数据select '1100','1001',2,3,5,0 union
select '1101','1002',2,3,5,0 union
select '1102','1003',2,5,5,0 union
那么只能统计
select '1100','1001',2,3,5,0 union
select '1101','1002',2,3,5,0 union
select '1102','1003',0,5,5,0 union

Answer 12

Id         UserId     JF1         JF2         JF3         Status
---------- ---------- ----------- ----------- ----------- -----------
1005       1005       2           3           5           1
1100       1001       2           3           5           0
1101       1002       2           3           5           0
1102       1003       2           5           5           0
1102       1003       2           6           5           1
1103       1004       2           3           5           0第一个红色的2作为0来统计，第二个红色的二要作为2来统计还是作为0来统计？

Answer 13

为 2
group by UserId 的

Answer 14

本帖最后由 htl258 于 2010-07-09 17:18:11 编辑

  

Answer 15

整理下 create table B
(
Id varchar(10),
UserId varchar(10),
JF1 int,
JF2 int,
JF3 intcreate table B
(
Id varchar(10),
UserId varchar(10),
JF1 int,
JF2 int,
JF3 int,
Status int
)  
insert into B(Id,UserId,JF1,JF2,JF3,Status)
select '1100','1001',2,3,5,0 union
select '1101','1001',2,3,5,0 union
select '1102','1001',2,5,5,0 union
select '1103','1001',2,3,3,1 union
select '1104','1001',2,5,3,1 union
select '1105','1002',2,2,5,0 union
select '1106','1002',2,3,3,0 union
select '1107','1002',2,2,3,1 union
) 
B表 UserId='1001' 积分实际总和为 
2+3+5 + 2+3+5 + 0+5+5 + 2+3+3 +2+5+3

Answer 16

 表 附加一个字段 stuats
当 status=0 时 B表统计的JF1 只能有2条参与 总和只有两条，随机的两条吗？

Answer 17

drop table b
create table B
(
Id varchar(10),
UserId varchar(10),
JF1 int,
JF2 int,
JF3 int,
Status int
)  
insert into B(Id,UserId,JF1,JF2,JF3,Status)
select '1100','1001',2,3,5,0 union
select '1101','1001',2,3,5,0 union
select '1102','1001',2,5,5,0 union
select '1103','1001',2,3,3,1 union
select '1104','1001',2,5,3,1 union
select '1105','1002',2,2,5,0 union
select '1106','1002',2,3,3,0 union
select '1107','1002',2,2,3,1 
 
--难道这样select UserId, (sum(jf1)-(sum(case when status=0 then 1 else 0 end)-2)*2)+
sum(jf2)+sum(jf3) as jf
from B
group by userid
order by jf desc/*
UserId     jf
---------- -----------
1001       48
1002       24(2 行受影响)*/

Answer 18

(sum(jf1)-(sum(case when status=0 then 1 else 0 end)-2)*2)
思路有点对 
结果不对，如果会员写了9篇都是status=0那么 
(sum(jf1)-(sum(case when status=0 then 1 else 0 end)-4)*2)

Answer 19

drop table b
create table B
(
Id varchar(10),
UserId varchar(10),
JF1 int,
JF2 int,
JF3 int,
Status int
)  
insert into B(Id,UserId,JF1,JF2,JF3,Status)
select '1100','1001',2,3,5,0 union
select '1101','1001',2,3,5,0 union
select '1102','1001',2,5,5,0 union
select '1103','1001',2,3,3,1 union
select '1104','1001',2,5,3,1 union
select '1105','1002',2,2,5,0 union
select '1106','1002',2,3,3,0 union
select '1107','1002',2,2,3,1 
 
--难道这样 select UserId, 
(sum(jf1)-case  when (sum(case when status=0 then 1 else 0 end)-5)*2 >0 then 
(sum(case when status=0 then 1 else 0 end)-5)*2 else 0 end)+
sum(jf2)+sum(jf3) as jf
from B
group by userid
order by jf desc
UserId     jf
---------- -----------
1001       50
1002       24(2 行受影响)
终于找到答案了，不过这个语句能不能优化呢
感谢 xys_777 提供的想法
和其他大侠的支持
随后会把积分送上