急~~~~~求助SQL汇总语句

lixw888 2004-12-15 05:36:15

小弟近来做一个学生学籍管理，其中有一个关于学生报到领书情况的汇总语句，不会写：（

详细如下：
一个表，STUDENTBOOK表，中有学生ID（StudentId）,班级ID(ClassId)学期（Semester）英语书（YY_Book）,语文（YW_Book）,物理（WL_Book）,化学（HX_Book）等几个字段，当其中的YY_Book，YW_Book，WL_Book，HX_Book为空时，表示此学生没有领取这本书，我想用一条SQL语句统计出一个班某个学期的学生相应的未领取书本的本数，即比如一个班一个学期共有多少本英语书（YY_Book）未领取,多少本语文（YW_Book）未领取,多少本物理（WL_Book）未领取,多少本化学（HX_Book）未领取。

请各位大虾们救救偶~~~~~~~~

...全文

113 点赞收藏 8

写回复

8 条回复

切换为时间正序

当前发帖距今超过3年，不再开放新的回复

发表回复

lixw888 2004-12-16

谢谢大家~~~~~

回复赞

huangsenji 2004-12-15

请问一下，SQL在那里可以写语句啊，我找不到啊，还有在这个论坛那里可以发问题啊，我怎么找不到的啊。

回复赞

vinsonshen 2004-12-15

呵呵，楼上的ok啦~~~你用case语句结合group by进行分组就行了~~

CASE
计算条件列表并返回多个可能结果表达式之一。

CASE 具有两种格式：

简单 CASE 函数将某个表达式与一组简单表达式进行比较以确定结果。

CASE 搜索函数计算一组布尔表达式以确定结果。
两种格式都支持可选的 ELSE 参数。

语法
简单 CASE 函数：

CASE input_expression
WHEN when_expression THEN result_expression
[ ...n ]
[
ELSE else_result_expression
END

CASE 搜索函数：

CASE
WHEN Boolean_expression THEN result_expression
[ ...n ]
[
ELSE else_result_expression
END

USE pubs
GO
SELECT Category =
CASE type
WHEN 'popular_comp' THEN 'Popular Computing'
WHEN 'mod_cook' THEN 'Modern Cooking'
WHEN 'business' THEN 'Business'
WHEN 'psychology' THEN 'Psychology'
WHEN 'trad_cook' THEN 'Traditional Cooking'
ELSE 'Not yet categorized'
END,
CAST(title AS varchar(25)) AS 'Shortened Title',
price AS Price
FROM titles
WHERE price IS NOT NULL
ORDER BY type, price
COMPUTE AVG(price) BY type
GO

回复赞

goregrypeck 2004-12-15

友情顶
学习

回复赞

Frewin 2004-12-15

select classid,semester,sum(case when yy_book is null then 1 else 0 end)
,sum(case when yw_book is null then 1 else 0 end)
,sum(case when wl_book is null then 1 else 0 end)
,sum(case when hx_book is null then 1 else 0 end)
from studentbook
group by classid,semester

Or
Select a.classid,a.semester,(Select Count(*) From studentbook Where yy_book IS null And classid=a.classid And semester =a.semester ) As yy_book,
(Select Count(*) From studentbook Where yw_book IS null And classid=a.classid And semester =a.semester ) As yw_book,
(Select Count(*) From studentbook Where wl_book IS null And classid=a.classid And semester =a.semester ) As wl_book,
(Select Count(*) From studentbook Where hx_book IS null And classid=a.classid And semester =a.semester ) As hx_book
From from studentbook a
group by a.classid,a.semester

回复赞

tengjian1981 2004-12-15

select classid,sum(case isnull(yy_book,0) when 0 then 1 else 0 end)
,sum(case isnull(yw_book,0) when 0 then 1 else 0 end)
,sum(case isnull(wl_book,0) when 0 then 1 else 0 end)
,sum(case isnull(hx_book,0) when 0 then 1 else 0 end)
from studentbook
group by classid

回复赞