如何显示此输出？
如何显示此输出？
提问于 2016-10-21 18:14:01
我的问题是
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回答 4
Stack Overflow用户
发布于 2016-10-21 18:24:51
您不能使用SELECT DISTINCT，但其他人可能会发现它很有用。您可以在编辑下找到答案。
关键字DISTINCT列出唯一行。要了解更多信息，请阅读以下内容：http://www.w3schools.com/sql/sql_distinct.asp。
编辑：
现在我看到了问题所在，我没有注意到之前的数字不符合。关键字DISTINCT在这种情况下不会有帮助。JOIN首先连接Shirt_Mes表，但有两条记录，这意味着它将生成两行。接下来，JOIN连接表Pant_Mes，condition也从该表中选择了两条记录。前面的每一行都针对这两条记录进行了扩展。在本例中，它生成2x2行。
我想，这是一个糟糕的表模式。您可以执行两个查询并在应用程序逻辑中组合结果：
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。
Stack Overflow用户
发布于 2016-10-21 18:22:58
这应该是可行的：
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_TypeStack Overflow用户
发布于 2016-10-21 18:24:54
尝试此查询
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页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/40173955
复制相关文章
相似问题
腾讯云开发者
