主页 / dba / 问题 / 11925
Accepted
shamim
Asked: 2012-01-31 09:30:18 +0800 CST

如何获取唯一记录

  • 772

表结构

CREATE TABLE [dbo].[Order Details2](
    [OrderID] [int] NOT NULL,
    [ProductID] [int] NOT NULL,
    [UnitPrice] [money] NOT NULL   DEFAULT (0),
    [Quantity] [smallint] NOT NULL   DEFAULT (1),
    [Discount] [real] NOT NULL   DEFAULT (0),
 CONSTRAINT [PK_Order_Details2] PRIMARY KEY CLUSTERED 
(
    [OrderID] ASC,
    [ProductID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

表值

+-------------+-----------+------------+----------+----------+
|     OrderID | ProductID | UnitePrice | Quantity | Discount |
+-------------+-----------+------------+----------+----------+
|       10248 |        11 | 14         |       12 | 0        |
|       10248 |        42 | 9.8        |       10 | 0        |
|       10248 |        72 | 34.8       |        5 | 0        |
|       10249 |        14 | 18.6       |        9 | 0        |
|       10249 |        51 | 42.4       |       40 | 0        |
|       10250 |        41 | 7.7        |       10 | 0        |
|       10250 |        51 | 42.4       |       35 | 0.15     |
|       10250 |        65 | 16.8       |       15 | 0.15     |
|       10251 |        22 | 16.8       |        6 | 0.05     |
|       10251 |        57 | 15.6       |       15 | 0.05     |
|       10251 |        65 | 16.8       |       20 | 0        |
|             |           |            |          |          |
+-------------+-----------+------------+----------+----------+

所需输出

+-------------+-----------+------------+----------+----------+
|     OrderID | ProductID | UnitePrice | Quantity | Discount |
+-------------+-----------+------------+----------+----------+
|       10248 |        72 | 14         |       12 |        0 |
|       10249 |        51 | 18.6       |        9 |        0 |
|       10250 |        65 | 7.7        |       10 |        0 |
+-------------+-----------+------------+----------+----------+

我的表有两个主键，我想从这个表中获取唯一记录。所需的输出仅包含列orderid和最大productid行（仅一行）。我需要帮助来解决这个问题。

如果有任何疑问，请询问。

sql-server sql-server-2008

2 个回答

  1. Best Answer

    就JOIN在一个子查询上：

    SELECT *
FROM MyTable T
INNER JOIN (SELECT OrderID, MAX(ProductID) as ProductID
            FROM MyTable
            GROUP BY OrderID) x
  ON x.orderid = t.orderid
  AND x.productid = t.productid
    • 6

  2. 好吧，正如@JNK 所说，您对问题的解释与您在示例中向我们展示的不同。所以，如果我按照你需要的输出，那么你可以试试这个查询：

    SELECT *
FROM (  SELECT  OrderID, MAX(ProductId) OVER(PARTITION BY OrderId) ProductId, 
                UnitePrice, Quantity, Discount, 
                ROW_NUMBER() OVER(PARTITION BY OrderId ORDER BY ProductId) Corr
        FROM #Temp1) Data
WHERE Corr = 1
    • 6

相关问题