代码之家 › 专栏 › 技术社区 › Sherin Green

如何将一个主表与两个表连接起来,以获取两个表中主表主id的计数,以及codeigniter中的主表信息

codeigniter-query-builder jointable join codeigniter mysql

-1

Sherin Green · 技术社区 · 8 年前

我有一个主表,它的名称是“用户”表,它包含用户的所有信息,有两个表“业务列表”和“分类列表”。

business_list classified_list 桌子

用户桌子

user_id    user_name     user_email         user_phone
-----------------------------------------------------     
001         Jose         jose@yahoo.in      457855654
002         Tom          tom@yahoo.in       5464644654
003         Nick         nick@yahoo.in      4545645644
004         Rose         rose@yahoo.in      554545441

桌子

bid        user_id      business_name
-----------------------------------------------
001        001          Construction business
002        003          Event business
003        001          Crane business
004        003          Furtinure business
005        004          Realestate business

分类清单 表格:

cid      user_id       classified_name
-------------------------------------------    
001      001           Roller classified
002      004           Home classified
003      003           Chair classified
004      004           Office Classified
005      002           Light decoration classified

User Name   User Email         User Phone     No Of Business     No Of Classified
---------------------------------------------------------------------------------
Jose        jose@yahoo.in      457855654           2                   1
Tom         tom@yahoo.in       5464644654          0                   1
Nick        nick@yahoo.in      4545645644          2                   1
Rose        rose@yahoo.in      554545441           1                   2

那么得到这个结果的mysql连接查询是什么呢?我使用的是php codeigniter 3.0框架,所以如果有人知道codeigniter的话,这很好是否查询此结果?

2 回复 | 直到 8 年前

Nick SamSmith1986 8 年前

此查询将提供所需的结果。它加入了 user UNION 的 business_list classified_list 表,并使用条件聚合对与每个用户关联的业务和分类的数量求和:

SELECT u.user_name AS `User Name`,
       u.user_email AS `User Email`,
       u.user_phone AS `User Phone`,
       SUM(CASE WHEN bc.type = 'business' THEN 1 ELSE 0 END) AS `No Of Business`,
       SUM(CASE WHEN bc.type = 'classified' THEN 1 ELSE 0 END) AS `No Of Classified`
FROM user u
JOIN (SELECT 'business' AS type, user_id 
      FROM business_list
      UNION ALL
      SELECT 'classified' AS type, user_id
      FROM classified_list) bc
ON bc.user_id = u.user_id
GROUP BY u.user_id;

或者,您可以使用 LEFT JOIN 每一张桌子, COUNT 惯性导航与制导 DISTINCT

SELECT u.user_name AS `User Name`,
       u.user_email AS `User Email`,
       u.user_phone AS `User Phone`,
       COUNT(DISTINCT b.bid) AS `No Of Business`,
       COUNT(DISTINCT c.cid) AS `No Of Classified`
FROM user u
LEFT JOIN business_list b ON b.user_id = u.user_id
LEFT JOIN classified_list c ON c.user_id = u.user_id
GROUP BY u.user_id

两个查询的输出相同:

User Name   User Email      User Phone  No Of Business  No Of Classified
Jose        jose@yahoo.in   457855654   2               1
Tom         tom@yahoo.in    5464644654  0               1
Nick        nick@yahoo.in   4545645644  2               1
Rose        rose@yahoo.in   554545441   1               2

SQLFiddle Demo

Vishal Raghavan 8 年前

select u.user_name, u.user_email, u.user_phone, count(distinct b.bid),
       count(distinct c.cid) from user_table u 
   left outer join business_list b 
       on  u.user_id=b.user_id 
   left outer join classified_list c 
       on c.user_id=u.user_id 
group by 
    u.user_name,u.user_email,u.user_phone