Microsoft SQL Server Management Studio - 从两个没有重复列的INNER JOIN表中选择ALL

I'm using Microsoft SQL Server Management Studio and have tried using GROUP BY and WHERE statements, I have also tried to use DISTINCT but am not sure if I am putting these in the wrong places or what. I've tried several different way, several different times with no luck. Either I get an error or I get duplicates.

我正在使用Microsoft SQL Server Management Studio并尝试使用GROUP BY和WHERE语句,我也尝试使用DISTINCT,但我不确定是否将这些放在错误的地方或者是什么。我尝试了几种不同的方式,几次不同的运气。要么我得到一个错误,要么我得到重复。

Here's the problem:

这是问题所在:

/*
JOIN the Orders and OrderDetails tables to display all available fields
about an order; however, only display the order number one time.
Display an additional column labeled SubTotal that will multiply
the quoted price by the quantity ordered.
*/

Here's what I've got so far minus the non-working failed attempted bits:

这是我到目前为止所得到的减去非工作失败的尝试位:

SELECT *,
       quotedPrice * quantityOrdered As [SubTotal]
FROM Orders INNER JOIN OrderDetails ON Orders.orderNumber = OrderDetails.orderNumber

• I tried to put GROUPBY orderNumber

我试图把GROUPBY的orderNumber

• I tried to make another row in the select statement for DISTINCT orderNumber just above the row with the asterisk(all).

我尝试在带有星号(全部)的行上方的DISTINCT orderNumber的select语句中创建另一行。

• I tried to use WHERE Orders.orderNumber = OrderDetails.orderNumber

我尝试使用WHERE Orders.orderNumber = OrderDetails.orderNumber

I've not been using SQL for very long, only about four months now. I'm still learning.

我已经很久没用SQL了,现在只用了四个月。我还在学习。

1 个解决方案

#1

You want something like the following. Because I don't know your column names the following is more like a guess. Notice that I have used table aliases O for Orders and OD for OrderDetails. This makes it easier to be specific about which columns to display. Also notice (importantly for your question) that I have only specified OrderNumber once (the other reference is commented out).

你想要的东西如下。因为我不知道你的列名,所以下面更像是一个猜测。请注意,我已使用表别名O表示Orders,OD表示OrderDetails。这使得更容易具体说明要显示的列。还要注意(重要的是你的问题)我只指定了OrderNumber一次(另一个引用被注释掉了)。

SELECT
  O.OrderNumber,
  O.ColName2
  O.ColName3,
  O.ColName4,
  O.ColName5,
  ... --etc
  --OD.OrderNumber,
  OD.ColNam1,
  OD.ColName2,
  ... --etc
  OD.quotedPrice,
  OD.quantityOrdered,
  OD.quotedPrice * OD.quantityOrdered AS [SubTotal]
FROM
  Orders O
  INNER JOIN OrderDetails OD
    ON O.orderNumber = OD.orderNumber

BTW It is rarely good practice to use the * character to list all columns even though it seems convenient to do so. This is one example where it can be bad.

BTW使用*字符列出所有列是很好的做法,即使这样做很方便。这是一个很糟糕的例子。