------建表
create table ttt
(
[time] datetime primary key,
simno varchar(5),
Sid varchar(4),
feecode int
)
-----插入数据
insert ttt values('2004-04-04','12345','1000',1)
insert ttt values('2004-04-05','12345','1000',1)
insert ttt values('2004-04-02','10000','1001',2)
insert ttt values('2004-03-04','12345','1000',1)---执行测试
select simno,Sid,sum(feecode) from ttt group by simno,sid-----测试结果
-------------
12345 1000 3
10000 1001 2
--------------
create table ttt
(
[time] datetime primary key,
simno varchar(5),
Sid varchar(4),
feecode int
)
-----插入数据
insert ttt values('2004-04-04','12345','1000',1)
insert ttt values('2004-04-05','12345','1000',1)
insert ttt values('2004-04-02','10000','1001',2)
insert ttt values('2004-03-04','12345','1000',1)---执行测试
select simno,Sid,sum(feecode) from ttt group by simno,sid-----测试结果
-------------
12345 1000 3
10000 1001 2
--------------
比如simno为12345 sid 为1000的在四月分出现两次计算时仅计算一次
--------------------
3 12345 1000 1
4 10000 1001 2
4 12345 1000 2--------------------
create table test
( time datetime,
simno varchar(5),
sid varchar(4),
feecode int)insert test
select '2004-04-04','12345','1000',1
union all select '2004-04-05','12345','1000',1
union all select '2004-04-05','10000','1001',2
union all select '2004-04-02','12345','1000',1--语句
select a.time2,a.simno,a.sid,[total]=sum(a.feecode) from
(select distinct [time2]=convert(varchar(7),time,120),simno,sid,feecode
from test) a
group by a.time2,a.simno,a.sid--运行结果time2 simno sid total
-------------------------------
2004-04 10000 1001 2
2004-04 12345 1000 1
(select distinct [time2]=convert(varchar(7),time,120),simno,sid,feecode
from test) a
(
select a.time2,a.simno,a.sid,[total]=sum(a.feecode) from
(select distinct [time2]=convert(varchar(7),time,120),simno,sid,feecode
from tabletest) a
group by a.time2,a.simno,a.sid
) c
(select distinct simno,sid,feecode
from tabletest) a
from tabletest
group by
simno,sid
比如simno为12345 sid 为1000的在四月分出现两次计算时仅计算一次,这样的语句怎么写假如这两条记录中的feecode 不一样, 该如何计算?
取最大值?最小值?平均值?随机?...