SQL 统计体验

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是一个判断,嗯,写这个破东西也婆婆妈妈的写了半个小时,到此收笔。


只有注册用户登录后才能发表评论。
网站导航: 博客园   IT新闻   BlogJava   知识库   博问   管理