时间戳

首页 > 解决方案 > TSQL 如果超过一个，则合并查询结果

sql - TSQL 如果超过一个，则合并查询结果

问题描述

SQL Server 2016 - 我有一个查询，它产生下面的结果，但想要组合块子块的任何多个值，如果只有一个值显示该值。

    SELECT        
BLOCKS.[BLOCK-REF] AS [BLOCK-SUBB-REF]
, BLOCKS.[PLACE-REF]

, tenure.[ESTATE-CODE]

FROM            [SQLViewsPro2EOD].[dbo].[IH-LOCATION] AS BLOCKS INNER JOIN
                         [SQLViewsPro2EOD].[dbo].[IH-LOCATION] AS SUBB ON BLOCKS.[BLOCK-REF] = SUBB.[PLACE-REF] inner join
                         [SQLViewsPro2EOD].[dbo].[IH_ESTATE] tenure on tenure.[ESTATE-CODE] = blocks.[ESTATE-CODE]
                         AND SUBB.[LOCATION-TYPE] IN ('BLOCK', 'SUBBLOCK', 'CBLOCK', 'HOSTEL', 'SCHEME') and  SUBB.[BLOCK-REF] <> ''
                         and BLOCKS.[LOCATION-TYPE] in ('FLAT', 'BEDSIT', 'BUNG', 'DUPLEX', 'ENDTER', 'GFLAT', 'GFLATOG', 'HOSTEL', 'HOUSE', 'MAISON', 'SEMDET', 'ROOM')
WHERE  
(BLOCKS.[LOCATION-STS] <> 'D')

|BLOCK-SUBB-REF      | PLACE-REF    | ESTATE-CODE|
|103                 |1331          |PSLH   |
|103                 |1332          |GNSOC  |
|103                 |1333          |GNSOC  |
|103                 |1334          |GNSOC  |
|104                 |1335          |PSLH   |

期望的输出

|BLOCK-SUBB-REF |Result
|103            | Mixed Results
|104            | PSLH

谢谢 - 任何帮助表示赞赏

标签： sqlsql-servertsqlgrouping

解决方案

似乎您可以只使用CASE表达式并检查MINandMAX值是否相同：

SELECT BLOCKS.[BLOCK-REF] AS [BLOCK-SUBB-REF],
       CASE WHEN MIN(tenure.[ESTATE-CODE]) = MAX(tenure.[ESTATE-CODE]) THEN MAX(tenure.[ESTATE-CODE]) ELSE 'Mixed Results' END
FROM [SQLViewsPro2EOD].[dbo].[IH-LOCATION] BLOCKS
     INNER JOIN [SQLViewsPro2EOD].[dbo].[IH-LOCATION] SUBB ON BLOCKS.[BLOCK-REF] = SUBB.[PLACE-REF]
     INNER JOIN [SQLViewsPro2EOD].[dbo].[IH_ESTATE] tenure ON tenure.[ESTATE-CODE] = BLOCKS.[ESTATE-CODE]
                                                          AND SUBB.[LOCATION-TYPE] IN ('BLOCK', 'SUBBLOCK', 'CBLOCK', 'HOSTEL', 'SCHEME')
                                                          AND SUBB.[BLOCK-REF] <> ''
                                                          AND BLOCKS.[LOCATION-TYPE] IN ('FLAT', 'BEDSIT', 'BUNG', 'DUPLEX', 'ENDTER', 'GFLAT', 'GFLATOG', 'HOSTEL', 'HOUSE', 'MAISON', 'SEMDET', 'ROOM')
WHERE BLOCKS.[LOCATION-STS] <> 'D'
GROUP BY BLOCKS.[BLOCK-REF];

推荐阅读