SQL2005:;WITH t AS ( SELECT rn=ROW_NUMBER()OVER(ORDER BY GETDATE()),* FROM 表名 ) SELECT * FROM 表名 t WHERE EXISTS( SELECT 1 FROM 表名 WHERE 列名=t.列名 AND rn<>t.rn)
--2005(修改一楼的回复) ;WITH t AS ( SELECT rn=ROW_NUMBER()OVER(ORDER BY GETDATE()),* FROM 表名 ) SELECT * FROM t a WHERE EXISTS( SELECT 1 FROM t WHERE 列名=a.列名 AND rn<>a.rn)
--SQL2000:表有ID直接用ID,没ID增一列ID或用临时表 ALTER TABLE 表名 ADD id INT IDENTITY GO SELECT * FROM 表名 a WHERE EXISTS( SELECT 1 FROM 表名 WHERE 列名=a.列名 AND id<>a.id)
--> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL DROP TABLE [tb] GO CREATE TABLE [tb]([Assetid] INT,[ValueName] NVARCHAR(10),[Value] NVARCHAR(10)) INSERT [tb] SELECT 123,'Width','900' UNION ALL SELECT 123,'Height','900' UNION ALL --这是第二条[Assetid]的重复记录 SELECT 123,'Descrip','image' UNION ALL SELECT 124,'Height','400' UNION ALL SELECT 124,'UPC','14767' UNION ALL --这也是第二条[Assetid]的重复记录 SELECT 124,'GTIN','00147' UNION ALL SELECT 125,'UPC','16842' GO --SELECT * FROM [tb]-->SQL查询如下: ;WITH t AS ( SELECT rn=ROW_NUMBER()OVER(PARTITION BY Assetid ORDER BY GETDATE()),* FROM tb ) SELECT * FROM t a WHERE EXISTS( SELECT 1 FROM t WHERE Assetid=a.Assetid AND rn<>a.rn) AND rn=2 /* rn Assetid ValueName Value -------------------- ----------- ---------- ---------- 2 123 Height 900 2 124 UPC 14767 */
htl258你好 SELECT rn=ROW_NUMBER()OVER(PARTITION BY Assetid ORDER BY GETDATE()),* 能解释下这行是什么意思吗? 我用的是SQL两千
--> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL DROP TABLE [tb] GO CREATE TABLE [tb]([Assetid] INT,[ValueName] NVARCHAR(10),[Value] NVARCHAR(10)) INSERT [tb] SELECT 123,'Width','900' UNION ALL SELECT 123,'Height','900' UNION ALL --这是第二条[Assetid]的重复记录 SELECT 123,'Descrip','image' UNION ALL SELECT 124,'Height','400' UNION ALL SELECT 124,'UPC','14767' UNION ALL --这也是第二条[Assetid]的重复记录 SELECT 124,'GTIN','00147' UNION ALL SELECT 125,'UPC','16842' GO --SELECT * FROM [tb]-->SQL2000查询如下: SELECT rn=IDENTITY(int),* INTO # FROM tb SELECT * FROM # a WHERE EXISTS( SELECT 1 FROM # WHERE Assetid=a.Assetid AND rn<>a.rn) AND (SELECT COUNT(1) FROM # WHERE Assetid=a.Assetid AND rn<=a.rn)=2 /* rn Assetid ValueName Value -------------------- ----------- ---------- ---------- 2 123 Height 900 5 124 UPC 14767 */2000的
IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB GO CREATE TABLE TB(COL1 INT ,COL2 VARCHAR(50)) INSERT INTO TB SELECT 1,'A' UNION ALL SELECT 2,'A' UNION ALL SELECT 3,'B' UNION ALL SELECT 4,'B' UNION ALL SELECT 5,'B' UNION ALL SELECT 6,'C' UNION ALL SELECT 7,'D' --2005用CTE ;WITH MU AS ( SELECT * FROM (SELECT ROW_NUMBER() OVER (PARTITION BY COL2 ORDER BY COL1 ASC) 'ROW',* FROM TB)T WHERE ROW IN (1,2)) SELECT COL1,COL2 FROM MU T1 WHERE NOT EXISTS(SELECT 1 FROM MU WHERE COL2=T1.COL2 AND ROW>T1.ROW) /* 2 A 4 B 6 C 7 D */ --2000用临时表 IF OBJECT_ID('TEMPDB..#TEMP') IS NOT NULL DROP TABLE TEMPDB..#TEMP SELECT * INTO #TEMP FROM (SELECT ROW_NUMBER() OVER (PARTITION BY COL2 ORDER BY COL1 ASC) 'ROW',* FROM TB)T WHERE ROW IN (1,2) SELECT COL1,COL2 FROM #TEMP T1 WHERE NOT EXISTS(SELECT 1 FROM #TEMP WHERE COL2=T1.COL2 AND ROW>T1.ROW) DROP TABLE #TEMP /* 2 A 4 B 6 C 7 D */
(
SELECT rn=ROW_NUMBER()OVER(ORDER BY GETDATE()),*
FROM 表名
)
SELECT *
FROM 表名 t
WHERE EXISTS(
SELECT 1
FROM 表名
WHERE 列名=t.列名
AND rn<>t.rn)
;WITH t AS
(
SELECT rn=ROW_NUMBER()OVER(ORDER BY GETDATE()),*
FROM 表名
)
SELECT *
FROM t a
WHERE EXISTS(
SELECT 1
FROM t
WHERE 列名=a.列名
AND rn<>a.rn)
ALTER TABLE 表名 ADD id INT IDENTITY
GO
SELECT *
FROM 表名 a
WHERE EXISTS(
SELECT 1
FROM 表名
WHERE 列名=a.列名
AND id<>a.id)
DROP TABLE [tb]
GO
CREATE TABLE [tb]([Assetid] INT,[ValueName] NVARCHAR(10),[Value] NVARCHAR(10))
INSERT [tb]
SELECT 123,'Width','900' UNION ALL
SELECT 123,'Height','900' UNION ALL --这是第二条[Assetid]的重复记录
SELECT 123,'Descrip','image' UNION ALL
SELECT 124,'Height','400' UNION ALL
SELECT 124,'UPC','14767' UNION ALL --这也是第二条[Assetid]的重复记录
SELECT 124,'GTIN','00147' UNION ALL
SELECT 125,'UPC','16842'
GO
--SELECT * FROM [tb]-->SQL查询如下:
;WITH t AS
(
SELECT rn=ROW_NUMBER()OVER(PARTITION BY Assetid ORDER BY GETDATE()),*
FROM tb
)
SELECT *
FROM t a
WHERE EXISTS(
SELECT 1
FROM t
WHERE Assetid=a.Assetid
AND rn<>a.rn)
AND rn=2
/*
rn Assetid ValueName Value
-------------------- ----------- ---------- ----------
2 123 Height 900
2 124 UPC 14767
*/
SELECT rn=ROW_NUMBER()OVER(PARTITION BY Assetid ORDER BY GETDATE()),*
能解释下这行是什么意思吗?
我用的是SQL两千
DROP TABLE [tb]
GO
CREATE TABLE [tb]([Assetid] INT,[ValueName] NVARCHAR(10),[Value] NVARCHAR(10))
INSERT [tb]
SELECT 123,'Width','900' UNION ALL
SELECT 123,'Height','900' UNION ALL --这是第二条[Assetid]的重复记录
SELECT 123,'Descrip','image' UNION ALL
SELECT 124,'Height','400' UNION ALL
SELECT 124,'UPC','14767' UNION ALL --这也是第二条[Assetid]的重复记录
SELECT 124,'GTIN','00147' UNION ALL
SELECT 125,'UPC','16842'
GO
--SELECT * FROM [tb]-->SQL2000查询如下:
SELECT rn=IDENTITY(int),* INTO # FROM tb
SELECT *
FROM # a
WHERE EXISTS(
SELECT 1
FROM #
WHERE Assetid=a.Assetid
AND rn<>a.rn)
AND (SELECT COUNT(1) FROM # WHERE Assetid=a.Assetid AND rn<=a.rn)=2
/*
rn Assetid ValueName Value
-------------------- ----------- ---------- ----------
2 123 Height 900
5 124 UPC 14767
*/2000的
GO
CREATE TABLE TB(COL1 INT ,COL2 VARCHAR(50))
INSERT INTO TB
SELECT 1,'A' UNION ALL
SELECT 2,'A' UNION ALL
SELECT 3,'B' UNION ALL
SELECT 4,'B' UNION ALL
SELECT 5,'B' UNION ALL
SELECT 6,'C' UNION ALL
SELECT 7,'D'
--2005用CTE
;WITH MU AS (
SELECT * FROM (SELECT ROW_NUMBER() OVER (PARTITION BY COL2 ORDER BY COL1 ASC) 'ROW',* FROM TB)T WHERE ROW IN (1,2))
SELECT COL1,COL2 FROM MU T1 WHERE NOT EXISTS(SELECT 1 FROM MU WHERE COL2=T1.COL2 AND ROW>T1.ROW)
/*
2 A
4 B
6 C
7 D
*/
--2000用临时表
IF OBJECT_ID('TEMPDB..#TEMP') IS NOT NULL DROP TABLE TEMPDB..#TEMP
SELECT * INTO #TEMP FROM (SELECT ROW_NUMBER() OVER (PARTITION BY COL2 ORDER BY COL1 ASC) 'ROW',* FROM TB)T WHERE ROW IN (1,2)
SELECT COL1,COL2 FROM #TEMP T1 WHERE NOT EXISTS(SELECT 1 FROM #TEMP WHERE COL2=T1.COL2 AND ROW>T1.ROW)
DROP TABLE #TEMP
/*
2 A
4 B
6 C
7 D
*/
谢谢拉!!
亲一个~
谢谢楼上各位