比如,有两张表student(班级编号cid,姓名sname,学生sid)和grade_sid(sid,成绩gpoind),一个学生id就对应一张表,比如学生编号是100,学生成绩表就是'grade_100',我现在传一个学生的姓名'xiaoming'过来,求整个班学生的成绩;
我的做法 select sid from student where cid=(select cid from student where sname='xiaoming')
然后遍历上面,把所有的sid,都组装grad_sid表,同一个班有多个同学就有多张表;
最后我循环使用 select * from grad_sid 查找学生成绩请问有没有通过一条sql语句实现,我现在是两条,因为c#不支持多个连接,所以每次都查完断开连接又重新找
我的做法 select sid from student where cid=(select cid from student where sname='xiaoming')
然后遍历上面,把所有的sid,都组装grad_sid表,同一个班有多个同学就有多张表;
最后我循环使用 select * from grad_sid 查找学生成绩请问有没有通过一条sql语句实现,我现在是两条,因为c#不支持多个连接,所以每次都查完断开连接又重新找
@ClassName varchar(20),
@StudentName varchar(20)
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @cid int; IF(@ClassName is NOT NULL)
BEGIN
SELECT @cid = cid FROM Class WHERE ClassName = @ClassName;
END
ELSE IF (@StudentName IS NOT NULL)
BEGIN
SELECT @cid = cid FROM student WHERE sname = @StudentName;
END
ELSE
BEGIN
PRINT 'Please give valid Class or Student name.';
Return;
END IF @cid IS NOT NULL
BEGIN
SELECT *
FROM grade_100
WHERE cid = @cid;
END
ELSE
BEGIN
PRINT 'The Class, ' + @ClassName + ' or ' + @StudentName + ', does not exits.';
Return;
END
GO
(希望这个能帮到你)
GO
-->生成表studentif object_id('student') is not null
drop table student
Go
Create table student([cid] smallint,[sname] nvarchar(2),[sid] nvarchar(3))
Insert into student
Select 1,N'張三',N'001'
Union all Select 1,N'李四',N'002'
Union all Select 1,N'王五',N'003'-->生成表grade_001if object_id('grade_001') is not null
drop table grade_001
Go
Create table grade_001([sid] nvarchar(3),[gpoind] smallint)
Insert into grade_001
Select N'001',93-->生成表grade_002if object_id('grade_002') is not null
drop table grade_002
Go
Create table grade_002([sid] nvarchar(3),[gpoind] smallint)
Insert into grade_002
Select N'002',81-->生成表grade_003if object_id('grade_003') is not null
drop table grade_003
Go
Create table grade_003([sid] nvarchar(3),[gpoind] smallint)
Insert into grade_003
Select N'003',98GoDECLARE @sname NVARCHAR(50),@sql NVARCHAR(MAX)SET @sname='張三'SELECT @sql=ISNULL(@sql+' Union all ','')+'Select * from grade_'+LTRIM(sid) FROM student AS a
WHERE EXISTS(SELECT 1 FROM student AS x
WHERE x.cid=a.cid
AND x.sname=@sname
)Exec ('Select * from ('+@sql+') As t')/*
print :Select
*
from (
Select * from grade_001 Union all
Select * from grade_002 Union all
Select * from grade_003
) As t
Result:
sid gpoind
---- ------
001 93
002 81
003 98
*/