mysql - 想要在IN子查询中使用LIMIT,如何解决?
问题描述:
我有一个查询是这样的:mysql - 想要在IN子查询中使用LIMIT,如何解决?
SELECT order_number,dr_amount FROM data WHERE order_number IN (select order_number from data where dr_machine='KBA R-' && dr_code='Tryckning' && dr_amount IS NOT NULL && dr_amount!=0 group by order_number having count(*)<2) && dr_machine='KBA R-' && dr_code='Tryckning' && dr_date>='1381442400' && id>'397433' && order_number!='16952' ORDER BY id limit 1;
的结果是这样的:
+--------------+-----------+
| order_number | dr_amount |
+--------------+-----------+
| 17047 | 1307 |
+--------------+-----------+
这就是结果,我想进一步处理。 现在我想为此添加一个子查询。我需要检查DATA WHERE oi_amount> 0中的order_number(17047)。 所以我尽量让查询此:
SELECT order_number,dr_amount FROM data WHERE order_number IN (select order_number from data where dr_machine='KBA R-' && dr_code='Tryckning' && dr_amount IS NOT NULL && dr_amount!=0 group by order_number having count(*)<2) && dr_machine='KBA R-' && dr_code='Tryckning' && dr_date>='1381442400' && id>'397433' && order_number!='16952' && order_number in (select order_number from data where oi_amount>0) ORDER BY id limit 1;
但是,这给了我:
+--------------+-----------+
| order_number | dr_amount |
+--------------+-----------+
| 17046 | 653 |
+--------------+-----------+
而且这不是我想要的。我想返回没有结果从该查询,因为order_number 17047有oi_amount < = 0(或NULL) 据我所知,当order_number 17047不匹配,然后跳到匹配的下一行,这是order_number 17046. 所以我想与限制1.添加一个子查询像这样:
SELECT order_number,dr_amount FROM data WHERE order_number IN (select order_number from data where dr_machine='KBA R-' && dr_code='Tryckning' && dr_amount IS NOT NULL && dr_amount!=0 group by order_number having count(*)<2) && order_number in (select order_number from data where dr_machine='KBA R-' && dr_code='Tryckning' && dr_date>='1381442400' && id>'397433' && order_number!='16952' ORDER BY id limit 1);
但抛出的错误:
This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
我的表看起来像这样:
+--------+--------------+-------------+------------+------------+-----------+-----------+
| id | order_number | dr_worker | dr_code | dr_machine | dr_amount | oi_amount |
+--------+--------------+-------------+------------+------------+-----------+-----------+
| 332144 | 16952 | NULL | NULL | NULL | NULL | 2000 |
| 397432 | 16952 | Gustafsson, | Intag | KBA R- | 1 | NULL |
| 397433 | 16952 | Gustafsson, | Tryckning | KBA R- | 1307 | NULL |
| 397434 | 17047 | Gustafsson, | Intag | KBA R- | 1 | NULL |
| 397435 | 17047 | Gustafsson, | Tryckning | KBA R- | 1307 | NULL |
+--------+--------------+-------------+------------+------------+-----------+-----------+
正如你所看到的order_number 17047没有记录oi_amount,这就是为什么我想返回没有结果/空结果。
我对如何使用JOIN知之甚少,但也许这就是要走的路?我试过索姆查询,但我不知道这甚至会工作(我所有的问题与JOIN有一些语法错误,所以我放弃了)
问候 尼克拉斯
答
尝试使用join
而不是:
SELECT order_number, dr_amount
FROM data d join
(select order_number
from data
where dr_machine = 'KBA R-' and
dr_code = 'Tryckning' and
dr_amount IS NOT NULL and
dr_amount <> 0
group by order_number
having count(*) < 2
) i
on d.order_number = i.order_number
WHERE d.dr_machine = 'KBA R-' and
d.dr_code = 'Tryckning' and
d.dr_date >= '1381442400' and
d.id > '397433' and
d.order_number <> '16952'
ORDER BY id
limit 1;
请注意,我改变了你的运营商,以符合SQL标准:<>
代替!=
和and
,而不是&&
。另外,如果看起来像数字的常量确实是数字,则不需要单引号。
我想问你在查询那张表的内容?你想总结order_no的每一个dr_amount吗?因为答案很可能对你有所帮助,但是应该有一个更简单的解决方案来解决你的问题 – Najzero 2014-10-07 11:30:19
最后,我正在寻找dr_code = Tryckning && dr_machine = KBA R-的行,并且“dr_amount与下面的行/下一个”。但结果必须也有oi_amount> 0 – Niclas 2014-10-07 11:36:55