只选择第一个结果…有没有更好的方法

Question

可以有多个Config_RecID，但我只想显示第一个附加到SR_Service_RecID，或NULL时为空。

SELECT s.SR_Service_RecID
     , sc.Config_RecID
  FROM SR_Service s
       LEFT JOIN SR_Config sc
              ON sc.SR_Service_RecID = s.SR_Service_RecID
 WHERE (sc.Config_RecID = 
       (
           SELECT TOP 1 sc.Config_RecID
             FROM SR_Config sc 
            WHERE sc.SR_Service_RecID = s.SR_Service_RecID
       ) 
       OR sc.Config_RecID IS NULL)

是否有更好/更整洁的方法来做这件事，或者我在正确的轨道上?

Answer 1

您可以将条件移动到ON子句中:

SELECT s.SR_Service_RecID
     , sc.Config_RecID
FROM SR_Service s
LEFT JOIN SR_Config sc
     ON sc.SR_Service_RecID = s.SR_Service_RecID
     AND sc.Config_RecID = (
           SELECT TOP 1 Config_RecID
           FROM SR_Config sc2 
           WHERE sc2.SR_Service_RecID = s.SR_Service_RecID
     )

这样您就不需要额外检查sc.Config_RecID IS NULL

Answer 2

这是你想要的吗?

;WITH SR_ConfigCTE AS
(
    SELECT DISTINCT 
         SR_Service_RecID
        ,Config_RecID
    FROM SR_Config
)
SELECT   s.SR_Service_RecID
        ,sc.Config_RecID
FROM SR_Service     s
LEFT 
JOIN SR_ConfigCTE   sc ON sc.SR_Service_RecID = s.SR_Service_RecID

Answer 3

如果你使用TOP N，那你就错了。它是非标准的，不确定的。

相反，你想要第一个…某物用min()找到它。连接到该查询或使用相关子查询。

我不太明白你的问题，但这里有一个更简单的，给每个类型的第一个名字:

select type, usertype, cast(name as varchar(30)) as name 
from systypes as t
where exists 
      ( select 1 from systypes 
        group by type 
        having min(name) = t.name 
        and type = t.type 
) 
order by type

生成:

 type usertype name                          
 ---- -------- ------------------------------
    0        0 xml                           
   34       20 image                         
   35        0 ntext                         
   37        0 uniqueidentifier              
   39        0 nvarchar                      
   45        3 binary                        
   47        1 char                          
   48        5 tinyint                       
   50       16 bit                           
   52        6 smallint                      
   55       24 decimal                       
   56        7 int                           
   58       22 smalldatetime                 
   59       23 real                          
   60       11 money                         
   61       12 datetime                      
   62        8 float                         
   63        0 bigint                        
  122       21 smallmoney                    

Answer 4

with Config_RecIDs as (
   select SR_Service_RecID
      , Config_RecID
      , row_number() over (partition by SR_Service_RecID order by Config_RecID) as [rn]
   from SR_Config
)
select sc.SR_Service_RecID, c.Config_RecID
from SR_Config as sc
left join Config_RecIDs as s
   on s.SR_Service_RecID = c.SR_Service_RecID
   and [rn] = 1