主页 / dba / 问题 / 135471
Accepted
user1786107
Asked: 2016-04-16 13:42:11 +0800 CST

删除 SQL Server Group By 中的重复项

  • 772

我有 2 个表，产品和类型，表看起来像这样：

Product
-Id
-Name
-TypeId

Type
-Id
-Name

我希望能够编写一个 SQL Server 查询来输出按类型分组的产品：

+------------+------------+
| Bikes      | Product 1  |
|            | Product 2  |
|            | Product 3  |
| Clothes    | Tshirt 1   |
|            | Trousers 2 |
|            | Hat 3      |
|            | Shoes 4    |
| Electrical | Tv         |
|            | Radio      |
|            | Laptop     |
+------------+------------+

如果我写：

SELECT Type.Name,Product.Name
FROM Product INNER JOIN Type ON Type.Id = Product.TypeId
ORDER By Type.Name

我得到了类似的结果，但是类型值包含在每一列中。

+------------+------------+
| Bikes      | Product 1  |
| Bikes      | Product 2  |
| Bikes      | Product 3  |
| Clothes    | Tshirt 1   |
| Clothes    | Trousers 2 |
| Clothes    | Hat 3      |
| Clothes    | Shoes 4    |
| Electrical | Tv         |
| Electrical | Radio      |
| Electrical | Laptop     |
+------------+------------+

使用 SQL Server 有什么办法只能显示第一个类型值吗？

sql-server t-sql

3 个回答

  1. Best Answer

    窗口函数在这里可以提供帮助。

    SELECT CASE WHEN RowNum = 1 THEN TypeName ELSE NULL END AS TypeName, ProductName
FROM (
SELECT Type.Name AS TypeName,Product.Name AS ProductName, ROW_NUMBER() OVER(PARTITION BY Type.Name ORDER BY Product.Name) AS Rownum, COUNT([whatever you want to count]) AS ItemCount
FROM Product INNER JOIN Type ON Type.Id = Product.TypeId
GROUP BY Type.Name, Product.Name
) X

    顺便说一句，该子句的 MSDN 参考在OVER这里：

    https://msdn.microsoft.com/en-us/library/ms189461.aspx

    • 4 
  2. SELECT CASE WHEN rn = 1 THEN TypeName ELSE NULL END AS TypeName 
     , ProductName, rn
FROM ( SELECT Type.Name AS [TypeName], Product.Name AS [ProductName]
            , ROW_NUMBER() OVER(PARTITION BY Type.Name ORDER BY Product.Name) AS rn    
         FROM Product 
         JOIN Type 
               ON Type.Id = Product.TypeId
     ) tt 
order by ProductName, rn

    ROW_NUMBER（事务处理 SQL）

    • 2

  3. 使用这个：

      Create Table [Type]
  (
    Id Int ,Name varchar(10)
  )
  Create Table [Product]
  (
    Id Int ,
    Name varchar(10),
    TypeID Int,
  )

  INSERT INTO [Type](ID,Name) 
    VALUES 
      (1,'Bikes'),
      (2,'Clothes'),
      (3,'Electrical')

  INSERT INTO [Product](ID,Name,TypeID) 
    VALUES 
      (1,'Product 1',1),
      (2,'Product 2',1),
      (3,'Product 3',1),
      (4,'Tshirt 1',2),
      (5,'Trousers 2',2),
      (6,'Hat 3',2),
      (7,'Shoes 4',2),
      (8,'Tv',3),
      (9,'Radio',3),
      (10,'Laptop',3)


  SELECT 
    CASE ProductWithRank.[Rank]
      WHEN 1 
        THEN [Type].Name 
      ELSE NULL 
    END AS TypeName 
    , ProductWithRank.Name, 
    ProductWithRank.[Rank]
  FROM ( SELECT                
            Product.TypeId, 
            Product.Name ,
            Product.ID
            , Rank() OVER(PARTITION BY Product.TypeId ORDER BY Product.ID) AS [Rank]    
         FROM Product 
       ) ProductWithRank 
  INNER JOIN [Type] ON [Type].Id = ProductWithRank.TypeId
  ORDER BY ProductWithRank.ID, [Rank]
    • 0

相关问题