目前表查出来是这样的:
NAME AGE SCORE
张三 23 100
张三 23 99
张三 23 91
王五 24 55
赵六 23 null 需要这样的结果:
NAME AGE SCORE
张三 23 100
NULL 23 99
NULL 23 91
王五 24 55
赵六 23 NULL
NAME AGE SCORE
张三 23 100
张三 23 99
张三 23 91
王五 24 55
赵六 23 null 需要这样的结果:
NAME AGE SCORE
张三 23 100
NULL 23 99
NULL 23 91
王五 24 55
赵六 23 NULL
WITH a1 (NAME,AGE,SCORE) AS
(
SELECT '张三',23,100 UNION ALL
SELECT '张三',23,99 UNION ALL
SELECT '张三',23,91 UNION ALL
SELECT '王五',24,55 UNION ALL
SELECT '赵六',23,null
)
SELECT CASE WHEN ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY GETDATE())>1 THEN NULL ELSE NAME END NAME,AGE,SCORE
FROM a1
CREATE TABLE #ta (NAME VARCHAR(8),AGE INT ,SCORE INT,ID INT IDENTITY(1,1))
go
INSERT INTO #ta (NAME,AGE,SCORE)
SELECT '张三',23,100 UNION ALL
SELECT '张三',23,99 UNION ALL
SELECT '张三',23,91 UNION ALL
SELECT '王五',24,55 UNION ALL
SELECT '赵六',23,null
go--查詢
SELECT
CASE
WHEN EXISTS(SELECT 1 FROM #ta WHERE NAME=a.NAME AND ID<a.ID) THEN NULL
ELSE NAME
END NAME,AGE,SCORE
FROM #ta a
ORDER BY id
,A.*
INTO #Temp
FROM (
SELECT ( SELECT COUNT(*)
FROM a1 p1
WHERE p1.NAME <= p2.NAME) AS ID2
,*
FROM a1 p2
) AS A
SELECT CASE WHEN EXISTS(SELECT 1 FROM #Temp WHERE ID2=T1.ID2 AND ID1<T1.ID1) THEN NULL
ELSE T1.Name END
,T1.AGE
,T1.SCORE
FROM #Temp AS T1