由于糟糕的数据库设计,我遇到了令人沮丧的情况——不幸的是,这部分是我无法控制的。
这是表定义:
CREATE TABLE `account` (
`account_no` int(10) unsigned NOT NULL AUTO_INCREMENT,
`company_name` varchar(120) NOT NULL DEFAULT '',
`admin_name` varchar(120) NOT NULL DEFAULT '',
`admin_email` varchar(120) DEFAULT '',
`admin_phone_src` varchar(33) DEFAULT NULL,
`admin_mobile_src` varchar(33) DEFAULT NULL,
`billing_name` varchar(120) NOT NULL DEFAULT '',
`billing_email` varchar(120) DEFAULT '',
`billing_phone_src` varchar(33) DEFAULT NULL,
`billing_mobile_src` varchar(33) DEFAULT NULL,
`technical_name` varchar(120) NOT NULL DEFAULT '',
`technical_email` varchar(120) DEFAULT '',
`technical_phone_src` varchar(33) DEFAULT NULL,
`technical_mobile_src` varchar(33) DEFAULT NULL
PRIMARY KEY (`account_no`)
) ENGINE=InnoDB AUTO_INCREMENT=10476 DEFAULT CHARSET=utf8
我需要生成一个以逗号分隔的角色列表。即管理、计费、技术可能都指同一个人(重复数据 - 呃!),或者他们可能是 3 个不同的人。
给定数据集:
+------------+--------------+------------+-----------------+-----------------+------------------+--------------+-------------------+-------------------+--------------------+----------------+-----------------+---------------------+----------------------+
| account_no | company_name | admin_name | admin_email | admin_phone_src | admin_mobile_src | billing_name | billing_email | billing_phone_src | billing_mobile_src | technical_name | technical_email | technical_phone_src | technical_mobile_src |
+------------+--------------+------------+-----------------+-----------------+------------------+--------------+-------------------+-------------------+--------------------+----------------+-----------------+---------------------+----------------------+
| 1 | Company 1 | John | [email protected] | 0123456789 | | Dave | [email protected] | 0123455789 | | John | [email protected] | 0123456789 | |
| 2 | Company 2 | Sarah | [email protected] | 0123466789 | 0723456781 | Sarah | [email protected] | 0123466789 | 0723456781 | Sarah | [email protected] | 0123466789 | 0723456781 |
| 3 | Company 3 | James | [email protected] | 0123477789 | 0743456788 | Richard | [email protected] | 0123444789 | 0723444781 | Tom | [email protected] | 0123433789 | 0723458881 |
+------------+--------------+------------+-----------------+-----------------+------------------+--------------+-------------------+-------------------+--------------------+----------------+-----------------+---------------------+----------------------+
我正在尝试:
+------------+--------------+---------+-------------------+------------+------------+-------------------------+
| account_no | company_name | name | email | phone_src | mobile_src | role |
+------------+--------------+---------+-------------------+------------+------------+-------------------------+
| 1 | Company 1 | John | [email protected] | 0123456789 | | Admin,Technical |
| 1 | Company 1 | Dave | [email protected] | 0123455789 | | Billing |
| 2 | Company 2 | Sarah | [email protected] | 0123466789 | 0723456781 | Admin,Billing,Technical |
| 3 | Company 3 | James | [email protected] | 0123477789 | 0743456788 | Admin |
| 3 | Company 3 | Richard | [email protected] | 0123444789 | 0723444781 | Billing |
| 3 | Company 3 | Tom | [email protected] | 0123433789 | 0723458881 | Technical |
+------------+--------------+---------+-------------------+------------+------------+-------------------------+
我已经设法UNION
让人们进入行而不是列(即模拟一个“理智”的数据库设计),并添加这样的角色:
SELECT account_no, company_name, admin_name AS 'Name', admin_email AS 'Email', admin_phone_src AS 'Phone', admin_mobile_src AS 'Mobile', 'Admin' AS role FROM account
UNION
SELECT account_no, company_name, billing_name AS 'Name', billing_email AS 'Email', billing_phone_src AS 'Phone', billing_mobile_src AS 'Mobile', 'Billing' AS role FROM account
UNION
SELECT account_no, company_name, technical_name AS 'Name', technical_email AS 'Email', technical_phone_src AS 'Phone', technical_mobile_src AS 'Mobile', 'Technical' AS role FROM account
我现在正在尝试合并这些行以获得 DISTINCT 人员并生成逗号分隔的角色列表。我认为这是可能的,GROUP_CONCAT
但我尝试这样做的结果只有 1 行:
SELECT t.account_no, t.company_name, t.Name, t.Email, t.Phone, t.Mobile, GROUP_CONCAT(t.role) FROM (
SELECT account_no, company_name, admin_name AS 'Name', admin_email AS 'Email', admin_phone_src AS 'Phone', admin_mobile_src AS 'Mobile', 'Admin' AS role FROM account
UNION
SELECT account_no, company_name, billing_name AS 'Name', billing_email AS 'Email', billing_phone_src AS 'Phone', billing_mobile_src AS 'Mobile', 'Billing' AS role FROM account
UNION
SELECT account_no, company_name, technical_name AS 'Name', technical_email AS 'Email', technical_phone_src AS 'Phone', technical_mobile_src AS 'Mobile', 'Technical' AS role FROM account
) t
非常感谢任何帮助。
您需要一个分组依据才能使用 group_concat、GROUP BY t.account_no、t.company_name、t.Name、t.Email、t.Phone、t.Mobile
无论如何,我会在两个单独的表中进行,这样你必须有很多重复的信息