我有一個像這樣組裝的查詢（使用 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從與條件和數(shù)據(jù)庫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;如果您還不知道，我對上面查詢中發(fā)生的事情感到非常困惑，并且它不起作用（顯然）。更新該表如下所示：+--------+----------+| 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"'建議/解釋真的很感激:)
2 回答
一只斗牛犬
TA貢獻(xiàn)1784條經(jīng)驗(yàn) 獲得超2個贊
根據(jù)您更新的問題
你可以
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
慕婉清6462132
TA貢獻(xiàn)1804條經(jīng)驗(yàn) 獲得超2個贊
國際大學(xué)聯(lián)盟
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
添加回答
舉報(bào)
0/150
提交
取消