【面经】Epic: 数据库去重

题目是：有2个10G的数据库，存储了一些string. 2者之间有一些重复的数据。请把它们合并为一个数据库，并且去除重复。

限制：内存是4G

例如: DB1: cmu, ucb, stanford, nyu

        DB2: ucsb, ucb, ucsd, cmu.

两者合并后，应该是: DB: cmu, ucb, stanford, nyu, ucsb, ucsd.

作法：把DB1分为5个小的数据库，分别是DB11, DB12, DB13, DB14, DB15

        把DB2分为5个小的数据库，分别是DB22, DB22, DB23, DB24, DB25

把DB11 与 DB22, DB22, DB23, DB24, DB25 分别进行Union操作，生成DB11Merge.

把DB12 与 DB22, DB22, DB23, DB24, DB25 分别进行Union操作，生成DB12Merge.

......

最后再把DB11Merge, DB12Merge, DB13Merge, DB14Merge, DB15Merge 合并在一起即可

用以下语句即可:

mysql> insert into merge select * from persons2;

1. How do I merge two tables in Access while removing duplicates?

ref: http://stackoverflow.com/questions/7615587/how-do-i-merge-two-tables-in-access-while-removing-duplicates

以下是实验结果：

A UNION query returns only distinct rows. (There is also UNION ALL, but that would include duplicate rows, so you don't want it here.)

mysql> select * from persons2;                                                  +-----------+

| FirstName |

+-----------+

| zelin     |

| qihao     |

+-----------+

2 rows in set (0.00 sec)

 

mysql> select * from persons;

+-----------+

| FirstName |

+-----------+

| yu        |

| zhixu     |

| zelin     |

+-----------+

3 rows in set (0.00 sec)

 

mysql> 

mysql> select * from persons union select * from persons2;

+-----------+

| FirstName |

+-----------+

| yu        |

| zhixu     |

| zelin     |

| qihao     |

+-----------+

4 rows in set (0.00 sec)

 

2. Join

顺便介绍几个DB常用的merge用的语句：

http://www.w3schools.com/sql/sql_join.asp

An SQL JOIN clause is used to combine rows from two or more tables, based on a common field between them.

The most common type of join is: SQL INNER JOIN (simple join). An SQL INNER JOIN return all rows from multiple tables where the join condition is met.

Let's look at a selection from the "Orders" table:

OrderID	CustomerID	OrderDate
10308	2	1996-09-18
10309	37	1996-09-19
10310	77	1996-09-20

Then, have a look at a selection from the "Customers" table:

CustomerID	CustomerName	ContactName	Country
1	Alfreds Futterkiste	Maria Anders	Germany
2	Ana Trujillo Emparedados y helados	Ana Trujillo	Mexico
3	Antonio Moreno Taquería	Antonio Moreno	Mexico

Notice that the "CustomerID" column in the "Orders" table refers to the "CustomerID" in the "Customers" table. The relationship between the two tables above is the "CustomerID" column.

Then, if we run the following SQL statement (that contains an INNER JOIN):

Example

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID=Customers.CustomerID;

Try it yourself »

it will produce something like this:

OrderID	CustomerName	OrderDate
10308	Ana Trujillo Emparedados y helados	9/18/1996
10365	Antonio Moreno Taquería	11/27/1996
10383	Around the Horn	12/16/1996
10355	Around the Horn	11/15/1996
10278	Berglunds snabbköp	8/12/1996

 

---

Different SQL JOINs

Before we continue with examples, we will list the types the different SQL JOINs you can use:

• INNER JOIN: Returns all rows when there is at least one match in BOTH tables
• LEFT JOIN: Return all rows from the left table, and the matched rows from the right table
• RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table
• FULL JOIN: Return all rows when there is a match in ONE of the tables

3. Full Join

 在mysql中没有full join语句，我们需要用union:

mysql> SELECT * FROM persons LEFT JOIN persons2 ON persons.firstName=persons2.firstName UNION SELECT * FROM persons RIGHT JOIN persons2 ON persons.firstName=persons2.firstName;

+-----------+-----------+

| FirstName | FirstName |

+-----------+-----------+

| zelin     | zelin     |

| yu        | NULL      |

| zhixu     | NULL      |

| NULL      | qihao     |

+-----------+-----------+

4 rows in set (0.00 sec)

4.  REPLACE Syntax

使用replace语句也可以达到去重的效果。前提是，我们把想要去重的项目设置为primary key即可。

REPLACE [LOW_PRIORITY | DELAYED] [INTO] tbl_name [(col_name,...)] {VALUES | VALUE} ({expr | DEFAULT},...),(...),...

Or:

REPLACE [LOW_PRIORITY | DELAYED]
    [INTO] tbl_name
    SET col_name={expr | DEFAULT}, ...

Or:

REPLACE [LOW_PRIORITY | DELAYED]
    [INTO] tbl_name [(col_name,...)]
    SELECT ...

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for aPRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted. See Section 13.2.5, “INSERT Syntax”.