mysql - 条件 SQL 选择 : when select has a value select all but the smallest date

Question

查看以下 SQL fiddle :http://sqlfiddle.com/#!2/962496/1

如何选择来自用户 b@b.cn 且 userpk = 2 且 reg = 1 的所有订单，但只有来自 a@a.cn 的两个最新订单，userpk = 1 和 reg = 0。因此查询将显示 userpk = 2 的 3 个订单，但只有 2 个订单(不是来自 userpk = 1 的最早订单 2012-01-01

所以条件是 reg，如果 reg = 0 则忽略一阶

Answer 1

我认为这会给你想要的结果:

select *
from users u
left join another a
  on u.userpk = a.uPK
where
(
  u.userpk = 2
  and u.reg = 1
)
or
(
  u.userpk = 1
  and u.reg = 0
  and a.odate not in (select min(odate)
                      from another a1
                      where u.userpk = a1.uPK)
) 

参见 SQL Fiddle with Demo

不特定于单个用户的版本(如果您有超过 2 个用户):

select *
from users u
left join another a
  on u.userpk = a.uPK
where
(
   u.reg = 1
)
or
(
  u.reg = 0
  and a.odate not in (select min(odate)
                      from another a1
                      where u.userpk = a1.uPK)
) 

参见 SQL Fiddle with Demo