id date count type
1 2014-1-1 5 1
2 2014-1-2 4 2
3 2014-1-2 3 1
4 2014-1-3 7 2
5 2014-1-3 6 1
然后想要查询出来的表样是这样
id date count type 新增列
1 2014-1-1 5 1 5
2 2014-1-2 4 2 4
3 2014-1-2 3 1 8
4 2014-1-3 7 2 11
5 2014-1-3 6 1 16
就是新增列等于 对应日期之前 type相同的数量之和.
4 个解决方案
#1
好吧..刚才进死胡同了..转换下思维就OK了- -
#2
create table tb(id int,date datetime,[count] int,[type] int)
insert into tb select 1,'2014-1-1',5,1
insert into tb select 2,'2014-1-2',4,2
insert into tb select 3,'2014-1-2',3,1
insert into tb select 4,'2014-1-3',7,2
insert into tb select 5,'2014-1-3',6,1
go
select *,(select sum([count]) from tb where id<=a.id and [type]=a.[type])as s from tb a
/*
id date count type s
----------- ----------------------- ----------- ----------- -----------
1 2014-01-01 00:00:00.000 5 1 5
2 2014-01-02 00:00:00.000 4 2 4
3 2014-01-02 00:00:00.000 3 1 8
4 2014-01-03 00:00:00.000 7 2 11
5 2014-01-03 00:00:00.000 6 1 14
(5 行受影响)
*/
go
drop table tb
#3
另外 我想问一下 如果数据量比较大的情况下,这样做查询会不会影响效率.
#4
会影响效率的,最高改成 join的那种
#1
好吧..刚才进死胡同了..转换下思维就OK了- -
#2
create table tb(id int,date datetime,[count] int,[type] int)
insert into tb select 1,'2014-1-1',5,1
insert into tb select 2,'2014-1-2',4,2
insert into tb select 3,'2014-1-2',3,1
insert into tb select 4,'2014-1-3',7,2
insert into tb select 5,'2014-1-3',6,1
go
select *,(select sum([count]) from tb where id<=a.id and [type]=a.[type])as s from tb a
/*
id date count type s
----------- ----------------------- ----------- ----------- -----------
1 2014-01-01 00:00:00.000 5 1 5
2 2014-01-02 00:00:00.000 4 2 4
3 2014-01-02 00:00:00.000 3 1 8
4 2014-01-03 00:00:00.000 7 2 11
5 2014-01-03 00:00:00.000 6 1 14
(5 行受影响)
*/
go
drop table tb
#3
create table tb(id int,date datetime,[count] int,[type] int)
insert into tb select 1,'2014-1-1',5,1
insert into tb select 2,'2014-1-2',4,2
insert into tb select 3,'2014-1-2',3,1
insert into tb select 4,'2014-1-3',7,2
insert into tb select 5,'2014-1-3',6,1
go
select *,(select sum([count]) from tb where id<=a.id and [type]=a.[type])as s from tb a
/*
id date count type s
----------- ----------------------- ----------- ----------- -----------
1 2014-01-01 00:00:00.000 5 1 5
2 2014-01-02 00:00:00.000 4 2 4
3 2014-01-02 00:00:00.000 3 1 8
4 2014-01-03 00:00:00.000 7 2 11
5 2014-01-03 00:00:00.000 6 1 14
(5 行受影响)
*/
go
drop table tb
另外 我想问一下 如果数据量比较大的情况下,这样做查询会不会影响效率.
#4
create table tb(id int,date datetime,[count] int,[type] int)
insert into tb select 1,'2014-1-1',5,1
insert into tb select 2,'2014-1-2',4,2
insert into tb select 3,'2014-1-2',3,1
insert into tb select 4,'2014-1-3',7,2
insert into tb select 5,'2014-1-3',6,1
go
select *,(select sum([count]) from tb where id<=a.id and [type]=a.[type])as s from tb a
/*
id date count type s
----------- ----------------------- ----------- ----------- -----------
1 2014-01-01 00:00:00.000 5 1 5
2 2014-01-02 00:00:00.000 4 2 4
3 2014-01-02 00:00:00.000 3 1 8
4 2014-01-03 00:00:00.000 7 2 11
5 2014-01-03 00:00:00.000 6 1 14
(5 行受影响)
*/
go
drop table tb
另外 我想问一下 如果数据量比较大的情况下,这样做查询会不会影响效率.
会影响效率的,最高改成 join的那种