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

I have a sql query with three columns .I want to remove any duplicate value exits in beam_current column.How to do so.I work in sql-server2012

我有一个带有三列的SQL查询。我想删除beam_current列中的任何重复值出口。如何这样做。我在sql-server2012中工作

I used Distinct but then also I'm getting duplicate values of beam_current. My sql Query is-

我使用了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'

EDIT-1 My output is-

EDIT-1我的输出是 -

In first column 29.98 is repeating thrice.I only want any one of the row corresponding to 29.98.How to do that??

在第一列29.98重复三次。我只想要对应于29.98的行中的任何一行。怎么做?

3 个解决方案

#1

This will return 1 row for each value of beam_current:

这将为beam_current的每个值返回1行:

;WITH CTE AS
(
SELECT
  row_number() over (partition by beam_current order by (select 1)) rn,
  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' 
       or beam_current like '%9.98' or  beam_current like '%9.99'
       or beam_current like '%0' or beam_current like '%_0.01' 
       or beam_current like '%_0.02' or beam_current like '%_0.03' 
       or beam_current like '%_0.04' or beam_current like '%_0.05' 
       or beam_current like '%_0.06') 
       and beam_energy between 550 and 552
)
SELECT beam_current, logtime, beam_energy 
FROM CTE
WHERE rn = 1

#2

The distinct keyword works on the entire row (all columns), so:

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

select DISTINCT (beam_current), logtime, beam_energy 

is the same as:

是相同的:

select DISTINCT beam_current, logtime, beam_energy 

is the same as:

是相同的:

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

You can use row_number() to select only the latest row per value of 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

#3

It seems a single row combination is distinct for each value of beam_current. If you will try only select Distinct(beam_current) from INDUS2_BDS.dbo.DCCT where .... then only you will get that unique value.

对于beam_current的每个值,似乎单行组合是不同的。如果您只尝试从INDUS2_BDS.dbo.DCCT中选择Distinct(beam_current)....那么只有您将获得该唯一值。

Group by will also not work because it will group the columns from left to right written after group by keyword.So anyhow you will get the same result.

分组依据也不会起作用,因为它会按照关键字从左到右分组。所以无论如何你都会得到相同的结果。