问题如何从两个不同的表（称他们为tab1和tab2）中进行select count(*)查询，从而得到如下结果：Count_1 Count_2 123 456我尝试过这个查询：select count(*) Count_1 from schema.tab1 union all select count(*) Count_2 from schema.tab2但最后只得到Count_1 123 456回答1SELECT ( SELECT COUNT(*) FROM tab1 ) AS count1, ( SELECT COUNT(*) FROM tab2 ) AS count2 FROM dual评论为什么你要用dual？那是什么意思？它是一个只有一条记录的伪表。在Oracle中你不能使用不带FROM的SELECT查询。回答2需要补充的是，想要在SQL Server中完成同样的事情，您只需要删除（上一条回答）查询的“FROM dual”部分。回答3只是因为一小点不一样：SELECT 'table_1' AS table_name, COUNT(*) FROM table_1 UNION SELECT 'table_2' AS table_name, COUNT(*) FROM table_2 UNION SELECT 'table_3' AS table_name, COUNT(*) FROM table_3它返回了转置的答案（每个表一行，而不是每个表一列），否则我认为没有太大区别。我认为在性能方面，它们应该是等效的。回答4我只在SQL Server上试过，但你可以这么做：select (select count(*) from table1) as count1, (select count(*) from table2) as count2在SQL Server中，我得到了你想要的结果。回答5另一个稍微有些不同的方法：with t1_count as (select count(*) c1 from t1), t2_count as (select count(*) c2 from t2) select c1, c2 from t1_count, t2_count / select c1, c2 from (select count(*) c1 from t1) t1_count, (select count(*) c2 from t2) t2_count /内容来源于StackOverflow, 遵循 CCBY-SA 4.0 许可协议进行翻译与使用。原文链接：Select count(*) from multiple tables

问题我该如何从 MySQL 中的多个表中选择 COUNT(*)？比如说：SELECT COUNT(*) AS table1Count FROM table1 WHERE someCondition JOIN?? SELECT COUNT(*) AS table2Count FROM table2 WHERE someCondition CROSS JOIN? subqueries? SELECT COUNT(*) AS table3Count FROM table3 WHERE someCondition编辑：我的目标是返回这个结果：+-------------+-------------+-------------+ | table1Count | table2Count | table3Count | +-------------+-------------+-------------+ | 14 | 27 | 0 | +-------------+-------------+-------------+回答1你可以通过使用子查询来实现它，每个tableCount对应一个子查询：SELECT (SELECT COUNT(*) FROM table1 WHERE someCondition) as table1Count, (SELECT COUNT(*) FROM table2 WHERE someCondition) as table2Count, (SELECT COUNT(*) FROM table3 WHERE someCondition) as table3Count回答2你可以使用子查询来做到这一点，比如：select (SELECT COUNT(*) FROM table1 WHERE someCondition) as table1Count, (SELECT COUNT(*) FROM table2 WHERE someCondition) as table2Count 回答3你可以使用联合查询 SELECT COUNT(*) FROM table1 WHERE someCondition UNION SELECT COUNT(*) FROM table2 WHERE someCondition UNION SELECT COUNT(*) FROM table3 WHERE someCondition内容来源于StackOverflow, 遵循 CCBY-SA 4.0 许可协议进行翻译与使用。原文链接：COUNT(*) from multiple tables in MySQL

提问你好，我使用的是django 1.2，然后我想知道如何对原生查询集(RawQuerySet)的行进行计数。惯例的.count()方法行不通。这是我的查询query = "SELECT *, ((ACOS(SIN(%s * PI() / 180) * SIN(lat * PI() / 180) + COS(%s * PI() / 180) * COS(lat * PI() / 180) * COS((%s - lon) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance FROM app_car WHERE price BETWEEN %s AND %s HAVING distance<=%s ORDER BY distance ASC" cars = Car.objects.raw(query, [lat, lat, lon, min_price, max_price, miles]) return HttpResponse( cars )然后它的返回值是Car_Deferred_model_id_user_id object大家有什么想法吗？回答1使用'len()'函数，这会带来：query = "SELECT *, ((ACOS(SIN(%s * PI() / 180) * SIN(lat * PI() / 180) + COS(%s * PI() / 180) * COS(lat * PI() / 180) * COS((%s - lon) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance FROM app_car WHERE price BETWEEN %s AND %s HAVING distance<=%s ORDER BY distance ASC" cars = Car.objects.raw(query, [lat, lat, lon, min_price, max_price, miles]) return HttpResponse(len(list(cars))另外：这里有一些关于Django 1.2 Model.objects.raw（）方法的有用信息: http://djangoadvent.com/1.2/smoothing-curve/ 【看起来该网站可能已经过期，但Internet Archive将其保存在：http://web.archive.org/web/20110513122309/http://djangoadvent.com/1.2/smoothing-curve/ 】内容来源于StackOverflow, 遵循 CCBY-SA 4.0 许可协议进行翻译与使用。原文链接：Django count RawQuerySet

问题我知道如何对不同的表做多个计数，但从没见过如何将他们合起来。我有一个MySQL数据库，在那里我执行以下查询：SELECT characters.name, COUNT(*) AS wiki_unlocks FROM wiki_items INNER JOIN characters ON characters.character_id=wiki_items.character_id GROUP BY wiki_items.character_id ORDER BY wiki_unlocks DESC LIMIT 10;这给我返回了以下结果，看着还不错：name wiki_unlocks player1 2 player2 1我想要得到一个所有'wiki_xxxx'表的加在一起的计数。比如说，我想要得到'wiki_items'(above) + 'wiki_armors' + 'wiki_weapons' + ...感谢大家的帮助！3个回答1. 如果性能方面是一个问题，毕竟表中有很多行数据，我会这么做。首先分组和计数，然后才是连接表。SELECT characters.name, COALESCE(count_unlocks,0) AS unlocks, COALESCE(count_armors,0) AS armors, COALESCE(count_weapons,0) AS weapons, COALESCE(count_unlocks,0) + COALESCE(count_armors,0) + COALESCE(count_weapons,0) AS total FROM characters LEFT JOIN (SELECT wiki_items.character_id, count(*) AS count_unlocks from wiki_items GROUP BY wiki_items.character_id) AS wiki_unlocks ON characters.character_id = wiki_unlocks.character_id LEFT JOIN (SELECT wiki_armors.character_id, count(*) AS count_armors from wiki_armors GROUP BY wiki_armors.character_id) AS wiki_armors ON characters.character_id = wiki_armors.character_id LEFT JOIN (SELECT wiki_weapons.character_id, count(*) AS count_weapons from wiki_weapons GROUP BY wiki_weapons.character_id) AS wiki_weapons ON characters.character_id = wiki_weapons.character_id2. 可能最简单的方法是将每个计数作为子选择：SELECT c.name , (select COUNT(i.character_id) From wiki_items i Where c.character_id=i.character_id ) as wiki_unlocks , (select COUNT(a.character_id) From wiki_armors a Where c.character_id=a.character_id ) as wiki_armors , (select COUNT(w.character_id) From wiki_weapons w Where c.character_id=w.character_id ) as wiki_weapons FROM characters c3. 这或许会有帮助：SELECT Sum( a.count ) FROM( SELECT Count( * ) AS count FROM Table1 UNION ALL SELECT Count( * ) AS count FROM Table2 UNION ALL SELECT Count( * ) AS count FROM Table3 UNION ALL SELECT Count( * ) AS count FROM Table4 ) a内容来源于Database Administrators Stack Exchange, 遵循 CCBY-SA 4.0 许可协议进行翻译与使用。原文链接：Count multiple tables as one count