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

[wtg321]

一个工作量统计按人分月统计今年的工作量，因为目前只到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语句或其它的办法直接把中文名字放到上面的那个查询结果中？

[wtg321]

头晕眼花中～还是用笨方法，循环嵌套＋判断解决了。还好数据量不大。

开始散分。。。
-------------------------------------
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 ]]

[sonzu]

顶帖,
不过说得好复杂......
我在脑里打了好多个小九九,也想不明白
愿牛人们把这贴顶起啦~~
智能建站

[MR丶CHAN]

帮顶

[junyi2003]

这个很简单啊，你把时间超过当前时间的过滤掉不就可以了。
Group by 后面再跟 have to

[三楼の郎]

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

[wtg321]

希望得出如下所示表格形状。
<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，累加汇总。
正在试验中～～

[野人丶嘎嘎]

RT

[三楼の郎]

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]

…………

[三楼の郎]

work1、work2、work3……对应就是1月、2月、3月……的工作量合计，年度工作量合计的话把work1、work2、work3都加起来就可以了，注意Null值的处理

[number123456]

SELECT compid,count(compid) a
FROM Table_hits
GROUP BY compid
HAVING COUNT(*) > 1
ORDER BY a DESC

compid=显示字段

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

by compid=重复的字段

给你个参考