简单的,我知道很简单,:(但是我不会.select * from tb1 where A<60 and A='z' and A='c' --由于A列中既包含数字又包含字符串,查询<时出错,应该咋写?复杂的存储过程,关于成绩查询的成绩表tb1
学号 成绩 教学任务书代号
id stNum stCj stSubID
1 200701 50 00001
.....学籍库 tb2stNum stName BJID.........
200701 小红 001教学任务书表 tb3
教学任务书代号 科目名 班级名称 班级代号
stSubID subName BJName BJID
00001 英语 英语1班 001
00002 数学 英语1班 001
00002 体育 英语1班 001
..........教学任务书表还有很多信息,我这里给出关键的
现在我想通过存储过程比如查询英语1班的教学任务书为00001的的成绩,得到下列表学号 姓名 体育 数学 英语 ......
200701 小红 45 70 50
200702 小名 56 87 98
200703 小白 未选 87 95
.......但是由于涉及到选修课,也就是说没有可能这个班的学生中,有的科目学生并没有成绩,成绩表里也就没记录,那么我希望显示未选高手帮忙一下,非常诚心的请教
学号 成绩 教学任务书代号
id stNum stCj stSubID
1 200701 50 00001
.....学籍库 tb2stNum stName BJID.........
200701 小红 001教学任务书表 tb3
教学任务书代号 科目名 班级名称 班级代号
stSubID subName BJName BJID
00001 英语 英语1班 001
00002 数学 英语1班 001
00002 体育 英语1班 001
..........教学任务书表还有很多信息,我这里给出关键的
现在我想通过存储过程比如查询英语1班的教学任务书为00001的的成绩,得到下列表学号 姓名 体育 数学 英语 ......
200701 小红 45 70 50
200702 小名 56 87 98
200703 小白 未选 87 95
.......但是由于涉及到选修课,也就是说没有可能这个班的学生中,有的科目学生并没有成绩,成绩表里也就没记录,那么我希望显示未选高手帮忙一下,非常诚心的请教
union all
select * from tb1 where A='z'
union all
select * from tb1 where A='c'
insert @t select 2,'z'
insert @t select 3,'b'
insert @t select 4,'100'
insert @t select 5,'50'select *
from @t
where (case when ISNUMERIC(col) = 1 then cast(col as int) else '100' end) < 60
union
select *
from @t
where col = 'z' or col = 'c'/*id col
----------- ----------
1 c
2 z
5 50(所影响的行数为 3 行)
*/
from @t
where (case when ISNUMERIC(col) = 1 then cast(col as int) else 100 end) < 60
union
select *
from @t
where col = 'z' or col = 'c'
你上面这个条件怎么也不对,应该是or吧?
select * from tb1 where ISNUMERIC(A) = 1 and cast(A as int) < 60
union all
select * from tb1 where A='z' or A='c'
--查询所有的
declare @sql varchar(8000)
set @sql = 'select 学号,姓名'
select @sql = @sql + ' , max(case subName when ''' + subName + ''' then stCj else 0 end) [' + subName + ']'
from (select distinct subName from (select tb2.stNum 学号,tb2.stName 姓名 , tb3.subName , tb1.stCj from tb2 , tb3 , tb1 where tb2.BJID = t3.BJID and tb3.stSubID = tb1.stSubID)t) as a
set @sql = @sql + ' from (select tb2.stNum 学号,tb2.stName 姓名 , tb3.subName , tb1.stCj from tb2 , tb3 , tb1 where tb2.BJID = t3.BJID and tb3.stSubID = tb1.stSubID)t group by 学号,姓名'
exec(@sql)
set @sql = 'select 学号,姓名'
select @sql = @sql + ' , max(case subName when ''' + subName + ''' then cast(stCj as varchar) else ''未选'' end) [' + subName + ']'
from (select distinct subName from (select tb2.stNum 学号,tb2.stName 姓名 , tb3.subName , tb1.stCj from tb2 , tb3 , tb1 where tb2.BJID = t3.BJID and tb3.stSubID = tb1.stSubID)t) as a
set @sql = @sql + ' from (select tb2.stNum 学号,tb2.stName 姓名 , tb3.subName , tb1.stCj from tb2 , tb3 , tb1 where tb2.BJID = t3.BJID and tb3.stSubID = tb1.stSubID)t group by 学号,姓名'
exec(@sql)
declare @sql varchar(8000)
set @sql = 'select 学号,姓名'
select @sql = @sql + ' , max(case subName when ''' + subName + ''' then cast(stCj as varchar) else ''未选'' end) [' + subName + ']'
from (select distinct subName from (select tb2.stNum 学号,tb2.stName 姓名 , tb3.subName , tb1.stCj from tb2 , tb3 , tb1 where tb2.BJID = t3.BJID and tb3.stSubID = tb1.stSubID)t) as a
set @sql = @sql + ' from (select tb2.stNum 学号,tb2.stName 姓名 , tb3.subName , tb1.stCj from tb2 , tb3 , tb1 where tb2.BJID = t3.BJID and tb3.stSubID = tb1.stSubID)t group by 学号,姓名'
exec(@sql) --查询英语1班的教学任务书为00001的
declare @sql varchar(8000)
set @sql = 'select 学号,姓名'
select @sql = @sql + ' , max(case subName when ''' + subName + ''' then cast(stCj as varchar) else ''未选'' end) [' + subName + ']'
from (select distinct subName from (select tb2.stNum 学号,tb2.stName 姓名 , tb3.subName , tb1.stCj from tb2 , tb3 , tb1 where tb2.BJID = t3.BJID and tb3.stSubID = tb1.stSubID and tb3.BJName= '英语1班' and tb3.stSubID='00001')t) as a
set @sql = @sql + ' from (select tb2.stNum 学号,tb2.stName 姓名 , tb3.subName , tb1.stCj from tb2 , tb3 , tb1 where tb2.BJID = t3.BJID and tb3.stSubID = tb1.stSubID and tb3.BJName= ''英语1班'' and tb3.stSubID=''00001'')t group by 学号,姓名'
exec(@sql)
另一个象上边的可以
2005里有个pivot关键字可以实现这个功能
动态的列也得拼一下,语句能稍微短一点
set @sql = 'select 学号,姓名'
select @sql = @sql + ' , max(case subName when ''' + subName + ''' then cast(stCj as varchar) else ''未选'' end) [' + subName + ']'
from (select distinct subName from (select tb2.stNum 学号,tb2.stName 姓名 , tb3.subName , tb1.stCj from tb2 , tb3 , tb1 where tb2.BJID = t3.BJID and tb3.stSubID = tb1.stSubID and tb3.BJName= '英语1班' and tb3.stSubID='00001')t) as a
set @sql = @sql + ' from (select tb2.stNum 学号,tb2.stName 姓名 , tb3.subName , tb1.stCj from tb2 , tb3 , tb1 where tb2.BJID = t3.BJID and tb3.stSubID = tb1.stSubID and tb3.BJName= ''英语1班'' and tb3.stSubID=''00001'')t group by 学号,姓名'
exec(@sql)
教学任务书代号 科目名 班级名称 班级代号
stSubID subName BJName BJID
00001 英语 英语1班 001
00002 数学 英语1班 001
00003 体育 英语1班 001
00004 政治 英语1班 001
.......... 说明班本学期开了4门课,分别是英语,数学,体育,政治.而现在需要查询是代号为001的班级所有学生的成绩那么首先从学籍库里先取出这个001班级所有的学生信息学号 姓名 英语 数学 体育 政治
200701 小名 50 80 90 76
200702 小红 50 87 未选 72
200703 小刚 未选 80 64 45
......因为我成绩库的记录是每学生每个科目一条信息,如果改课程为选修课,并且学生没有选这门课,那么这个该学生对应的成绩是
不会插入到数据库的,刚才您给我写的语句查询出来的结果全部是"未选",估计是我表达错误了吧.教学任务书的记录方式是如果一个班这个学期开了10门科,那么则有10条信息来记录这些科目的详细信息.
但是教学任务书的代码是主键,所以不可能相同,我上面是复制弄错了.
哎,昨天晚上没注意,非常不好意思.
insert into tb1 values(1, '200701', 50, '00001' )
create table tb2(stNum varchar(10) , stName varchar(10) , BJID varchar(10) ,)
insert into tb2 values('200701', '小红', '001')
create table tb3(stSubID varchar(10) , subName varchar(10) , BJName varchar(10) , BJID varchar(10) , )
insert into tb3 values('00001', '英语' , '英语1班', '001')
insert into tb3 values('00002', '数学' , '英语1班', '001')
insert into tb3 values('00003', '体育' , '英语1班', '001')
insert into tb3 values('00004', '政治' , '英语1班', '001')
go--查询英语1班的教学任务书为00001的
declare @sql varchar(8000)
set @sql = 'select 学号 , 姓名'
select @sql = @sql + ' , max(case subname when ''' + subname + ''' then stcj else 0 end) [' + subName + ']'
from (select distinct subName from (select tb2.stNum 学号,tb2.stName 姓名 , tb3.subName , tb1.stCj from tb2 , tb3 , tb1 where tb2.BJID = tb3.BJID and tb2.stnum = tb1.stnum and tb2.BJID = '001')t) as a
set @sql = @sql + ' from (select tb2.stNum 学号,tb2.stName 姓名 , tb3.subName , tb1.stCj from tb2 , tb3 , tb1 where tb2.BJID = tb3.BJID and tb2.stnum = tb1.stnum and tb2.BJID = ''001'')t group by 学号,姓名'
exec (@sql)drop table tb1,tb2,tb3 /*
学号 姓名 数学 体育 英语 政治
---------- ---------- ----------- ----------- ----------- -----------
200701 小红 50 50 50 50(1 行受影响)
*/
create table tb1(id int, stNum varchar(10) , stCj int , stSubID varchar(10))
insert into tb1 values(1, '200701', 50, '00001' )
create table tb2(stNum varchar(10) , stName varchar(10) , BJID varchar(10) ,)
insert into tb2 values('200701', '小红', '001')
create table tb3(stSubID varchar(10) , subName varchar(10) , BJName varchar(10) , BJID varchar(10) , )
insert into tb3 values('00001', '英语' , '英语1班', '001')
insert into tb3 values('00002', '数学' , '英语1班', '001')
insert into tb3 values('00003', '体育' , '英语1班', '001')
insert into tb3 values('00004', '政治' , '英语1班', '001')
go--查询英语1班的教学任务书为00001的
declare @sql varchar(8000)
set @sql = 'select 学号 , 姓名'
select @sql = @sql + ' , max(case subname when ''' + subname + ''' then stcj else 0 end) [' + subName + ']'
from (select distinct subName from (select tb2.stNum 学号,tb2.stName 姓名 , tb3.subName , tb1.stCj from tb2 , tb3 , tb1 where tb2.BJID = tb3.BJID and tb2.stnum = tb1.stnum and tb3.stSubID = tb1.stSubID and tb2.BJID = '001')t) as a
set @sql = @sql + ' from (select tb2.stNum 学号,tb2.stName 姓名 , tb3.subName , tb1.stCj from tb2 , tb3 , tb1 where tb2.BJID = tb3.BJID and tb2.stnum = tb1.stnum and tb3.stSubID = tb1.stSubID and tb2.BJID = ''001'')t group by 学号,姓名'
exec (@sql)drop table tb1,tb2,tb3 /*
学号 姓名 英语
---------- ---------- -----------
200701 小红 50(1 行受影响)
*/
create table tb1(id int, stNum varchar(10) , stCj int , stSubID varchar(10))
insert into tb1 values(1, '200701', 50, '00001' )
create table tb2(stNum varchar(10) , stName varchar(10) , BJID varchar(10) ,)
insert into tb2 values('200701', '小红', '001')
create table tb3(stSubID varchar(10) , subName varchar(10) , BJName varchar(10) , BJID varchar(10) , )
insert into tb3 values('00001', '英语' , '英语1班', '001')
insert into tb3 values('00002', '数学' , '英语1班', '001')
insert into tb3 values('00003', '体育' , '英语1班', '001')
insert into tb3 values('00004', '政治' , '英语1班', '001')
go--查询英语1班的教学任务书为00001的
declare @sql varchar(8000)
set @sql = 'select 学号 , 姓名'
select @sql = @sql + ' , max(case subname when ''' + subname + ''' then cast(stcj as varchar) else ''未选'' end) [' + subName + ']'
from (select distinct subName from (select tb2.stNum 学号,tb2.stName 姓名 , tb3.subName , tb1.stCj from tb2 , tb3 , tb1 where tb2.BJID = tb3.BJID and tb2.stnum = tb1.stnum and tb3.stSubID = tb1.stSubID and tb2.BJID = '001')t) as a
set @sql = @sql + ' from (select tb2.stNum 学号,tb2.stName 姓名 , tb3.subName , tb1.stCj from tb2 , tb3 , tb1 where tb2.BJID = tb3.BJID and tb2.stnum = tb1.stnum and tb3.stSubID = tb1.stSubID and tb2.BJID = ''001'')t group by 学号,姓名'
exec (@sql)drop table tb1,tb2,tb3 /*
学号 姓名 英语
---------- ---------- -----------
200701 小红 50(1 行受影响)
*/
select * from tb1 where A <60 and A='z' and A='c' --由于A列中既包含数字又包含字符串,查询 <时出错,应该咋写?
--------------------------------------------------------------------
select * from tb1 where (isnumeric(A)=1 and A<60) or A='Z' or A='C'
create table tb1(id int, stNum varchar(10) , stCj int , stSubID varchar(10))
insert into tb1 values(1, '200701', 50, '00001' )
create table tb2(stNum varchar(10) , stName varchar(10) , BJID varchar(10) ,)
insert into tb2 values('200701', '小红', '001')
create table tb3(stSubID varchar(10) , subName varchar(10) , BJName varchar(10) , BJID varchar(10) , )
insert into tb3 values('00001', '英语' , '英语1班', '001')
insert into tb3 values('00002', '数学' , '英语1班', '001')
insert into tb3 values('00003', '体育' , '英语1班', '001')
insert into tb3 values('00004', '政治' , '英语1班', '001')
go--查询英语1班的教学任务书为00001的
declare @sql varchar(8000)
set @sql = 'select stNum 学号 ,stName 姓名'
select @sql = @sql + ' , max(case subname when ''' + subname + ''' then cast(stcj as varchar) else ''未选'' end) [' + subName + ']'
from (select distinct subName from tb3) as a
set @sql = @sql + ' from (select tb2.stNum,tb2.stName,tb2.BJID,tb1.stCj,tb1.stSubID from tb2 left join tb1 on tb2.stNum = tb1.stNum) a, (select tb3.* from tb3 where BJID = ''001'') b where a.BJID = b.BJID and a.stSubID = b.stSubID group by stNum,stName'
exec (@sql)drop table tb1,tb2,tb3 /*
学号 姓名 数学 体育 英语 政治
---------- ---------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
200701 小红 未选 未选 50 未选(1 行受影响)
*/
insert into tb1 values(1, '200701', 50, '00001' )
create table tb2(stNum varchar(10) , stName varchar(10) , BJID varchar(10) ,)
insert into tb2 values('200701', '小红', '001')
create table tb3(stSubID varchar(10) , subName varchar(10) , BJName varchar(10) , BJID varchar(10) , )
insert into tb3 values('00001', '英语' , '英语1班', '001')
insert into tb3 values('00002', '数学' , '英语1班', '001')
insert into tb3 values('00003', '体育' , '英语1班', '001')
insert into tb3 values('00004', '政治' , '英语1班', '001')
go--查询BJID为'001'的
declare @sql varchar(8000)
set @sql = 'select stNum 学号 ,stName 姓名'
select @sql = @sql + ' , max(case subname when ''' + subname + ''' then cast(stcj as varchar) else ''未选'' end) [' + subName + ']'
from (select distinct subName from tb3) as a
set @sql = @sql + ' from (select tb2.stNum,tb2.stName,tb2.BJID,tb1.stCj,tb1.stSubID from tb2 left join tb1 on tb2.stNum = tb1.stNum) a, (select tb3.* from tb3 where BJID = ''001'') b where a.BJID = b.BJID and a.stSubID = b.stSubID group by stNum,stName'
exec (@sql)drop table tb1,tb2,tb3 /*
学号 姓名 数学 体育 英语 政治
---------- ---------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
200701 小红 未选 未选 50 未选(1 行受影响)
*/
create table tb1(id int, stNum varchar(10) , stCj int , stSubID varchar(10))
insert into tb1 values(1, '200701', 50, '00001' )
create table tb2(stNum varchar(10) , stName varchar(10) , BJID varchar(10) ,)
insert into tb2 values('200701', '小红', '001')
create table tb3(stSubID varchar(10) , subName varchar(10) , BJName varchar(10) , BJID varchar(10) , )
insert into tb3 values('00001', '英语' , '英语1班', '001')
insert into tb3 values('00002', '数学' , '英语1班', '001')
insert into tb3 values('00003', '体育' , '英语1班', '001')
insert into tb3 values('00004', '政治' , '英语1班', '001')
go--查询BJID为'001'的
declare @bjid as varchar(10)
set @bjid = '001'
declare @sql varchar(8000)
set @sql = 'select stNum 学号 ,stName 姓名'
select @sql = @sql + ' , max(case subname when ''' + subname + ''' then cast(stcj as varchar) else ''未选'' end) [' + subName + ']'
from (select distinct subName from tb3 where BJID = @bjid) as a
set @sql = @sql + ' from (select tb2.stNum,tb2.stName,tb2.BJID,tb1.stCj,tb1.stSubID from tb2 left join tb1 on tb2.stNum = tb1.stNum) a, (select tb3.* from tb3 where BJID = ''' + @bjid + ''') b where a.BJID = b.BJID and a.stSubID = b.stSubID group by stNum,stName'
exec (@sql)drop table tb1,tb2,tb3 /*
学号 姓名 数学 体育 英语 政治
---------- ---------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
200701 小红 未选 未选 50 未选(1 行受影响)
*/
insert into tb1 values(1, '200701', 50, '00001' )
create table tb2(stNum varchar(10) , stName varchar(10) , BJID varchar(10) ,)
insert into tb2 values('200701', '小红', '001')
create table tb3(stSubID varchar(10) , subName varchar(10) , BJName varchar(10) , BJID varchar(10) , )
insert into tb3 values('00001', '英语' , '英语1班', '001')
insert into tb3 values('00002', '数学' , '英语1班', '001')
insert into tb3 values('00003', '体育' , '英语1班', '001')
insert into tb3 values('00004', '政治' , '英语1班', '001')
go--查询英语1班的教学任务书为00001的
declare @sql varchar(8000)
set @sql = 'select 学号 , 姓名'
select @sql = @sql + ' , max(case subname when ''' + subname + ''' then stcj else 0 end) [' + subName + ']'
from (select distinct subName from (select tb2.stNum 学号,tb2.stName 姓名 , tb3.subName , tb1.stCj from tb2 , tb3 , tb1 where tb2.BJID = tb3.BJID and tb2.stnum = tb1.stnum and tb2.BJID = '001')t) as a
set @sql = @sql + ' from (select tb2.stNum 学号,tb2.stName 姓名 , tb3.subName , tb1.stCj from tb2 , tb3 , tb1 where tb2.BJID = tb3.BJID and tb2.stnum = tb1.stnum and tb2.BJID = ''001'')t group by 学号,姓名'
exec (@sql)drop table tb1,tb2,tb3
成绩库有该生成绩两门以上就全部变成未选了
create table tb1(id int, stNum varchar(10) , stCj int , stSubID varchar(10))
insert into tb1 values(1, '200701', 50, '00001' )
insert into tb1 values(1, '200701', 60, '00002' )
insert into tb1 values(1, '200701', 80, '00003' )
insert into tb1 values(1, '200701', 95, '00004' )
insert into tb1 values(1, '200702', 54, '00001' )
create table tb2(stNum varchar(10) , stName varchar(10) , BJID varchar(10) ,)
insert into tb2 values('200701', '小红', '001')
insert into tb2 values('200702', '小刚', '001')
create table tb3(stSubID varchar(10) , subName varchar(10) , BJName varchar(10) , BJID varchar(10) , )
insert into tb3 values('00001', '英语' , '英语1班', '001')
insert into tb3 values('00002', '数学' , '英语1班', '001')
insert into tb3 values('00003', '体育' , '英语1班', '001')
insert into tb3 values('00004', '政治' , '英语1班', '001')
go
看来还是要继续麻烦您在看看
--将上述语句总的未选改为null,其他位变,语句成功,难道这是SQL SERVER的BUG?
create table tb1(id int, stNum varchar(10) , stCj int , stSubID varchar(10))
insert into tb1 values(1, '200701', 50, '00001' )
insert into tb1 values(1, '200701', 60, '00002' )
insert into tb1 values(1, '200701', 80, '00003' )
insert into tb1 values(1, '200701', 95, '00004' )
insert into tb1 values(1, '200702', 54, '00001' )
create table tb2(stNum varchar(10) , stName varchar(10) , BJID varchar(10) ,)
insert into tb2 values('200701', '小红', '001')
insert into tb2 values('200702', '小刚', '001')
create table tb3(stSubID varchar(10) , subName varchar(10) , BJName varchar(10) , BJID varchar(10) , )
insert into tb3 values('00001', '英语' , '英语1班', '001')
insert into tb3 values('00002', '数学' , '英语1班', '001')
insert into tb3 values('00003', '体育' , '英语1班', '001')
insert into tb3 values('00004', '政治' , '英语1班', '001')
go
--查询BJID为'001'的
declare @bjid as varchar(10)
set @bjid = '001'
declare @sql varchar(8000)
set @sql = 'select stNum 学号 ,stName 姓名'
select @sql = @sql + ' , max(case subname when ''' + subname + ''' then stcj else null end) [' + subName + ']'
from (select distinct subName from tb3 where BJID = @bjid) as a
set @sql = @sql + ' from (select tb2.stNum,tb2.stName,tb2.BJID,tb1.stCj,tb1.stSubID from tb2 left join tb1 on tb2.stNum = tb1.stNum) a, (select tb3.* from tb3 where BJID = ''' + @bjid + ''') b where a.BJID = b.BJID and a.stSubID = b.stSubID group by stNum,stName'
exec (@sql)drop table tb1,tb2,tb3 /*
学号 姓名 数学 体育 英语 政治
---------- ---------- ----------- ----------- ----------- -----------
200701 小红 60 80 50 95
200702 小刚 NULL NULL 54 NULL(2 行受影响)
*/
create table tb1(id int, stNum varchar(10) , stCj int , stSubID varchar(10))
insert into tb1 values(1, '200701', 50, '00001' )
insert into tb1 values(1, '200701', 60, '00002' )
insert into tb1 values(1, '200701', 80, '00003' )
insert into tb1 values(1, '200701', 95, '00004' )
insert into tb1 values(1, '200702', 54, '00001' )
create table tb2(stNum varchar(10) , stName varchar(10) , BJID varchar(10) ,)
insert into tb2 values('200701', '小红', '001')
insert into tb2 values('200702', '小刚', '001')
create table tb3(stSubID varchar(10) , subName varchar(10) , BJName varchar(10) , BJID varchar(10) , )
insert into tb3 values('00001', '英语' , '英语1班', '001')
insert into tb3 values('00002', '数学' , '英语1班', '001')
insert into tb3 values('00003', '体育' , '英语1班', '001')
insert into tb3 values('00004', '政治' , '英语1班', '001')
go
--查询BJID为'001'的
declare @bjid as varchar(10)
set @bjid = '001'
declare @sql varchar(8000)
set @sql = 'select stNum 学号 ,stName 姓名'
select @sql = @sql + ' , max(case subname when ''' + subname + ''' then cast(stcj as varchar) else '' 未选'' end) [' + subName + ']'
from (select distinct subName from tb3 where BJID = @bjid) as a
set @sql = @sql + ' from (select tb2.stNum,tb2.stName,tb2.BJID,tb1.stCj,tb1.stSubID from tb2 left join tb1 on tb2.stNum = tb1.stNum) a, (select tb3.* from tb3 where BJID = ''' + @bjid + ''') b where a.BJID = b.BJID and a.stSubID = b.stSubID group by stNum,stName'
exec (@sql)drop table tb1,tb2,tb3 /*
学号 姓名 数学 体育 英语 政治
---------- ---------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
200701 小红 60 80 50 95
200702 小刚 未选 未选 54 未选(2 行受影响)
*/
原来前面加个空格就可以解决了,非常感谢,明天上班结账。--
我足足看了十分钟我的代码,未发现有错误.后面只好改用其他的东西试试,结果发现这里出了问题.不知道这算不算SQL SERVER的BUG?