代码之家 › 专栏 › 技术社区 › Kannan K

使用三个表的连接查询mysql

group-by mysql

-1

Kannan K · 技术社区 · 7 年前

我有三张桌子p_c_n_详细信息,供应商详细信息,pcn_类型。我尝试使用这些表连接查询。但是分组时有一个错误。

我的问题:

SELECT pcn_type.name, p_c_n_details.SupplierName, COUNT(p_c_n_details.JPN_ID) 
FROM pcn_type LEFT OUTER JOIN p_c_n_details RIGHT OUTER JOIN supplier_details 
ON p_c_n_details.type = pcn_type.name AND p_c_n_details.SupplierName = 
supplier_details.SupplierName GROUP BY 
pcn_type.name,supplier_details.SupplierName;

pcn U类型表:

id    |    name
-------------------------
1          Process Change
2          Design Change
3          EOL

供应商详细信息表:

id    |    SupplierName
------------------------
1          abc
2          def
3          ghi

p_c_n_详细信息表:

id.   |    SupplierName    |    type           |    JPN_ID
1          abc                  Process Change      0023
2          abc                  Process Change      0024
3          abc                  Process Change      0025
4          abc                  Design Change       0026
5          abc                  Design Change       0027
6          def                  Process Change      0028
7          def                  Process Change      0029
8          def                  EOL                 0030
9          def                  EOL                 0031

预期结果:

name            |    supplier  |    total
------------------------------------------------------    
Process Change       abc            03
Design Change        abc            02
EOL                  abc             0
Process Change       def            02
Design Change        def             0
EOL                  def            02

我得到的错误:

#1064 - You have an error in your SQL syntax; check the manual that 
corresponds to your MySQL server version for the right syntax to use near 
'GROUP BY pcn_type.name,supplier_details.SupplierName LIMIT 0, 25' at line 1

我需要在查询中更改什么来更正错误。

3 回复 | 直到 7 年前

Rupesh Agrawal 7 年前

您可以尝试使用如下查询

Select pt.name  as name,  sd.SupplierName  as supplier,count(pd.id)
from pcn_type pt 
join   supplier_details sd 
left join   p_c_n_details pd on  pd.type = pt.name and pd.Suppliername = sd.SupplierName 
group by  pt.name,  sd.SupplierName 
order by  sd.SupplierName

输出:-

使用表和sql查询创建环境,在其中可以直接测试查询。请访问fiddle了解更多详细信息 http://sqlfiddle.com/#!9/d379c3/18

D P 7 年前

查询出错,请检查下面更新的查询并与旧查询进行比较:

SELECT
    pcn_type. NAME,
    p_c_n_details.SupplierName,
    COUNT(p_c_n_details.id)
FROM
    pcn_type
LEFT OUTER JOIN p_c_n_details ON p_c_n_details.type = pcn_type.name 
RIGHT OUTER JOIN supplier_details ON p_c_n_details.Suppliername = supplier_details.SupplierName
GROUP BY p_c_n_details.Suppliername, p_c_n_details.type;

D-Shih 7 年前

你可以试着用 CROSS JOIN 笛卡尔积 得到 pcn_type.name 和 supplier_details.SupplierName .

您似乎想过滤名称,如果它不存在 p_c_n_details 表中,写入子查询条件 where 获得名称存在 详细信息 ,然后使用 OUTER JOIN 和 COUNT

CREATE TABLE pcn_type(
     id int,
     name varchar(50)
);


INSERT INTO pcn_type VALUES (1,'Process Change');
INSERT INTO pcn_type VALUES (2,'Design Change');
INSERT INTO pcn_type VALUES (3,'EOL');

CREATE TABLE supplier_details(
     id int,
     SupplierName varchar(50)
);

INSERT INTO supplier_details VALUES (1,'abc');
INSERT INTO supplier_details VALUES (2,'def');
INSERT INTO supplier_details VALUES (3,'ghi');

CREATE TABLE p_c_n_details(
     id int,
     SupplierName varchar(50),
     type  varchar(50)
);


INSERT INTO p_c_n_details VALUES (1,'abc','Process Change');
INSERT INTO p_c_n_details VALUES (2,'abc','Process Change');
INSERT INTO p_c_n_details VALUES (3,'abc','Process Change');
INSERT INTO p_c_n_details VALUES (4,'abc','Design Change');
INSERT INTO p_c_n_details VALUES (5,'abc','Design Change');
INSERT INTO p_c_n_details VALUES (6,'def','Process Change');
INSERT INTO p_c_n_details VALUES (7,'def','Process Change');
INSERT INTO p_c_n_details VALUES (8,'def','EOL');
INSERT INTO p_c_n_details VALUES (9,'def','EOL');

问题1 :

select t.name,t.SupplierName,COUNT(t1.id) total
from
(
  SELECT p.name,s.SupplierName 
  FROM   
    pcn_type p
  CROSS JOIN 
    supplier_details s
  WHERE 
    p.name IN (SELECT DISTINCT type FROM p_c_n_details) 
  AND
    s.SupplierName IN (SELECT DISTINCT SupplierName FROM p_c_n_details) 
) t
LEFT JOIN p_c_n_details t1 on t.name = t1.type and t.SupplierName = t1.SupplierName
GROUP BY  t.name,t.SupplierName
ORDER BY  t.SupplierName

Results :

|           name | SupplierName | total |
|----------------|--------------|-------|
|            EOL |          abc |     0 |
| Process Change |          abc |     3 |
|  Design Change |          abc |     2 |
| Process Change |          def |     2 |
|            EOL |          def |     2 |
|  Design Change |          def |     0 |