sqlserver选课人数统计 20C

7个回答

Course表中设置Student为外键，比如学生A选择课程1，那么Course表下就产生一条数据记录(学生A, ‘课程1’)；

（学生B，‘课程2’），（学生C，‘课程3’），（学生D，‘课程3’），
（学生E，‘课程4’），（学生F，‘课程5’），

Course
|- Student
|- CourseName

CREATE DEFINER=`root`@`localhost` PROCEDURE `countSoreP`()
BEGIN
declare less60 smallint default 0; /*x <= 60*/
declare b60a70 smallint default 0;
declare b70a80 smallint default 0;
declare b80a90 smallint default 0;
declare more90 smallint default 0;
declare countcno char(4) default '****';

``````SELECT cno
INTO countcno FROM course
WHERE cname = '离散数学';/*确定course表中有离散数学这门课*/

SELECT COUNT(*)
INTO less60 FROM sc
WHERE cno = '8' AND grade < 60;
SELECT COUNT(*)
INTO b60a70 FROM sc
WHERE cno = '8' AND grade >= 60 AND grade < 70;

SELECT COUNT(*)
INTO b70a80 FROM sc
WHERE cno = '8' AND grade >= 70 AND grade < 80;

SELECT COUNT(*)
INTO b80a90 FROM sc
WHERE cno = '8' AND grade >= 80 AND grade < 90;

SELECT COUNT(*)INTO more90 FROM sc
WHERE cno = '8' AND grade >= 90;
create table countScore( scorestage char(10), number smallint);
insert into countscore values('x<60', less60);
insert into countscore values('60<=x<70', b60a70);
insert into countscore values('70<=x<80', b70a80);
insert into countscore values('80<=x<90', b80a90);
insert into countscore values('x>=90', more90);
``````

END

-- exec Course '','课程1,课程2,课程3,课程4,课程5,课程6,课程7,课程8,课程9,课程10 '

alter proc Course
@StuNo nvarchar(50) , -- 学号，只统计人数可以不用
@CourseList nvarchar(1000) -- 选课字符串，逗号分隔 如 '课程1,课程2,课程3,课程4,课程5,课程6,课程7,课程8,课程9,课程10 '

as
begin

``````declare @CourseName nvarchar(50)

while len(@CourseList)>0
begin

if CHARINDEX(',',@CourseList)>0
begin
set @CourseName= SUBSTRING( @CourseList ,0 ,CHARINDEX(',',@CourseList))
update courseTable set num=num+1 where CourseName=@CourseName
set @CourseList= SUBSTRING( @CourseList ,CHARINDEX(',',@CourseList)+1,len(@CourseList))
end
else
begin
set @CourseName = @CourseList
update courseTable set num=num+1 where CourseName=@CourseName
set @CourseList=''
end
end
``````

end

/*

# 没有C币了，穷的很，按要求写了很久，希望采纳！！！！！

*/

--1.创建课程表，表名可以自己修改，编号自增列。

CREATE TABLE 课程
(

PRIMARY KEY ,

DEFAULT 0
);

--2.插入课程表数据

INSERT INTO dbo.课程
( 课程 )
VALUES ( '课程1' )
, ( '课程2' )
, ( '课程3' )
, ( '课程4' )
, ( '课程5' )
, ( '课程6' )
, ( '课程7' )
, ( '课程8' )
, ( '课程9' )
, ( '课程10' );

GO

--3.参入选课课程编号，并更新选课人数。

--参数设置为字符参，已英文逗号","分隔。如：1,2,5

--存储过程如下：
CREATE PROC usp_web_proc_updClass
(
@classIds VARCHAR(1000)
)
AS
BEGIN

``````    DECLARE @CurrentIndex INT;
DECLARE @NextIndex INT;
DECLARE @Separator CHAR(1)= ',';
DECLARE @ReturnText NVARCHAR(MAX);

DECLARE @classList TABLE ( classId INT );

SELECT  @CurrentIndex = 1;
WHILE ( @CurrentIndex <= LEN(@classIds) )
BEGIN
SELECT  @NextIndex = CHARINDEX(@Separator, @classIds,
@CurrentIndex);
IF ( @NextIndex = 0
OR @NextIndex IS NULL
)
SELECT  @NextIndex = LEN(@classIds) + 1;
SELECT  @ReturnText = SUBSTRING(@classIds, @CurrentIndex,
@NextIndex - @CurrentIndex);
INSERT  INTO @classList
( classId )
VALUES  ( @ReturnText );
SELECT  @CurrentIndex = @NextIndex + 1;
END;

--更新选课人数
UPDATE a
SET a.选课人数 = a.选课人数+1
FROM 课程 AS a
INNER JOIN @classList b ON a.编号 = b.classId

END;
``````

GO

--4.执行例子

EXEC dbo.usp_web_proc_updClass @classIds = '1,2,5' -- varchar(1000)

--5.查询结果

SELECT * FROM dbo.课程

create function dbo.f_split
(
@pSourceSql varchar(8000),
@pSeprateStr varchar(10) = ','
)

returns @temp table
(
id int identity(1,1),
)

with encryption
as

begin

declare @i int

``````  set @pSourceSql = rtrim(ltrim(@pSourceSql));

set @i = charindex(@pSeprateStr,@pSourceSql);

while(@i >= 1)
begin
insert @temp values(left(@pSourceSql,@i-1));

set @pSourceSql = substring(@pSourceSql,@i+1,len(@pSourceSql)-@i)
set @i = charindex(@pSeprateStr,@pSourceSql)
end

if(len(@pSourceSql) > 0)
begin
insert @temp values(@pSourceSql);
end;

return
``````

end

update table_name set num = num + 1 where id in (select col from f_split('1,2,3,5',','))

string t = "1,2,3,5";
var k = t.Split(',');
string strparams = "(";
foreach (string temp in k)
{
strparams += "'"+ temp+"',";
}
strparams.TrimEnd(',');
strparams = ")";

``````string strSql = "update table_name set num = num + 1 where id in " + strparams;
``````

1. 利用group by和case语句，从sc表获取所有学生的选课情况（这是一个殿下的行变列的过程）
2. 以1的查询结果作为子查询，利用sum计算各各个课程的报名数