epic 面经:数据库去重

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.)

技术分享
 1 mysql> select * from persons2;                                                  +-----------+
 2 
 3 | FirstName |
 4 
 5 +-----------+
 6 
 7 | zelin     |
 8 
 9 | qihao     |
10 
11 +-----------+
12 
13 2 rows in set (0.00 sec)
14 
15  
16 
17 mysql> select * from persons;
18 
19 +-----------+
20 
21 | FirstName |
22 
23 +-----------+
24 
25 | yu        |
26 
27 | zhixu     |
28 
29 | zelin     |
30 
31 +-----------+
32 
33 3 rows in set (0.00 sec)
34 
35  
36 
37 mysql> 
38 
39 mysql> select * from persons union select * from persons2;
40 
41 +-----------+
42 
43 | FirstName |
44 
45 +-----------+
46 
47 | yu        |
48 
49 | zhixu     |
50 
51 | zelin     |
52 
53 | qihao     |
54 
55 +-----------+
56 
57 4 rows in set (0.00 sec)
View Code

 

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:

OrderIDCustomerIDOrderDate
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:

CustomerIDCustomerNameContactNameCountry
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:

OrderIDCustomerNameOrderDate
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)

 

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。