• 主页

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

wtg321 2009-08-14 10:11:54

work
name work date
s1 2 2007-05-01
w1 1 2009-08-02
.............
------------------------------------
userdz
name zhongwen
s1 沙场
w1 王府井

...全文
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=重复的字段

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

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]

…………

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楼的做法有待考虑。

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，累加汇总。

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

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

2.8w+

ASP即Active Server Pages，是Microsoft公司开发的服务器端脚本环境。