MySQL根据附加表的条件选择行
总而言之，我有几家公司拥有多个站点和多个部门。每个部门都有一个或多个网站。我正在重复使用所有公司的部门和网站表格。我基本上拥有一个全球性的分支机构和网站，供每家公司使用。我的最终目标是将所有属于公司分部的网站都拉出来。MySQL根据附加表的条件选择行
Company 1 |
+ Division 1
| |
| + Site 1
| + Site 2
|
+ Division 2
|
Company 2
|
+ Division 3
| |
| + Site 3
| + Site 2
|
+ Division 1
|
公司
+----+-------------+ | id | name |
+----+-------------+
| 1 | company 1 |
+----+-------------+
| 2 | company 2 |
+----+-------------+
网站
+----+-------------+ | id | name |
+----+-------------+
| 1 | site 1 |
+----+-------------+
| 2 | site 2 |
+----+-------------+
| 3 | site 3 |
+----+-------------+
部门
+----+-----------------+ | id | name |
+----+-----------------+
| 1 | division 1 |
+----+-----------------+
| 2 | division 2 |
+----+-----------------+
| 3 | division 3 |
+----+-----------------+
company_divisions
+----------+--------------+ | company | division |
+----------+--------------+
| 1 | 1 |
+----------+--------------+
| 1 | 2 |
+----------+--------------+
| 2 | 1 |
+----------+--------------+
| 2 | 3 |
+----------+--------------+
company_sites
+----------+------------+ | company | site |
+----------+------------+
| 1 | 1 |
+----------+------------+
| 1 | 2 |
+----------+------------+
| 2 | 2 |
+----------+------------+
| 2 | 3 |
+----------+------------+
我本来以为我可以选择全部由company.id和division.id制约sites的，但我已经没有这样的运气。我试图子查询：
select * from sites
where id IN (select site from company_sites where company = 3)
，并加入：
select s.* from sites s
inner join company_sites cs on s.id = cs.site
where cs.company = 3
但这些结果仅与company_site而不是division。我似乎无法弄清楚如何获得company_divisions表参与..这样的事情：
select s.* from sites s
inner join company_sites cs on s.id = cs.site
inner join company_divisions cd on divisions.id = cd.division
where cs.company = 2 AND cd.division = 3
我如何添加，以确保一个附加条件或查询，同样的company.id这是用来选择site在company_sites涉及company.id在company_division通过division.id？
例如给出company.id = 2和division.id = 3我期望site 2和site 3的结果。
建设性的批评总是值得欢迎的。
回答：
对于这样的树结构，我可能会删除company_divisions和company_sites表并设计它。
company +----+-------------+
| id | name |
+----+-------------+
| 1 | company 1 |
+----+-------------+
| 2 | company 2 |
+----+-------------+
name should be unique, id is the primary key
divisions
+----+-----------------+-------------+
| id | name | company id |
+----+-----------------+-------------+
| 1 | division 1 | 1 |
+----+-----------------+-------------+
| 2 | division 2 | 1 |
+----+-----------------+-------------+
| 3 | division 3 | 2 |
+----+-----------------+-------------+
| 4 | division 1 | 2 |
+----+-----------------+-------------+
id is the primary key, company id is foreign key referenced to company.id.
sites
+----+-------------+-------------+
| id | name | division id |
+----+-------------+-------------+
| 1 | site 1 | 1 |
+----+-------------+-------------+
| 2 | site 2 | 1 |
+----+-------------+-------------+
| 3 | site 3 | 3 |
+----+-------------+-------------+
| 4 | site 2 | 3 |
+----+-------------+-------------+
id is the primary key, division id is foreign key referenced to divisions.id.
使用查询
SELECT sites.name as `site`,
divisions.name as `division`,
company.name as `company`
FROM sites
LEFT JOIN divisions ON sites.`division id` = divisions.id
LEFT JOIN company ON divisions.`company id` = company.id
会从这里起给
+-------------+------------+-----------+ | site | division | company |
+-------------+------------+-----------+
| site 1 | division 1 | company 1 |
+-------------+------------+-----------+
| site 2 | division 1 | company 1 |
+-------------+------------+-----------+
| site 3 | division 3 | company 2 |
+-------------+------------+-----------+
| site 2 | division 3 | company 2 |
+-------------+------------+-----------+
过滤应该是很容易的，只需要添加WHERE条件落后。
回答：
您在这样的事情中给出的代码会检查company_site.company = 3，这是表中不存在的值。与company_divisions表类似。事实上，没有公司3.尝试存在的数据
回答：
我会建议这个，如果你可以修改结构并有单独的分割网站映射表。这也可以让一个网站属于多个分部到多个网站。
Company表：
+----+-------------+ | id | name |
+----+-------------+
| 1 | company_1 |
+----+-------------+
| 2 | company_2 |
+----+-------------+
司表：
+----+-------------+--------------+ | id | name | company_id |
+----+-------------+ -------------+
| 1 | division 1 | 1 |
+----+-------------+--------------+
| 2 | division 2 | 2 |
+----+-------------+--------------+
| 3 | division 3 | 2 |
+----+-------------+--------------+
| 4 | division 4 | 3 |
+----+-------------+--------------+
站点表：
+----+----------------------+ | id | url |
+----+----------------------+
| 1 | http:\www.url1.com |
+----+----------------------+
| 2 | http:\www.url2.com |
+----+----------------------+
| 3 | http:\www.url3.com |
+----+----------------------+
| 4 | http:\www.url4.com |
+----+----------------------+
division_site表：
+----+-------------+--------------+ | id | div_id | site_id |
+----+-------------+ -------------+
| 1 | 1 | 1 |
+----+-------------+--------------+
| 2 | 1 | 2 |
+----+-------------+--------------+
| 3 | 2 | 1 |
+----+-------------+--------------+
| 4 | 2 | 3 |
+----+-------------+--------------+
| 5 | 2 | 4 |
+----+-------------+--------------+
| 6 | 3 | 1 |
+----+-------------+--------------+
| 7 | 4 | 2 |
+----+-------------+--------------+
所以，你可以有这样的：
select company.name as company_name ,division.name as division_name ,GROUP_CONCAT(sites.url) as "Site URL's" from company inner join division on division.company_id = company.id left JOIN div_sites on div_sites.div_id = division.id inner join sites on sites.id = div_sites.site_id where company.id = 1 and division.id =1 GROUP by division.id 这将返回
+----+-------------+-------------------+-----------------------+ | company name | division name | Url's |
+----+-------------+-------------------+------------------------
| company_1 | div 1 | http:\www.url1.com, |
| | | http:\www.url2.co |
+------------------+-------------------+-----------------------+
以上是 MySQL根据附加表的条件选择行 的全部内容， 来源链接： utcz.com/qa/259840.html
