leetcode182. Duplicate Emails

182. Duplicate Emails
1。建表

create table if not exists Person_182 (
    Id int(10) not null auto_increment,
    Email varchar(20) default null,
    primary key(Id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8

insert into Person_182(Email) values ('a@b.com');
insert into Person_182(Email) values ('a@b.com');
insert into Person_182(Email) values ('a@b.com');
insert into Person_182(Email) values ('c@d.com');
insert into Person_182(Email) values ('c@d.com');
insert into Person_182(Email) values ('d@e.com');

select * from Person_182 order by Email;

2。答案
说明：提交时需要将Person_182修改为Person

SELECT Email 
FROM Person_182
GROUP BY Email 
HAVING COUNT(*) > 1;

SELECT Email 
FROM Person_182
GROUP BY Email 
HAVING COUNT(Id) > 1;

SELECT Email 
FROM Person_182
GROUP BY Email 
HAVING COUNT(Email) > 1;

SELECT distinct a.Email
FROM Person_182 a,Person_182 b
WHERE a.Id > b.Id
AND a.Email=b.Email

#Use self join
SELECT DISTINCT a.Email
 FROM Person_182 a JOIN Person_182 b
 ON (a.Email = b.Email)
 WHERE a.Id <> b.Id

 SELECT DISTINCT a.Email
 FROM Person_182 a
 WHERE EXISTS(
     SELECT 1
     FROM Person_182 b
     WHERE a.Email = b.Email
     LIMIT 1, 1
 )

练习

建立熟悉的订单表

create table if not exists Orders (
    O_Id int(10) not null auto_increment,
    OrderDate date default null,
    OrderPrice int(20),
    Customer varchar(20) default null,
    primary key(O_Id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8

insert into Orders(OrderDate, OrderPrice, Customer) values ('2008/12/29', 1000, 'Bush');
insert into Orders(OrderDate, OrderPrice, Customer) values ('2008/12/29', 1600, 'Carter');
insert into Orders(OrderDate, OrderPrice, Customer) values ('2008/12/29', 700, 'Bush');
insert into Orders(OrderDate, OrderPrice, Customer) values ('2008/12/29', 300, 'Bush');
insert into Orders(OrderDate, OrderPrice, Customer) values ('2008/12/29', 2000, 'Adams');
insert into Orders(OrderDate, OrderPrice, Customer) values ('2008/12/29', 100, 'Carter');

select * from Orders order by Customer;

需求1：我们希望查找每个客户的总金额（总订单）。我们想要使用 GROUP BY 语句对客户进行组合。

SELECT Customer,SUM(OrderPrice) 
FROM Orders
GROUP BY Customer

需求2：我们希望查找订单总金额少于 2000 的客户。
在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与合计函数一起使用。

SELECT Customer,SUM(OrderPrice) 
FROM Orders
GROUP BY Customer
HAVING SUM(OrderPrice)<2000

需求3：希望查找客户 “Bush” 或 “Adams” 拥有超过 1500 的订单总金额。
我们在 SQL 语句中增加了一个普通的 WHERE 子句：

SELECT Customer,SUM(OrderPrice) 
FROM Orders
WHERE Customer='Bush' OR Customer='Adams'
GROUP BY Customer
HAVING SUM(OrderPrice)>1500

需求4：将Orders表按照OrderPrice降序（升序）后选择前5个元素？

select * from Orders order by OrderPrice asc limit 5;
select * from Orders order by OrderPrice desc limit 5;

需求5：将将Orders表按照OrderPrice降序后从第3条记录开始选择4条记录？

select * from Orders order by OrderPrice asc limit 2, 4

解释：
我们知道，在ms sql server中或access中，
若要查询前10条记录，使用top 10即可，
但在mysql中不支持这个写法，它用limit 10。
我们可以利用MySQL中SELECT支持的一个子句——LIMIT——来完成这项功能。
LIMIT可以实现top N查询，也可以实现M至N（某一段）的记录查询，具体语法如下：

SELECT * FROM MYTABLE
ORDER BY AFIELD 
LIMIT offset, recnum
其中offset为从第几条（M+1）记录开始，recnum为返回的记录条数。例： 
select * from mytable
order by afield 
limit 2, 5 
即意为从第3条记录开始的5条记录。

参考文章

创建默认的日期时间类型。
OrderDate datetime NOT NULL DEFAULT NOW(),

CREATE TABLE OrdersTestxx
(
    OrderId int NOT NULL auto_increment,
    ProductName varchar(50) NOT NULL,
    OrderDate datetime NOT NULL DEFAULT NOW(),
    PRIMARY KEY (OrderId)
)