SELECT * FROM ( SELECT *, rid=ROW_NUMBER() OVER(PARTITION BY 分组列 ORDER BY 主键) FROM tb_name ) WHERE rid>2
CREATE TABLE #A (S_NO VARCHAR(10),C_NO VARCHAR(10)) INSERT INTO #A SELECT 10,1 INSERT INTO #A SELECT 10,3 INSERT INTO #A SELECT 10,2 INSERT INTO #A SELECT 20,12 INSERT INTO #A SELECT 20,12 INSERT INTO #A SELECT 30,6 INSERT INTO #A SELECT 30,8 INSERT INTO #A SELECT 30,7 INSERT INTO #A SELECT 30,9 INSERT INTO #A SELECT 40,9---只有一条数据,结果中不显示SELECT IDENTITY(INT,1,1)SORT,* INTO #B FROM #A ORDER BY S_NOSELECT S_NO,C_NO FROM #B A WHERE (SELECT COUNT(*) FROM #B WHERE S_NO=A.S_NO AND SORT<=A.SORT )>1DROP TABLE #A,#B/*** 10 3 10 2 20 12 30 8 30 7 30 9
CREATE TABLE #A (S_NO VARCHAR(10),C_NO VARCHAR(10)) INSERT INTO #A SELECT 10,1 INSERT INTO #A SELECT 10,3 INSERT INTO #A SELECT 10,2 INSERT INTO #A SELECT 20,12 INSERT INTO #A SELECT 20,12 INSERT INTO #A SELECT 30,6 INSERT INTO #A SELECT 30,8 INSERT INTO #A SELECT 30,7 INSERT INTO #A SELECT 30,9 INSERT INTO #A SELECT 40,9---只有一条数据,结果中不显示 SELECT IDENTITY(INT,1,1)SORT,* INTO #B FROM #A ORDER BY S_NO SELECT S_NO,C_NO FROM #B A WHERE (SELECT COUNT(*) FROM #B WHERE S_NO=A.S_NO AND SORT <=A.SORT )>1 DROP TABLE #A,#B /*** 10 3 10 2 20 12 30 8 30 7 30 9
CREATE TABLE #A (S_NO VARCHAR(10),C_NO VARCHAR(10)) INSERT INTO #A SELECT 10,1 INSERT INTO #A SELECT 10,3 INSERT INTO #A SELECT 10,2 INSERT INTO #A SELECT 20,12 INSERT INTO #A SELECT 20,12 INSERT INTO #A SELECT 30,6 INSERT INTO #A SELECT 30,8 INSERT INTO #A SELECT 30,7 INSERT INTO #A SELECT 30,9 INSERT INTO #A SELECT 40,9CREATE TABLE #B (S_NO VARCHAR(10),C_NO VARCHAR(10)) INSERT INTO #B SELECT 10,1 INSERT INTO #B SELECT 10,3 INSERT INTO #B SELECT 10,2 INSERT INTO #B SELECT 20,12/***#A有两条重复的,#B中有一条,这样结果中不会出现***/ SELECT * FROM #A A WHERE NOT EXISTS (SELECT * FROM #B WHERE S_NO=A.S_NO AND C_NO=A.C_NO)DROP TABLE #A,#B
FROM
(
SELECT *,
rid=ROW_NUMBER()
OVER(PARTITION BY 分组列 ORDER BY 主键)
FROM tb_name
)
WHERE rid>2
INSERT INTO #A SELECT 10,1
INSERT INTO #A SELECT 10,3
INSERT INTO #A SELECT 10,2
INSERT INTO #A SELECT 20,12
INSERT INTO #A SELECT 20,12
INSERT INTO #A SELECT 30,6
INSERT INTO #A SELECT 30,8
INSERT INTO #A SELECT 30,7
INSERT INTO #A SELECT 30,9
INSERT INTO #A SELECT 40,9---只有一条数据,结果中不显示SELECT IDENTITY(INT,1,1)SORT,* INTO #B FROM #A ORDER BY S_NOSELECT S_NO,C_NO FROM #B A
WHERE (SELECT COUNT(*) FROM #B WHERE S_NO=A.S_NO AND SORT<=A.SORT )>1DROP TABLE #A,#B/***
10 3
10 2
20 12
30 8
30 7
30 9
INSERT INTO #A SELECT 10,1
INSERT INTO #A SELECT 10,3
INSERT INTO #A SELECT 10,2
INSERT INTO #A SELECT 20,12
INSERT INTO #A SELECT 20,12
INSERT INTO #A SELECT 30,6
INSERT INTO #A SELECT 30,8
INSERT INTO #A SELECT 30,7
INSERT INTO #A SELECT 30,9
INSERT INTO #A SELECT 40,9---只有一条数据,结果中不显示 SELECT IDENTITY(INT,1,1)SORT,* INTO #B FROM #A ORDER BY S_NO SELECT S_NO,C_NO FROM #B A
WHERE (SELECT COUNT(*) FROM #B WHERE S_NO=A.S_NO AND SORT <=A.SORT )>1 DROP TABLE #A,#B
/***
10 3
10 2
20 12
30 8
30 7
30 9
我现在有A表和B表,A表内包含B表内所有的记录,现在我想查出A表中没有在B表出现的记录
INSERT INTO #A SELECT 10,1
INSERT INTO #A SELECT 10,3
INSERT INTO #A SELECT 10,2
INSERT INTO #A SELECT 20,12
INSERT INTO #A SELECT 20,12
INSERT INTO #A SELECT 30,6
INSERT INTO #A SELECT 30,8
INSERT INTO #A SELECT 30,7
INSERT INTO #A SELECT 30,9
INSERT INTO #A SELECT 40,9CREATE TABLE #B (S_NO VARCHAR(10),C_NO VARCHAR(10))
INSERT INTO #B SELECT 10,1
INSERT INTO #B SELECT 10,3
INSERT INTO #B SELECT 10,2
INSERT INTO #B SELECT 20,12/***#A有两条重复的,#B中有一条,这样结果中不会出现***/
SELECT * FROM #A A WHERE NOT EXISTS (SELECT * FROM #B WHERE S_NO=A.S_NO AND C_NO=A.C_NO)DROP TABLE #A,#B