相似的列出现次数 [英] 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屋!