• 主页
• 基础类
• 应用实例
• 新技术前沿

# 关于分组,一个很有意义的问题

romail 2005-03-22 11:14:24

id date num
a 2005-1-1 3
a 2005-1-1 2
c 2005-1-1 3
b 2005-1-2 4
b 2005-1-2 1
c 2005-1-2 1
a 2005-1-3 2
a 2005-1-3 4
c 2005-1-3 3
c 2005-1-3 2

id 当日合计数 累计数
a 6 11
c 5 9
...全文
127 点赞 收藏 3

3 条回复
jinjazz 2005年03月22日
--全部计算
--建立测试环境
Create Table 表(id varchar(10),date varchar(10),num integer)
--插入数据
insert into 表
select 'a','2005-1-1','3' union
select 'a','2005-1-1','2' union
select 'c','2005-1-1','3' union
select 'b','2005-1-2','4' union
select 'b','2005-1-2','1' union
select 'c','2005-1-2','1' union
select 'a','2005-1-3','2' union
select 'a','2005-1-3','4' union
select 'c','2005-1-3','3' union
select 'c','2005-1-3','2'
select * from 表
--测试语句
select id,date, 当日合计数=sum(num),

from 表 a group by id,date
order by id,date

--删除测试环境
Drop Table 表

xiaomeixiang 2005年03月22日
declare @tb1 table(id varchar(5), date datetime, num int)
insert into @tb1
select 'a','2005-1-1', 3
union all select 'a' , '2005-1-1' , 2
union all select 'c' , '2005-1-1' , 3
union all select 'b' , '2005-1-2' , 4
union all select 'b' , '2005-1-2' , 1
union all select 'c' , '2005-1-2' , 1
union all select 'a' , '2005-1-3' , 2
union all select 'a' , '2005-1-3' , 4
union all select 'c' , '2005-1-3' , 3
union all select 'c', '2005-1-3' , 2

select a.id,当日合计数,累计数 from
(select id,sum(num) as 当日合计数 from @tb1 where date='2005-1-3' group by id ) a left join
(select id,sum(num) as 累计数 from @tb1 where date<='2005-1-3' group by id ) b on a.id=b.id

pbsql 2005年03月22日
select id,

where id in(select id from tbl where datediff(day,[date],'2005-1-3')=0)
group by id

MS-SQL Server

1.4w+

25.3w+

MS-SQL Server相关内容讨论专区