SQL 如何使用SQL计算表中的唯一记录并获取这些唯一记录的数量？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/237302/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to count unique records and get number of these uniques in table using SQL?
提问by Skuta
Imagine I have table like this:
想象一下我有这样的表：
id:Product:shop_id
id：产品：shop_id
1:Basketball:41
1：篮球：41
2:Football:41
2：足球：41
3:Rocket:45
3：火箭：45
4:Car:86
4：汽车：86
5:Plane:86
5：平面：86
Now, this is an example of large internet mall, where there are shops which sell to one customer, so customer can choose more products from each shop and buy it in one basket.
现在，这是一个大型互联网商城的例子，其中有商店向一个客户销售，因此客户可以从每个商店中选择更多产品并在一个篮子中购买。
However, I am not sure if there is any SQL syntax which allows me to simply get unique shop_ids and total number of those shops' products in customer basket. So I'd get something like:
但是，我不确定是否有任何 SQL 语法可以让我简单地获取唯一的 shop_ids 和客户购物篮中这些商店的产品总数。所以我会得到类似的东西：
Shop 41 has 2 products
店铺 41 有 2 个产品
Shop 45 one product
购买 45 个产品
Shop 86 two product
店铺 86 二品
I can make SQL queries to scoop through table to make some kind of ['shop_id']['number_of_products'] array variable that would store all products' shop_ids, then "unique them" - up and count how many times I had to cut one more shop_id out to have some remaining but that just seems as a lot of useless scripting.
我可以进行 SQL 查询以获取表以创建某种 ['shop_id']['number_of_products'] 数组变量，该变量将存储所有产品的 shop_id，然后“使它们独一无二” - 并计算我必须切割的次数再多一个 shop_id 来保留一些剩余的，但这似乎是很多无用的脚本。
If you got some nice and neat idea, please, let me know.
如果你有一些不错的主意，请告诉我。
回答by Andru Luvisi
This is exactly the sort of thing that aggregate functions are for. You make one row of output for each group of rows in the table. Group them by shop_id and count how many rows are in each group.
这正是聚合函数的用途。您为表中的每组行制作一行输出。按 shop_id 将它们分组并计算每个组中有多少行。
select shop_id, count(1) from TABLE_NAME
group by shop_id
