Mysql条件大小写查询-找出每个条件返回多少行 - Mysql conditional case query - finding out how many rows each condition returned

Mysql conditional case query - finding out how many rows each condition returned

请看下面这个mysql条件查询:

SELECT attr1,attr2,attr3 FROM table 
WHERE 
( attr1 LIKE '%cond1%' OR attr1 LIKE '%cond2%' ) 
OR 
( attr2 BETWEEN '30' AND '45' ) 
OR 
( attr3 IN ('cond3') AND attr3 = 'cond4' AND attr3 BETWEEN '18' AND '30' ) 
ORDER BY CASE 
WHEN ( attr1 LIKE '%cond1%' OR attr1 LIKE '%cond2%' ) THEN 1 
WHEN ( attr2 BETWEEN '30' AND '45' ) THEN 2 
ELSE 3 
END

是否有一种方法，我可以得到mysql和php的行数每个条件返回?或者至少是条件3?

我需要一种方法来知道条件3返回200行，例如，并将此值存储到php变量中。

您可以使用CASE WHEN ... THEN ... ELSE ... END与SUM(或COUNT使用NULL作为ELSE的结果)作为SELECT的表达式。使用上面给出的例子，你可以这样做:

SELECT
    SUM(CASE WHEN attr1 LIKE '%cond1%' OR attr1 LIKE '%cond2%' THEN 1 ELSE 0 END) AS condition1count,
    SUM(CASE WHEN attr2 BETWEEN '30' AND '45' THEN 1 ELSE 0 END) AS condition2count,
    SUM(CASE WHEN attr3 IN ('cond3') AND attr3 = 'cond4' AND attr3 BETWEEN '18' AND '30' THEN 1 ELSE 0 END) AS condition3count
FROM
    table
...

但是，请记住，根据数据的内容，某些行可能匹配3个条件中的多个，因此3个计数的总和可能大于返回的行数。