34,588
社区成员
发帖
与我相关
我的任务
分享
SELECT A.批号,A.班次,A.产量,SUM(B.产量) 当班总量
FROM TAB A LEFT JOIN TAB B ON A.班次>=B.班次
GROUP BY A.批号,A.班次,A.产量
--> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([批号] [nvarchar](10),[班次] [int],[产量] [int],[当班总量(要增加的列)] [int])
INSERT INTO [tb]
SELECT 'a','1','20','20' UNION ALL
SELECT 'a','2','10','30' UNION ALL
SELECT 'a','3','10','40' UNION ALL
SELECT 'a','4','20','60'
--SELECT * FROM [tb]
-->SQL查询如下:
SELECT *, newcol = (
SELECT SUM(产量)
FROM tb
WHERE 批号 = t.批号
AND 班次<= t.班次
)
FROM tb t
/*
批号 班次 产量 当班总量(要增加的列) newcol
---------- ----------- ----------- ----------- -----------
a 1 20 20 20
a 2 10 30 30
a 3 10 40 40
a 4 20 60 60
(4 行受影响)
*/
declare @a table(a varchar(20),o int)
insert @a select 'aaa',null
union all select 'aaa',null
union all select 'bbb',null
union all select 'ccc',null
--2000
declare @i int,@s varchar(20)
set @i=0
update @a set @i=case when @s=a then @i else @i+1 end,@s=a,o=@i
select * from @a
--2008
select a,DENSE_RANK() over (order by a) from @a
--result
/*a o
aaa 1
aaa 1
bbb 2
ccc 3*/
--> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([col] [nvarchar](10))
INSERT INTO [tb]
SELECT 'aaa' UNION ALL
SELECT 'aaa' UNION ALL
SELECT 'bbb' UNION ALL
SELECT 'ccc'
--SELECT * FROM [tb]
-->SQL查询如下:
SELECT col, rn = DENSE_RANK()OVER(ORDER BY col)
FROM tb
/*
col rn
---------- --------------------
aaa 1
aaa 1
bbb 2
ccc 3
(4 行受影响)
*/
--2k如下:
SELECT col,rn=(SELECT COUNT(DISTINCT col) FROM tb WHERE col<=t.col)
FROM tb t
/*
col rn
---------- -----------
aaa 1
aaa 1
bbb 2
ccc 3
(4 行受影响)
*/
select col,rn=dense_rank()over(order by col) from tb