Posted on 2007-07-13 09:32
小苏 阅读(662)
评论(0) 编辑 收藏 引用
今日见小王同志眉头微皱,心想这兄台必然遇到难题,遂问其故。果不其然,他在通过日志表统计用户使用情况时创建试图屡屡失败。
我以前也没有做过类似的SQL,但又想这实现总该不难,于是拿来分析,情况如下:
表1-日志表,表结构如下:
ID,F_LOGIN,MTime,ManageName 这ID是主键(ID在我看来都是主键,下文不再赘述),F_LOGIN是用户的登陆名缩写,MTime是用户的操作时间,ManageName是用户操作的模块名称
表2-用户表,结构如下:
ID,F_ORDER,F_LOGIN,F_USERNAME,F_DEPTNAME...,F_ORDER是用户的顺序号,F_LOGIN是用户的登陆名缩写,F_USERNAME是用户的中文名,F_DEPTNAME是用户所在单位的名称
表3-部门表,结构如下:
ID,F_DEPTORDER,F_DEPTNAME F_DEPTORDER是部门顺序号,F_DEPTNAME是部门名称。
好了,就是这么三个表,客户要求根据统计用户对每个模块的使用次数,并要求按照部门顺序进行排序,并且统计结果排除管理帐号admin:
怎么办? 看到小王以前的视图是:
SELECT 用户表.F_DEPTNAME, COUNT(*)
AS count, 部门表.F_ORDER
FROM 日志表 INNER JOIN
用户表 ON
日志表.F_login = 用户表.F_LOGIN INNER JOIN
部门表 ON
用户表.F_DEPTNAME = 部门表.F_DEPATNAME
WHERE (日志表.F_login <> 'admin')
GROUP BY 用户表.F_DEPTNAME,
部门表.F_NO
ORDER BY 部门表.F_NO
郁闷,这试图看起来没什么问题啊,但是一运行问题就来了:
考,如果部门A的用户都没有使用,也就是日志表里没有记录,那么视图里根本就不会显示该单位,但是很明显这样不对,我们需要没有使用的单位显示次数为0嘛,
我想办法不是明摆着的嘛,把"INNER JOIN 部门表"改为"RIGHT JOIN"部门表不就ok了么,好,改变:
SELECT 用户表.F_DEPTNAME, COUNT(*)
AS count, 部门表.F_ORDER
FROM 日志表 INNER JOIN
用户表 ON
日志表.F_login = 用户表.F_LOGIN RIGHT JOIN
部门表 ON
用户表.F_DEPTNAME = 部门表.F_DEPATNAME
WHERE (日志表.F_login <> 'admin')
GROUP BY 用户表.F_DEPTNAME,
部门表.F_NO
ORDER BY 部门表.F_NO
运行,又郁闷,怎么还是没有出现,抓耳挠腮半晌弄不明白,心想反正老子最不怕的就是困难(最怕的是美女放电^_^),我一句一句来,调试、调试,终于发现问题所在:
"WHERE (日志表.F_login <> 'admin')"
当Right join以后,没有操作的部门会在视图留下一条记录,而这条记录只包含部门表的信息,用户表和日志表均为NULL,NULL是没有办法和'admin'比较的,也就是说NULL <> 'admin' 返回的是false,怎么办?调整视图join的次序,如下:
SELECT 用户表.F_DEPTNAME, COUNT(*)
AS count, 部门表.F_ORDER
FROM 用户表 INNER JOIN
部门表 ON
用户表.F_DEPTNAME = 部门表.F_DEPATNAME LEFT JOIN
日志表 ON
日志表.F_login = 用户表.F_LOGIN
WHERE (用户表.F_login <> 'admin')
GROUP BY 用户表.F_DEPTNAME,
部门表.F_NO
ORDER BY 部门表.F_NO
这样不管怎么变,这所有用户和部门都是有的,而且admin也过滤的,但是....不对啊,怎么没有用户的单位使用次数都很大啊,哦,原来是我用的count(*)
有问题,肯定得用sum函数啦。查查联机丛书,最后定稿如下:
SELECT 用户表.F_DEPTNAME,
SUM(CASE WHEN 统计表.F_login IS NULL THEN 0 ELSE 1 END) as count,
部门表.F_ORDER
FROM 用户表 INNER JOIN
部门表 ON
用户表.F_DEPTNAME = 部门表.F_DEPATNAME LEFT JOIN
日志表 ON
日志表.F_login = 用户表.F_LOGIN
WHERE (用户表.F_login <> 'admin')
GROUP BY 用户表.F_DEPTNAME,
部门表.F_NO
ORDER BY 部门表.F_NO
终于搞定了,万岁!!不过CASE的使用也分两种,一种是简单CASE函数,一种是CASE搜索函数,联机从书中关于when_expression 和Boolean_expression 写的很笼统,我的理解when_expression就是一个值,而Boolean_expression是一个判断,嗯,写这个破东西也婆婆妈妈的写了半个小时,到此收笔。