我有一个视图vperformanceuser有列:PerformanceValue为业绩值,UserId员工号,Date录入业绩日期
我要实现一个功能为:查出 (个人当月业绩总和)减去(个人上月业绩总和)最高业绩值的UserId。下面是该视图的一些查询结果:
select PerformanceValue,UserId,Date from vperformanceuser
==========================PerformanceValue UseId Date
30000 17 2009-11-24 15:18:56.000
10000 17 2009-12-24 15:48:26.000
1 17 2009-12-24 16:06:25.000
19999 17 2009-11-24 16:08:02.000
20000 5 2009-11-28 13:40:23.000
10000 5 2009-12-28 13:41:03.000
10000 6 2009-12-28 18:19:14.000
10000 6 2009-12-28 18:21:11.000
100000 21 2009-11-29 18:34:06.000
55000 22 2009-12-29 18:38:59.000
10000 23 2009-12-29 18:42:18.000======
希望大家能给予帮助,问题解决马上送分 谢谢大家
我要实现一个功能为:查出 (个人当月业绩总和)减去(个人上月业绩总和)最高业绩值的UserId。下面是该视图的一些查询结果:
select PerformanceValue,UserId,Date from vperformanceuser
==========================PerformanceValue UseId Date
30000 17 2009-11-24 15:18:56.000
10000 17 2009-12-24 15:48:26.000
1 17 2009-12-24 16:06:25.000
19999 17 2009-11-24 16:08:02.000
20000 5 2009-11-28 13:40:23.000
10000 5 2009-12-28 13:41:03.000
10000 6 2009-12-28 18:19:14.000
10000 6 2009-12-28 18:21:11.000
100000 21 2009-11-29 18:34:06.000
55000 22 2009-12-29 18:38:59.000
10000 23 2009-12-29 18:42:18.000======
希望大家能给予帮助,问题解决马上送分 谢谢大家
解决方案 »
- 求助啊!用导入语句将一个文本导入数据库
- 如何用 check 设置邮箱的格式
- [求助] 如何循环一张表的每行,并将每个字段里的数据拿出来做另一条sql语句的参数?
- 如何比较表数据的SQL语句,在线等,答对结贴...
- 一个比较复杂的查询,请教各位大虾,在线等,急!!!!!!!
- 很复杂的问题。我快疯了!!
- bcp out 怎么往导出文件temp.xls添加数据库表的列名
- SQL 2005 开发版新建一个作业时出错??
- 欢迎大家参与,高分等待
- sqlserver2000是不是在98上面不能安装,,为什么没有想7。0的桌面系统
- sql 2000 xl_article articleid 选择7-12位
- 急救.数学高手请进
if object_id('[tb]') is not null drop table [tb]
create table [tb]([PerformanceValue] int,[UseId] int,[Date] datetime)
insert [tb]
select 30000,17,'2009-11-24 15:18:56.000' union all
select 10000,17,'2009-12-24 15:48:26.000' union all
select 1,17,'2009-12-24 16:06:25.000' union all
select 19999,17,'2009-11-24 16:08:02.000' union all
select 20000,5,'2009-11-28 13:40:23.000' union all
select 10000,5,'2009-12-28 13:41:03.000' union all
select 10000,6,'2009-12-28 18:19:14.000' union all
select 10000,6,'2009-12-28 18:21:11.000' union all
select 100000,21,'2009-11-29 18:34:06.000' union all
select 55000,22,'2009-12-29 18:38:59.000' union all
select 10000,23,'2009-12-29 18:42:18.000'select top 1 [UseId]
from
(
select [UseId],
sum(case convert(varchar(6),[Date],112) when '200912' then PerformanceValue else 0 end) as [200912],
sum(case convert(varchar(6),[Date],112) when '200911' then PerformanceValue else 0 end) as [200911]
from [tb]
group by [UseId]
) t
order by [200912]- [200911] desc
-----------------------
22
GO
CREATE TABLE TB(PerformanceValue NUMERIC(19,6), UseId INT, Date DATETIME)
INSERT INTO TB
SELECT 30000, 17 ,'2009-11-24 15:18:56.000' UNION ALL
SELECT 10000, 17 ,'2009-12-24 15:48:26.000' UNION ALL
SELECT 1, 17 ,'2009-12-24 16:06:25.000' UNION ALL
SELECT 19999, 17 ,'2009-11-24 16:08:02.000' UNION ALL
SELECT 20000, 5 ,'2009-11-28 13:40:23.000' UNION ALL
SELECT 10000, 5 ,'2009-12-28 13:41:03.000' UNION ALL
SELECT 10000, 6 ,'2009-12-28 18:19:14.000' UNION ALL
SELECT 10000, 6 ,'2009-12-28 18:21:11.000' UNION ALL
SELECT 100000, 21 ,'2009-11-29 18:34:06.000' UNION ALL
SELECT 55000, 22 ,'2009-12-29 18:38:59.000' UNION ALL
SELECT 10000, 23 ,'2009-12-29 18:42:18.000'
SELECT TOP 1 WITH TIES T1.USEID,T1.TOTAL-ISNULL(T2.TOTAL,0) --如果上月没总计不算的话把ISNULL去掉
FROM (
SELECT USEID,YEAR(DATE) 'YEAR',MONTH(DATE) 'MONTH',SUM(PERFORMANCEVALUE) 'TOTAL'
FROM TB
GROUP BY USEID,YEAR(DATE),MONTH(DATE)
) T1
LEFT JOIN (
SELECT USEID,YEAR(DATE) 'YEAR',MONTH(DATE) 'MONTH',SUM(PERFORMANCEVALUE) 'TOTAL'
FROM TB
GROUP BY USEID,YEAR(DATE),MONTH(DATE)
) T2 ON T2.USEID=T1.USEID
AND (
(T2.[YEAR]=T1.[YEAR] AND T2.[MONTH]=T1.[MONTH]-1)
OR (T2.[YEAR]=T1.[YEAR]-1 AND T2.[MONTH]=T1.[MONTH]+11)
)
ORDER BY T1.TOTAL-ISNULL(T2.TOTAL,0) DESC --如果上月没总计不算的话把ISNULL去掉/*
21 100000.000000
*/
GO
CREATE TABLE TB(PerformanceValue NUMERIC(19,6), UseId INT, Date DATETIME)
INSERT INTO TB
SELECT 30000, 17 ,'2009-11-24 15:18:56.000' UNION ALL
SELECT 10000, 17 ,'2009-12-24 15:48:26.000' UNION ALL
SELECT 1, 17 ,'2009-12-24 16:06:25.000' UNION ALL
SELECT 19999, 17 ,'2009-11-24 16:08:02.000' UNION ALL
SELECT 20000, 5 ,'2009-11-28 13:40:23.000' UNION ALL
SELECT 10000, 5 ,'2009-12-28 13:41:03.000' UNION ALL
SELECT 10000, 6 ,'2009-12-28 18:19:14.000' UNION ALL
SELECT 10000, 6 ,'2009-12-28 18:21:11.000' UNION ALL
SELECT 100000, 21 ,'2009-11-29 18:34:06.000' UNION ALL
SELECT 55000, 22 ,'2009-12-29 18:38:59.000' UNION ALL
SELECT 10000, 23 ,'2009-12-29 18:42:18.000'
--只统计指定月份的话用参数指定一下
DECLARE @YEAR INT,@MONTH INT
SELECT @YEAR=2009,@MONTH=12
SELECT TOP 1 WITH TIES T1.USEID,T1.TOTAL-ISNULL(T2.TOTAL,0) --如果上月没总计不算的话把ISNULL去掉
FROM (
SELECT USEID,YEAR(DATE) 'YEAR',MONTH(DATE) 'MONTH',SUM(PERFORMANCEVALUE) 'TOTAL'
FROM TB
GROUP BY USEID,YEAR(DATE),MONTH(DATE)
) T1
LEFT JOIN (
SELECT USEID,YEAR(DATE) 'YEAR',MONTH(DATE) 'MONTH',SUM(PERFORMANCEVALUE) 'TOTAL'
FROM TB
GROUP BY USEID,YEAR(DATE),MONTH(DATE)
) T2 ON T2.USEID=T1.USEID
AND (
(T2.[YEAR]=T1.[YEAR] AND T2.[MONTH]=T1.[MONTH]-1)
OR (T2.[YEAR]=T1.[YEAR]-1 AND T2.[MONTH]=T1.[MONTH]+11)
)
WHERE T1.[YEAR]=@YEAR AND T1.[MONTH]=@MONTH
ORDER BY T1.TOTAL-ISNULL(T2.TOTAL,0) DESC --如果上月没总计不算的话把ISNULL去掉/*
22 55000.000000
*/
(select UseId,业绩差=sum(performancevalue)-isnull((select sum(performancevalue) from [vperformanceuser] where UseId=a.UseId and datediff(mm,date,convert(varchar(7),a.date,120)+'-1')=1),0)
from [vperformanceuser] a
where datediff(mm,date,getdate())=0
group by UseId,convert(varchar(7),date,120))a
order by 业绩差 desc--结果:
UseId
-----------
22
select top 1 * from
(select UseId,业绩差=sum(performancevalue)-isnull((select sum(performancevalue) from [vperformanceuser] where UseId=a.UseId and datediff(mm,date,convert(varchar(7),a.date,120)+'-1')=1),0)
from [vperformanceuser] a
where datediff(mm,date,getdate())=0
group by UseId,convert(varchar(7),date,120))a
order by 业绩差 desc--结果:
UseId 业绩差
----------- -----------
22 55000
from (
select UseId,sum(PerformanceValue) as s
from vperformanceuser
where year([date])=year(getdate()) and month([date])=month(getdate())
group by UseId) a inner join (
select UseId,sum(PerformanceValue) as s
from vperformanceuser
where year([date])=year(dateadd(m,-1,getdate())) and month([date])=month(dateadd(m,-1,getdate()))
group by UseId) b on a.UseId=b.UseId
order by a.s-b.s desc
insert [tb]
select 30000,17,'2009-11-24 15:18:56.000' union all
select 10000,17,'2009-12-24 15:48:26.000' union all
select 1,17,'2009-12-24 16:06:25.000' union all
select 19999,17,'2009-11-24 16:08:02.000' union all
select 20000,5,'2009-11-28 13:40:23.000' union all
select 10000,5,'2009-12-28 13:41:03.000' union all
select 10000,6,'2009-12-28 18:19:14.000' union all
select 10000,6,'2009-12-28 18:21:11.000' union all
select 100000,21,'2009-11-29 18:34:06.000' union all
select 55000,22,'2009-12-29 18:38:59.000' union all
select 10000,23,'2009-12-29 18:42:18.000'
go--定义一个变量,你可以自己输入不同的月份
declare @date as varchar(7)
set @date = '2009-12'--如果只考虑本月,上月都存在的userid
select top 1 m.UseId , m.PerformanceValue - n.PerformanceValue PerformanceValue from
(select UseId , sum(PerformanceValue) PerformanceValue from tb where convert(varchar(7),date,120) = @date group by UseId) m,
(select UseId , sum(PerformanceValue) PerformanceValue from tb where convert(varchar(7),date,120) = convert(varchar(7),dateadd(mm,-1,@date+'-01'),120) group by UseId) n
where m.UseId = n.UseId order by m.PerformanceValue - n.PerformanceValue desc
/*
UseId PerformanceValue
----------- ----------------
5 -10000(所影响的行数为 1 行)
*/--如果不考虑本月,上月都存在的userid
select top 1 isnull(m.UseId,n.UseId) UseId , isnull(m.PerformanceValue,0) - isnull(n.PerformanceValue,0) PerformanceValue from
(select UseId , sum(PerformanceValue) PerformanceValue from tb where convert(varchar(7),date,120) = @date group by UseId) m
full join
(select UseId , sum(PerformanceValue) PerformanceValue from tb where convert(varchar(7),date,120) = convert(varchar(7),dateadd(mm,-1,@date+'-01'),120) group by UseId) n
on m.UseId = n.UseId order by isnull(m.PerformanceValue,0) - isnull(n.PerformanceValue,0) desc
/*
UseId PerformanceValue
----------- ----------------
22 55000(所影响的行数为 1 行)
*/drop table tb