利用select into 做一个临时表
34> CREATE TABLE works_on (emp_no INTEGER NOT NULL,
35> project_no CHAR(4) NOT NULL,
36> job CHAR (15) NULL,
37> enter_date DATETIME NULL)
38>
39> insert into works_on values (1, 'p1', 'analyst', '1997.10.1')
40> insert into works_on values (1, 'p3', 'manager', '1999.1.1')
41> insert into works_on values (2, 'p2', 'clerk', '1998.2.15')
42> insert into works_on values (2, 'p2', NULL, '1998.6.1')
43> insert into works_on values (3, 'p2', NULL, '1997.12.15')
44> insert into works_on values (4, 'p3', 'analyst', '1998.10.15')
45> insert into works_on values (5, 'p1', 'manager', '1998.4.15')
46> insert into works_on values (6, 'p1', NULL, '1998.8.1')
47> insert into works_on values (7, 'p2', 'clerk', '1999.2.1')
48> insert into works_on values (8, 'p3', 'clerk', '1997.11.15')
49> insert into works_on values (7, 'p1', 'clerk', '1998.1.4')
50> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1> select * from works_on
2> GO
emp_no project_no job enter_date
----------- ---------- --------------- -----------------------
1 p1 analyst 1997-10-01 00:00:00.000
1 p3 manager 1999-01-01 00:00:00.000
2 p2 clerk 1998-02-15 00:00:00.000
2 p2 NULL 1998-06-01 00:00:00.000
3 p2 NULL 1997-12-15 00:00:00.000
4 p3 analyst 1998-10-15 00:00:00.000
5 p1 manager 1998-04-15 00:00:00.000
6 p1 NULL 1998-08-01 00:00:00.000
7 p2 clerk 1999-02-01 00:00:00.000
8 p3 clerk 1997-11-15 00:00:00.000
7 p1 clerk 1998-01-04 00:00:00.000
(11 rows affected)
1>
2> -- Remove duplicate data and create a unique index
3>
4> SELECT emp_no, MAX(enter_date) max_date
5> INTO #works_on
6> FROM works_on
7> GROUP BY emp_no
8> HAVING COUNT(*) > 1
9> GO
(3 rows affected)
1> DELETE works_on FROM works_on, #works_on
2> WHERE works_on.emp_no = #works_on.emp_no
3> AND works_on.enter_date < #works_on.max_date
4> GO
(3 rows affected)
1> select * from works_on
2> GO
emp_no project_no job enter_date
----------- ---------- --------------- -----------------------
1 p3 manager 1999-01-01 00:00:00.000
2 p2 NULL 1998-06-01 00:00:00.000
3 p2 NULL 1997-12-15 00:00:00.000
4 p3 analyst 1998-10-15 00:00:00.000
5 p1 manager 1998-04-15 00:00:00.000
6 p1 NULL 1998-08-01 00:00:00.000
7 p2 clerk 1999-02-01 00:00:00.000
8 p3 clerk 1997-11-15 00:00:00.000
(8 rows affected)
注间：select into必须两个表的结果完整一致能也很好的把数据完整备份哦。