/**/
/* Formatted on 2008/05/31 12:29 (Formatter Plus v4.8.8) */
MERGE INTO copy_emp a
USING employees b
ON (b.employee_id = a.employee_id)
WHEN MATCHED THEN
UPDATE
SET a.employee_id = b.employee_id, a.first_name = b.first_name,
a.last_name = b.last_name, a.email = b.email,
a.phone_number = b.phone_number, a.hire_date = b.hire_date,
a.job_id = b.job_id, a.salary = b.salary,
a.commission_pct = b.commission_pct, a.manager_id = b.manager_id,
a.department_id = b.department_id
WHEN NOT MATCHED THEN
INSERT
VALUES (b.employee_id, b.first_name, b.last_name, b.email,
b.phone_number, b.hire_date, b.job_id, b.salary,
b.commission_pct, b.manager_id, b.department_id);
MERGE INTO copy_emp a
USING employees b
ON (b.employee_id = a.employee_id)
WHEN MATCHED THEN
UPDATE
SET a.employee_id = b.employee_id, a.first_name = b.first_name,
a.last_name = b.last_name, a.email = b.email,
a.phone_number = b.phone_number, a.hire_date = b.hire_date,
a.job_id = b.job_id, a.salary = b.salary,
a.commission_pct = b.commission_pct, a.manager_id = b.manager_id,
a.department_id = b.department_id
WHEN NOT MATCHED THEN
INSERT
VALUES (b.employee_id, b.first_name, b.last_name, b.email,
b.phone_number, b.hire_date, b.job_id, b.salary,
b.commission_pct, b.manager_id, b.department_id);
故事就这样发生了:
1 .删除重复记录
查出重复记录
SQL > select rowid,bm,mc from a where a.rowid != ( select max (rowid) from a b where a.bm = b.bm and a.mc = b.mc);
删除重复记录
SQL > delete from a a where a.rowid != ( select max (rowid) from a b where a.bm = b.bm and a.mc = b.mc);
1 .删除重复记录
查出重复记录
SQL > select rowid,bm,mc from a where a.rowid != ( select max (rowid) from a b where a.bm = b.bm and a.mc = b.mc);
删除重复记录
SQL > delete from a a where a.rowid != ( select max (rowid) from a b where a.bm = b.bm and a.mc = b.mc);
查找重复记录2:
select
column
from
table
group
by
clolumn
having
count
(
*
)
>
1
关于 FROM a,b 和 a LEFT JOIN b 的区别
给个通俗的解释吧.
例表a
aid adate
1 a1
2 a2
3 a3
表b
bid bdate
1 b1
2 b2
4 b4
两个表a,b相连接,要取出id相同的字段
select * from a ,b where a.aid = b.bid这是仅取出匹配的数据.
此时的取出的是:
1 a1 b1
2 a2 b2
那么left join 指:
select * from a left join b on a.aid = b.bid
首先取出a表中所有数据,然后再加上与a,b匹配的的数据
此时的取出的是:
1 a1 b1
2 a2 b2
3 a3 空字符
同样的也有right join
指的是首先取出b表中所有数据,然后再加上与a,b匹配的的数据
此时的取出的是:
1 a1 b1
2 a2 b2
4 空字符 b4
left outer join=left join
给个通俗的解释吧.
例表a
aid adate
1 a1
2 a2
3 a3
表b
bid bdate
1 b1
2 b2
4 b4
两个表a,b相连接,要取出id相同的字段
select * from a ,b where a.aid = b.bid这是仅取出匹配的数据.
此时的取出的是:
1 a1 b1
2 a2 b2
那么left join 指:
select * from a left join b on a.aid = b.bid
首先取出a表中所有数据,然后再加上与a,b匹配的的数据
此时的取出的是:
1 a1 b1
2 a2 b2
3 a3 空字符
同样的也有right join
指的是首先取出b表中所有数据,然后再加上与a,b匹配的的数据
此时的取出的是:
1 a1 b1
2 a2 b2
4 空字符 b4
left outer join=left join