问GROUP BYR的联合复杂查询问题

wtg321 2009-08-14 10:11:54
一个工作量统计按人分月统计今年的工作量,因为目前只到8月,所以9月到12就为0。说明这一点的原因是如果用1到12循环用GROUP的话会出错。(数据库为SQL SERVER,表名为work)。WORK表里有2007年到2009年8月的工作量细则。
work
name work date
s1 2 2007-05-01
w1 1 2009-08-02
.............
------------------------------------
userdz
name zhongwen
s1 沙场
w1 王府井
问题一:如何得出《今年》每个人每月的工作量及总工作量的汇总值。

问题二:如果在另外一个数据库(conn2)里有一个中文名字的对照表userdz,能不能用一个INNER JOIN语句或其它的办法直接把中文名字放到上面的那个查询结果中?
...全文
99 点赞 收藏 10
写回复
10 条回复
切换为时间正序
请发表友善的回复…
发表回复
number123456 2009-08-14
SELECT compid,count(compid) a
FROM Table_hits
GROUP BY compid
HAVING COUNT(*) > 1
ORDER BY a DESC

compid=显示字段

count(compid)=显示重复字段的重复数量

by compid=重复的字段

给你个参考
回复
三楼の郎 2009-08-14
work1、work2、work3……对应就是1月、2月、3月……的工作量合计,年度工作量合计的话把work1、work2、work3都加起来就可以了,注意Null值的处理
回复
三楼の郎 2009-08-14
1、指定年度每人每月工作量汇总
--1月工作量
select b.zhongwen,sum(a.work) as work,'2009-01' as wm from work as a inner join userdz as b on a.[name]=b.[name] where datediff(month,[date],'2009-01-01')=0 group by b.[zhongwen]
union
--2月工作量
select b.zhongwen,sum(a.work) as work,'2009-02' as wm from work as a inner join userdz as b on a.[name]=b.[name] where datediff(month,[date],'2009-02-01')=0 group by b.[zhongwen]
union
…………

改成:

select a.zhongwen,b.work as work1,c.work as work2,d.work as work3 …… from userdz as a

left join
(select [name],sum(work) as work from work where datediff(month,[date],'2009-01-01')=0 group by [name])as b on a.[name]=b.[name]

left join
(select [name],sum(work) as work from work where datediff(month,[date],'2009-02-01')=0 group by [name])as c on a.[name]=c.[name]

left join
(select [name],sum(work) as work from work where datediff(month,[date],'2009-03-01')=0 group by [name])as c on a.[name]=c.[name]

…………
回复
野人丶嘎嘎 2009-08-14
RT
回复
wtg321 2009-08-14
希望得出如下所示表格形状。
<tr ><td>姓名</td><td>1月</td><td>2月</td><td>3月</td><td>4月</td> <td>5月</td><td>6月</td><td>7月</td><td>8月</td><td>9月</td><td>10月</td><td>11月</td><td>12月</td><td>总量</td></tr>
4楼的做法有待考虑。
我之前的想法是:1是从1月到12月来个FOR循环:(没成功,9月以后就出错了)
yyy=year(now)
for i=1 to 12
select name,count(isnull(work,0)) as hz FROM work where month(date) = "&i&" and year(date)="&yyy&" group by name order by hz
2是先取出不同的人的名字,再每个名字从1到12循环
3是先取出不同的人的名字,再逐一COUNT,累加汇总。
正在试验中~~
回复
三楼の郎 2009-08-14
1、指定年度每人每月工作量汇总
--1月工作量
select b.zhongwen,sum(a.work) as work,'2009-01' as wm from work as a inner join userdz as b on a.[name]=b.[name] where datediff(month,[date],'2009-01-01')=0 group by b.[zhongwen]
union
--2月工作量
select b.zhongwen,sum(a.work) as work,'2009-02' as wm from work as a inner join userdz as b on a.[name]=b.[name] where datediff(month,[date],'2009-02-01')=0 group by b.[zhongwen]
union
…………

2、指定年度总工作量的汇总
select zhongwen,sum(work) from (sql1) as a group by zhongwen
这里面的sql1直接用上面的SQL语句替代

3、总工作量汇总
select b.zhongwen,sum(a.work) as work from work as a inner join userdz as b on a.[name]=b.[name] group by b.zhongwen
回复
junyi2003 2009-08-14
这个很简单啊,你把时间超过当前时间的过滤掉不就可以了。
Group by 后面再跟 have to
回复
chenlongit 2009-08-14
帮顶
回复
sonzu 2009-08-14
顶帖,
不过说得好复杂......
我在脑里打了好多个小九九,也想不明白
愿牛人们把这贴顶起啦~~
智能建站
回复
wtg321 2009-08-14
头晕眼花中~还是用笨方法,循环嵌套+判断解决了。还好数据量不大。

开始散分。。。
-------------------------------------
SQL 里面最常用的命令是 SELECT 语句,用于检索数据。语法是:
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
* | expression [ AS output_name ] [, ...]
[ INTO [ TEMPORARY | TEMP ] [ TABLE ] new_table ]
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ GROUP BY expression [, ...] ]
[ HAVING condition [, ...] ]
[ { UNION | INTERSECT | EXCEPT [ ALL ] } select ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]
[ FOR UPDATE [ OF class_name [, ...] ] ]
[ LIMIT { count | ALL } [ { OFFSET | , } start ]]
回复
发动态
发帖子
ASP
创建于2007-09-28

2.8w+

社区成员

ASP即Active Server Pages,是Microsoft公司开发的服务器端脚本环境。
申请成为版主
社区公告
暂无公告