如何使用mysql单一查询获取总计数的数据 [英] How to fetch data with total count using mysql single query

问题描述

我想编写一个mysql查询来获取我所有的表数据并记录总数。

I want to write one mysql query to fetch all my table data and with total records count.

例如这是我的桌子

ID     Name    typeId
1      test1   1
2      test2   1
3      test3   2

如果我要获取ID为1的数据，则需要这样的结果

If I am going to fetch data with type id 1 I need a result like this

Name    count 
test1   2
test2   2

如何为这种结果编写sql。
我不要子查询。我想不带子查询
来获取数据。

How can I write sql for this kind of a result. I dont want subquery. I want to fetch data with out subquery Please help me.

谢谢

推荐答案

看看您想要的结果，这是一个奇怪的要求，因为您希望全部计数（不是每个名称）。您可以为此使用子查询：

Looking at your desired result this is a bizarre requirement as you want all count(not for each Name). You can use sub-query for that:

SELECT `Name`, (SELECT COUNT(*) FROM myTable) as `COUNT`
FROM myTable

或者如果要对每个计数试试这个：

Or if you want count for each Name try this:

SELECT `Name`, COUNT(*) AS `COUNT` 
FROM myTable 
GROUP BY `Name`;

>编辑：

更新您的问题后，您的查询应如下所示：

As you have updated your question your query should be like this:

SELECT `Name`,
(SELECT COUNT(*) FROM myTable WHERE TypeId = 1 GROUP BY TypeId) as `COUNT`
FROM myTable WHERE TypeId = 1;

这篇关于如何使用mysql单一查询获取总计数的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！