易截截图软件、单文件、免安装、纯绿色、仅160KB

SQL统计大全收藏版 个人使用

文章来源:IT工程技术网, 全文链接:http://www.systhinker.com/html/81/n-11481.html
1.计算每个人的总成绩并排名
select name,sum(score) as allscore from stuscore group by name order by allscore
2.计算每个人的总成绩并排名
select distinct t1.name,t1.stuid,t2.allscore from stuscore t1,( select stuid,sum(score) as allscore from stuscore group by stuid)t2where t1.stuid=t2.stuidorder by t2.allscore desc
3. 计算每个人单科的最高成绩
select t1.stuid,t1.name,t1.subject,t1.score from stuscore t1,(select stuid,max(score) as maxscore from stuscore group by stuid) t2where t1.stuid=t2.stuid and t1.score=t2.maxscore
4.计算每个人的平均成绩
select distinct t1.stuid,t1.name,t2.avgscore from stuscore t1,(select stuid,avg(score) as avgscore from stuscore group by stuid) t2where t1.stuid=t2.stuid
5.列出各门课程成绩最好的学生
select t1.stuid,t1.name,t1.subject,t2.maxscore from stuscore t1,(select subject,max(score) as maxscore from stuscore group by subject) t2where t1.subject=t2.subject and t1.score=t2.maxscore
6.列出各门课程成绩最好的两位学生
select distinct t1.* from stuscore t1 where t1.id in (select top 2 stuscore.id from stuscore where subject = t1.subject order by score desc) order by t1.subject
7.学号     姓名     语文      数学      英语      总分   平均分
select stuid as 学号,name as 姓名,sum(case when subject='语文' then score else 0 end) as 语文,sum(case when subject='数学' then score else 0 end) as 数学,sum(case when subject='英语' then score else 0 end) as 英语,sum(score) as 总分,(sum(score)/count(*)) as 平均分from stuscoregroup by stuid,name order by 总分desc
8.列出各门课程的平均成绩
select subject,avg(score) as avgscore from stuscoregroup by subject
9.列出数学成绩的排名
declare @tmp table(pm int,name varchar(50),score int,stuid int)insert into @tmp select null,name,score,stuid from stuscore where subject='数学' order by sc


相关文档:

Microsoft SQL Server 2005 JDBC Driver 1.1

Microsoft SQL Server 2005 JDBC Driver
1.1
快速描述
下载 Microsoft SQL Server
2005 JDBC Driver 1.1,这是一种 Type 4 JDBC 驱动程序,可通过 J2EE(Java2 企业版)中的标准 JDBC
应用程序编程接口 (API) 提供数据库链接。
本页内容

速详细信息



统要求



关资源

他用户正在 ......

在Windows2003下安装SQL Server 2000

     在windows2003下面安装SQL Server2000,会提示windows不支持SQL Server2000的版本,安装后,在本机是可以正常使用的,但是无法连接到其他SQL Server2000的服务器,也无法通过其他机器上的SQL Server2000访问,必须安装SQL Server2000sp3或sp4补丁才可以。其实用SQL Server2005就没这么麻烦了。 ......

SQL Group By Top 实现

DECLARE @temp TABLE(
id INT,
[name] VARCHAR(50),
class VARCHAR(50)
)
INSERT INTO @temp
SELECT 1,'a','A'
UNION ALL SELECT 2,'b','C'
UNION ALL SELECT 3,'c','B'
UNION ALL SELECT 4,'d','C'
UNION ALL SELECT 5,'e','B'
UNION ALL SELECT 6,'f','A'
SELECT * from @temp AS _temp WHERE [name] IN
( ......

SQL 2005远程访问的设置

SQL 2005远程访问的设置
2009-02-27 08:13
1、首先打开SQL Server 外围应用配置器,点击“服务和连接的外围应用配置器”,选择数据库→远程服务,点上本地连接和远程连接,再点上同时使用tcp/ip和named pipes
2、打开SQL Server Configuration Manager,SQL Server 2005网络配置,将TCP/IP启用了。
3、一 ......
© 2009 ej38.com All Rights Reserved. 关于E健网联系我们 | 站点地图 | 赣ICP备09004571号