sql从查询填充新表

guykilcj 于 2021-08-09 发布在 Java

关注(0)|答案(2)|浏览(274)

嗨，伙计们，我真的是新的sql，所以我很抱歉，如果这听起来很愚蠢。
我有一个查询，显示了两个表中的所有女性驱动程序（employee和role每个表总共有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...

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

sql mysql-workbench

来源：https://stackoverflow.com/questions/62616908/sql-populate-a-new-table-from-a-query

2条答案

按热度按时间

7eumitmz1#

如果您在工作台版本中运行mysql 8，那么您可以使用 ROW_NUMBER() 要生成团队编号，请将行号除以2，将2个人分配给一个团队：

SELECT r.role, e.*,
       (ROW_NUMBER() OVER (ORDER BY idemployee) + 1) DIV 2 AS Team
FROM employee e
JOIN role r ON r.id = e.idrole
WHERE r.role = 'Driver' AND e.gender = 'Female'

输出（基于问题中的数据）：

role    idemployee  firstname   lastname    gender  idrole  Team
Driver  7           Katalin     Coverly     Female  3       1
Driver  26          Jenilee     Soeiro      Female  3       1
Driver  34          Rachel      Tourner     Female  3       2
Driver  46          Joanne      McCallister Female  3       2
Driver  47          Maure       Wingrove    Female  3       3
Driver  63          Bettina     Mattecot    Female  3       3
Driver  74          Nelle       MacRierie   Female  3       4
Driver  77          Elsinore    Milius      Female  3       4
Driver  80          Emalia      Hellewell   Female  3       5
Driver  82          Kerianne    Pirkis      Female  3       5
Driver  95          Teena       Pesak       Female  3       6
Driver  99          Harriot     Seyffert    Female  3       6

在dbfiddle上演示
注意，在我的演示中，我修改了你的 role table正常化，带有 id 以及 role 列；使用外键( idrole )在employee表中引用 id 在 role table。所以呢 role 看起来像这样：

id  role
1   Mechanic
2   Admin
3   Driver

从上面的输出可以看出，所有驱动程序 idrole = 3

赞(0）回复(0）举报 2021-08-09

bvuwiixz2#

您只需设置一个变量并向select语句中添加一个计算就可以做到这一点。像这样：

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

赞(0）回复(0）举报 2021-08-09

我来回答

sql从查询填充新表

2条答案

相关问题

热门标签

最新问答