把distict 去掉试试:declare @day date
set @day='2013-12-05'
declare @day_2 date
set @day_2='2013-12-08'
while(@day<@day_2)
begin
SELECT opttype as 消耗项目,sum(ABS(lijinchange))as 消耗量,sum(count(distinct account))as 消费人数,SUM(sum(abs(lijinchange)))as 一天总消耗量,@day as 日期
FROM [mydb_extendsgamelog1].[dbo].[12_TS_PlayerLiJinChange_log]where lijinchange<0 and @day=(convert(date,logtime,105))and opttype not like 'auction buy item comm-%'group by opttype,@day
set @day=dateadd(dd,1,@day)
end
set @day='2013-12-05'
declare @day_2 date
set @day_2='2013-12-08'
while(@day<@day_2)
begin
SELECT opttype as 消耗项目,sum(ABS(lijinchange))as 消耗量,sum(count(distinct account))as 消费人数,SUM(sum(abs(lijinchange)))as 一天总消耗量,@day as 日期
FROM [mydb_extendsgamelog1].[dbo].[12_TS_PlayerLiJinChange_log]where lijinchange<0 and @day=(convert(date,logtime,105))and opttype not like 'auction buy item comm-%'group by opttype,@day
set @day=dateadd(dd,1,@day)
end
set @day='2013-12-05'
declare @day_2 date
set @day_2='2013-12-08'
while(@day<@day_2)
begin
SELECT distinct opttype as 消耗项目,
sum(ABS(lijinchange))as 消耗量,
sum(count(distinct account))as 消费人数,
SUM(sum(abs(lijinchange)))as 一天总消耗量,
@day as 日期
FROM [mydb_extendsgamelog1].[dbo].[12_TS_PlayerLiJinChange_log]
where lijinchange<0 and @day=(convert(date,logtime,105))and opttype not like 'auction buy item comm-%'
group by opttype,@day -->group by opttype,日期
set @day=dateadd(dd,1,@day)
end
--試試以下:
declare @day date
set @day='2013-12-05'
declare @day_2 date
set @day_2='2013-12-08'
while(@day<@day_2)
begin
SELECT distinct opttype as 消耗项目,sum(ABS(lijinchange))as 消耗量,sum(count(distinct account))as 消费人数,SUM(sum(abs(lijinchange)))as 一天总消耗量,@day as 日期
FROM [mydb_extendsgamelog1].[dbo].[12_TS_PlayerLiJinChange_log]where lijinchange<0 and @day=(convert(date,logtime,105))and opttype not like 'auction buy item comm-%'group by opttype
set @day=dateadd(dd,1,@day)
end
set @day='2013-12-05'
declare @day_2 date
set @day_2='2013-12-08'
while(@day<@day_2)
beginselect distinct opttype as 消耗项目,sum(ABS(lijinchange))as 消耗量,sum(count(distinct account))as 消费人数,SUM(sum(abs(lijinchange)))as 一天总消耗量,日期
from
(
SELECT opttype,lijinchange,account,@day as 日期
FROM [mydb_extendsgamelog1].[dbo].[12_TS_PlayerLiJinChange_log]where lijinchange<0 and @day=(convert(date,logtime,105))and opttype not like 'auction buy item comm-%'
)t
group by opttype,日期 set @day=dateadd(dd,1,@day)
end
set @day='2013-12-05'
declare @day_2 date
set @day_2='2013-12-08'
while(@day<@day_2)
begin
SELECT distinct opttype as 消耗项目,
sum(ABS(lijinchange))as 消耗量,
sum(count(distinct account))as 消费人数,
SUM(sum(abs(lijinchange)))as 一天总消耗量,
@day as 日期
FROM [mydb_extendsgamelog1].[dbo].[12_TS_PlayerLiJinChange_log]
where lijinchange<0 and @day=(convert(date,logtime,105))and opttype not like 'auction buy item comm-%'
group by opttype,@day -->group by opttype,日期
set @day=dateadd(dd,1,@day)
end
显示
消息 207,级别 16,状态 1,第 9 行
列名 '日期' 无效。
set @day='2013-12-05'
declare @day_2 date
set @day_2='2013-12-08'
while(@day<@day_2)
begin
SELECT distinct opttype as 消耗项目,
sum(ABS(lijinchange))as 消耗量,
sum(count(distinct account))as 消费人数,
SUM(sum(abs(lijinchange)))as 一天总消耗量,
@day as 日期
FROM [mydb_extendsgamelog1].[dbo].[12_TS_PlayerLiJinChange_log]
where lijinchange<0 and @day=(convert(date,logtime,105))and opttype not like 'auction buy item comm-%'
group by opttype,@day -->group by opttype,日期
set @day=dateadd(dd,1,@day)
end
显示
消息 207,级别 16,状态 1,第 9 行
列名 '日期' 无效。
你试试,直接把group by opttype,@day 改成 group by opttype应该就可以了
set @day='2013-12-05'
declare @day_2 date
set @day_2='2013-12-08'
while(@day<@day_2)
begin
SELECT distinct opttype as 消耗项目,
sum(ABS(lijinchange))as 消耗量,
sum(count(distinct account))as 消费人数,
SUM(sum(abs(lijinchange)))as 一天总消耗量,
@day as 日期
FROM [mydb_extendsgamelog1].[dbo].[12_TS_PlayerLiJinChange_log]
where lijinchange<0 and @day=(convert(date,logtime,105))and opttype not like 'auction buy item comm-%'
group by opttype,@day -->group by opttype,日期
set @day=dateadd(dd,1,@day)
end
显示
消息 207,级别 16,状态 1,第 9 行
列名 '日期' 无效。
你试试,直接把group by opttype,@day 改成 group by opttype应该就可以了
不行
消息 130,级别 15,状态 1,第 7 行
不能对包含聚合或子查询的表达式执行聚合函数。还是错误的
消息 130,级别 15,状态 1,第 7 行
不能对包含聚合或子查询的表达式执行聚合函数。
再试试这个,你用的聚合函数中,有问题,不能写成这样的sum(sum(xxx)):
declare @day date
set @day='2013-12-05'
declare @day_2 date
set @day_2='2013-12-08'
while(@day<@day_2)
beginSELECT distinct opttype as 消耗项目,
sum(ABS(lijinchange))as 消耗量,
count(distinct account)as 消费人数,
sum(abs(lijinchange))as 一天总消耗量,
@day as 日期
FROM [mydb_extendsgamelog1].[dbo].[12_TS_PlayerLiJinChange_log]
where lijinchange<0 and @day=(convert(date,logtime,105))
and opttype not like 'auction buy item comm-%'
group by opttypeset @day=dateadd(dd,1,@day)
end
消息 130,级别 15,状态 1,第 7 行
不能对包含聚合或子查询的表达式执行聚合函数。
再试试这个,你用的聚合函数中,有问题,不能写成这样的sum(sum(xxx)):
declare @day date
set @day='2013-12-05'
declare @day_2 date
set @day_2='2013-12-08'
while(@day<@day_2)
beginSELECT distinct opttype as 消耗项目,
sum(ABS(lijinchange))as 消耗量,
count(distinct account)as 消费人数,
sum(abs(lijinchange))as 一天总消耗量,
@day as 日期
FROM [mydb_extendsgamelog1].[dbo].[12_TS_PlayerLiJinChange_log]
where lijinchange<0 and @day=(convert(date,logtime,105))
and opttype not like 'auction buy item comm-%'
group by opttypeset @day=dateadd(dd,1,@day)
end
可以运行了 但是还是需要把每天的结果合并到同一个表中,可不可以不用临时表的情况下达成要求
消息 130,级别 15,状态 1,第 7 行
不能对包含聚合或子查询的表达式执行聚合函数。
再试试这个,你用的聚合函数中,有问题,不能写成这样的sum(sum(xxx)):
declare @day date
set @day='2013-12-05'
declare @day_2 date
set @day_2='2013-12-08'
while(@day<@day_2)
beginSELECT distinct opttype as 消耗项目,
sum(ABS(lijinchange))as 消耗量,
count(distinct account)as 消费人数,
sum(abs(lijinchange))as 一天总消耗量,
@day as 日期
FROM [mydb_extendsgamelog1].[dbo].[12_TS_PlayerLiJinChange_log]
where lijinchange<0 and @day=(convert(date,logtime,105))
and opttype not like 'auction buy item comm-%'
group by opttypeset @day=dateadd(dd,1,@day)
end
可以运行了 但是还是需要把每天的结果合并到同一个表中,可不可以不用临时表的情况下达成要求你的意思是,上面的代码是每天的明细,然后,你还想要个 累计所有天的值是吗?
消息 130,级别 15,状态 1,第 7 行
不能对包含聚合或子查询的表达式执行聚合函数。
再试试这个,你用的聚合函数中,有问题,不能写成这样的sum(sum(xxx)):
declare @day date
set @day='2013-12-05'
declare @day_2 date
set @day_2='2013-12-08'
while(@day<@day_2)
beginSELECT distinct opttype as 消耗项目,
sum(ABS(lijinchange))as 消耗量,
count(distinct account)as 消费人数,
sum(abs(lijinchange))as 一天总消耗量,
@day as 日期
FROM [mydb_extendsgamelog1].[dbo].[12_TS_PlayerLiJinChange_log]
where lijinchange<0 and @day=(convert(date,logtime,105))
and opttype not like 'auction buy item comm-%'
group by opttypeset @day=dateadd(dd,1,@day)
end
可以运行了 但是还是需要把每天的结果合并到同一个表中,可不可以不用临时表的情况下达成要求你的意思是,上面的代码是每天的明细,然后,你还想要个 累计所有天的值是吗?
是啊 就是每天求出的值 汇总到一起
declare @day date
set @day='2013-12-05'
declare @day_2 date
set @day_2='2013-12-08'
while(@day<@day_2)
beginSELECT distinct opttype as 消耗项目,
sum(ABS(lijinchange))as 消耗量,
count(distinct account)as 消费人数,
sum(abs(lijinchange))as 一天总消耗量,
@day as 日期
FROM [mydb_extendsgamelog1].[dbo].[12_TS_PlayerLiJinChange_log]
where lijinchange<0 and @day=(convert(date,logtime,105))
and opttype not like 'auction buy item comm-%'
group by opttypeset @day=dateadd(dd,1,@day)
endSELECT distinct opttype as 消耗项目,
sum(ABS(lijinchange))as 消耗量,
count(distinct account)as 消费人数,
sum(abs(lijinchange))as 一天总消耗量,
@day as 日期
FROM [mydb_extendsgamelog1].[dbo].[12_TS_PlayerLiJinChange_log]
where lijinchange<0
and (convert(date,logtime,105))>=@day
and (convert(date,logtime,105))<@day2
and opttype not like 'auction buy item comm-%'
group by opttype
declare @day date
declare @day1 date set @day='2013-12-05'
set @day1 = @day
declare @day_2 date
set @day_2='2013-12-08'
while(@day<@day_2)
beginSELECT distinct opttype as 消耗项目,
sum(ABS(lijinchange))as 消耗量,
count(distinct account)as 消费人数,
sum(abs(lijinchange))as 一天总消耗量,
@day as 日期
FROM [mydb_extendsgamelog1].[dbo].[12_TS_PlayerLiJinChange_log]
where lijinchange<0 and @day=(convert(date,logtime,105))
and opttype not like 'auction buy item comm-%'
group by opttypeset @day=dateadd(dd,1,@day)
endSELECT distinct opttype as 消耗项目,
sum(ABS(lijinchange))as 消耗量,
count(distinct account)as 消费人数,
sum(abs(lijinchange))as 一天总消耗量,
@day as 日期 --合计
FROM [mydb_extendsgamelog1].[dbo].[12_TS_PlayerLiJinChange_log]
where lijinchange<0
and (convert(date,logtime,105))>=@day1
and (convert(date,logtime,105))<@day2
and opttype not like 'auction buy item comm-%'
group by opttype
declare @day date
declare @day1 date set @day='2013-12-05'
set @day1 = @day
declare @day_2 date
set @day_2='2013-12-08'
while(@day<@day_2)
beginSELECT distinct opttype as 消耗项目,
sum(ABS(lijinchange))as 消耗量,
count(distinct account)as 消费人数,
sum(abs(lijinchange))as 一天总消耗量,
@day as 日期
FROM [mydb_extendsgamelog1].[dbo].[12_TS_PlayerLiJinChange_log]
where lijinchange<0 and @day=(convert(date,logtime,105))
and opttype not like 'auction buy item comm-%'
group by opttypeset @day=dateadd(dd,1,@day)
endSELECT distinct opttype as 消耗项目,
sum(ABS(lijinchange))as 消耗量,
count(distinct account)as 消费人数,
sum(abs(lijinchange))as 一天总消耗量,
@day as 日期 --合计
FROM [mydb_extendsgamelog1].[dbo].[12_TS_PlayerLiJinChange_log]
where lijinchange<0
and (convert(date,logtime,105))>=@day1
and (convert(date,logtime,105))<@day2
and opttype not like 'auction buy item comm-%'
group by opttype
还是不行
declare @day date
declare @day1 date set @day='2013-12-05'
set @day1 = @day
declare @day_2 date
set @day_2='2013-12-08'
while(@day<@day_2)
beginSELECT distinct opttype as 消耗项目,
sum(ABS(lijinchange))as 消耗量,
count(distinct account)as 消费人数,
sum(abs(lijinchange))as 一天总消耗量,
@day as 日期
FROM [mydb_extendsgamelog1].[dbo].[12_TS_PlayerLiJinChange_log]
where lijinchange<0 and @day=(convert(date,logtime,105))
and opttype not like 'auction buy item comm-%'
group by opttypeset @day=dateadd(dd,1,@day)
endSELECT distinct opttype as 消耗项目,
sum(ABS(lijinchange))as 消耗量,
count(distinct account)as 消费人数,
sum(abs(lijinchange))as 一天总消耗量,
@day as 日期 --合计
FROM [mydb_extendsgamelog1].[dbo].[12_TS_PlayerLiJinChange_log]
where lijinchange<0
and (convert(date,logtime,105))>=@day1
and (convert(date,logtime,105))<@day2
and opttype not like 'auction buy item comm-%'
group by opttype
还是不行
我搞定了~谢谢大神了