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=utf8insert into Person_182(Email) values ('[e-mail]');insert into Person_182(Email) values ('[e-mail]');insert into Person_182(Email) values ('[e-mail]');insert into Person_182(Email) values ('[e-mail]');insert into Person_182(Email) values ('[e-mail]');insert into Person_182(Email) values ('[e-mail]');select * from Person_182 order by Email;

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

SELECT Email FROM Person_182GROUP BY Email HAVING COUNT(*) > 1;SELECT Email FROM Person_182GROUP BY Email HAVING COUNT(Id) > 1;SELECT Email FROM Person_182GROUP BY Email HAVING COUNT(Email) > 1;SELECT distinct a.EmailFROM Person_182 a,Person_182 bWHERE a.Id > b.IdAND a.Email=b.Email#Use self joinSELECT 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=utf8insert 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 OrdersGROUP BY Customer

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

SELECT Customer,SUM(OrderPrice) FROM OrdersGROUP BY CustomerHAVING SUM(OrderPrice)<2000

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

SELECT Customer,SUM(OrderPrice) FROM OrdersWHERE Customer='Bush' OR Customer='Adams'GROUP BY CustomerHAVING 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 MYTABLEORDER BY AFIELD LIMIT offset, recnum其中offset为从第几条（M+1）记录开始，recnum为返回的记录条数。例： select * from mytableorder 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))