现有一个表,表结构[ID] [int] IDENTITY(1,1) NOT NULL,
[EmpID] [int] NULL,
[EmpName] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[EmpNum] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[EmpDept] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[Manager] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[Context] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL,
[Score] [float] NULL,
[Date] [datetime] NULL,
[DealDept] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[ManageMent] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[IsAdd] [bit] NULL,
[InDate] [datetime] NULL,
[Result] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL
要求以Date为条件,统计本周,本月,本季度,本年的总分,并且每次的分数也要显示,我现在的写法是用 sum() over 加join on ,但是2个条件还行,4个条件就要join on 3次,感觉很麻烦,有谁有好的解决办法吗?
我自己的Sql语句select *,totalW=sum(Score) OVER (partition BY Score.[EmpName]) from Score left join (select ID,totalM=sum(Score) OVER (partition BY [EmpName]) from Score where IsAdd='" + state + "' and Date between '" + barEditItem5.EditValue + "' and '" + barEditItem6.EditValue + "') as tb on Score.ID=tb.ID where Date between '" + barEditItem1.EditValue + "' and '" + barEditItem2.EditValue + "' and IsAdd='" + state + "' order by Manager,EmpDept小弟第一次发帖,大家多多关照啊
[EmpID] [int] NULL,
[EmpName] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[EmpNum] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[EmpDept] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[Manager] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[Context] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL,
[Score] [float] NULL,
[Date] [datetime] NULL,
[DealDept] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[ManageMent] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[IsAdd] [bit] NULL,
[InDate] [datetime] NULL,
[Result] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL
要求以Date为条件,统计本周,本月,本季度,本年的总分,并且每次的分数也要显示,我现在的写法是用 sum() over 加join on ,但是2个条件还行,4个条件就要join on 3次,感觉很麻烦,有谁有好的解决办法吗?
我自己的Sql语句select *,totalW=sum(Score) OVER (partition BY Score.[EmpName]) from Score left join (select ID,totalM=sum(Score) OVER (partition BY [EmpName]) from Score where IsAdd='" + state + "' and Date between '" + barEditItem5.EditValue + "' and '" + barEditItem6.EditValue + "') as tb on Score.ID=tb.ID where Date between '" + barEditItem1.EditValue + "' and '" + barEditItem2.EditValue + "' and IsAdd='" + state + "' order by Manager,EmpDept小弟第一次发帖,大家多多关照啊
周 day week
月 day month
年 day ear
select sum(b,month([date]) from Tb re year([date]) = '2010'
group by month([date])
比如
id name Score date
1 张三 1 2010-7-11
2 张三 1 2010-7-19
3 张三 1 2010-8-11
4 张三 1 2010-12-11
5 张三 1 2010-4-5
统计出来就应该是 id name Score date week month Quarter Year
1 张三 1 2010-7-11 1 2 4 5
2 张三 1 2010-7-19 1 2 4 5
3 张三 1 2010-8-11 1 2 4 5
4 张三 1 2010-12-11 1 2 4 5
5 张三 1 2010-4-5 1 2 4 5
sum(case when 本周 then score end) as week,
sum(case when 本月 then score end) as month
……
手写的,不知道语法是不是完全正确,反正就这思路