除Group By之外我还能使用什么？

I have a question that uses this DML statement

我有一个使用此DML语句的问题

SELECT SupplierID, COUNT(*) AS TotalProducts
FROM Products
GROUP BY SupplierID;

I'm trying to get the same results without using "Group By". I can use a table variable or a temp table, with Insert and Update if needed. Also, using While and IF-Else is allowed.

我试图在不使用“分组依据”的情况下获得相同的结果。我可以使用表变量或临时表，如果需要可以使用Insert和Update。此外，允许使用While和IF-Else。

I'm really lost any help would be awesome. Thanks SO Community.

我真的失去了任何帮助都会很棒。谢谢SO社区。

This is used in SQL Server. Thanks again.

这在SQL Server中使用。再次感谢。

4 个解决方案

#1

You can always use SELECT DISTINCT with window functions:

您始终可以将SELECT DISTINCT与窗口函数一起使用：

SELECT DISTINCT SupplierID,
       COUNT(*) OVER (PARTITION BY SupplierId) AS TotalProducts
FROM Products;

But GROUP BY is the right way to write an aggregation query.

但GROUP BY是编写聚合查询的正确方法。

#2

You may also use the following query :

您还可以使用以下查询：

select distinct P.SupplierID, (select count(*) from Products 
where SupplierID=P.SupplierID) TotalProducts from Products P

You will get the same result using the above query, but i don't think avoiding GROUP BY is a good idea!

您将使用上述查询获得相同的结果，但我不认为避免GROUP BY是个好主意！

#3

Using a subquery:

使用子查询：

SELECT DISTINCT SupplierID
      ,(SELECT COUNT(*) 
          FROM Products P2 
         WHERE P2.SupplierID = P.SupplierID
       ) AS TotalProducts
  FROM Products P

The distinct is to remove duplicates... the count executes for every row so without distinct you would get repeat answers for supplierID.

不同的是删除重复...计数执行每一行，所以没有区别你会得到supplierID的重复答案。

#4

Another way

其他方式

select distinct supplierId, p2.ttl
from products p1
cross apply
(
  select count(*)
  from products p2
  where p1.supplierId = p2.supplierId
) p2(ttl);

#1