求一sql写法

[lei414]

有如下表
goods|payfee|act_time4
100 10 2008-01-22 00:00:01
100 10 2008-01-22 00:00:01
101 5 2008-01-22 01:00:01
102 15 2008-01-22 12:00:01
103 0 2008-01-23 10:00:01
103 0 2008-01-23 10:00:01
104 10 2008-01-23 22:00:01
104 10 2008-01-23 22:00:01
如果得到每天运费的和，goods是订单号
act_time4 |当天payfee的和
2008-01-22 30
2008-01-23 10

[青锋-SS]

select convert(varchar(10),act_time4,120) as act_time4,sum(payfee) as '当天payfee的和'
from table1
group by convert(varchar(10),act_time4,120)

[青锋-SS]

declare @t table(goods int,payfee int,act_time4 datetime)
insert into @t
select 100,10,'2008-01-22   00:00:01'
union select 100,10,'2008-01-22   00:00:01'
union select 101,5,'2008-01-22   01:00:01'
union select 102,15,'2008-01-22   12:00:01'
union select 103,0,'2008-01-23   10:00:01'
union select 103,0,'2008-01-23   10:00:01'
union select 104,10,'2008-01-23   22:00:01'
union select 104,10,'2008-01-23   22:00:01'

select * from @t

select   convert(varchar(10),act_time4,120)   as   act_time4,sum(payfee)   as   '当天payfee的和'
from   @t
group   by   convert(varchar(10),act_time4,120)
(5 行受影响)
goods       payfee      act_time4
----------- ----------- -----------------------
100         10          2008-01-22 00:00:01.000
101         5           2008-01-22 01:00:01.000
102         15          2008-01-22 12:00:01.000
103         0           2008-01-23 10:00:01.000
104         10          2008-01-23 22:00:01.000

(5 行受影响)

act_time4  当天payfee的和
---------- -----------
2008-01-22 30
2008-01-23 10

(2 行受影响)

[昵称被占用了]

select cast(convert(varchar(10),act_time4,120) as datetime) as act_time4,sum(Payfee)as Payfee
from tab 
group by cast(convert(varchar(10),act_time4,120) as datetime)

[qqhmitzk]

ls能得到楼主的结果吗??
楼主请说详细些

[lei414]

不对，表中的数据不是
goods payfee act_time4
----------- ----------- -----------------------
100 10 2008-01-22 00:00:01.000
101 5 2008-01-22 01:00:01.000
102 15 2008-01-22 12:00:01.000
103 0 2008-01-23 10:00:01.000
104 10 2008-01-23 22:00:01.000
应该是

1 100 10 2008-01-22 00:00:01
2 100 10 2008-01-22 00:00:01
3 101 5 2008-01-22 01:00:01
4 102 15 2008-01-22 12:00:01
5 103 0 2008-01-23 10:00:01
6 103 0 2008-01-23 10:00:01
7 104 10 2008-01-23 22:00:01
8 104 10 2008-01-23 22:00:01

[青锋-SS]

没有影响啊,实际有用的就是act_time4和payfee两个字段,其它的字段不需要.

select convert(varchar(10),act_time4,120) as act_time4,sum(payfee) as '当天payfee的和'
from @t
group by convert(varchar(10),act_time4,120)

[popeye627]

declare @t table(goods int,payfee int,act_time4 datetime)
insert into @t
select 100,10,'2008-01-22   00:00:01'
union all select 100,10,'2008-01-22   00:00:01'
union all  select 101,5,'2008-01-22   01:00:01'
union all  select 102,15,'2008-01-22   12:00:01'
union all  select 103,0,'2008-01-23   10:00:01'
union all  select 103,0,'2008-01-23   10:00:01'
union all  select 104,10,'2008-01-23   22:00:01'
union all  select 104,10,'2008-01-23   22:00:01'

select * from @t

select  convert(varchar(10),act_time4,120) as act_time4,sum(payfee) as '当天payfee的和'
from   (select distinct * from @t)t
group   by   convert(varchar(10),act_time4,120)

--------------------------------------------------
(8 個資料列受到影響)
goods       payfee      act_time4
----------- ----------- -----------------------
100         10          2008-01-22 00:00:01.000
100         10          2008-01-22 00:00:01.000
101         5           2008-01-22 01:00:01.000
102         15          2008-01-22 12:00:01.000
103         0           2008-01-23 10:00:01.000
103         0           2008-01-23 10:00:01.000
104         10          2008-01-23 22:00:01.000
104         10          2008-01-23 22:00:01.000

(8 個資料列受到影響)

act_time4  当天payfee的和
---------- -----------
2008-01-22 30
2008-01-23 10

(2 個資料列受到影響)

[青锋-SS]

declare @t table(goods int,payfee int,act_time4 datetime)
insert into @t
select 100,10,'2008-01-22   00:00:01'
union all select 100,10,'2008-01-22   00:00:01'
union all select 101,5,'2008-01-22   01:00:01'
union all select 102,15,'2008-01-22   12:00:01'
union all select 103,0,'2008-01-23   10:00:01'
union all select 103,0,'2008-01-23   10:00:01'
union all select 104,10,'2008-01-23   22:00:01'
union all select 104,10,'2008-01-23   22:00:01'

select * from @t

select   cast(convert(varchar(10),act_time4,120) as datetime)   as   act_time4,sum(payfee)   as   '当天payfee的和'
from   @t
group   by   cast(convert(varchar(10),act_time4,120) as datetime)
(8 行受影响)
goods       payfee      act_time4
----------- ----------- -----------------------
100         10          2008-01-22 00:00:01.000
100         10          2008-01-22 00:00:01.000
101         5           2008-01-22 01:00:01.000
102         15          2008-01-22 12:00:01.000
103         0           2008-01-23 10:00:01.000
103         0           2008-01-23 10:00:01.000
104         10          2008-01-23 22:00:01.000
104         10          2008-01-23 22:00:01.000

(8 行受影响)

act_time4               当天payfee的和
----------------------- -----------
2008-01-22 00:00:00.000 40
2008-01-23 00:00:00.000 20

(2 行受影响)

[青锋-SS]

晕,刚才改东西了.正确的如下:

declare @t table(goods int,payfee int,act_time4 datetime)
insert into @t
select 100,10,'2008-01-22   00:00:01'
union all select 100,10,'2008-01-22   00:00:01'
union all select 101,5,'2008-01-22   01:00:01'
union all select 102,15,'2008-01-22   12:00:01'
union all select 103,0,'2008-01-23   10:00:01'
union all select 103,0,'2008-01-23   10:00:01'
union all select 104,10,'2008-01-23   22:00:01'
union all select 104,10,'2008-01-23   22:00:01'

select * from @t

select   convert(varchar(10),act_time4,120)   as   act_time4,sum(payfee)   as   '当天payfee的和'
from   @t
group   by   convert(varchar(10),act_time4,120)
(8 行受影响)
goods       payfee      act_time4
----------- ----------- -----------------------
100         10          2008-01-22 00:00:01.000
100         10          2008-01-22 00:00:01.000
101         5           2008-01-22 01:00:01.000
102         15          2008-01-22 12:00:01.000
103         0           2008-01-23 10:00:01.000
103         0           2008-01-23 10:00:01.000
104         10          2008-01-23 22:00:01.000
104         10          2008-01-23 22:00:01.000

(8 行受影响)

act_time4  当天payfee的和
---------- -----------
2008-01-22 40
2008-01-23 20

(2 行受影响)

[青锋-SS]

你的记录只是有重复的数据,统计用的SQL语句还是用原来的,没有问题,数据只是测试用.

[青锋-SS]

晕,白快活了一场!

[yinqi025]

--code
declare @t table(
goods varchar(20),
payfee int,
act_time4 datetime
)

insert into @t
select '100',10,'2008-01-22 00:00:01'
union all
select '100',10,'2008-01-22 00:00:01'
union all
select '101',5,'2008-01-22 01:00:01'
union all
select '102',15,'2008-01-22 12:00:01'
union all
select '103',0,'2008-01-23 10:00:01'
union all
select '103',0,'2008-01-23 10:00:01'
union all
select '104',10,'2008-01-23 22:00:01'
union all
select '104',10,'2008-01-23 22:00:01'

select Convert(varchar(10),act_time4,120) as 时间,sum(payfee) as payfee
from @t
group by Convert(varchar(10),act_time4,120)