CREATE TABLE [TB] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[ITEM] [int] NULL ,
[NOTE] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
CONSTRAINT [PK_TB] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO TB (ITEM, NOTE) SELECT 3,'XX'
INSERT INTO TB (ITEM, NOTE) SELECT 3,'XX'
INSERT INTO TB (ITEM, NOTE) SELECT 4,'XX'
INSERT INTO TB (ITEM, NOTE) SELECT 5,'XX'
INSERT INTO TB (ITEM, NOTE) SELECT 5,'XX'
INSERT INTO TB (ITEM, NOTE) SELECT 6,'XX'SELECT * FROM TB/*-----------------------
3 XX
3 XX
4 XX
5 XX
5 XX
6 XX
*/
SELECT
ITEM=CASE WHEN ID=(
SELECT MIN(ID)
FROM TB
WHERE ITEM=A.ITEM
)
THEN RTRIM(ITEM) ELSE '' END,
NOTE
FROM TB AS A
ORDER BY ID/*
ITEM NOTE
------------ --------------------
3 XX
XX
4 XX
5 XX
XX
6 XX
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[ITEM] [int] NULL ,
[NOTE] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
CONSTRAINT [PK_TB] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO TB (ITEM, NOTE) SELECT 3,'XX'
INSERT INTO TB (ITEM, NOTE) SELECT 3,'XX'
INSERT INTO TB (ITEM, NOTE) SELECT 4,'XX'
INSERT INTO TB (ITEM, NOTE) SELECT 5,'XX'
INSERT INTO TB (ITEM, NOTE) SELECT 5,'XX'
INSERT INTO TB (ITEM, NOTE) SELECT 6,'XX'SELECT * FROM TB/*-----------------------
3 XX
3 XX
4 XX
5 XX
5 XX
6 XX
*/
SELECT
ITEM=CASE WHEN ID=(
SELECT MIN(ID)
FROM TB
WHERE ITEM=A.ITEM
)
THEN RTRIM(ITEM) ELSE '' END,
NOTE
FROM TB AS A
ORDER BY ID/*
ITEM NOTE
------------ --------------------
3 XX
XX
4 XX
5 XX
XX
6 XX
SELECT
ITEM=CASE WHEN ID=(
SELECT MIN(ID)
FROM TB
WHERE ITEM=A.ITEM
)
THEN RTRIM(ITEM) ELSE '' END,
NOTE
FROM TB AS A
ORDER BY ID没用过 今天第一次见