时间戳

首页 > 解决方案 > Join 语句的 SQL 查询

mysql - Join 语句的 SQL 查询

问题描述

我有 3 个表products，pricingGroup和productPricing。我想显示所有产品，即使它没有服务定价并将其状态/IsActive 设置为 false。

我现在有什么疑问

SELECT product.productId, 
       products.ProductCode, 
       product.ProductDetails, 
       product.ProductDescription,
       (CASE WHEN prgrp.pricingGroupId IS NOT NULL && product.IsActive = 1 THEN 1 
       ELSE 0 END) IsActive, 
       prgrp.PricingName,
       pricing.amount 
FROM Products product
LEFT OUTER JOIN productPricing AS pricing ON product.productId = pricing.productId OR pricing.productId IS NULLL
LEFT OUTER JOIN pricingGroup as prgrp on true

我想得到什么

ProductId    ProductCode  ProductDetails  ProductDescrition  IsActive   PricingGroupName  Amount
1            PRCD1        detail1         Descrition1        1          MERCHANT          20
1            PRCD1        detail1         Descrition1        0          Consumer          20
1            PRCD1        detail1         Descrition1        0          OTHERS            20
2            PRCD2        detail2         Descrition2        1          Consumer          25
2            PRCD2        detail2         Descrition2        0          MERCHANT          25
2            PRCD2        detail2         Descrition2        0          OTHERS            25
3            PRCD3        detail3         Descrition3        0          Consumer          0
3            PRCD3        detail3         Descrition3        0          MERCHANT          0
3            PRCD3        detail3         Descrition3        0          OTHERS            0

产品表

ProductId    ProductCode  ProductDetails  ProductDescrition  IsActive
1            PRCD1        detail1         Descrition1        1
2            PRCD2        detail2         Descrition2        1
3            PRCD3        detail3         Descrition3        0

产品定价表

ProductPricingId    PricingGroup  ProductId  Amount  Fee
1                   1             1          20      5
2                   1             2          25      5

定价组表

PricingGroupId    PricingName  
1                 Merchant       
2                 Consumer
3                 Others

标签： mysqlsql

解决方案

  • 您缺少最后一个连接子句pricing.PricingGroup = prgrp.PricingGroupId。
  • 您不需要OR products.productId IS NULL，因为这是Products表的主键并且不会为空。
SELECT product.ProductId, 
    product.ProductCode, 
    product.ProductDetails, 
    product.ProductDescription,
    IF(pricing.ProductId = product.ProductId && product.IsActive = 1, 1, 0) IsActive, 
    prgrp.PricingName, 
    pricing.amount 
FROM Products product
JOIN pricingGroup as prgrp
LEFT JOIN productPricing AS pricing ON pricing.PricingGroup = prgrp.PricingGroupId && pricing.productId = product.productId;

推荐阅读