如何从具有特定列优先级的表中选择数据

本文介绍了如何从具有特定列优先级的表中选择数据的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我的表格包含以下行

i have table that has rows as below

DECLARE @Table TABLE
(minv_code INT,
 alert_msg varchar(10),
 alert_time Datetime)

 INSERT INTO @Table VALUES
 (873939, 'Reverse', '7/24/2015 3:31:18'),									
 (873939, 'Tamper',  '7/24/2015 3:30:00'),									
 (873939, 'Meter',   '7/24/2015 3:31:22'), 
 (873940, 'Reverse', '7/24/2015 3:30:00'),
 (873940, 'Tamper',  '7/24/2015 3:31:22')

i想要明智地选择数据

例如

i want to select the data priority wise
e.g

first row - 873939, 'Meter', '7/24/2015 3:31:22'
second row - 873939, 'Tamper', '7/24/2015 3:30:00'
third row - 873939, 'Reverse', '7/24/2015 3:31:18'
fourth row -873940, 'Tamper', '7/24/2015 3:31:22'      
fifth row - 873940, 'Reverse', '7/24/2015 3:30:00'

推荐答案

DECLARE @Table TABLE
(minv_code INT,
 alert_msg varchar(10),
 alert_time Datetime)

 INSERT INTO @Table VALUES
 (873939, 'Reverse', '7/24/2015 3:31:18'),
 (873939, 'Tamper',  '7/24/2015 3:30:00'),
 (873939, 'Meter',   '7/24/2015 3:31:22'),
 (873940, 'Reverse', '7/24/2015 3:30:00'),
 (873940, 'Tamper',  '7/24/2015 3:31:22')

 select a.minv_code, a.alert_msg, a.alert_time
 from @Table a,
      (select 1 as ordinal, 'Meter' as alert_msg union all
       select 2 as ordinal, 'Tamper' as alert_msg union all
       select 3 as ordinal, 'Reverse' as alert_msg) b
where a.alert_msg = b.alert_msg
order by a.minv_code, b.ordinal;

select * from  Table1 order by minv_code, 
   case when alert_msg ='Meter' then 1
        when  alert_msg ='Tamper' then 2
        when  alert_msg ='Reverse' then 3 end;

[]

这篇关于如何从具有特定列优先级的表中选择数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！