如何从具有特定列优先级的表中选择数据 [英] How Do I Select Data From Table With A Particular Column Priority

问题描述

我的表格包含以下行

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'

推荐答案

一种方法是添加一个表定义订单消息并使用该查找表对数据进行排序。



考虑以下

One way is to add a table which defines the order messages and use that lookup table to sort the data.

Consider the following

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;

DEMO [ ^ ]

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