如何在sql查询中对内连接和case语句应用partition by和row

我有一个SQL查询

select b.logtime,
       b.beam_current,
       b.beam_energy,
       case when a.st2_vs2_bag1_onoff=0 then c.st2_vs2_bag1_rb ELSE 0 END
         as st2_vs2_bag1_rb,
       CASE when a.st2_vs2_bag8_onoff=0 then c.st2_vs2_bag8_rb else '0' END
         as st2_vs2_bag8_rb,
       CASE when a.st2_vs2_bag9_onoff=0  then c.st2_vs2_bag9_rb else '0' END
         as st2_vs2_bag9_rb 
from   INDUS2_BDS.dbo.DCCT b
       INNER JOIN (
         INDUS2_VACUUM.dbo.main_vacuum_analog c
         inner join INDUS2_VACUUM.dbo.main_vacuum_status a
           on c.logtime=a.logtime
       )
       ON a.LOGTIME = b.LOGTIME
         and (b.beam_current like '%9.96' etc)
         and b.logtime between '2014-08-09 00:00:00' and '2014-08-09 23:59:59'
         and b.beam_current in('10.01', '20.02', etc)
         and ( b.beam_energy between '550' and '551' )
order by b.logtime

现在在给定的logtime中，一个beam_current值在不同的logtime时多次出现 . 我只想要一个beam_current值不重复 . 为此我想在上面的Sql Query中应用row_number和beam_current分区 . 但是我没有得到如何应用它 .

1 回答

做了一些改变

在 Join 条件中删除了不需要的括号 . 在没有_2512926条件的情况下， Join 也出现了错误，纠正了它

然后只保留 On 子句中的 join 条件，将过滤器移动到 where 子句 .

试试这个 .

;WITH cte
     AS (SELECT b.logtime,
                b.beam_current,
                b.beam_energy,
                CASE
                  WHEN a.st2_vs2_bag1_onoff = 0 THEN c.st2_vs2_bag1_rb
                  ELSE 0
                END                     AS st2_vs2_bag1_rb,
                CASE
                  WHEN a.st2_vs2_bag8_onoff = 0 THEN c.st2_vs2_bag8_rb
                  ELSE '0'
                END                     AS st2_vs2_bag8_rb,
                CASE
                  WHEN a.st2_vs2_bag9_onoff = 0 THEN c.st2_vs2_bag9_rb
                  ELSE '0'
                END                     AS st2_vs2_bag9_rb,
                Row_number() OVER(partition BY b.beam_current
                    ORDER BY b.logtime) RN
         FROM   INDUS2_VACUUM.dbo.main_vacuum_analog c
                INNER JOIN INDUS2_VACUUM.dbo.main_vacuum_status a
                        ON c.logtime = a.logtime
                INNER JOIN INDUS2_BDS.dbo.DCCT b
                        ON a.LOGTIME = b.LOGTIME
         WHERE  b.beam_current LIKE '%9.96'
                AND b.logtime BETWEEN '2014-08-09 00:00:00' AND '2014-08-09 23:59:59'
                AND b.beam_current IN ( '10.01', '20.02' )
                AND b.beam_energy BETWEEN '550' AND '551')
SELECT logtime,
       beam_current,
       beam_energy,
       st2_vs2_bag1_rb,
       st2_vs2_bag8_rb,
       st2_vs2_bag9_rb
FROM   cte
WHERE  rn = 1
ORDER  BY logtime

回复于 2024-04-29T14:29:51+08:00

如何在sql查询中对内连接和case语句应用partition by和row_number（）

1 回答

相关问题