User time order type amount
1 20190201 sale 299
2 20190401 sale 599
3 20190501 other 199
3 20190601 other 199
3 20190801 other 399
Add a column value, when the order type is other, amount & gt; 0 and & lt; 200 and minimum time value to AA
When the order type is other, amount & gt;=200 and & lt; 400 and minimum time values for BB
Results:
User time order type amount value
1 20190201 sale 299
2 20190401 sale 599
3 20190501 other 199 AA
3 20190601 other 199
3 20190801 other 399 BB
CodePudding user response:
USE tempdb for
GO
IF OBJECT_ID (' dbo. [t]) IS NOT NULL
DROP TABLE dbo. [t]
GO
The CREATE TABLE dbo. [t] (
[users] NVARCHAR (20)
[time] DATETIME
, order type NVARCHAR (20)
, [amount] INT
)
GO
SET NOCOUNT ON
INSERT INTO dbo. [t] VALUES (N '1', N '20190201', N 'sale', N '299')
INSERT INTO dbo. [t] VALUES (' 2 'N, N' 20190401 ', N 'sale', N '599')
INSERT INTO dbo. [t] VALUES (' 3 'N, N' 20190501 ', N 'other', N '199')
INSERT INTO dbo. [t] VALUES (' 3 'N, N' 20190601 ', N 'other', N '199')
INSERT INTO dbo. [t] VALUES (' 3 'N, N' 20190801 ', N 'other', N '399')
-- -- -- -- -- -- -- -- -- -- -- -- test data tables and
- add 1 column
The ALTER TABLE t ADD value] [VARCHAR (20)
GO
; WITH cte AS (
The SELECT ROW_NUMBER () OVER (ORDER BY [time]) AS rids, *
The FROM t WHERE order type='other' AND amount & gt; 0 AND amount & lt; 200
)
The UPDATE cte
The SET value] [='AA'
The FROM cte WHERE cte. Rids=1
; WITH cte AS (
The SELECT ROW_NUMBER () OVER (ORDER BY [time]) AS rids, *
The FROM t WHERE order type='other' AND amount & gt;=200 AND amount & lt; 400
)
The UPDATE cte
The SET value] [='BB'
The FROM cte WHERE cte. Rids=1
SELECT * FROM t
/*
User time order type amount value
1 2019-02-01 00:00:00. 000 sale 299 NULL
2 2019-04-01 00:00:00. 000 sale 599 NULL
3 the 2019-05-01 00:00:00. 000 other 199 AA
3 the 2019-06-01 00:00:00. 000 other 199 NULL
3 the 2019-08-01 00:00:00. 000 other 399 BB
*/