mysql - SQL查询未显示所需结果

Question

我有一个 SQL 查询，我必须查询三个表(其中一些表两次)，但没有得到我需要的结果:

我需要在“文章”中搜索搜索字符串(“网球”)并查找这些文章的第二个表“订单详细信息”。点击数显示订购量。

现在我需要检查这些元素是否已经交付。因此，我在“订单”表中查找“订单详细信息”，并在同一个表中查找这些订单是否有“交货”。 “订单”和“送货”有一个字段显示其类型。

所以我必须检查“交货”是否由“订单”转发。

接下来检查此“交付”是否包含“文章”并总结已交付的文章。

如果发送的文章数量少于订购的文章数量，我想显示此记录。

到目前为止，除了这些项目之外，它还可以工作: - [已解决] 感谢 HLGEM，没有“交货”的“订单”根本不显示 - [已解决] 显示了与“交货”金额相同的“订单”，但我不希望它们这样做。

这是我到目前为止所拥有的:

PrO:处理订单
PrD:流程交付
a: 文章项目
p: 处理订单项
d: 交货项目

[更新了代码]

SELECT 
    a.Articlenumber AS Article,
    PrO.Number AS Order, 
    PrD.Number AS Delivery,
    p.Amount AS Orderamount,
    SUM(d.Amount) AS Deliveryamount,
    (p.Amount - Deliveryamount) AS OpenAmount

FROM Article AS a
    INNER JOIN ProcessesDetails AS p
    ON  (a.ArticleNumber = p.Article)
        AND LEFT(p.Order, 3) = 'OR-'
    INNER JOIN Processes as PrO
    ON  PrO.Number = p.Order
        AND TEXTSEARCH('Delivery:' IN PrO.Forwarded)
    LEFT JOIN Processes as PrD
    ON  PrO.Nummer = PrD.ForwardedFrom
        AND LEFT(PrD.Number,3) = 'DE-' 
    INNER JOIN ProcessesDetails as d
    ON  PrD.Number = d.Order
        AND d.Article = p.Article
WHERE (a.Categorie = 'tennis') 
GROUP BY(Article)

添加以下行解决了第二个问题:

HAVING Deliveryamount < Orderamount

Answer 1

SELECT 
    a.Articlenumber AS Article,        
    PrO.Number AS Order, 
    PrD.Number AS Delivery,    
    p.Amount AS Orderamount,    
    SUM(d.Amount) AS Deliveryamount,    
    (p.Amount - Deliveryamount) AS OpenAmount    
FROM Article AS a    
    INNER JOIN ProcessesDetails AS p ON (a.ArticleNumber = p.Article)   
    INNER JOIN Processes as PrO ON PrO.Number = p.Order
    LEFT JOIN Processes as PrD ON PrO.Nummer = PrD.ForwardedFrom AND LEFT(PrD.Number,3) = 'DE-'
    INNER JOIN ProcessesDetails as d ON PrD.Number = d.Order
WHERE (a.Categorie = 'tennis') 
AND LEFT(p.Order, 3) = 'OR-'
AND TEXTSEARCH('Delivery:' IN PrO.Forwarded)AND d.Article = p.Article
GROUP BY(Article)

这将解决您的左连接问题。