我有三张表，包括采购详情、供应商详情、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类型表：
id | name
-------------------------
1 Process Change
2 Design Change
3 EOL供应商详细信息表：
id | SupplierName
------------------------
1 abc
2 def
3 ghip\ c\ n\ U明细表：
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条答案
按热度按时间dgjrabp21#
您的查询有错误，请检查以下更新的查询并与旧查询进行比较：
wz1wpwve2#
您可以尝试使用查询
output:-
使用表ans sql query创建环境，您可以在其中直接测试查询。更多详情请访问fiddlehttp://sqlfiddle.com/#!2018年9月d379c3日
fkvaft9z3#
你可以试着用
CROSS JOIN笛卡尔积pcn_type.name以及supplier_details.SupplierName.你似乎想过滤这个名字，如果它不存在的话
p_c_n_details表中写入子查询条件where获取要存在于中的名称p_c_n_details，然后使用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');
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