表1:
用户 时间 订单类型 金额
1 20190201 sale 299
2 20190401 sale 599
3 20190501 other 199
3 20190601 other 199
3 20190801 other 399加一列 value ,当订单类型是other,金额>0 且<200且最小时间值为 AA
当订单类型是other,金额>=200 且<400且最小时间值为 BB结果:
用户 时间 订单类型 金额 value
1 20190201 sale 299
2 20190401 sale 599
3 20190501 other 199 AA
3 20190601 other 199
3 20190801 other 399 BB
用户 时间 订单类型 金额
1 20190201 sale 299
2 20190401 sale 599
3 20190501 other 199
3 20190601 other 199
3 20190801 other 399加一列 value ,当订单类型是other,金额>0 且<200且最小时间值为 AA
当订单类型是other,金额>=200 且<400且最小时间值为 BB结果:
用户 时间 订单类型 金额 value
1 20190201 sale 299
2 20190401 sale 599
3 20190501 other 199 AA
3 20190601 other 199
3 20190801 other 399 BB
GO
IF OBJECT_ID('dbo.[t]') IS NOT NULL
DROP TABLE dbo.[t]
GO
CREATE TABLE dbo.[t](
[用户] NVARCHAR(20)
,[时间] DATETIME
,[订单类型] NVARCHAR(20)
,[金额] INT
)
GO
SET NOCOUNT ON
INSERT INTO dbo.[t] VALUES(N'1',N'20190201',N'sale',N'299')
INSERT INTO dbo.[t] VALUES(N'2',N'20190401',N'sale',N'599')
INSERT INTO dbo.[t] VALUES(N'3',N'20190501',N'other',N'199')
INSERT INTO dbo.[t] VALUES(N'3',N'20190601',N'other',N'199')
INSERT INTO dbo.[t] VALUES(N'3',N'20190801',N'other',N'399')
------------ 测试表及测试数据
--添加 1 列
ALTER TABLE t ADD [value] VARCHAR(20)
GO;WITH cte AS (
SELECT ROW_NUMBER() OVER (ORDER BY [时间]) AS rid,*
FROM t WHERE 订单类型='other' AND 金额>0 AND 金额<200
)
UPDATE cte
SET [value]='AA'
FROM cte WHERE cte.rid=1;WITH cte AS (
SELECT ROW_NUMBER() OVER (ORDER BY [时间]) AS rid,*
FROM t WHERE 订单类型='other' AND 金额>=200 AND 金额<400
)
UPDATE cte
SET [value]='BB'
FROM cte WHERE cte.rid=1SELECT * FROM t
/*
用户 时间 订单类型 金额 value
1 2019-02-01 00:00:00.000 sale 299 NULL
2 2019-04-01 00:00:00.000 sale 599 NULL
3 2019-05-01 00:00:00.000 other 199 AA
3 2019-06-01 00:00:00.000 other 199 NULL
3 2019-08-01 00:00:00.000 other 399 BB
*/