两位大哥还是上次的哪个项目
drop table test
create table test(A1 varchar(20),A2 varchar(20),A3 varchar(20),A4 varchar(20),A5 varchar(20),A6 varchar(20))
insert into test select '2006级期末考','01班','20080101','张三','语文','A+'
insert into test select '2006级期末考','01班','20080202','李四','语文','B+'
insert into test select '2006级期末考','01班','20080303','王五','语文','C'
insert into test select '2006级期末考','01班','20080101','张三','数学','C'
insert into test select '2006级期末考','01班','20080202','李四','数学','D'
insert into test select '2006级期末考','01班','20080303','王五','数学','C'
insert into test select '2006级期末考','01班','20080101','张三','政治','E'
insert into test select '2006级期末考','01班','20080202','李四','政治','A+'
insert into test select '2006级期末考','01班','20080303','王五','政治','B'
insert into test select '2006级期末考','01班','20080101','张三','英语','C'
insert into test select '2006级期末考','01班','20080202','李四','英语','A+'
insert into test select '2006级期末考','01班','20080303','王五','英语','B'
go
--测试 declare @sql varchar(8000)
set @sql='select A1,A2,A3,A4'
select @sql=@sql+',max(case when A5='''+A5+''' then A6 else '''' end)['+A5+']' from
(select distinct A5 from test)a
set @sql=@sql+' from test group by A1,A2,A3,A4 order by A1,A2,A3' exec(@sql) 这个是上次liangpei2008帮我解决的问题万分感谢!但这次我想在里面加个子查询然后求和,但他提示"不能对包含聚合或子查询的表达式执行聚合函数。"该如何解决!以下是我写的sql
set @sql2='select count(*) from student where class_id=a.A5'
declare @sql varchar(8000)
set @sql='select A1,A2,A3,A4'
select @sql=@sql+',max(case when A5='''+A5+''' then A6 else '''' end)['+A5+'],sum(case when A5='''+A5+''' then ('+@sql2+') else 0 end) as 年级' from
(select distinct A5 from test)a
set @sql=@sql+' from test as aa group by A1,A2,A3,A4 order by A1,A2,A3' 我的字查询就是想把每个科目的年级排名抓出来,如果是不加sum函数的话就没问题,可是记录是分开的没有合并,但加了sum函数他就提示"不能对包含聚合或子查询的表达式执行聚合函数。"麻烦两位大哥帮我看看,小弟万分感谢!!!
drop table test
create table test(A1 varchar(20),A2 varchar(20),A3 varchar(20),A4 varchar(20),A5 varchar(20),A6 varchar(20))
insert into test select '2006级期末考','01班','20080101','张三','语文','A+'
insert into test select '2006级期末考','01班','20080202','李四','语文','B+'
insert into test select '2006级期末考','01班','20080303','王五','语文','C'
insert into test select '2006级期末考','01班','20080101','张三','数学','C'
insert into test select '2006级期末考','01班','20080202','李四','数学','D'
insert into test select '2006级期末考','01班','20080303','王五','数学','C'
insert into test select '2006级期末考','01班','20080101','张三','政治','E'
insert into test select '2006级期末考','01班','20080202','李四','政治','A+'
insert into test select '2006级期末考','01班','20080303','王五','政治','B'
insert into test select '2006级期末考','01班','20080101','张三','英语','C'
insert into test select '2006级期末考','01班','20080202','李四','英语','A+'
insert into test select '2006级期末考','01班','20080303','王五','英语','B'
go
--测试 declare @sql varchar(8000)
set @sql='select A1,A2,A3,A4'
select @sql=@sql+',max(case when A5='''+A5+''' then A6 else '''' end)['+A5+']' from
(select distinct A5 from test)a
set @sql=@sql+' from test group by A1,A2,A3,A4 order by A1,A2,A3' exec(@sql) 这个是上次liangpei2008帮我解决的问题万分感谢!但这次我想在里面加个子查询然后求和,但他提示"不能对包含聚合或子查询的表达式执行聚合函数。"该如何解决!以下是我写的sql
set @sql2='select count(*) from student where class_id=a.A5'
declare @sql varchar(8000)
set @sql='select A1,A2,A3,A4'
select @sql=@sql+',max(case when A5='''+A5+''' then A6 else '''' end)['+A5+'],sum(case when A5='''+A5+''' then ('+@sql2+') else 0 end) as 年级' from
(select distinct A5 from test)a
set @sql=@sql+' from test as aa group by A1,A2,A3,A4 order by A1,A2,A3' 我的字查询就是想把每个科目的年级排名抓出来,如果是不加sum函数的话就没问题,可是记录是分开的没有合并,但加了sum函数他就提示"不能对包含聚合或子查询的表达式执行聚合函数。"麻烦两位大哥帮我看看,小弟万分感谢!!!
解决方案 »
- 请问这个该如何在WebBrowser中调用?
- 一个关于字符串ASCII码转换为二进制的程序,看看我哪个地方错了【50分送上】
- -=高分求點代碼=-用返回的數據集生成xxx.db文件
- 為何我用ADOConnection連接數據庫總是提示連不上?
- 请问:在web页上用active form能否做一个exe文件来向数据库中写入用户输入的信息?(急,在线等待)
- 谁帮我做出下面程序,我将我所有分数全都赠与
- Lparam(str) 怎么转化成字符串
- 怎么做个程序,把txt文件里的内容导入SQL数据库中????
- 如何将QUERY1查询获得的记录并进行相关操作后,将修改数据写入数据表中。
- 如何在delphi中自应全局变量,并在哪儿赋初值。
- delphi运行时有这样的提示
- sql 数据库 日期时间的转换
关键是子查询是根据主表学科子段相关联查出来的呀!
以下是用你的饿方法但是没有用到sum函数得到的数据
考试项目 班级 姓名 化学 班级排名 数学 班级排名 英语 班级排名 2006级段考 2008级04班 钟晓宇 86.0(B+) 145
2006级段考 2008级04班 钟晓宇 81(B+) 56
2006级段考 2008级04班 钟晓宇 82(C+) 25
班级排名是根据子查询得出来的,但不能有sum函数进行group by 所以分开变成了三条记录,如何把他们弄成一条数据呢
set @sql2='select count(*) from student where class_id=a.A5'
declare @sql varchar(8000)
set @sql='select A1,A2,A3,A4'
select @sql=@sql+',max(case when A5='''+A5+''' then A6 else '''' end)['+A5+'],max(case when A5='''+A5+''' then ('+@sql2+') else 0 end) as 年级' from
(select distinct A5 from test)a
set @sql=@sql+' from test as aa group by A1,A2,A3,A4 order by A1,A2,A3' 但他就会提示"不能对包含聚合或子查询的表达式执行聚合函数。"也就是说max,或sum里不能包含子查询!你可以随便用一条子查询来看看,但是要包含在max或sum函数里!麻烦你了谢谢!
呵,这位大哥,我把库结构和存储过程都放到这个包里了,麻烦帮我测试一下,小弟万分感谢!!!
http://221.7.193.200/test/1.rar里面有个库文件随便建立个库直接还原就可以了,还有个txt文件是我写的存储过程还原数据库后可直接运行!呵,其实我想要的效果就是在每个学科后面列出他在年级和班级的排名!!麻烦你了,jinjazz 大哥!!!
insert into #t
select student_cjbb.student_cjbb_name,student_cjbb.id,
class_name_id.class_name,class_name_id.id,student_id.student_id,
student_id.student_name,class_id.class_name as class_name2,
student_achievement.class_id,
convert(VARCHAR,student_achievement.achievement)+'('+rtrim(student_achievement2.name)+')' as name3 ,
(select isnull(count(*),0) from student_achievement a
where a.achievement>student_achievement.achievement
and a.class_id=student_achievement.class_id
and a.student_cjbb_id=student_achievement.student_cjbb_id)+1
from student_achievement left join
student_achievement2
on student_achievement.student_cjbb_id= student_achievement2.student_cjbb_id
and student_achievement.class_id = student_achievement2.class_id
and student_achievement.student_id = student_achievement2.student_id
left join student_cjbb on student_achievement.student_cjbb_id = student_cjbb.id
left join student_id on student_achievement.student_id = student_id.student_id
left join class_id on student_achievement.class_id = class_id.id,class_name_id where student_id.class_id=class_name_id.id
and student_achievement.student_id=533
declare @sql varchar(8000)
set @sql='select A1 as 考试项目,A2 as 班级,A3 as 学号,A4 as 姓名'
select @sql=@sql+',max(case when A5='''+A5+''' then A6 else '''' end) ['+A5+'],
max(case when A5='''+A5+''' then A7 else '''' end) as 年级' from
(select distinct A5 from #t )a
set @sql=@sql+' from #t as a group by A1,A1X,A2,A2X,A3,A4 order by A1,A2,A3,A4'
exec(@sql)
drop table #t
/*
2006级段考 2008级04班 533 钟晓宇 86.0(B+) 145 81.0(B) 334 0.0(E) 722 91.0(A) 86 348.0(C) 534
2006级期末考 2008级04班 533 钟晓宇 81.0(B+) 334 0.0(E) 722 86.0(B+) 145 91.0(A) 86 348.0(C) 533
*/
set @banji='select isnull(count(*),0)+1 from student_achievement,student_id,class_name_id where student_achievement.student_id = student_id.student_id and student_id.class_id =class_name_id.id and student_cjbb_id=a.A1X and class_name_id.id =a.A2X and student_achievement.class_id =a.A5X and student_achievement.achievement >(select achievement from student_achievement where student_cjbb_id=a.A1X and student_id='''+@student_id+''' and class_id =a.A5X)'班级排名也做到里面,麻烦您了!呵,可能你写的sql太高深了,小弟一时还没办法理解得过来,但时间比较紧还得麻烦大哥费费力,小弟万分感谢
在我给你的txt文件里有个@banji变量,这个就是班级的拍名,方法也是和年级排名一样,把班级排名放到年纪排名前面就可以了!小弟感激不尽,这位大哥能不能留个Q号给小弟呢?呵,你的sql太厉害了,小弟以后想跟你学学东西!!
提一点小技巧:
你可以拼接完毕后,用Print @banji来看一下拼接好的语句;便于你的理解!
修改动态SQL前,你首先要把静态的语句总结成规律,再处理动态拼接!很显然:
LZ一直都没有理解SQL语句,有一点小需求就让别人替你写,对你也没什么好处!