如何显示这个输出

本文关键字：输出 显示 何显示 | 更新日期: 2023-09-27 18:14:24

我的查询是

SELECT 
    CustBillPayment.CustomerId, CustBillPayment.Customer_Name, 
    CustBillPayment.CustBill_Total, CustBillPayment.CustBill_Paid,
    CustBillPayment.CustBill_ReamingAmt, CustBillPayment.Created_Date, 
    CustBillPayment.Delivery_Date, CustBillPayment.Updated_Date, 
    Shirt_Mes.Shirt_Type, Shirt_Mes.Shirt_Qty, Shirt_Mes.Shirt_Price, 
    Shirt_Mes.Shirt_Total, 
    Pant_Mes.Pant_Type, Pant_Mes.Pant_QTY, Pant_Mes.Pant_Total, 
    Pant_Mes.Pant_Price
FROM 
    ((CustBillPayment 
INNER JOIN
    Shirt_Mes ON CustBillPayment.CustomerId = Shirt_Mes.CustomerId) 
INNER JOIN
    Pant_Mes ON CustBillPayment.CustomerId = Pant_Mes.CustomerId) 
WHERE 
    CustBillPayment.CustomerId = 17

输出如下所示，显示重复的行:

CustomerId CustNm  SType   SQty SPrice  STotal PType    PQTY    PTotal  PPrice
-------------------------------------------------------------------------------
17         lakhan  sType1   2    200     400   PType3   3         900     300
17         lakhan  sType1   2    200     400   PType4   1         400     400
17         lakhan  sType2   1    250     250   PType3   3         900     300
17         lakhan  sType2   1    250     250   PType4   1         400     400

但是我想

CustomerId CustNm  SType   SQty SPrice  STotal PType    PQTY    PTotal  PPrice
-------------------------------------------------------------------------------
17         lakhan  sType1   2    200     400   PType3   3         900     300
17         lakhan  sType2   1    250     250   PType4   1         400     400

如何显示这个输出

您不能SELECT DISTINCT，但其他人可能会发现它很有用。你的答案在下编辑

关键字DISTINCT列出唯一的行。要了解更多信息，请阅读:http://www.w3schools.com/sql/sql_distinct.asp.

编辑:

现在我看到问题了，我之前没有注意到数字不合适。关键词DISTINCT在这种情况下没有帮助。JOIN首先连接Shirt_Mes表，但是有两条记录，这意味着它将产生两行。接下来，JOIN连接表Pant_Mes，并且condition也从这个表中选择了两条记录。前面的每一行都针对这两条记录进行扩展。在本例中，它生成2 x 2行。

我猜，这是一个糟糕的表模式。您可以执行两个查询并在应用程序逻辑中组合结果:

SELECT
  CustBillPayment.CustomerId,
  CustBillPayment.Customer_Name,
  CustBillPayment.CustBill_Total,
  CustBillPayment.CustBill_Paid,
  CustBillPayment.CustBill_ReamingAmt,
  CustBillPayment.Created_Date,
  CustBillPayment.Delivery_Date,
  CustBillPayment.Updated_Date,
  Shirt_Mes.Shirt_Type,
  Shirt_Mes.Shirt_Qty,
  Shirt_Mes.Shirt_Price,
  Shirt_Mes.Shirt_Total
FROM
  CustBillPayment
  JOIN Shirt_Mes ON CustBillPayment.CustomerId = Shirt_Mes.CustomerId
WHERE
  CustBillPayment.CustomerId=17

和

SELECT
  CustBillPayment.CustomerId,
  CustBillPayment.Customer_Name,
  CustBillPayment.CustBill_Total,
  CustBillPayment.CustBill_Paid,
  CustBillPayment.CustBill_ReamingAmt,
  CustBillPayment.Created_Date,
  CustBillPayment.Delivery_Date,
  CustBillPayment.Updated_Date,
  Pant_Mes.Pant_Type,
  Pant_Mes.Pant_QTY,
  Pant_Mes.Pant_Total,
  Pant_Mes.Pant_Price
FROM
  CustBillPayment
  JOIN Pant_Mes ON CustBillPayment.CustomerId = Pant_Mes.CustomerId
WHERE
  CustBillPayment.CustomerId=17

或者你可以把所有的商品放在一个表(我猜这是为电子商店或类似的东西)。更好的方法是更改表模式，但是如果您有很好的理由将商品存储在单独的表中(并且商品类型不是很好的理由)，您可以执行以下查询:

SELECT
  CustBillPayment.CustomerId,
  CustBillPayment.Customer_Name,
  CustBillPayment.CustBill_Total,
  CustBillPayment.CustBill_Paid,
  CustBillPayment.CustBill_ReamingAmt,
  CustBillPayment.Created_Date,
  CustBillPayment.Delivery_Date,
  CustBillPayment.Updated_Date,
  Goods_Mes.Type,
  Goods_Mes.Qty,
  Goods_Mes.Price,
  Goods_Mes.Total
FROM
  CustBillPayment
  JOIN (SELECT Shirt_Mes.Shirt_Type AS Type,
               Shirt_Mes.Shirt_Qty AS Qty,
               Shirt_Mes.Shirt_Price AS Price,
               Shirt_Mes.Shirt_Total AS Total,
               Shirt_Mes.CustomerId FROM Shirt_Mes
        UNION
        SELECT Pant_Mes.Pant_Type AS Type,
               Pant_Mes.Pant_Qty AS Qty,
               Pant_Mes.Pant_Price AS Price,
               Pant_Mes.Pant_Total AS Total,
               Pant_Mes.CustomerId FROM Pant_Mes
        ) Goods_Mes ON CustBillPayment.CustomerId = Goods_Mes.CustomerId
WHERE
  CustBillPayment.CustomerId=17

为了使查询更好，您可以删除Shirt_Total和Pant_Total，并用Goods_Mes.Qty * Goods_Mes.Price替换Goods_Mes.Total。

应该可以了:

SELECT DISTINCT CustBillPayment.CustomerId,
            CustBillPayment.Customer_Name,
            CustBillPayment.CustBill_Total,
            CustBillPayment.CustBill_Paid,
            CustBillPayment.CustBill_ReamingAmt,
            CustBillPayment.Created_Date,
            CustBillPayment.Delivery_Date,
            CustBillPayment.Updated_Date,
            Shirt_Mes.Shirt_Type,
            Shirt_Mes.Shirt_Qty,
            Shirt_Mes.Shirt_Price,
            Shirt_Mes.Shirt_Total,
            Pant_Mes.Pant_Type,
            Pant_Mes.Pant_QTY,
            Pant_Mes.Pant_Total,
            Pant_Mes.Pant_Price
  FROM ((CustBillPayment INNER JOIN Shirt_Mes ON CustBillPayment.CustomerId = Shirt_Mes.CustomerId) INNER JOIN
        Pant_Mes ON CustBillPayment.CustomerId = Pant_Mes.CustomerId)
 WHERE CustBillPayment.CustomerId = 17
 GROUP BY Shirt_Mes.Shirt_Type

试试这个查询

SELECT CBP.CustomerId, CBP.Customer_Name, 
       CBP.CustBill_Total, CBP.CustBill_Paid, 
       CBP.CustBill_ReamingAmt, CBP.Created_Date, 
       CBP.Delivery_Date, CBP.Updated_Date, 
       SMES.Shirt_Type, SMES.Shirt_Qty, SMES.Shirt_Price, 
       SMES.Shirt_Total, PMES.Pant_Type, PMES.Pant_QTY,    
       PMES.Pant_Total, PMES.Pant_Price 
FROM   CustBillPayment AS CBP 
LEFT JOIN Shirt_Mes AS SMES ON CBP.CustomerId = SMES.CustomerId 
LEFT JOIN Pant_Mes AS PMES ON CBP.CustomerId = PMES.CustomerId 
WHERE CBP.CustomerId = 17

Distinct and group by只有在记录是唯一的情况下才有效。

Distinct仍然会返回所有4行，因为每行的pant_type不同。

组也是如此。

当顶部所需的输出对于pant_type没有意义时，您期望的最终输出是什么?

相关文章：