SQL筛选每个订单(orderId)的时间(createTime)最早的那条记录
select a.* from orders a where not exists --将orders表命名为a,下面一句又将orders表命名为b;可看做将orders表复制了两份,一份叫a,一份叫b
(select 1 from orders b where b.orderId=a.orderId and b.createTime>a.createTime) --常规写法是select b.orderId from ...,这里写成select 1 可以减少系统开销,提升运行效率
/*
注意最终结果是从a中select的,所以not exists条件里是b.createTime>a.createTime,
即当“a和b的orderId 相同时,b的createTime大于a的createTime”这种情况不存在,才保留a中的记录
*/
- 原理:
首先,需要明确、最外层的select是从表a中筛选,因此最后的结果是表a中筛出的数据。(后面的not exists 条件与这个大前提有关)
- not exists
将主查询的数据,放到子查询中做条件验证,根据验证结果(TRUE 或 FALSE)来决定主查询的数据结果是否得以保留。
exists : 强调的是是否返回结果集,不要求知道返回什么, 只要exists引导的子句有结果集返回,那么exists这个条件就算成立了
not exists:只要not exists引导的子句无结果返回,那么此 not exists 条件成立,符合条件的该条数据得以保留
exists(sql 返回结果集为真)
not exists (sql 不返回结果集为真)
- select 1
select 1常搭配exists 当做条件使用,这里的select和 select 任何一列 、select * 的功能相同。select 1中的1是常量(当然也可以写成select 0或select 2等等),select 1 from table 得到的结果每一行都是1,结果行数与table行数相等。
因为和not exists搭配使用时,目地是判断是否有存在,所以不需要返回任何字段信息。写select 1比返回字段信息效率更高。
如果想同时用 where not exists 和 where... in ... ,
需要嵌套select,将 select... where ... in... 的结果看做一个新表,再从这个新表中用where not exists 进行select
select * from (
select orderId,createTime,signid from leasecontract where orderId in (11111,22222)
) a
where not exists(select 1 from (
select orderId,createTime,signid from leasecontract where orderId in(11111,22222))
) b
where b.orderId=a.orderId and b.createTime>a.createTime)
order by field(orderId,11111,22222)
/*
上面用到了select嵌套。
将 select orderId,createTime,signid from leasecontract where orderId in (11111,22222) 得到的数据作为一个新表
*/
2018-12-11日更新:
另一种方法(用max()方法选出时间最大的那条,作为一个新表): select * from table a where a.date in (select max(b.date) from table b where b.id=a.id)