SELECT 非规范化列到单独的记录中？

我正在使用 SQL 一点，这样如果我在工作面试中被问到，我就不会完全不知道它。我的朋友最近在面试时被问到以下问题，他答不出来，我问了工作中熟悉 SQL 的人，他也不知道。你们能帮我回答这个问题，然后解释一下它是如何工作的吗？请？

*问题*

数据库规范化（或缺乏规范化）常常给开发人员带来挑战。

考虑一个包含三个字段的员工数据库表：

EmployeeID
EmployeeName
EmailAddresses

由唯一的 EmployeeID 标识的每位员工在 EmailAddresses 字段中可能有一个或多个以逗号分隔的 @rockauto.com 电子邮件地址。

数据库表定义如下：

CREATE TABLE Employees
(
  EmployeeID int UNSIGNED NOT NULL PRIMARY KEY,
  EmployeeName varchar(50) NOT NULL,
  EmailAddresses varchar(40) NOT NULL ,
  PRIMARY KEY(EmployeeID)
);

出于测试目的，以下是一些示例数据：

INSERT INTO Employees (EmployeeID, EmployeeName, EmailAddresses) VALUES
('1', 'Bill', '[email protected] /cdn-cgi/l/email-protection'),
('2', 'Fred', '[email protected] /cdn-cgi/l/email-protection,[email protected] /cdn-cgi/l/email-protection'),
('3', 'Fred', '[email protected] /cdn-cgi/l/email-protection'),
('4', 'Joe', '[email protected] /cdn-cgi/l/email-protection,[email protected] /cdn-cgi/l/email-protection');

您的任务是编写一个 MySQL SELECT 查询，该查询将显示上述示例数据的以下输出：

Employee    EmailAddress
Bill    [email protected] /cdn-cgi/l/email-protection
Fred (2)    [email protected] /cdn-cgi/l/email-protection
Fred (2)    [email protected] /cdn-cgi/l/email-protection
Fred (3)    [email protected] /cdn-cgi/l/email-protection
Joe     [email protected] /cdn-cgi/l/email-protection
Joe     [email protected] /cdn-cgi/l/email-protection

请注意，由于有多个同名人员（在本例中为“Fred”），因此 EmployeeID 包含在括号中。

您的查询需要使用 MySQL 版本 5.1.41 兼容语法编写。您应该假设排序是使用标准数据库升序完成的：“ORDER BY EmployeeID ASC”

对于此问题，您需要提交单个 SQL SELECT 查询。您的查询应该能够在合理的时间内处理包含 1000 条记录的表。

仅当您的电子邮件少于 10000 封时...可以接受吗？

select 
       if(t1.c > 1, concat(e.employeename, ' (', e.employeeid, ')'), e.employeename) as Employee,
       replace(substring(substring_index(e.EmailAddresses, ',', n.row), length(substring_index(e.EmailAddresses, ',', n.row - 1)) + 1), ',', '') EmailAddress 
from 
       (select employeename, count(*) as c from Employees group by employeename) as t1, 
       (select EmployeeID, length(EmailAddresses) - length(replace(EmailAddresses,',','')) + 1 as emails from Employees) as t2,
       (SELECT @row := @row + 1 as row FROM 
       (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) x,
       (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) x2, 
       (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) x3, 
       (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) x4, 
       (SELECT @row:=0) as ff) as n,
       Employees e
where 
      e.employeename = t1.employeename and
      e.employeeid = t2.employeeid and
      n.row <= t2.emails
order by e.employeeid;

EDIT:

生成的无用数字更少：

select 
       if(t1.c > 1, concat(e.EmployeeName, ' (', e.EmployeeID, ')'), e.EmployeeName) as Employee,
       replace(substring(substring_index(e.EmailAddresses, ',', n.row), length(substring_index(e.EmailAddresses, ',', n.row - 1)) + 1), ',', '') as EmailAddress 
from 
       (select EmployeeName, count(*) as c from Employees group by EmployeeName) as t1, 
       (select EmployeeID, length(EmailAddresses) - length(replace(EmailAddresses,',','')) + 1 as emails from Employees) as t2,
       (select `1` as row from (select 1 union all select 2 union all select 3 union all select 4) x) as n,
       Employees e
where 
      e.EmployeeName = t1.EmployeeName and
      e.EmployeeID = t2.EmployeeID and
      n.row <= t2.emails
order by e.EmployeeID;

我们学到了什么？糟糕的数据库设计会导致糟糕的查询。你可以用 SQL 做一些事情，这可能只是因为人们的数据库设计很糟糕......:)