怎么得到这个表的查询（记录运行） [英] how get tihs query for this table(The record run)

问题描述

hi
例如这个表

hi for exmple this table

id        name       code     
 1        test       20
 2        test2      30  
 3        test       20
 4        test3      40
 5        test       20
 6        test       20

i want this result 
 id       name      code       count
  1       test       20          4

推荐答案

使用group by和COUNT。

Use the group by and COUNT.

SELECT MIN(id) AS id, name, code, COUNT(*) AS count_of_name
FROM table
GROUP BY id, name, code

理查德提出了一个我忽略的好点。如果您想要计数，则不能在结果集中包含ID，因为您在计算名称和代码，而不是ID，名称和代码。



我更新了sql，这样你就可以得到每个名字和代码组合的最低ID。



更新：



Kornfeld也提出了一个好点。即使你将ID放在结果集中，你很可能在结果集中实际上不需要它，而且只是UI中行的编号。



然后只需从sql中删除ID，你将拥有：

Richard brought up a good point that I overlooked. You cannot have the ID in your result set if you want the count because you are counting name and code, not id, name, and code.

I updated the sql so that you'll get the lowest ID for each name and code combination.

Update:

Kornfeld also brings up a good point. Even though you put ID in the result set, there's a good chance you don't actually want that in your result set and it's just the numbering of rows in the UI.

Then just remove ID from the sql and you'll have:

SELECT name, code, COUNT(*) AS count_of_name
FROM table
GROUP BY name, code

但是对于我们肯定知道，我们需要听到您的一些反馈。

But for us to know for sure, we need to hear some feedback from you.

您好，这是您的查询。 />

Hi Here is your query.

SELECT MIN(id) AS id, name, code, COUNT(*) AS [Count]
FROM MyTable
GROUP BY  name, code

请标记为答案。



谢谢，

Vipul

Please mark as answer.

Thanks,
Vipul

iam wriet this code
 

SELECT code, COUNT (code)
FROM table GROUP BY code
HAVING COUNT (code)=
(
SELECT MAX (mycount)
FROM (
SELECT code, COUNT (code) mycount
FROM table
GROUP BY code
)tt
)

这篇关于怎么得到这个表的查询（记录运行）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！