SQL Server 2008：TOP 10与众不同

SQL Server 2008：TOP 10与众不同

简单的选项是使用分组依据，并为所有其他字段选择最小值/最大值

SELECt TOP 10     p.id,     max(pl.nm),    max(pl.val),    max(pl.txt_val)from     dm.labs pljoin     mas_data.patients p    on     pl.id = p.id  where     pl.nm like '%LDL%'and     val is not nullgroup by     p.id

对于宽表来说这可能会很繁琐，所以另一种选择是使用排名和参与

SELECt TOP 10     p.id,      pl.nm,      pl.val,    pl.txt_val,     rank() over(partition by p.id order by p.id) as Rankfrom     dm.labs pljoin     mas_data.patients p    on     pl.id = p.id  where     pl.nm like '%LDL%'and     val is not nulland    Rank = 1