用Datediff()函数,比如查询当月的:SELECT EMP_ID,SUM(SALE_AMOUNT) AS TOTAL FROM TABNAME WHERE DATEDIFF(MM,COL_DATE,GETDATE())=0 GROUP BY EMP_ID
create table t(name varchar(20),date int,sales int)insert t values('a',1,500) insert t values('a',1,500) insert t values('a',2,504) insert t values('b',5,5200) insert t values('c',6,5030) insert t values('a',7,5030) insert t values('b',8,500) insert t values('a',8,5050) insert t values('a',10,506) insert t values('a',23,500) insert t values('a',31,507) insert t values('b',31,504) insert t values('b',31,508) insert t values('c',31,9500) insert t values('c',31,500) select name,sum(sales) as 销售总额 from t group by name/* name 销售总额 -------------------- ----------- a 13097 b 6712 c 15030 */
select name,date,sum(sales) as 销售总额 from t group by name ,date --再找一下行列转换的
是这个意思吗?select * ,总计 = 第一天+第二天+…………+第三十一天 from tb
试试 select name, sum(case day(日期) when 1 then 销售额 end) as [1], sum(case day(日期) when 2 then 销售额 end) as [2], sum(case day(日期) when 3 then 销售额 end) as [3], sum(case day(日期) when 4 then 销售额 end) as [4], sum(case day(日期) when 5 then 销售额 end) as [5], sum(case day(日期) when 6 then 销售额 end) as [6], sum(case day(日期) when 7 then 销售额 end) as [7], sum(case day(日期) when 8 then 销售额 end) as [8], sum(case day(日期) when 9 then 销售额 end) as [9], sum(case day(日期) when 10 then 销售额 end) as [10], sum(case day(日期) when 11 then 销售额 end) as [11], sum(case day(日期) when 12 then 销售额 end) as [12], sum(case day(日期) when 13 then 销售额 end) as [13], sum(case day(日期) when 14 then 销售额 end) as [14], sum(case day(日期) when 15 then 销售额 end) as [15], sum(case day(日期) when 16 then 销售额 end) as [16], sum(case day(日期) when 17 then 销售额 end) as [17], sum(case day(日期) when 18 then 销售额 end) as [18], sum(case day(日期) when 19 then 销售额 end) as [19], sum(case day(日期) when 20 then 销售额 end) as [20], sum(case day(日期) when 21 then 销售额 end) as [21], sum(case day(日期) when 22 then 销售额 end) as [22], sum(case day(日期) when 23 then 销售额 end) as [23], sum(case day(日期) when 24 then 销售额 end) as [24], sum(case day(日期) when 25 then 销售额 end) as [25], sum(case day(日期) when 26 then 销售额 end) as [26], sum(case day(日期) when 27 then 销售额 end) as [27], sum(case day(日期) when 28 then 销售额 end) as [28], sum(case day(日期) when 29 then 销售额 end) as [29], sum(case day(日期) when 30 then 销售额 end) as [30], sum(case day(日期) when 31 then 销售额 end) as [31] from tb where datediff(mm,日期,getdate())=0 group by name
如果是这样就是 9楼的答案 否则是 select id,sum(amount) as total from tabname where datediff(mm,[date],@datetime)=0 group by id
-- day1,day2,day3,...,day31 必须是数值类型,如果不是,则需要转换case(day1 as int/float) select id, day1 + day2 + day3 + ... + day31 as total from TBName
insert t values('a',1,500)
insert t values('a',2,504)
insert t values('b',5,5200)
insert t values('c',6,5030)
insert t values('a',7,5030)
insert t values('b',8,500)
insert t values('a',8,5050)
insert t values('a',10,506)
insert t values('a',23,500)
insert t values('a',31,507)
insert t values('b',31,504)
insert t values('b',31,508)
insert t values('c',31,9500)
insert t values('c',31,500)
select name,sum(sales) as 销售总额 from t group by name/*
name 销售总额
-------------------- -----------
a 13097
b 6712
c 15030
*/
DATEDIFF
返回跨两个指定日期的日期和时间边界数。
语法
*/
DATEDIFF ( datepart , startdate , enddate)
name ,第一天,第二天,。第三十一天
a 50 100 0
select name,date,sum(sales) as 销售总额 from t group by name ,date
--再找一下行列转换的
是这个意思吗?select *
,总计 = 第一天+第二天+…………+第三十一天
from tb
select
name,
sum(case day(日期) when 1 then 销售额 end) as [1],
sum(case day(日期) when 2 then 销售额 end) as [2],
sum(case day(日期) when 3 then 销售额 end) as [3],
sum(case day(日期) when 4 then 销售额 end) as [4],
sum(case day(日期) when 5 then 销售额 end) as [5],
sum(case day(日期) when 6 then 销售额 end) as [6],
sum(case day(日期) when 7 then 销售额 end) as [7],
sum(case day(日期) when 8 then 销售额 end) as [8],
sum(case day(日期) when 9 then 销售额 end) as [9],
sum(case day(日期) when 10 then 销售额 end) as [10],
sum(case day(日期) when 11 then 销售额 end) as [11],
sum(case day(日期) when 12 then 销售额 end) as [12],
sum(case day(日期) when 13 then 销售额 end) as [13],
sum(case day(日期) when 14 then 销售额 end) as [14],
sum(case day(日期) when 15 then 销售额 end) as [15],
sum(case day(日期) when 16 then 销售额 end) as [16],
sum(case day(日期) when 17 then 销售额 end) as [17],
sum(case day(日期) when 18 then 销售额 end) as [18],
sum(case day(日期) when 19 then 销售额 end) as [19],
sum(case day(日期) when 20 then 销售额 end) as [20],
sum(case day(日期) when 21 then 销售额 end) as [21],
sum(case day(日期) when 22 then 销售额 end) as [22],
sum(case day(日期) when 23 then 销售额 end) as [23],
sum(case day(日期) when 24 then 销售额 end) as [24],
sum(case day(日期) when 25 then 销售额 end) as [25],
sum(case day(日期) when 26 then 销售额 end) as [26],
sum(case day(日期) when 27 then 销售额 end) as [27],
sum(case day(日期) when 28 then 销售额 end) as [28],
sum(case day(日期) when 29 then 销售额 end) as [29],
sum(case day(日期) when 30 then 销售额 end) as [30],
sum(case day(日期) when 31 then 销售额 end) as [31]
from tb
where datediff(mm,日期,getdate())=0
group by name
9楼的答案
否则是
select id,sum(amount) as total from tabname where datediff(mm,[date],@datetime)=0 group by id
-- day1,day2,day3,...,day31 必须是数值类型,如果不是,则需要转换case(day1 as int/float)
select id, day1 + day2 + day3 + ... + day31 as total from TBName