||
以下是在教学及图书管理工作中写的SQL语句,经常是现用现写,发现过段时间又用到了还得重新想,正好有博客这个好平台,记录下来不断更新,共享也自享。
若问我“最好的计算机技术是哪一项”,我会毫不犹豫地答“关系数据库SQL”,十多年前我和某培训班的学生说“学会Select一句话,就够你吃一辈子”,当然不一定能吃好,找份工作吃饱是没问题的。可是有些做MIS的把SQL隐藏到长长的菜单下,故弄玄虚,刻意不让用户知晓,大概也是怕夺饭碗吧,现在我手头还有一些老系统的数据无法打开导出,开发的公司早已不知去向,他们怕丢饭碗还是丢了,结果也害了用户。
所以还是开放的好。
以下是OPAC数据统计,举几个典型的查询,相关表名字段名的意义都相对规范,真要用的人是好猜的,我就不详述了。
1、看一下2010级各专业学生的借书率,对两个表分组统计,还是分开写简单,查询后计数除以人数就是借书率。t_xs201212是从教务处要来的学生表,因有少部分转专业学生,所以读者数据和学籍数据不一致,按学籍为准。
select 院系,专业,count(*) as 人数 from t_xs201212 where 年级='2010级' group by 院系,专业 order by 院系,专
业
select 院系,专业,count(*) as 计数 from lend_hist inner join t_xs201212 on t_xs201212.学号=lend_hist.cert_id_f
where 年级='2010级' group by 院系,专业 order by 院系,专业
此类统计也能比较出不同专业的特点,文学类专业常是高居榜首。但这几年借阅率在下降,多数大学图书馆都如此,估计是笔记本电脑和智能手机普及的原因。
2、按专业,按图书类别统计
select 院系,专业,left(call_no,1) as 书类 ,count(*) as 计数 into #ttt from lend_hist inner join t_xs201212 on
t_xs201212.学号=lend_hist.cert_id_f where 年级='2010级' and 学生类别='本科'group by 院系,专业,left
(call_no,1) order by 院系,专业,书类
生成临时表 #ttt,再导入access 利用TRANSFORM Count(class07081.人数) AS 人数之计数 SELECT
class07081.系部FROM class07081GROUP BY class07081.系部PIVOT class07081.专业;
有时复杂查询就得用这类方法解决
3、生成长期不还图书者名单。
select DEPT,CERT_ID,NAME,left(LEND_DATE,10) as rq,M_CALL_NO ,M_TITLE,PROP_NO_F from
lend_hist,reader,marc
where lend_hist.cert_id_f=reader.cert_id and lend_hist.Marc_rec_no_f=marc.marc_rec_no and ret_date is null and
lend_date<'2011-06-01'
order by dept,name
4、CASE的使用一例,按职称类别算课时费
CREATE VIEW dbo.ksf
AS
SELECT 人员编号, 姓名, 部门, 行政系数, 职称系数, str(总学时, 10, 4) AS 总学时计, 基本学时,
str(CASE WHEN 职称系数 < 1 THEN (总学时 * 行政系数 - 基本学时)
* 职称系数 ELSE (总学时 * 行政系数) * 职称系数 - 基本学时 END, 10, 4) AS 折后课时,
str((CASE WHEN 职称系数 < 1 THEN (总学时 * 行政系数 - 基本学时)
* 职称系数 ELSE (总学时 * 行政系数) * 职称系数 - 基本学时 END) * 35, 11, 3) AS 课时费,
上期余,本期9月
FROM ks_sum INNER JOIN
teacher ON ks_sum.教师 = teacher.姓名
5、多表查询一例,学生评教用,涉及学生选课表、学籍表、教师表等
SELECT XK_KCAPB.KCH, XK_KCAPB.KCM, CODE_JSB.JSH, XK_XKB.XH,
XJ_XJB.XM, XK_MMB.MM, CODE_JSB.JSM
FROM XK_MMB INNER JOIN
XJ_XJB ON XK_MMB.XH = XJ_XJB.XH INNER JOIN
XK_XKB ON XJ_XJB.XH = XK_XKB.XH INNER JOIN
XK_KCAPB INNER JOIN
CODE_JSB ON XK_KCAPB.JSH1 = CODE_JSB.JSH ON
XK_XKB.KCH = XK_KCAPB.KCH
order by CODE_JSB.JSM,XK_XKB.xh
查询比较复杂的还是毕业生资格审查,没见那个教务管理系统做好过,所以开放系统,让用户清楚表结构很重要。
6、用临时表,查询年借阅量高的读者,读书日该奖
--select top 15 cert_id_f,count(*) as js into #ttt from lend_hist where lend_date>'2012-04-23' and cert_id_f like '12%'
-- group by cert_id_f order by count(*) desc
--select * from #ttt
select 学号,姓名,性别,学生类别,院系,专业,班级,js as 册数 from T_xs201212 inner join #ttt on #ttt.cert_id_f=T_xs201212.学号 order by 年级,册数 desc
=====补充,批量处理数据,用到自定义函数或游标,以下为处理教学评估数据
--SELECT * FROM KJ_JXPG WHERE TJ=1 and (xh like '02%' or xh like '04%')
---------计算每条计录的得分
/*create function dbo.sumfs3(@c char(10))
returns real
as
begin
return
(case substring(@c,1,1) when 'A' then 1 when 'B' then 0.8 when 'C' then 0.6 when 'D' then 0.2 else 0 end
+ case substring(@c,2,1) when 'A' then 1 when 'B' then 0.8 when 'C' then 0.6 when 'D' then 0.2 else 0 end
+ case substring(@c,3,1) when 'A' then 1 when 'B' then 0.8 when 'C' then 0.6 when 'D' then 0.2 else 0 end
+ case substring(@c,4,1) when 'A' then 1 when 'B' then 0.8 when 'C' then 0.6 when 'D' then 0.2 else 0 end
+ case substring(@c,5,1) when 'A' then 1 when 'B' then 0.8 when 'C' then 0.6 when 'D' then 0.2 else 0 end
+ case substring(@c,6,1) when 'A' then 1 when 'B' then 0.8 when 'C' then 0.6 when 'D' then 0.2 else 0 end
)/6.0
end
*/
--select KCM,XH,XM,JSM,DF,BM into #pj1 from kj_jxpg where tJ='1'
--alter table #pj1 add jf real
--update #pj1 set jf=dbo.sumfs3(DF)
--select * from #pj1
--select bm,jsm,avg(jf) as pjf,count(xh) as xss into #pj2 from #pj1 group by bm,jsm
--select * from #pj2 where xss>=5 order by bm,pjf desc
--select * into #pj3 from #pj2 where xss>=5
--alter table #pj3 add mc int,jss int
--select * from #pj3
--select bm,count(bm) as mbjss into #aaa from #pj3 group by bm
--update #pj3 set jss=#aaa.mbjss from #aaa inner join #pj3 on #pj3.bm=#aaa.bm
--drop table #aaa
--select * into pg672 from #pj3 order by bm,pjf desc
--select * from pg672
/*declare @i int
declare @bm char(6)
declare @bm2 char(6)
declare @mc int
declare @jss int
set @i=1
set @bm='05111'
declare mycur cursor for
select bm,mc,jss from pg672
for update
open mycur
while @@fetch_status=0--@i<=2512 --
begin
fetch next from mycur into @bm2,@mc,@jss
if @bm<>@bm2
begin
set @i=1
set @bm=@bm2
end
update pg672 set mc=@i where current of mycur
set @i=@i+1
end
close mycur
deallocate mycur*/
select * from pg672 order by jsm,mc
Archiver|手机版|科学网 ( 京ICP备07017567号-12 )
GMT+8, 2024-9-1 14:16
Powered by ScienceNet.cn
Copyright © 2007- 中国科学报社