sql - PHP/MySQL COUNT 似乎没有正常工作

Question

我正在为此烦恼，这个查询有什么问题:

  SELECT COUNT(id), * 
    FROM location 
   WHERE country = '$country' 
     AND LCASE(namenodiacritics) LIKE LCASE('%$locname%') 
ORDER BY name ASC

我可以在单个查询中使用 COUNT(id) 和 * 吗？

我一直收到这个错误:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* FROM location WHERE country = 'AQ' AND LCASE(namenodiacritics) LIKE LCASE('%%'' at line 1

奇怪的是它与“SELECT COUNT(id) FROM...”以及“SELECT * FROM...”一起使用。

Answer 1

在不使用 GROUP BY 的情况下使用 COUNT() 会将结果减少到单行。您无法通过这种方式在其余列中获得有意义的值。

您可以在两个查询中完成:

  -- this returns a single row
  SELECT COUNT(id)
    FROM location 
   WHERE country = '$country' 
     AND LCASE(namenodiacritics) LIKE LCASE('%$locname%');

  -- this returns multiple rows, one per matching location    
  SELECT *
    FROM location 
   WHERE country = '$country' 
     AND LCASE(namenodiacritics) LIKE LCASE('%$locname%')
ORDER BY name ASC;