MS-SQL分组和计数查询

So I have a table and the columns are Product and ProductGroupID. Every Product can be in 1 or more groups as shown in this example:

所以我有一个表,列是Product和ProductGroupID。每个产品可以包含一个或多个组,如下例所示:

+-------+--------------+
|product|ProductGroupId|
+-------+--------------+
| 10    | 2            |
| 10    | 9            |
| 10    | 4            |
| 10    | 7            |
| 20    | 7            |
| 30    | 4            |
| 40    | 1            |
| 50    | 11           |
| 50    | 12           |
| 60    | 2            |
| 70    | 9            |
| 80    | 11           |
| 90    | 12           |
| 100   | 13           |
+-------+--------------+

For every product I need to get it's group or groups and to bring the number of product which are in those groups. For example product 10 is in groups 2,4,9,7 so I need to count all the products that are in those groups, in this case 5 (for count of products 10,60,70,30,20). I attach the full desired outcome for this example. https://i.stack.imgur.com/XTa4R.png Any suggestion how to do this in ms-sql?

对于每个产品,我需要获得它的组或组,并带来这些组中的产品数量。例如,产品10是2,4,9,7组,因此我需要计算这些组中的所有产品,在这种情况下为5(对于产品数量10,60,70,30,20)。我附上了这个例子的完整预期结果。 https://i.stack.imgur.com/XTa4R.png任何建议如何在ms-sql中执行此操作?

Thnaks!

1 个解决方案

#1

If I understood it correctly this will work for you.

如果我理解正确,这对你有用。

Schema from your image:

您图片中的架构:

CREATE TABLE #PRODUCTS (PRODUCT INT, PRODUCTGROUP_ID INT)

INSERT INTO #PRODUCTS
SELECT 10,2
UNION ALL
SELECT 10,9
UNION ALL
SELECT 10,4
UNION ALL
SELECT 10,7
UNION ALL
SELECT 20,7
UNION ALL
SELECT 30,4
UNION ALL
SELECT 40,1
UNION ALL
SELECT 50,11
UNION ALL
SELECT 50,12
UNION ALL
SELECT 60,2
UNION ALL
SELECT 70,9
UNION ALL
SELECT 80,11
UNION ALL
SELECT 90,12
UNION ALL
SELECT 100,13

Now do a Self Join with GroupId on condition

现在,在条件下自行加入GroupId

SELECT P.PRODUCT, COUNT(DISTINCT G.PRODUCT) Linked_GroupProducts
FROM #PRODUCTS P
INNER JOIN #PRODUCTS G ON P.PRODUCTGROUP_ID = G.PRODUCTGROUP_ID
GROUP BY P.PRODUCT

And the result will be

结果将是

+---------+----------------------+
| PRODUCT | Linked_GroupProducts |
+---------+----------------------+
|      10 |                    5 |
|      20 |                    2 |
|      30 |                    2 |
|      40 |                    1 |
|      50 |                    3 |
|      60 |                    2 |
|      70 |                    2 |
|      80 |                    2 |
|      90 |                    2 |
|     100 |                    1 |
+---------+----------------------+

#1