34,837
社区成员
发帖
与我相关
我的任务
分享求一sql语句,要求完成如下功能.
id name num
1001 语文 97.5
1001 数学 87.5
1001 英语 97.5
1002 语文 95
1002 数学 96.5
1003 语文 89.5
1003 英语 91.5
1003 数学 79.44
------------------表中数据----------------------
使用sql查询后得出的结果
学号 语文 数学 英语 总分
1001 97.5 87.5 97.5 282.5
1003 89.5 79.44 91.5 260.44
1001 语文 97.5
1001 数学 87.5
1001 英语 97.5
1002 语文 95
1002 数学 96.5
1003 语文 89.5
1003 英语 91.5
1003 数学 79.44
------------------表中数据----------------------
使用sql查询后得出的结果
学号 语文 数学 英语 总分
1001 97.5 87.5 97.5 282.5
1003 89.5 79.44 91.5 260.44
...全文
请发表友善的回复…
发表回复
Bestkiller 2008-03-18
- 打赏
- 举报
上述SQL语句能否使用连接实现，使用子查询对于效率上存在影响了
dawugui 2008-03-18
- 打赏
- 举报
select t.* from tb t where 工资 = (select max(工资) from tb where 部门 = t.部门)其他方法见下:
--按某一字段分组取最大(小)值所在行的数据
(爱新觉罗.毓华 2007-10-23于浙江杭州)
/*
数据如下：
name val memo
a 2 a2(a的第二个值)
a 1 a1--a的第一个值
a 3 a3:a的第三个值
b 1 b1--b的第一个值
b 3 b3:b的第三个值
b 2 b2b2b2b2
b 4 b4b4
b 5 b5b5b5b5b5
*/
--创建表并插入数据：
create table tb(name varchar(10),val int,memo varchar(20))
insert into tb values('a', 2, 'a2(a的第二个值)')
insert into tb values('a', 1, 'a1--a的第一个值')
insert into tb values('a', 3, 'a3:a的第三个值')
insert into tb values('b', 1, 'b1--b的第一个值')
insert into tb values('b', 3, 'b3:b的第三个值')
insert into tb values('b', 2, 'b2b2b2b2')
insert into tb values('b', 4, 'b4b4')
insert into tb values('b', 5, 'b5b5b5b5b5')
go
--一、按name分组取val最大的值所在行的数据。
--方法1：
select a.* from tb a where val = (select max(val) from tb where name = a.name) order by a.name
--方法2：
select a.* from tb a where not exists(select 1 from tb where name = a.name and val > a.val)
--方法3：
select a.* from tb a,(select name,max(val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name
--方法4：
select a.* from tb a inner join (select name , max(val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name
--方法5
select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val > a.val ) order by a.name
/*
name val memo
---------- ----------- --------------------
a 3 a3:a的第三个值
b 5 b5b5b5b5b5
*/
--二、按name分组取val最小的值所在行的数据。
--方法1：
select a.* from tb a where val = (select min(val) from tb where name = a.name) order by a.name
--方法2：
select a.* from tb a where not exists(select 1 from tb where name = a.name and val < a.val)
--方法3：
select a.* from tb a,(select name,min(val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name
--方法4：
select a.* from tb a inner join (select name , min(val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name
--方法5
select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val < a.val) order by a.name
/*
name val memo
---------- ----------- --------------------
a 1 a1--a的第一个值
b 1 b1--b的第一个值
*/
--三、按name分组取第一次出现的行所在的数据。
select a.* from tb a where val = (select top 1 val from tb where name = a.name) order by a.name
/*
name val memo
---------- ----------- --------------------
a 2 a2(a的第二个值)
b 1 b1--b的第一个值
*/
--四、按name分组随机取一条数据。
select a.* from tb a where val = (select top 1 val from tb where name = a.name order by newid()) order by a.name
/*
name val memo
---------- ----------- --------------------
a 1 a1--a的第一个值
b 5 b5b5b5b5b5
*/
--五、按name分组取最小的两个(N个)val
select a.* from tb a where 2 > (select count(*) from tb where name = a.name and val < a.val ) order by a.name,a.val
select a.* from tb a where val in (select top 2 val from tb where name=a.name order by val) order by a.name,a.val
select a.* from tb a where exists (select count(*) from tb where name = a.name and val < a.val having Count(*) < 2) order by a.name
/*
name val memo
---------- ----------- --------------------
a 1 a1--a的第一个值
a 2 a2(a的第二个值)
b 1 b1--b的第一个值
b 2 b2b2b2b2
*/
--六、按name分组取最大的两个(N个)val
select a.* from tb a where 2 > (select count(*) from tb where name = a.name and val > a.val ) order by a.name,a.val
select a.* from tb a where val in (select top 2 val from tb where name=a.name order by val desc) order by a.name,a.val
select a.* from tb a where exists (select count(*) from tb where name = a.name and val > a.val having Count(*) < 2) order by a.name
/*
name val memo
---------- ----------- --------------------
a 2 a2(a的第二个值)
a 3 a3:a的第三个值
b 4 b4b4
b 5 b5b5b5b5b5
*/
--七，如果整行数据有重复，所有的列都相同。
/*
数据如下：
name val memo
a 2 a2(a的第二个值)
a 1 a1--a的第一个值
a 1 a1--a的第一个值
a 3 a3:a的第三个值
a 3 a3:a的第三个值
b 1 b1--b的第一个值
b 3 b3:b的第三个值
b 2 b2b2b2b2
b 4 b4b4
b 5 b5b5b5b5b5
*/
--在sql server 2000中只能用一个临时表来解决，生成一个自增列，先对val取最大或最小，然后再通过自增列来取数据。
--创建表并插入数据：
create table tb(name varchar(10),val int,memo varchar(20))
insert into tb values('a', 2, 'a2(a的第二个值)')
insert into tb values('a', 1, 'a1--a的第一个值')
insert into tb values('a', 1, 'a1--a的第一个值')
insert into tb values('a', 3, 'a3:a的第三个值')
insert into tb values('a', 3, 'a3:a的第三个值')
insert into tb values('b', 1, 'b1--b的第一个值')
insert into tb values('b', 3, 'b3:b的第三个值')
insert into tb values('b', 2, 'b2b2b2b2')
insert into tb values('b', 4, 'b4b4')
insert into tb values('b', 5, 'b5b5b5b5b5')
go
select * , px = identity(int,1,1) into tmp from tb
select m.name,m.val,m.memo from
(
select t.* from tmp t where val = (select min(val) from tmp where name = t.name)
) m where px = (select min(px) from
(
select t.* from tmp t where val = (select min(val) from tmp where name = t.name)
) n where n.name = m.name)
drop table tb,tmp
/*
name val memo
---------- ----------- --------------------
a 1 a1--a的第一个值
b 1 b1--b的第一个值
(2 行受影响)
*/
--在sql server 2005中可以使用row_number函数，不需要使用临时表。
--创建表并插入数据：
create table tb(name varchar(10),val int,memo varchar(20))
insert into tb values('a', 2, 'a2(a的第二个值)')
insert into tb values('a', 1, 'a1--a的第一个值')
insert into tb values('a', 1, 'a1--a的第一个值')
insert into tb values('a', 3, 'a3:a的第三个值')
insert into tb values('a', 3, 'a3:a的第三个值')
insert into tb values('b', 1, 'b1--b的第一个值')
insert into tb values('b', 3, 'b3:b的第三个值')
insert into tb values('b', 2, 'b2b2b2b2')
insert into tb values('b', 4, 'b4b4')
insert into tb values('b', 5, 'b5b5b5b5b5')
go
select m.name,m.val,m.memo from
(
select * , px = row_number() over(order by name , val) from tb
) m where px = (select min(px) from
(
select * , px = row_number() over(order by name , val) from tb
) n where n.name = m.name)
drop table tb
/*
name val memo
---------- ----------- --------------------
a 1 a1--a的第一个值
b 1 b1--b的第一个值
(2 行受影响)
*/
Bestkiller 2008-03-18
- 打赏
- 举报
再请教一个问题
工号 工资 部门
1001 8966 1
1002 4567 1
1003 12345 1
1004 1231 2
1005 3456 2
1006 352335 2
我怎么查找各个部门工资最高的职工工号了?
工号 工资 部门
1001 8966 1
1002 4567 1
1003 12345 1
1004 1231 2
1005 3456 2
1006 352335 2
我怎么查找各个部门工资最高的职工工号了?
yananguo_1985 2008-03-18
- 打赏
- 举报
select id as 学号 ,
max(case name when '语文' then num else 0 end) 语文,
max(case name when '数学' then num else 0 end) 数学,
max(case name when '英语' then num else 0 end) 英语
from tb
group by id
max(case name when '语文' then num else 0 end) 语文,
max(case name when '数学' then num else 0 end) 数学,
max(case name when '英语' then num else 0 end) 英语
from tb
group by id
dawugui 2008-03-18
- 打赏
- 举报
[Quote=引用 6 楼 Bestkiller 的回复:]
select id ,
max(case name when '语文' then num else 0 end) 语文,
max(case name when '数学' then num else 0 end) 数学,
max(case name when '英语' then num else 0 end) 英语,
sum(num) 总分
from tb
group by id
如果对于表中的课程不确定的情况上述语句就达不到想要的结果了..请问有没有办法解决这种情况了
[/Quote]
固定的用静态SQL.
不固定的用动态SQL.
上面已经分2000和2005写得很清楚了.
select id ,
max(case name when '语文' then num else 0 end) 语文,
max(case name when '数学' then num else 0 end) 数学,
max(case name when '英语' then num else 0 end) 英语,
sum(num) 总分
from tb
group by id
如果对于表中的课程不确定的情况上述语句就达不到想要的结果了..请问有没有办法解决这种情况了
[/Quote]
固定的用静态SQL.
不固定的用动态SQL.
上面已经分2000和2005写得很清楚了.
Bestkiller 2008-03-18
- 打赏
- 举报
select id ,
max(case name when '语文' then num else 0 end) 语文,
max(case name when '数学' then num else 0 end) 数学,
max(case name when '英语' then num else 0 end) 英语,
sum(num) 总分
from tb
group by id
如果对于表中的课程不确定的情况上述语句就达不到想要的结果了..请问有没有办法解决这种情况了
max(case name when '语文' then num else 0 end) 语文,
max(case name when '数学' then num else 0 end) 数学,
max(case name when '英语' then num else 0 end) 英语,
sum(num) 总分
from tb
group by id
如果对于表中的课程不确定的情况上述语句就达不到想要的结果了..请问有没有办法解决这种情况了
dawugui 2008-03-18
- 打赏
- 举报
create table tb (id varchar(10) , name varchar(10) , num decimal(18,2))
insert into tb values('1001', '语文', 97.5 )
insert into tb values('1001', '数学', 87.5 )
insert into tb values('1001', '英语', 97.5 )
insert into tb values('1002', '语文', 95 )
insert into tb values('1002', '数学', 96.5 )
insert into tb values('1003', '语文', 89.5 )
insert into tb values('1003', '英语', 91.5 )
insert into tb values('1003', '数学', 79.44 )
go
--SQL SERVER 2005 静态SQL。
select m.* , n.总分 from
(select * from (select * from tb) a pivot (max(num) for name in (语文,数学,英语)) b) m,
(select id , sum(num) 总分 from tb group by id) n
where m.id = n.id
/*
id 语文 数学 英语 总分
---------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
1001 97.50 87.50 97.50 282.50
1002 95.00 96.50 NULL 191.50
1003 89.50 79.44 91.50 260.44
(3 行受影响)
*/
--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + ',' , '') + name from tb group by name
exec ('select m.* , n.总分 from
(select * from (select * from tb) a pivot (max(num) for name in (' + @sql + ')) b) m ,
(select id , sum(num) 总分 from tb group by id) n
where m.id = n.id')
/*
id 数学 英语 语文 总分
---------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
1001 87.50 97.50 97.50 282.50
1002 96.50 NULL 95.00 191.50
1003 79.44 91.50 89.50 260.44
(3 行受影响)
*/
drop table tbdawugui 2008-03-18
- 打赏
- 举报
create table tb (id varchar(10) , name varchar(10) , num decimal(18,2))
insert into tb values('1001', '语文', 97.5 )
insert into tb values('1001', '数学', 87.5 )
insert into tb values('1001', '英语', 97.5 )
insert into tb values('1002', '语文', 95 )
insert into tb values('1002', '数学', 96.5 )
insert into tb values('1003', '语文', 89.5 )
insert into tb values('1003', '英语', 91.5 )
insert into tb values('1003', '数学', 79.44 )
go
--SQL SERVER 2000 静态SQL,指课程只有语文、数学、英语这三门课程。(以下同)
select id ,
max(case name when '语文' then num else 0 end) 语文,
max(case name when '数学' then num else 0 end) 数学,
max(case name when '英语' then num else 0 end) 英语,
sum(num) 总分
from tb
group by id
/*
id 语文 数学 英语 总分
---------- -------------------- -------------------- -------------------- ----------------------------------------
1001 97.50 87.50 97.50 282.50
1002 95.00 96.50 .00 191.50
1003 89.50 79.44 91.50 260.44
（所影响的行数为 3 行）
*/
--SQL SERVER 2000 动态SQL,指课程不止语文、数学、英语这三门课程。(以下同)
declare @sql varchar(8000)
set @sql = 'select id '
select @sql = @sql + ' , max(case name when ''' + name + ''' then num else 0 end) [' + name + ']'
from (select distinct name from tb) as a
set @sql = @sql + ', sum(num) as 总分 from tb group by id'
exec(@sql)
/*
id 数学 英语 语文 总分
---------- -------------------- -------------------- -------------------- ----------------------------------------
1001 87.50 97.50 97.50 282.50
1002 96.50 .00 95.00 191.50
1003 79.44 91.50 89.50 260.44
*/
drop table tb
pt1314917 2008-03-18
- 打赏
- 举报
将以上“表名”改为自己的表名就可以了`
pt1314917 2008-03-18
- 打赏
- 举报
declare @sql varchar(8000)
set @sql='select id[学号]'
select @sql=@sql+',['+name+']=max(case name when '''+ name +''' then num else 0 end)' from (select distinct name from 表名)a
set @sql=@sql+',sum(num)[总分] from 表名 group by id'
exec(@sql )
dawugui 2008-03-18
- 打赏
- 举报
/*
标题：普通行列转换(version 2.0)
作者：爱新觉罗.毓华
时间：2008-03-09
地点：广东深圳
说明：普通行列转换(version 1.0)仅针对sql server 2000提供静态和动态写法，version 2.0增加sql server 2005的有关写法。
问题：假设有张学生成绩表(tb)如下:
姓名 课程 分数
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
想变成(得到如下结果)：
姓名 语文 数学 物理
---- ---- ---- ----
李四 74 84 94
张三 74 83 93
-------------------
*/
create table tb(姓名 varchar(10) , 课程 varchar(10) , 分数 int)
insert into tb values('张三' , '语文' , 74)
insert into tb values('张三' , '数学' , 83)
insert into tb values('张三' , '物理' , 93)
insert into tb values('李四' , '语文' , 74)
insert into tb values('李四' , '数学' , 84)
insert into tb values('李四' , '物理' , 94)
go
--SQL SERVER 2000 静态SQL,指课程只有语文、数学、物理这三门课程。(以下同)
select 姓名 as 姓名 ,
max(case 课程 when '语文' then 分数 else 0 end) 语文,
max(case 课程 when '数学' then 分数 else 0 end) 数学,
max(case 课程 when '物理' then 分数 else 0 end) 物理
from tb
group by 姓名
--SQL SERVER 2000 动态SQL,指课程不止语文、数学、物理这三门课程。(以下同)
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' from tb group by 姓名'
exec(@sql)
--SQL SERVER 2005 静态SQL。
select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b
--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + '],[' , '') + 课程 from tb group by 课程
set @sql = '[' + @sql + ']'
exec ('select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b')
---------------------------------
/*
问题：在上述结果的基础上加平均分，总分，得到如下结果：
姓名 语文 数学 物理 平均分 总分
---- ---- ---- ---- ------ ----
李四 74 84 94 84.00 252
张三 74 83 93 83.33 250
*/
--SQL SERVER 2000 静态SQL。
select 姓名 姓名,
max(case 课程 when '语文' then 分数 else 0 end) 语文,
max(case 课程 when '数学' then 分数 else 0 end) 数学,
max(case 课程 when '物理' then 分数 else 0 end) 物理,
cast(avg(分数*1.0) as decimal(18,2)) 平均分,
sum(分数) 总分
from tb
group by 姓名
--SQL SERVER 2000 动态SQL。
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名'
exec(@sql)
--SQL SERVER 2005 静态SQL。
select m.* , n.平均分 , n.总分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b) m,
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n
where m.姓名 = n.姓名
--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + ',' , '') + 课程 from tb group by 课程
exec ('select m.* , n.平均分 , n.总分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b) m ,
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n
where m.姓名 = n.姓名')
drop table tb
------------------
------------------
/*
问题：如果上述两表互相换一下：即表结构和数据为：
姓名 语文 数学 物理
张三 74 83 93
李四 74 84 94
想变成(得到如下结果)：
姓名 课程 分数
---- ---- ----
李四 语文 74
李四 数学 84
李四 物理 94
张三 语文 74
张三 数学 83
张三 物理 93
--------------
*/
create table tb(姓名 varchar(10) , 语文 int , 数学 int , 物理 int)
insert into tb values('张三',74,83,93)
insert into tb values('李四',74,84,94)
go
--SQL SERVER 2000 静态SQL。
select * from
(
select 姓名 , 课程 = '语文' , 分数 = 语文 from tb
union all
select 姓名 , 课程 = '数学' , 分数 = 数学 from tb
union all
select 姓名 , 课程 = '物理' , 分数 = 物理 from tb
) t
order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 end
--SQL SERVER 2000 动态SQL。
--调用系统表动态生态。
declare @sql varchar(8000)
select @sql = isnull(@sql + ' union all ' , '' ) + ' select 姓名 , [课程] = ' + quotename(Name , '''') + ' , [分数] = ' + quotename(Name) + ' from tb'
from syscolumns
where name! = N'姓名' and ID = object_id('tb') --表名tb，不包含列名为姓名的其它列
order by colid asc
exec(@sql + ' order by 姓名 ')
--SQL SERVER 2005 动态SQL。
select 姓名 , 课程 , 分数 from tb unpivot (分数 for 课程 in([语文] , [数学] , [物理])) t
--SQL SERVER 2005 动态SQL，同SQL SERVER 2000 动态SQL。
--------------------
/*
问题：在上述的结果上加个平均分，总分，得到如下结果：
姓名 课程 分数
---- ------ ------
李四 语文 74.00
李四 数学 84.00
李四 物理 94.00
李四 平均分 84.00
李四 总分 252.00
张三 语文 74.00
张三 数学 83.00
张三 物理 93.00
张三 平均分 83.33
张三 总分 250.00
------------------
*/
select * from
(
select 姓名 as 姓名 , 课程 = '语文' , 分数 = 语文 from tb
union all
select 姓名 as 姓名 , 课程 = '数学' , 分数 = 数学 from tb
union all
select 姓名 as 姓名 , 课程 = '物理' , 分数 = 物理 from tb
union all
select 姓名 as 姓名 , 课程 = '平均分' , 分数 = cast((语文 + 数学 + 物理)*1.0/3 as decimal(18,2)) from tb
union all
select 姓名 as 姓名 , 课程 = '总分' , 分数 = 语文 + 数学 + 物理 from tb
) t
order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 when '平均分' then 4 when '总分' then 5 end
drop table tb
