将6个表连接成一个查询？

Question

将6个表连接成一个查询？

4

嘿，有人可以帮我将下面的5个表连接成一个查询吗？我目前有以下查询，但似乎不起作用，如果在hires表中有两个具有相同ID的产品，则从products表中返回所有产品，这显然是错误的。

SELECT products.prod_id, products.title, products.price, product_types.name,  
listagg(suppliers.name, ',') WITHIN GROUP(ORDER BY suppliers.name) suppliers
FROM products 
INNER JOIN product_suppliers ON products.prod_id = product_suppluer.prod_id 
INNER JOIN product_types ON product_types.type_id = products.type_id 
INNER JOIN suppliers ON product_suppliers.supp_id = suppliers.supp_id 
LEFT OUTER JOIN hires ON hires.prod_id = products.prod_id 
    WHERE (hires.hire_end < to_date('21-JAN-13') OR hires.hire_start > to_date('26-JAN-13')) 
    OR hires.prod_id IS NULL 
GROUP BY products.prod_id, products.title, products.price, product_types.name

表格数据：

PRODUCTS                 
--------------------------------------------
| Prod_ID  | Title   | Price   | Type_ID   |
|------------------------------------------|
| 1        | A       | 5       | 1         |
| 2        | B       | 7       | 1         |
| 3        | C       | 3       | 2         |
| 4        | D       | 3       | 3         |
|------------------------------------------|

PRODUCT_TYPES                 
----------------------
| Type_ID  | Type    |
|--------------------|
| 1        | TYPE_A  |
| 2        | TYPE_B  |
| 3        | TYPE_C  |
| 4        | TYPE_D  |
|--------------------|

PRODUCT_SUPPLIERS                 
-------------------------
| Prod_ID  | Supp_ID    |
|-----------------------|
| 1        | 1          |
| 1        | 2          |
| 2        | 2          |
| 3        | 3          |
| 4        | 4          |
|-----------------------|

SUPPLIERS                 
----------------------
| Supp_ID  | Name    |
|--------------------|
| 1        | SUPP_A  |
| 2        | SUPP_B  |
| 3        | SUPP_C  |
| 4        | SUPP_D  |
|--------------------|

HIRES                
---------------------------------------------------------------
| Hire_ID  | Prod_ID    | Cust_ID    | Hire_Start | Hire_End  |
|-----------------------|------------|------------------------|
| 1        | 1          | 1          | 22-Jan-13  | 23-Jan-13 |
| 2        | 2          | 2          | 27-Jan-13  | 29-Jan-13 |
| 3        | 1          | 3          | 30-Jan-13  | 31-Jan-13 |
|-----------------------|------------|------------|-----------|

PRODUCTS                 
--------------------------------
| Cust_ID  | Name    | Phone   |
|------------------------------|
| 1        | Cust_A  | 555-666 |
| 2        | Cust_B  | 444-234 |
| 3        | Cust_C  | 319-234 |
| 4        | Cust_D  | 398-092 |
|------------------------------|

目前查询的输出如下：

-------------------------------------------------------------
| Prod_ID  | Title   | Price   | Type_ID   | Suppliers      |
|------------------------------------------|----------------|
| 1        | A       | 5       | Type_A    | SUPP_A,SUPP_B |
| 2        | B       | 7       | Type_B    | SUPP_B         |
| 3        | C       | 3       | Type_C    | SUPP_C         |
| 4        | D       | 3       | Type_D    | SUPP_D         |
|------------------------------------------|----------------|

当然应该是这样的，因为Prod_ID '1'在查询日期范围内被租用。

-------------------------------------------------------------
| Prod_ID  | Title   | Price   | Type_ID   | Suppliers      |
|------------------------------------------|----------------|
| 2        | B       | 7       | Type_B    | SUPP_B         |
| 3        | C       | 3       | Type_C    | SUPP_C         |
| 4        | D       | 3       | Type_D    | SUPP_D         |
|------------------------------------------|----------------|

如果有人能帮助修改查询语句以按建议输出，我将非常感激。因为我的理解是它应该按照编写的方式工作？

- user1851487

你有两个 PRODUCTS 表。怎么回事？ - Hamlet Hakobyan

2个产品表存在错误，已做更改，抱歉。 - user1851487

4个回答

0

当左外连接没有匹配时，它将返回空值，这意味着当此连接查询的结果为 Null 时，您仍将拥有一行（没有 HIRE 表数据）：

LEFT OUTER JOIN hires ON hires.prod_id = products.prod_id 
WHERE (hires.hire_end < to_date('21-JAN-13') 
OR hires.hire_start > to_date('26-JAN-13')) 
OR hires.prod_id IS NULL

尝试在查询中添加从hires表（例如hire.Hire_Start）选择以查看发生的情况，然后将其切换为内部连接，我认为您的问题将得到解决。

或者在完整查询中添加WHERE子句，例如hire.Hire_Start不为空

编辑

如果您将原始查询更改为：

SELECT hires.Hire_Start, products.prod_id, products.title, products.price, product_types.name,  
listagg(suppliers.name, ',') WITHIN GROUP(ORDER BY suppliers.name) suppliers
FROM products 
INNER JOIN product_suppliers ON products.prod_id = product_suppluer.prod_id 
INNER JOIN product_types ON product_types.type_id = products.type_id 
INNER JOIN suppliers ON product_suppliers.supp_id = suppliers.supp_id 
LEFT OUTER JOIN hires ON hires.prod_id = products.prod_id 
    WHERE (hires.hire_end < to_date('21-JAN-13') OR hires.hire_start > to_date('26- JAN-13')) 
    OR hires.prod_id IS NULL 
GROUP BY products.prod_id, products.title, products.price, product_types.name

“Hire_Start”列返回什么？

然后，如果您将它添加到where子句中，是否会得到预期结果:

SELECT hires.Hire_Start, products.prod_id, products.title, products.price, product_types.name,  
listagg(suppliers.name, ',') WITHIN GROUP(ORDER BY suppliers.name) suppliers
FROM products 
INNER JOIN product_suppliers ON products.prod_id = product_suppluer.prod_id 
INNER JOIN product_types ON product_types.type_id = products.type_id 
INNER JOIN suppliers ON product_suppliers.supp_id = suppliers.supp_id 
LEFT OUTER JOIN hires ON hires.prod_id = products.prod_id 
    WHERE (hires.hire_end < to_date('21-JAN-13') OR hires.hire_start > to_date('26- JAN-13')) 
    OR hires.prod_id IS NULL 
WHERE hires.Hire_Start is not null
GROUP BY products.prod_id, products.title, products.price, product_types.name

最后，完全放弃外连接，这样做是否符合预期？

SELECT hires.Hire_Start, products.prod_id, products.title, products.price, product_types.name,  
listagg(suppliers.name, ',') WITHIN GROUP(ORDER BY suppliers.name) suppliers
FROM products 
INNER JOIN product_suppliers ON products.prod_id = product_suppluer.prod_id 
INNER JOIN product_types ON product_types.type_id = products.type_id 
INNER JOIN suppliers ON product_suppliers.supp_id = suppliers.supp_id 
INNER JOIN hires ON hires.prod_id = products.prod_id 
    WHERE (hires.hire_end < to_date('21-JAN-13') OR hires.hire_start > to_date('26- JAN-13'))
GROUP BY products.prod_id, products.title, products.price, product_types.name

请注意：OR Hires.prod_ID是否意味着如果结果没有返回雇佣信息，则该信息可用，如果是这种情况，您需要像提供的其他答案一样编写查询。

- Matthew

嗨，谢谢回复。抱歉我不太明白你说的添加一个从“hires”表中选择并将其更改为内部连接的意思。 - user1851487

0

这里有一些可能会对你有帮助的代码：

SELECT L.V_PRODUCT_ID  "PROD_ID"   , L.TITLE "TITLE" , L.PRICE "PRICE"  , L.TYPE "TYPE" , S.NAME "SUPPLIERS"
FROM 

(SELECT V_PRODUCT_ID   , TITLE , PRICE , TYPE , SUPPLIER_ID FROM 

((select p.prod_id  v_product_id , p.title  TITLE , p.price PRICE  , t.type  TYPE
from products p , products_types t 
where p.type_id = t_type_id)   A 

JOIN
 (SELECT PROD_ID  VV_PRODUCT_ID  ,  SUPP_ID  SUPPLIER_ID  
FROM  PRODUCTS_SUPPLIERS)  H 

ON (A.V_PRODUCT_ID  = H.VV_PRODUCT_ID)))   L
JOIN 
SUPLLIERS  S 
ON (L.SUPPLIER_ID = S.SUPP_ID);

- amr

-3

SELECT Emp.Empid, Emp.EmpFirstName, Emp.EmpLastName, Dept.DepartmentName 从 Employee Emp INNER JOIN Department dept ON Emp.Departmentid=Dept.Departmenttid

- user2011577

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- sgeddes · Accepted Answer

你的问题是 Prod_Id 1 同时在这些日期范围内和之外。因此，使用子查询来过滤出在这些范围内的 Prod_Id，并将其排除掉。

以下是你查询语句的简化版：

SELECT P.Prod_ID
FROM Products P 
 LEFT JOIN (
   SELECT Prod_ID 
   FROM Hires
   WHERE hire_end >= To_Date('20130121', 'yyyymmdd') AND hire_start <= To_Date('20130126', 'yyyymmdd')
   ) H ON P.Prod_ID = H.Prod_ID
WHERE h.prod_id IS NULL

并且还有 SQL Fiddle。

假设我复制和粘贴正确，这应该是您的查询：

SELECT products.prod_id, products.title, products.price, product_types.name,  
listagg(suppliers.name, ',') WITHIN GROUP(ORDER BY suppliers.name) suppliers
FROM products 
INNER JOIN product_suppliers ON products.prod_id = product_suppluer.prod_id 
INNER JOIN product_types ON product_types.type_id = products.type_id 
INNER JOIN suppliers ON product_suppliers.supp_id = suppliers.supp_id 
LEFT JOIN (
   SELECT Prod_ID 
   FROM Hires
   WHERE hire_end >= To_Date('20130121', 'yyyymmdd') AND hire_start <= To_Date('20130126', 'yyyymmdd')
   ) H ON products.Prod_ID = H.Prod_ID
WHERE H.Prod_ID IS NULL
GROUP BY products.prod_id, products.title, products.price, product_types.name

希望这可以帮到您。