如何在同一查询中为所有人选择并获取 COUNT？

Question

我有一个像这样组装的查询（使用 Django 后端）：sql = 'SELECT * FROM `table` WHERE'if condition1:    sql += ' `col1` = "foo" OR'if condition2:    sql += ' `col2` = "bar" OR'...if conditionN:    sql += ' `colN` = "foobar" OR'sql = sql[:-2] //to remove the extra 'OR'sql += 'ORDER BY `col1` LIMIT x OFFSET y'print(sql) //'SELECT * FROM `table` WHERE `col1` = "foo" OR `col2` = "bar" ... OR `colN` == "foobar" ORDER BY `col1` LIMIT x OFFSET y'我需要一个查询来获取一个COUNT(*)对所有的行table，但仍SELECT从与条件和数据库LIMIT从上面我尝试做这样的事情：SELECT t1.*, IFNULL(COUNT(t2.*), 0) AS childCount FROM `table` AS t1 LEFT JOIN `table` AS t2WHERE `col1` = "foo" OR `col2` = 'bar' OR `colN` = 'foobar' ORDER BY `col1` desc LIMIT 10;如果您还不知道，我对上面查询中发生的事情感到非常困惑，并且它不起作用（显然）。更新该表如下所示：+--------+----------+|  col1  |   col2   | +--------+----------+|   a    |   foo    |      |   b    |   foo    |      |   c    |   bar    |      |   d    |   bar    |     |   e    |   foo    | +--------+----------+我需要的示例查询：'SELECT *, COUNT(*) as childCount FROM `table` WHERE `col2` = "foo" LIMIT 1 OFFSET 0'它需要返回这个：("a", "foo") //because of the 'LIMIT 1 OFFSET 0' childCount = 3 //because there is 3 columns 'WHERE `col2` = "foo"'建议/解释真的很感激:)

Answer 1

根据您更新的问题

你可以

select col1,col2,count from (

select col2,count(col2) as count from table1 group by col2 order by col1)a

order by col1

limit 1

offset 0

Answer 2

国际大学联盟

SELECT a.col1, a.col2, b.childCount

FROM table a

JOIN (

  SELECT c.col2, COUNT(1)

  FROM table c

  GROUP BY 1

  ORDER BY 2 DESC) b

ON a.col2 = b.col2

LIMIT 1

OFFSET 0