代码之家 › 专栏 › 技术社区 › veljasije

MIN()带有某些条件的聚合函数

sql-server-2012 tsql sql-server

veljasije · 技术社区 · 11 年前

我有桌子 Places 具有以下列:

PlaceId     CityId     Name       Postcode
-----------------------------------------
1           1          Place1     NULL
2           1          Place1     10000
3           2          Place1     10300
4           2          Place2     10500
5           3          Place3     NULL
6           3          Place3     NULL

我想要的输出是:

PlaceId     CityId     Name       Postcode
-----------------------------------------
2           1          Place1     10000
3           2          Place1     10300
4           2          Place2     10500
5           3          Place3     NULL

因此,我需要以下内容:

如果一个城市有重复 CityId 和 Name 列,那么我需要一行 PlaceId 。但是,如果两个重复项中的第一个重复项在 Postcode 列,并且更大的id在同一列中有一些值,那么我需要输出中的第二行(例如,id为1和2的行,返回的id为2的行)。如果所有重复项在Postcode列中都有NULL值,则只返回最小值 地点ID (例如,id为5和6的行,返回的id为5的行)

所以,专栏 邮政编码 影响最终输出

我试过这样做:

SELECT 
        MIN(nm.PlaceId) AS PlaceId,
        nm.CityId,
        nm.name,        
        COUNT(*) AS Number
    FROM dbo.Place AS nm
    GROUP BY
        nm.CityId ,
        nm.name

我可以解决这个问题,但解决方案并不好,我要求一些好的、优雅的解决方案。

2 回复 | 直到 11 年前

singhswat marc_s 11 年前

这个怎么样。。。

CREATE TABLE #PLACES
( PlaceId int, CityId int, Name varchar(50), Postcode varchar(50)
)
INSERT INTO #PLACES (PlaceId, CityId, Name)VALUES(1, 1, 'Place1' )
INSERT INTO #PLACES (PlaceId, CityId, Name, Postcode)VALUES(2, 1, 'Place1','10000' )
INSERT INTO #PLACES (PlaceId, CityId, Name, Postcode)VALUES(3, 2, 'Place1' , '10300')
INSERT INTO #PLACES (PlaceId, CityId, Name, Postcode)VALUES(4, 2, 'Place2','10500' )
INSERT INTO #PLACES (PlaceId, CityId, Name)VALUES(5, 3, 'Place3' )
INSERT INTO #PLACES (PlaceId, CityId, Name)VALUES(6, 3, 'Place3' )

---查询

;WITH CTE_RESULTS AS
(
    SELECT ROW_NUMBER() OVER (PARTITION BY CityId, Name ORDER BY Postcode DESC) ROW_NO, PlaceId , CityId, Name, Postcode
    FROM #PLACES
)
SELECT * FROM CTE_RESULTS WHERE ROW_NO=1

ypercubeáµá´¹ 11 年前

以下查询将对您有所帮助:

;WITH Places as 
(
    select 1 as PlaceId, 1 as CityId, 'Place1' as Name, NULL as Postcode
    UNION ALL 
    select 2, 1, 'Place1', 10000
    UNION ALL 
    select 3, 2, 'Place1', 10300
    UNION ALL 
    select 4, 2, 'Place2', 10300
    UNION ALL 
    select 5, 3, 'Place3', NULL
    UNION ALL 
    select 6, 3, 'Place3', NULL
)
SELECT t2.PlaceId, groups.CityId, groups.Name, t2.Postcode FROM (
    select CityId, Name from Places GROUP BY CityId, Name
    ) groups 
    CROSS APPLY  
    (
        SELECT TOP(1) 
          d2.PlaceId, d2.Postcode, 
          CASE WHEN d2.Postcode IS NOT NULL THEN 1 ELSE 2 END AS RecordPriority 
        FROM Places d2 
        WHERE d2.CityId = groups.CityId AND d2.Name = groups.Name 
        ORDER BY RecordPriority, PlaceId
    ) t2 ;

或使用 ROW_NUMBER() 具有相同的 ORDER BY 如上所述:

;WITH Places AS 
(
...
),
OrderedPlaces AS
(
    SELECT *, 
        Rn = ROW_NUMBER() OVER
            (PARTITION BY CityId, Name
             ORDER BY CASE WHEN Postcode IS NOT NULL THEN 1 ELSE 2 END, 
                      PlaceID)
    FROM Places  
)
SELECT PlaceId, CityId, Name, Postcode 
FROM OrderedPlaces
WHERE Rn = 1 ;