sql-server - 从sql查询中的所有可用列中仅删除一列值的重复值

Question

我有一个包含三列的sql查询。我想删除beam_current列中的任何重复值退出。如何做到这一点。我在sql-server2012中工作

我使用了 Distinct，但我也得到了 beam_current 的重复值。我的 sql 查询是-

select DISTINCT (beam_current), logtime, beam_energy 
from INDUS2_BDS.dbo.DCCT 
where logtime between '2014-08-09 01:13:03' and '2014-08-09 02:16:53'
      and (beam_current like '%9.96' 
           or beam_current like '%9.97' 
           ... etc ...) 
      and beam_energy between '550' and '552'

编辑-1我的输出是-

在第一列中，29.98 重复三次。我只想要与 29.98 对应的任何一行。如何做到这一点？

Answer 1

distinct 关键字适用于整行(所有列)，因此:

select DISTINCT (beam_current), logtime, beam_energy 

等同于:

select DISTINCT beam_current, logtime, beam_energy 

等同于:

select DISTINCT ((beam_current)), (logtime), (((((beam_energy)))))

您可以使用 row_number() 仅选择每个 beam_energy 值的最新行:

select  *
from    (
        select  row_number() over (
                    partition by beam_current
                    order by logtime desc) as rn
        ,       *
        from    INDUS2_BDS.dbo.DCCT 
        where   logtime between '2014-08-09 01:13:03' and '2014-08-09 02:16:53'
                and (beam_current like '%9.96' 
                    or beam_current like '%9.97' 
                    ... etc ...) 
                and beam_energy between '550' and '552'
        ) numbered_rows
where   rn = 1 -- Latest row per beam_current