CREATE TABLE [dbo].[Table_AA](
[ID] [int] IDENTITY(1,1) NOT NULL,
[AA] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,
[B1] [int] NULL)
ON [PRIMARY]
insert into Table_AA(AA,B1)values('1',200)
insert into Table_AA(AA,B1)values('1',100)
insert into Table_AA(AA,B1)values('1',-200)
insert into Table_AA(AA,B1)values('1',-50)insert into Table_AA(AA,B1)values('2',100)
insert into Table_AA(AA,B1)values('2',200)
insert into Table_AA(AA,B1)values('2',-300)insert into Table_AA(AA,B1)values('3',200)
insert into Table_AA(AA,B1)values('3',200)
insert into Table_AA(AA,B1)values('3',-300)insert into Table_AA(AA,B1)values('4',300)
查询出来的结果如下:请高手们指点指点
ID AA B1 QC
----------------------------
1 1 200 200
2 1 100 200
3 1 -200 100
4 1 -50 50
5 2 100 100
6 2 200 300
7 2 -300 0
8 3 200
9 3 200
10 3 -300 100
11 4 300 300
*
,(select sum(B1) from Table_AA where AA=a.AA and ID<=a.ID) as QC
from Table_AA as a
就是说没有ID这个字段 我该怎么样去实现呢?
非常感谢您的回答 谢谢!
就是说没有ID这个字段 我该怎么样去实现呢?
非常感谢您的回答 谢谢!没有时,需要生成一列,用临时表,或用Row_number()over(order by getdate()) as ID
select *,qc=(select sum(b1) from Table_AA ta where tb.aa=ta.aa and ta.ID<=tb.ID) from (
select ROW_NUMBER() over(order by aa) as id, aa,b1 from Table_AA
) tb