没有1个条件的结果数

There is complicated MySql SELECT query:

有复杂的MySql SELECT查询:

SELECT id, login, date...
FROM tab1
  LEFT JOIN tab2
  LEFT JOIN tab3
  LEFT JOIN tab4
  ...
WHERE
 condition1 = value
 condition2 = value
 ...
ORDER ...
LIMIT ...

I need to get the number of results from same select but without condition1 and limit. What is the best solution? Is there better solution than just make 2 SELECT query?

我需要从相同的select中获取结果数,但没有condition1和limit。什么是最好的解决方案?有没有比只做2 SELECT查询更好的解决方案?

2 个解决方案

#1

Pass condition1 in as a parameter and use boolean logic to include it or exclude it:

将condition1作为参数传递,并使用布尔逻辑将其包含或排除:

...
WHERE
condition1 = value OR condition1 IS NULL

If you don't want to use condition1 set it to null or pass in a null parameter. This ensures that the SELECT doesnt care about the first condition. It then evaluates to:

如果您不想使用condition1,请将其设置为null或传入null参数。这确保SELECT不关心第一个条件。然后评估为:

...
WHERE
FALSE OR TRUE

And boolean logic states when one value is true the result is true. If you need condition1 pass an actual value and the result becomes

当一个值为true时,布尔逻辑表明结果为true。如果你需要condition1传递一个实际值,结果就变成了

...
WHERE
TRUE OR FALSE

This evaluates to TRUE as well and ensures your condition is met because you've filtered on value.

此评估也为TRUE,并确保您的条件得到满足,因为您已过滤了值。

#2

If you need a COUNT of the number of rows you have to perform another query, you cannot return an aggregate result and actual rows in one go.

如果您需要执行另一个查询的行数COUNT,则无法一次性返回聚合结果和实际行。

In your case you are also changing the WHERE clause so it's not possible to count the rows at the client side either.

在您的情况下,您还要更改WHERE子句,因此无法计算客户端的行数。

If you are concerned about performance you need to show the actual query you are using for anyone to be able to give help.

如果您担心性能,则需要显示您正在使用的实际查询,以便任何人能够提供帮助。

#1