大佬教程收集整理的这篇文章主要介绍了php – 使用父表加入2个子表而不重复，大佬教程大佬觉得挺不错的，现在分享给大家，也给大家做个参考。
我有3张桌子：人,电话和电子邮件.每个人都有一个独特的ID,每个人可以有多个号码或多个电子邮件.
简化它看起来像这样：
+---------+----------+
| ID | Name |
+---------+----------+
| 5000003 | Amy |
| 5000004 | George |
| 5000005 | John |
| 5000008 | Steven |
| 8000009 | Ashley |
+---------+----------+
+---------+-----------------+
| ID | number |
+---------+-----------------+
| 5000005 | 5551234 |
| 5000005 | 5154324 |
| 5000008 | 2487312 |
| 8000009 | 7134584 |
| 5000008 | 8451384 |
+---------+-----------------+
+---------+------------------------------+
| ID | Email |
+---------+------------------------------+
| 5000005 | Smithley@goodmail.com.com |
| 5000005 | Smithley.j@gmail.com |
| 5000008 | Smithley@gmail.com |
| 5000008 | tech@goodmail.com |
| 5000008 | feler@campuS.Uni.com |
| 8000009 | Ashley.hill86@gmail.com |
| 5000004 | georgestanko@hotmail.com |
+---------+------------------------------+
我试图加入他们没有重复.当我尝试仅与人们联系电子邮件或仅与人们联系电话时,它的效果很好.
SELECT People.Name,People.ID,Phones.number FROM People LEFT OUTER JOIN Phones ON People.ID=Phones.ID ORDER BY Name,ID,number; +----------+---------+-----------------+ | Name | ID | number | +----------+---------+-----------------+ | Steven | 5000008 | 8451384 | | Steven | 5000008 | 24887312 | | John | 5000005 | 5551234 | | John | 5000005 | 5154324 | | George | 5000004 | NULL | | Ashley | 8000009 | 7134584 | | Amy | 5000003 | NULL | +----------+---------+-----------------+ SELECT People.Name,Emails.Email FROM People LEFT OUTER JOIN Emails ON People.ID=Emails.ID ORDER BY Name,Email; +----------+---------+------------------------------+ | Name | ID | Email | +----------+---------+------------------------------+ | Steven | 5000008 | Smithley@gmail.com | | Steven | 5000008 | tech@goodmail.com | | Steven | 5000008 | feler@campuS.Uni.com | | John | 5000005 | Smithley@goodmail.com.com | | John | 5000005 | Smithley.j@gmail.com | | George | 5000004 | georgestanko@hotmail.com | | Ashley | 8000009 | Ashley.hill86@gmail.com | | Amy | 5000003 | NULL | +----------+---------+------------------------------+
但是,当我尝试加入人们的电子邮件和电话时 – 我得到了这个：
SELECT People.Name,Phones.number,Emails.Email FROM People LEFT OUTER JOIN Phones ON People.ID = Phones.ID LEFT OUTER JOIN Emails ON People.ID = Emails.ID ORDER BY Name,number,Email; +----------+---------+-----------------+------------------------------+ | Name | ID | number | Email | +----------+---------+-----------------+------------------------------+ | Steven | 5000008 | 8451384 | feler@campuS.Uni.com | | Steven | 5000008 | 8451384 | Smithley@gmail.com | | Steven | 5000008 | 8451384 | tech@goodmail.com | | Steven | 5000008 | 24887312 | feler@campuS.Uni.com | | Steven | 5000008 | 24887312 | Smithley@gmail.com | | Steven | 5000008 | 24887312 | tech@goodmail.com | | John | 5000005 | 5551234 | Smithley@goodmail.com | | John | 5000005 | 5551234 | Smithley.j@gmail.com | | John | 5000005 | 5154324 | Smithley@goodmail.com | | John | 5000005 | 5154324 | Smithley.j@gmail.com | | George | 5000004 | NULL | georgestanko@hotmail.com | | Ashley | 8000009 | 7134584 | Ashley.hill86@gmail.com | | Amy | 5000003 | NULL | NULL | +----------+---------+-----------------+------------------------------+
会发生什么 – 如果一个人有2个号码,他的所有电子邮件都会被显示两次(它们无法排序！这意味着它们不能被@last删除)
我想要的是：
最后,和@last一起玩,我想最终得到像这样的东西,但是如果我没有以正确的方式排列ORDER列,那么@last将无法工作 – 这似乎是一个大问题.orderin the电子邮件列.因为从上面的例子看：
史蒂文有2个电话号码和3封电子邮件.带有数字的JOIN电子邮件发生在每封电子邮件中 - 因此无法排序的重复值(SORT BY对它们不起作用).
**THIS IS what I WANT** +----------+---------+-----------------+------------------------------+ | Name | ID | number | Email | +----------+---------+-----------------+------------------------------+ | Steven | 5000008 | 8451384 | feler@campuS.Uni.com | | | | 24887312 | Smithley@gmail.com | | | | | tech@goodmail.com | | John | 5000005 | 5551234 | Smithley@goodmail.com | | | | 5154324 | Smithley.j@gmail.com | | George | 5000004 | NULL | georgestanko@hotmail.com | | Ashley | 8000009 | 7134584 | Ashley.hill86@gmail.com | | Amy | 5000003 | NULL | NULL | +----------+---------+-----------------+------------------------------+
现在我被告知最好将电子邮件和号码保存在单独的表中,因为可以有很多电子邮件.因此,如果它是如此常见的事情,那么什么不是一个简单的解决方案？
我现在知道如何做到满足它,但不是那么漂亮.
如果我使用GROUP_CONTACT这样做,我会得到满意的结果,但它看起来并不漂亮：我不能在它旁边加上“电子邮件类型=工作”.
SELECT People.Ime,GROUP_CONCAT(DISTinCT Phones.number),GROUP_CONCAT(DISTinCT Emails.Email) FROM People LEFT OUTER JOIN Phones ON People.ID=Phones.ID LEFT OUTER JOIN Emails ON People.ID=Emails.ID GROUP BY Name; +----------+----------------------------------------------+---------------------------------------------------------------------+ | Name | GROUP_CONCAT(DISTinCT Phones.number) | GROUP_CONCAT(DISTinCT Emails.Email) | +----------+----------------------------------------------+---------------------------------------------------------------------+ | Steven | 8451384,24887312 | Smithley@gmail.com,tech@goodmail.com,feler@campuS.Uni.com | | John | 5551234,5154324 | Smithley@goodmail.com,Smithley.j@gmail.com | | George | NULL | georgestanko@hotmail.com | | Ashley | 7134584 | Ashley.hill86@gmail.com | | Amy | NULL | NULL | +----------+----------------------------------------------+---------------------------------------------------------------------+
既然你说过“我对PHP解决方案感到满意.” …你真正想要的东西就像一个PHP“用户”对象,类似的东西(当然这都是假设的)：
<?PHP class User { private $_id; private $_telNos = array(); private $_emails = array(); public function __construct($iUserId = null,$oDatabaseAbstractionObject = null) { if(!is_null($iUserId)) $this->setId($iUserId); if(!is_null($oDatabaseConnectionObject)) $this->load($iUserId,$oDatabaseAbstractionObject); } public setId($iUserId) { $this->_id = (int) $iUserId; } public getId() { return $this->_id; } /* telephone and email setters and getters */ public function load($iUserId,$oDatabaseAbstractionObject) { /* error trapping - for example if $iUserId is null */ $this->setTelNos($oDatabaseAbstractionObject->readTelNos($iUserId)); $this->setEmails($oDatabaseAbstractionObject->readEmails(iUserId)); } } ?>
然后,您的数据库抽象对象只需要执行一些非常简单的查询来读取您的用户,用户电子邮件和电话号码表,并将结果作为数组返回,然后您可以将这些数据直接放入PHP对象中.例如：
<?PHP /** * this implements a database connection object as a private class member */ class DBUser { private $_conn; /* constructor other functionality */ /** * method to pass an sql query to the database and return an array of results */ public function readTelNos($iUserId) { return $this->_conn->read("SELECT `number` from `tel` WHERE `user_id` = " . (int) $iUserId); } } ?>
这会将您的问题分解为更小,更容易处理的问题,并将它们全部包含在您可以实际使用的漂亮PHP对象中.
如果电话号码和电子邮件可以通过类似$oUser-> getEmails()的内容轻松检索,那么您的用户对象将有一个列表;如果它们作为关联数组存储在对象中,您甚至可以通过“label”$oUser-> getEmail(‘work’)检索它们;
以上是大佬教程为你收集整理的php – 使用父表加入2个子表而不重复全部内容，希望文章能够帮你解决php – 使用父表加入2个子表而不重复所遇到的程序开发问题。
如果觉得大佬教程网站内容还不错，欢迎将大佬教程推荐给程序员好友。
本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ：384754419，请注明来意。