时间戳

首页 > 解决方案 > SQL 从查询中填充新表

sql - SQL 从查询中填充新表

问题描述

嗨，伙计们，我对 sql 真的很陌生，所以如果这听起来很愚蠢，我很抱歉。

我有一个查询显示两个表中的所有女性司机（每个表的员工和角色总共有 1000 行）

员工表

    1   Brendon Hayford     Male
    2   Appolonia   Mawer   Female
    3   Stanly  Loachhead   Male
    4   Kinny   Hulcoop     Male
    5   Alyssa  Vose        Female
    6   Gerrie  Cradey      Female
    7   Katalin Coverly     Female
    8   Michelina   Yukhov  Female
    9   Abbey   Thunnerclif Male
    10  Skippy  Juett       Male
    11  Kizzee  Bleythin    Female
    12  Shea    Coxhell     Male
    13  Win Shilstone       Male
    14  Carita  Giddy       Female
    15  Tiebout Creffield   Male

角色表

1   Mechanic
2   Admin
3   Mechanic
4   Admin
5   Admin
6   Mechanic
7   Driver
8   Mechanic
9   Admin
10  Driver
11  Admin
12  Driver
13  Admin
14  Mechanic
15  Mechanic

询问

SELECT role.idrole, employee.*, role.role 
FROM employee 
INNER JOIN role 
ON idrole=idemployee 
WHERE role.role="Driver" 
AND employee.gender="Female";

此查询返回 161 行女司机。

7   7   Katalin Coverly     Female  Driver
26  26  Jenilee Soeiro      Female  Driver
34  34  Rachel  Tourner     Female  Driver
46  46  Joanne  McCallister Female  Driver
47  47  Maure   Wingrove    Female  Driver
63  63  Bettina Mattecot    Female  Driver
74  74  Nelle   MacRierie   Female  Driver
77  77  Elsinore    Milius  Female  Driver
80  80  Emalia  Hellewell   Female  Driver
82  82  Kerianne    Pirkis  Female  Driver
95  95  Teena   Pesak       Female  Driver
99  99  Harriot Seyffert    Female  Driver

我需要一种方法将此查询中的每 2 名女性司机分配给一个团队（例如第 1 行和第 2 行在 A 队第 3 行和第 4 行 B 队等）

7   7   Katalin Coverly     Female  Driver Team A
26  26  Jenilee Soeiro      Female  Driver Team A
34  34  Rachel  Tourner     Female  Driver Team B
46  46  Joanne  McCallister Female  Driver Team B
47  47  Maure   Wingrove    Female  Driver Team C
63  63  Bettina Mattecot    Female  Driver Team C
74  74  Nelle   MacRierie   Female  Driver Team D
77  77  Elsinore    Milius  Female  Driver Team D
80  80  Emalia  Hellewell   Female  Driver Team E
82  82  Kerianne    Pirkis  Female  Driver Team E
95  95  Teena   Pesak       Female  Driver Team F
99  99  Harriot Seyffert    Female  Driver Team F...

任何信息或建议将不胜感激。

标签： sqlmysql-workbench

解决方案

您可以通过设置一个变量并将计算添加到您的 SELECT 语句来非常简单地做到这一点。像这样的东西：

SET @var=64;
SELECT field1, ...,
CONCAT('Team-', CHAR(ROUND(@var:=@var+.5))) AS Team
FROM table_name;

推荐阅读