mysql - MySQL group_concat与联接

我有联络台。每个联系人可以有多个不同或相同表的电话号码。同样，每个联系人可以有多封标签不同或相同的电子邮件。我需要选择属于特定联系人的所有不同电话号码和电子邮件ID。

CREATE TABLE IF NOT EXISTS `contact` (
  `id` int(8) unsigned NOT NULL ,
  `fname` varchar(64)  NOT NULL,
  `lname` varchar(64)  NOT NULL
) ENGINE=InnoDB  DEFAULT CHARSET=latin1  ;
CREATE TABLE IF NOT EXISTS `phone` (
  `id` int(8) unsigned NOT NULL ,
  `sourceid` int(8) unsigned NOT NULL ,
   `type` varchar(16)  NOT NULL,
  `phone` varchar(16)  NOT NULL
) ENGINE=InnoDB  DEFAULT CHARSET=latin1  ;
CREATE TABLE IF NOT EXISTS `email` (
  `id` int(8) unsigned NOT NULL ,
  `sourceid` int(8) unsigned NOT NULL ,
  `type` varchar(16)  NOT NULL,
  `email` varchar(128)  NOT NULL
) ENGINE=InnoDB  DEFAULT CHARSET=latin1  ;

INSERT INTO contact values ( 1,'john'      ,'j' ),
(2, 'jose'      ,'f' ),
(3, 'test'      ,'k' ),
(4, 'tester'      ,'j' );


INSERT INTO phone values( 1 ,1, 'Home', '123456' ),
( 2 ,1, 'Home', '123456342' ),
( 3 ,1, 'Office', '12345645' ),
( 4 ,1, 'Mobile', '1234567' ),
( 5 ,2, 'Home', '123456' ),
( 5 ,2, 'Home', '987556' );

INSERT INTO email values
( 1 ,1, 'Home', '[email protected]' ),
( 2 ,1, 'Home', '[email protected]' ),
( 3 ,1, 'Office', '[email protected]' ),
( 4 ,2, 'Home', '[email protected]' ),
( 5 ,4, 'Home', '[email protected]' );

我试过GROUP_CONCAT，但它有重复的标签条目。

SELECT C.id, fname, lname
, GROUP_CONCAT(ph.sourceid),
GROUP_CONCAT(em.sourceid),
GROUP_CONCAT(ph.type), GROUP_CONCAT(ph.phone),
GROUP_CONCAT(em.type), GROUP_CONCAT(em.email)
FROM contact AS C
LEFT  JOIN phone AS PH ON PH.sourceid = C.id
LEFT  JOIN email EM ON EM.sourceid = C.id
WHERE C.id='1'
GROUP BY C.id

SQL FIDDLE

最佳答案

试试这个：

SELECT c.id, c.fname, c.lname,
       ph.PhoneType, ph.PhoneNos,
       em.EmailType, em.EmailIds
FROM contact AS c
LEFT JOIN (SELECT ph.sourceid, GROUP_CONCAT(ph.type) AS PhoneType, GROUP_CONCAT(ph.phone) AS PhoneNos
           FROM phone AS ph
           GROUP BY ph.sourceid
          ) AS ph ON ph.sourceid = c.id
LEFT JOIN (SELECT em.sourceid, GROUP_CONCAT(em.type) AS EmailType, GROUP_CONCAT(em.email) AS EmailIds
           FROM email AS em
           GROUP BY em.sourceid
          ) AS em ON em.sourceid = c.id
WHERE c.id = '1'
GROUP BY c.id

检查SQL FIDDLE DEMO
输出

| ID | FNAME | LNAME |               PHONETYPE |                          PHONENOS |        EMAILTYPE |                                   EMAILIDS |
|----|-------|-------|-------------------------|-----------------------------------|------------------|--------------------------------------------|
|  1 |  john |     j | Home,Home,Office,Mobile | 123456,123456342,12345645,1234567 | Home,Home,Office | [email protected],[email protected],[email protected] |

关于mysql - MySQL group_concat与联接，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/27687655/

fiddle

mysql - MySQL group_concat与联接