I have the form below, to fill the unit, use A number 2-4, serial number 6-8 with b, this pattern, can you tell me the statements in the query analyzer how to write

The serial number unit name
1 A
And the king
3
4 li
5 b
6 the king 1
7 pieces of 1
8 li 1
9 c
Then the king 2
11 zhang 2
12 li 2

Thank you

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] (
[number] INT 
, [unit] NVARCHAR (10) 
, [name] NVARCHAR (10) 
) 
GO 
SET NOCOUNT ON 
INSERT INTO dbo. [t] VALUES (N '1', N 'A', NULL) 
INSERT INTO dbo. [t] VALUES (N '2', NULL, N 'king') 
INSERT INTO dbo. [t] VALUES (N '3', NULL, N 'a') 
INSERT INTO dbo. [t] VALUES (N '4', NULL, N 'li') 
INSERT INTO dbo. [t] VALUES (N '5', N 'b', NULL) 
INSERT INTO dbo. [t] VALUES (N '6', NULL, N '1' king) 
INSERT INTO dbo. [t] VALUES (N '7', NULL, N '1' zhang) 
INSERT INTO dbo. [t] VALUES (N '8', NULL, N '1' lee) 
INSERT INTO dbo. [t] VALUES (N '9', N 'c', NULL) 
INSERT INTO dbo. [t] VALUES (N '10', NULL, N 'the king 2') 
INSERT INTO dbo. [t] VALUES (N '11', NULL, N '2' zhang) 
INSERT INTO dbo. [t] VALUES (N '12' NULL, N '2' lee) 
GO 
-- -- -- -- -- - above for testing table and test data -- -- -- -- -- -- -- 

SELECT the serial number, 
CASE 
WHEN the unit & gt; 'THEN the unit 
The ELSE (
SELECT the TOP 1 unit 
The FROM t AS b 
WHERE b. [number] AND b. [unit] & gt; '
The ORDER BY 
B. [number] DESC 
) 
END the AS unit, 
A. [name] 
The FROM t AS a 
/* 
The serial number unit name 
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
1 A NULL 
2 A king 
3 A picture 
4 A lee 
5 b NULL 
6 b king 1 
7 b zhang 1 
8 b l 1 
9 c NULL 
He was king for 2 c 
11 c 2 
12 c lee 2 
*/

CodePudding user response:

Set nocount on
Declare @ TB as table (INT [number], [unit] varchar (10), [name] NVARCHAR (10))

INSERT INTO @ TB (serial number, the unit, name) VALUES (N '1', N 'A', NULL)
INSERT INTO @ TB (serial number, the unit, name) VALUES (N '2', NULL, N 'king')
INSERT INTO @ TB (serial number, the unit, name) VALUES (N '3', NULL, N 'a')
INSERT INTO @ TB (serial number, the unit, name) VALUES (N '4', NULL, N 'li')
INSERT INTO @ TB (serial number, the unit, name) VALUES (N '5', N 'b', NULL)
INSERT INTO @ TB (serial number, the unit, name) VALUES (N '6', NULL, N '1' king)
INSERT INTO @ TB (serial number, the unit, name) VALUES (N '7', NULL, N '1' zhang)
INSERT INTO @ TB (serial number, the unit, name) VALUES (N '8', NULL, N '1' lee)
INSERT INTO @ TB (serial number, the unit, name) VALUES (N '9', N 'c', NULL)
INSERT INTO @ TB (serial number, the unit, name) VALUES (N '10', NULL, N 'the king 2')
INSERT INTO @ TB (serial number, the unit, name) VALUES (N '11', NULL, N '2' zhang)
INSERT INTO @ TB (serial number, the unit, name) VALUES (N '12' NULL, N '2' lee)


Declare @ Unit varchar (100)='
Update set @ @ TB Unit==Unit isnull (Unit, @ Unit)