VB sql分数段及相关问题

Question

用数据库的知识做
学号       姓名     英语   数学   物理    化学    VB
200235882   张一山   81     75     63      58     87
200235883   张二山   81     75     63      58     87
200235882   张撒山   81     75     63      58     87
200235893   李的斯   71     87     73      78     97
200236004   王永民   91     58     83      88     67
200236016   李鹏     87     79     93      98     77
200236088   胡萝卜   51     95     69      76     37
200236099   赵来财   61     77     77      87     89
200236113   钱广     78     56     57      76     81
200236124   孙悟空   88     68     89      66     67
200236132   周全     21     75     73      87     57
200236152   吴大庆   96     85     93      67     17
200236182   赵无财   61     77     77      87     89
200235883   张说山   81     75     63      58     87
200235883   张的山   81     75     63      58     87
200235883   张飞山   81     75     63      58     87
200235891   李斯     71     87     73      78     97
200236000   王永民   91     58     83      88     67
200236010   李鹏     87     79     93      98     77
200236088   胡萝卜   51     95     69      45     37
200236099   赵来财   61     77     77      87     89
200236115   钱广     78     56     57      76     81
200236125   孙悟空   88     68     89      66     67
200236134   周全     21     75     73      87     57
200236152   吴大庆   96     85     93      67     17
200236181   赵无财   61     77     77      87     89
统计出分数段范围人数
范围     英语     数学   物理    化学    VB
>=90       3        5      .......
80-90      10       12     ......
....
....
<60       ....     ....    ....然后就是算出每个人的加权平均分，进行排名~~~
谢谢各位大侠了~~我菜鸟在线等

Answer 1

select '[>=90]'范围 ,
        sum(case when 英语>=90 then 1 else 0 end)英语,
        sum(case when 数学>=90 then 1 else 0 end)数学,
        sum(case when 物理>=90 then 1 else 0 end)物理,
        sum(case when 化学>=90 then 1 else 0 end)化学,
        sum(case when VB>=90 then 1 else 0 end)VB
from tb
union all
select '[80-90]'范围 ,
        sum(case when 英语 between 80 and 90 then 1 else 0 end)英语,
        sum(case when 数学 between 80 and 90 then 1 else 0 end)数学,
        sum(case when 物理 between 80 and 90 then 1 else 0 end)物理,
        sum(case when 化学 between 80 and 90 then 1 else 0 end)化学,
        sum(case when VB between 80 and 90 then 1 else 0 end)VB
from tb
....

Answer 2

CREATE TABLE tb(ID int,Num int)
INSERT tb SELECT 1,2
UNION ALL ALL SELECT 6,2
UNION ALL SELECT 7,1
UNION ALL SELECT 8,5
UNION ALL SELECT 9,1
GO--查询的存储过程
CREATE PROC p_Qry
@group VARCHAR(1000)
AS
SET NOCOUNT ON
IF @group LIKE '%[^0-9,]%'
BEGIN
RAISERROR(N'"%s" 中包含非数字数据',1,16,@group)
RETURN
END
--将字符串分拆为分组表
DECLARE @t TABLE(ID int IDENTITY,Groups varchar(10),a int,b int)
DECLARE @i int,@pid varchar(10)
SELECT @i=CHARINDEX(',',@group+',')
 ,@pid=LEFT(@group,@i-1)
 ,@group=STUFF(@group,1,@i,'')+','
 ,@i=CHARINDEX(',',@group)
INSERT @t SELECT 'ID<='+@pid,NULL,@pid
WHILE @i>1
BEGIN
INSERT @t SELECT @pid+'<ID<='+LEFT(@group,@i-1),@pid,LEFT(@group,@i-1)
SELECT @pid=LEFT(@group,@i-1)
,@group=STUFF(@group,1,@i,'')
,@i=CHARINDEX(',',@group)
END
INSERT @t SELECT 'ID>'+@pid,@pid,NULL--根据分组表统计
SELECT b.Groups,Num=ISNULL(SUM(a.Num),0)
FROM tb a RIGHT JOIN @t b 
ON (a.ID<=b.b OR b.b IS NULL)
AND(a.ID>b.a OR b.a IS NULL)
GROUP BY b.ID,b.Groups
ORDER BY b.ID
GO--调用存储过程进行查询
EXEC p_Qry '2,3,6'
/*--测试结果
Groups     Num         
---------- ----------- 
ID<=2      5
2<ID<=3    2
3<ID<=6    16
ID>6       7
--*/

Answer 3

---------------------------------------------
--> Author : js_szy
--> Target : ★★★
--> Date   : 2009-12-16 12:11:24
--> Version: SQL Server 2005
---------------------------------------------
    
--> 测试数据: @tb
declare @tb table (学号 int,姓名 varchar(6),英语 int,数学 int,物理 int,化学 int,VB int)
insert into @tb
select 200235882,'张一山',81,75,63,58,87 union all
select 200235883,'张二山',81,75,63,58,87 union all
select 200235882,'张撒山',81,75,63,58,87 union all
select 200235893,'李的斯',71,87,73,78,97 union all
select 200236004,'王永民',91,58,83,88,67 union all
select 200236016,'李鹏',87,79,93,98,77 union all
select 200236088,'胡萝卜',51,95,69,76,37 union all
select 200236099,'赵来财',61,77,77,87,89 union all
select 200236113,'钱广',78,56,57,76,81 union all
select 200236124,'孙悟空',88,68,89,66,67 union all
select 200236132,'周全',21,75,73,87,57 union all
select 200236152,'吴大庆',96,85,93,67,17 union all
select 200236182,'赵无财',61,77,77,87,89 union all
select 200235883,'张说山',81,75,63,58,87 union all
select 200235883,'张的山',81,75,63,58,87 union all
select 200235883,'张飞山',81,75,63,58,87 union all
select 200235891,'李斯',71,87,73,78,97 union all
select 200236000,'王永民',91,58,83,88,67 union all
select 200236010,'李鹏',87,79,93,98,77 union all
select 200236088,'胡萝卜',51,95,69,45,37 union all
select 200236099,'赵来财',61,77,77,87,89 union all
select 200236115,'钱广',78,56,57,76,81 union all
select 200236125,'孙悟空',88,68,89,66,67 union all
select 200236134,'周全',21,75,73,87,57 union all
select 200236152,'吴大庆',96,85,93,67,17 union all
select 200236181,'赵无财',61,77,77,87,89select 
范围=case when 英语>=90 then '[>=90]'
  when 英语>=80 and 英语<90 then '[80-90]'
  when 英语>=70 and 英语<80 then '[70-80]'
  when 英语>=60 and 英语<70 then '[60-70]'
  when 英语<60 then '[<60]' end ,英语=count(*)
from @tb
group by case when 英语>=90 then '[>=90]'
  when 英语>=80 and 英语<90 then '[80-90]'
  when 英语>=70 and 英语<80 then '[70-80]'
  when 英语>=60 and 英语<70 then '[60-70]'
  when 英语<60 then '[<60]' end
--....其他的类似
范围      英语
------- -----------
[<60]   4
[>=90]  4
[60-70] 4
[70-80] 4
[80-90] 10(5 行受影响)

Answer 4

if object_id('[tb]') is not null drop table [tb]
go
create table [tb] (学号 int,姓名 nvarchar(6),英语 int,数学 int,物理 int,化学 int,VB int)
insert into [tb]
select 200235882,'张一山',81,75,63,58,87 union all
select 200235883,'张二山',81,75,63,58,87 union all
select 200235882,'张撒山',81,75,63,58,87 union all
select 200235893,'李的斯',71,87,73,78,97 union all
select 200236004,'王永民',91,58,83,88,67 union all
select 200236016,'李鹏',87,79,93,98,77 union all
select 200236088,'胡萝卜',51,95,69,76,37 union all
select 200236099,'赵来财',61,77,77,87,89 union all
select 200236113,'钱广',78,56,57,76,81 union all
select 200236124,'孙悟空',88,68,89,66,67 union all
select 200236132,'周全',21,75,73,87,57 union all
select 200236152,'吴大庆',96,85,93,67,17 union all
select 200236182,'赵无财',61,77,77,87,89 union all
select 200235883,'张说山',81,75,63,58,87 union all
select 200235883,'张的山',81,75,63,58,87 union all
select 200235883,'张飞山',81,75,63,58,87 union all
select 200235891,'李斯',71,87,73,78,97 union all
select 200236000,'王永民',91,58,83,88,67 union all
select 200236010,'李鹏',87,79,93,98,77 union all
select 200236088,'胡萝卜',51,95,69,45,37 union all
select 200236099,'赵来财',61,77,77,87,89 union all
select 200236115,'钱广',78,56,57,76,81 union all
select 200236125,'孙悟空',88,68,89,66,67 union all
select 200236134,'周全',21,75,73,87,57 union all
select 200236152,'吴大庆',96,85,93,67,17 union all
select 200236181,'赵无财',61,77,77,87,89
select '[>=90]'范围 ,
        sum(case when 英语>=90 then 1 else 0 end)英语,
        sum(case when 数学>=90 then 1 else 0 end)数学,
        sum(case when 物理>=90 then 1 else 0 end)物理,
        sum(case when 化学>=90 then 1 else 0 end)化学,
        sum(case when VB>=90 then 1 else 0 end)VB
from tb
union all
select '[80-90]'范围 ,
        sum(case when 英语 between 80 and 90 then 1 else 0 end)英语,
        sum(case when 数学 between 80 and 90 then 1 else 0 end)数学,
        sum(case when 物理 between 80 and 90 then 1 else 0 end)物理,
        sum(case when 化学 between 80 and 90 then 1 else 0 end)化学,
        sum(case when VB between 80 and 90 then 1 else 0 end)VB
from tb
/*
范围      英语          数学          物理          化学          VB
------- ----------- ----------- ----------- ----------- -----------
[>=90]  4           2           4           2           2
[80-90] 10          4           4           8           12(2 個資料列受到影響)
*/

Answer 5

高效寫法需要用輔助表實現以2005為例use Tempdb
go
--> --> 
 
if not object_id(N'Tempdb..#1') is null
drop table #1
Go
Create table #1([学号] int,[姓名] nvarchar(3),[英语] int,[数学] int,[物理] int,[化学] int,[VB] int)
Insert #1
select 200235882,N'张一山',81,75,63,58,87 union all
select 200235883,N'张二山',81,75,63,58,87 union all
select 200235882,N'张撒山',81,75,63,58,87 union all
select 200235893,N'李的斯',71,87,73,78,97 union all
select 200236004,N'王永民',91,58,83,88,67 union all
select 200236016,N'李鹏',87,79,93,98,77 union all
select 200236088,N'胡萝卜',51,95,69,76,37 union all
select 200236099,N'赵来财',61,77,77,87,89 union all
select 200236113,N'钱广',78,56,57,76,81 union all
select 200236124,N'孙悟空',88,68,89,66,67 union all
select 200236132,N'周全',21,75,73,87,57 union all
select 200236152,N'吴大庆',96,85,93,67,17 union all
select 200236182,N'赵无财',61,77,77,87,89 union all
select 200235883,N'张说山',81,75,63,58,87 union all
select 200235883,N'张的山',81,75,63,58,87 union all
select 200235883,N'张飞山',81,75,63,58,87 union all
select 200235891,N'李斯',71,87,73,78,97 union all
select 200236000,N'王永民',91,58,83,88,67 union all
select 200236010,N'李鹏',87,79,93,98,77 union all
select 200236088,N'胡萝卜',51,95,69,45,37 union all
select 200236099,N'赵来财',61,77,77,87,89 union all
select 200236115,N'钱广',78,56,57,76,81 union all
select 200236125,N'孙悟空',88,68,89,66,67 union all
select 200236134,N'周全',21,75,73,87,57 union all
select 200236152,N'吴大庆',96,85,93,67,17 union all
select 200236181,N'赵无财',61,77,77,87,89;with Cte
as
(select 90 as Start,101 as [End],'>=90' as Area
union all 
select 80 ,90,'80-90' as Area
union all 
select 70 ,80,'70-80' as Area
union all
select 60 ,70,'60-70' as Area
union all
select 0 ,60,'<60' as Area)select 
a.Area as 範圍,
sum(case when [英语]>=a.Start and [英语]<a.[End] then 1 else 0 end) as [英语],
sum(case when [数学]>=a.Start and [数学]<a.[End] then 1 else 0 end) as [数学],
sum(case when [物理]>=a.Start and [物理]<a.[End] then 1 else 0 end) as [物理],
sum(case when [化学]>=a.Start and [化学]<a.[End] then 1 else 0 end) as [化学],
sum(case when [VB]>=a.Start and [VB]<a.[End] then 1 else 0 end) as [VB]
from Cte a
left join #1 b on 1=1
group by a.Area

Answer 6

我的写的那个数据已经在ACCESS文件里了~~是想从文件读取，然后统计分数段人数
因为没学这么深，只是尝试下，所以请大虾把第一句可以告诉我什么意思么~~书上没有~~还有那个加权平均分和排名，怎么弄呢？

Answer 7

加權平均要自己寫函數
可以參考這個
http://blog.csdn.net/wufeng4552/archive/2009/10/16/4681510.aspx