根据条件分配组号 [英] Assigning group number based on condition

问题描述

我们有一个像下面这样的表格

We have a table like the following

我们想用生成的组 ID 编写一个查询(基于以下内容)

and we want to write a query with generated Group ID(based on the following)

如果一个国家有超过 1 个相同类别的项目，那么应该为 [country, category] ​​组记录分配一个唯一的组 ID，

If a country has more than 1 item with the same category then that [country, category] group records should be assigned with a unique group ID,

因此对于上表，我们需要编写一个查询(无存储过程或函数)，它将返回如下结果.

so for the above table we need to write a single query (no stored proc or function) which will return the result like below.

我们尝试了查询，它看起来非常复杂且不可读.

We tried the query and its look like very complicated and not readable.

所以我们正在寻找更好的查询来实现相同的目标.

So we are looking for a better query to achieve the same.

提前致谢

推荐答案

假设 SQL Server，您可以通过将 OVER() 添加到 COUNT() 和使用带有 DENSE_RANK() 的 CASE 语句:

Assuming SQL Server, you can do this by adding OVER() to a COUNT() and using CASE statements with DENSE_RANK():

;with cte AS (SELECT *,COUNT(*) OVER(PARTITION BY Country,Category) CT
                      ,CASE WHEN Category IS NOT NULL THEN DENSE_RANK() OVER (ORDER BY Country) END Rank_
              FROM Table1)
SELECT Country
      ,State
      ,Category
      ,CASE WHEN CT > 1 AND Rank_ IS NOT NULL THEN DENSE_Rank() OVER(ORDER BY Rank_ DESC) END AS Group_
FROM cte
ORDER BY Country DESC,State

演示:SQL Fiddle

仅当您关心它从 1-n 编号时，您才需要多个 DENSE_RANK().

Only if you care about it being numbered from 1-n do you need more than one DENSE_RANK().

这篇关于根据条件分配组号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！