如何在 MySQL 中的选定值上创建序列? [英] How to create a sequence on selected values in MySQL?

问题描述

我正在尝试创建一个查询，该查询将从已声明的变量中选择一个团队，然后通过为其余团队提供通用品牌和序列 ID 使他们成为匿名"团队.

I'm trying to create a query which will select a team from a declared variable and then make the remaining teams "anonymous" by giving them generic brands and sequential IDs.

例如，如果我的数据集有 3 个不同的团队名称(ABC、DEF 和 GHI)，但我只想显示 1 个所选团队 (ABC) 的真实值.这是我正在使用的查询的框架:

For example, if my dataset has 3 different team names (ABC, DEF, and GHI), but I would only like the true value of the 1 chosen team (ABC) displayed. Here is the skeleton of the query I'm working with:

SET @teamid = 123;

SELECT     CASE WHEN ID = @teamid
                THEN NAME
                ELSE 'Team' + ' - ' + SEQ
                END AS 'Team Name',
           SUM(TOTAL) AS 'Team Total'
FROM       TEAM
GROUP BY   1;

我希望结果如下所示:

 Team Name:    Team Total:
 ABC           100
 Team - 1      50
 Team - 2      150

我如何创建一个查询，该查询将创建一个唯一的编号，我可以用它来替换原始团队名称?我知道我必须替换 case 语句的SEQ"部分，但我不确定用什么来替换它.此外，无论是否匿名，每个团队都具有相同的 ID 很重要(因此，如果团队 DEF 有 50 行，则应仅显示为团队 - 1，而不是团队 - 1-50)，以便我的分组正常工作.

How can I go about creating a query which will create a unique number that I can replace the original team name with? I know I have to replace the "SEQ" portion of the case statement, but I'm not sure what exactly to replace it with. Also, it is important that each team has the same ID whether or not it is anonymous (so if team DEF has 50 rows, it should be shown as Team - 1 only as opposed to Team - 1-50) so that my groupings will work properly.

感谢您的帮助.

推荐答案

使用用户定义的自增变量.此外，MySQL 使用 CONCAT 来连接字符串，而不是 +.

Use a user-defined variable that you increment. Also, MySQL uses CONCAT to concatenate strings, not +.

SET @teamid = 123;

SELECT     CASE WHEN ID = @teamid
                THEN NAME
                ELSE CONCAT('Team  - ', @SEQ)
                END AS 'Team Name',
           SUM(TOTAL) AS 'Team Total',
           @SEQ := CASE WHEN ID = @teamid 
                        THEN @SEQ
                        ELSE @SEQ + 1
                   END
FROM       TEAM
CROSS JOIN (SELECT @SEQ := 1) AS vars
GROUP BY   ID;

演示

这篇关于如何在 MySQL 中的选定值上创建序列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！