sql怎么统计某一课程的各分数段的人数？（在php程序里）

$cid = $_GET['id']; 

回复内容：

除以10后拿来group by之后再count一次aggregate起来赶紧把sql做成动态语言吧，真是的– 我是菜鸟，随便写写···
DECLARE @grade TABLE(
[id] [int] IDENTITY(1,1) NOT NULL,
[cId] [int] NOT NULL,
[sId] [int] NOT NULL,
[sgrade] [int] NOT NULL
);

INSERT INTO @grade(cId,sId,sgrade)VALUES(1,0,0);
INSERT INTO @grade(cId,sId,sgrade)VALUES(1,1,3);
INSERT INTO @grade(cId,sId,sgrade)VALUES(1,2,6);
INSERT INTO @grade(cId,sId,sgrade)VALUES(1,3,9);
INSERT INTO @grade(cId,sId,sgrade)VALUES(1,4,12);
INSERT INTO @grade(cId,sId,sgrade)VALUES(1,5,15);
INSERT INTO @grade(cId,sId,sgrade)VALUES(1,6,18);
INSERT INTO @grade(cId,sId,sgrade)VALUES(1,7,21);
INSERT INTO @grade(cId,sId,sgrade)VALUES(1,8,24);
INSERT INTO @grade(cId,sId,sgrade)VALUES(1,9,27);
INSERT INTO @grade(cId,sId,sgrade)VALUES(1,10,30);
INSERT INTO @grade(cId,sId,sgrade)VALUES(1,11,33);
INSERT INTO @grade(cId,sId,sgrade)VALUES(1,12,36);
INSERT INTO @grade(cId,sId,sgrade)VALUES(1,13,39);
INSERT INTO @grade(cId,sId,sgrade)VALUES(1,14,42);
INSERT INTO @grade(cId,sId,sgrade)VALUES(1,15,45);
INSERT INTO @grade(cId,sId,sgrade)VALUES(1,16,48);
INSERT INTO @grade(cId,sId,sgrade)VALUES(1,17,51);
INSERT INTO @grade(cId,sId,sgrade)VALUES(1,18,54);
INSERT INTO @grade(cId,sId,sgrade)VALUES(1,19,57);
INSERT INTO @grade(cId,sId,sgrade)VALUES(1,20,60);
INSERT INTO @grade(cId,sId,sgrade)VALUES(1,21,63);
INSERT INTO @grade(cId,sId,sgrade)VALUES(1,22,66);
INSERT INTO @grade(cId,sId,sgrade)VALUES(1,23,69);
INSERT INTO @grade(cId,sId,sgrade)VALUES(1,24,72);
INSERT INTO @grade(cId,sId,sgrade)VALUES(1,25,75);
INSERT INTO @grade(cId,sId,sgrade)VALUES(1,26,78);
INSERT INTO @grade(cId,sId,sgrade)VALUES(1,27,81);
INSERT INTO @grade(cId,sId,sgrade)VALUES(1,28,84);
INSERT INTO @grade(cId,sId,sgrade)VALUES(1,29,87);
INSERT INTO @grade(cId,sId,sgrade)VALUES(1,30,90);
INSERT INTO @grade(cId,sId,sgrade)VALUES(1,31,93);
INSERT INTO @grade(cId,sId,sgrade)VALUES(1,32,96);
INSERT INTO @grade(cId,sId,sgrade)VALUES(1,33,99);
INSERT INTO @grade(cId,sId,sgrade)VALUES(1,34,100);

SELECT COUNT(sId) [Count],sgrade/10*10 lowersgrade,sgrade/10*10+9 uppersgrade
FROM @grade cs WHERE cs.cId=1
group By sgrade/10;

SELECT COUNT(1) [Count],st*10 lowersgrade,st*10+9 uppe本文来源gaodai#ma#com搞@@代~&码*网/搞gaodaima代码rsgrade FROM
(SELECT sId,sgrade/10 st
FROM @grade cs WHERE cs.cId=1) t
GROUP BY t.st;

GO用sql计算的话，我不知道有没有frequency之类的函数。我通常都用余数来算，比如分组的组距是7，那么用分数除以7求余数，然后再用分数-余数/7就得到了分组的组号了。

喜欢 (0)赏

[搞代码]

分享 (0)