相似的列出现次数 [英] Number of similar columns occurrences

问题描述

我是SQL SERVER的新手，需要您提供一些建议。
我有一张存储着商店信息的大桌子。这是bench_id列：

I am new to SQL SERVER and need your advise on something. I have a big table with stores information. Here is bench_id column:

**bench_id**
31
51
51
61
61
61
71
71

我创建了另一个带有两列的小表：

I have created another small table with two columns:

**distinct_bench**           **number**
-----------------------------

31                               1
51                               2
61                               3
71                               2

第二个表的NUMBER列显示bench_id出现的次数。
我已经尝试过此代码：

The second table's NUMBER column is showing the number of bench_id's occurrences. I have tried this code:

insert into [dbo].BATCH_ID (batch_id, number)
select 
     distinct [dbo].big_table.batch_id,
           (select count([dbo].big_table.batch_id) from [dbo].big_table)
     from [dbo].big_table

但是无法获得正确的答案，我期望的是Bench_id出现的次数。
您能指出我在做什么吗？

But can not get the right answer I am expecting with number of bench_id occurrences. Can you point out what I am doing wrong?

推荐答案

您需要拥有 GROUP BY 子句，因为您需要使用 COUNT（）来计算 benchID 的出现。

You need to have GROUP BY clause since you need to use COUNT() to count the occurence of benchID.

INSERT INTO BATCH_ID (distinct_bench), number)
SELECT  benchID as distinct_bench, COUNT(*) number
FROM    big_table
GROUP   BY BATCH_ID 

这篇关于相似的列出现次数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！