发生类似列的数量

本文介绍了发生类似列的数量的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我是新的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? thank you in advance!

推荐答案

您需要拥有 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

这篇关于发生类似列的数量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！