如何从表中对数据进行排序以获取唯一值 [英] How Do I sort data From Table to get unique values
本文介绍了如何从表中对数据进行排序以获取唯一值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我的表格包含以下行
DECLARE @Table 表(minv_code INT ,alert_msg varchar ( 10 ),alert_time 日期时间)
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 ,' 反向',' 7/24/2015 3:30:00'),
( 873940 ,' 篡改',' 7/24/2015 3:31:22')
我想排序并选择数据以这种方式获得每minv_code一个值。输出应如下所示:
第一行:873939'Meter''7 / 24/2015 3:31:22'
第二行:873940'Tamper' '2015年7月24日3:31:22'
其余列
有人可以帮我这个
解决方案
由于您使用的是Microsoft SQL Server 2008 R2,另一种方法是使用ROW_NUMBER
函数 [ ^ ]:
WITH cteSortedValues As
(
SELECT
minv_code,
alert_msg,
alert_time,
ROW_NUMBER() OVER ( PARTITION < span class =code-keyword > BY minv_code ORDER BY alert_time DESC ) As RN
FROM
@ Table
)
SELECT
minv_code,
alert_msg,
alert_time
FROM
cteSortedValues
WHERE
RN = 1
;
尝试:
SELECT m。* 从 MyTable m
INNER 加入
( SELECT minv_code,MAX([ date ]) AS [ date ] FROM myTable GROUP by minv_code)g
ON m.minv_code = g.minv_code AND m。[ date ] = g。[ date ]
ORDER BY minv_code ASC
选择 minv_code,alert_msg,alert_time
< span class =code-keyword> from table1 u1
where alert_time =( select max(alert_time)
来自 table1 u2
其中 u1.minv_code = u2.minv_code)
订单 按 minv_code
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 want to sort and select the data in such a way as to get one value per minv_code. The output should look like:
first row: 873939 'Meter' '7/24/2015 3:31:22' second row: 873940 'Tamper' '7/24/2015 3:31:22'
then the rest of the columns
can somebody help me in this
解决方案
Since you're using Microsoft SQL Server 2008 R2, another alternative would be to use theROW_NUMBER
function[^]:
WITH cteSortedValues As ( SELECT minv_code, alert_msg, alert_time, ROW_NUMBER() OVER (PARTITION BY minv_code ORDER BY alert_time DESC) As RN FROM @Table ) SELECT minv_code, alert_msg, alert_time FROM cteSortedValues WHERE RN = 1 ;
Try:
SELECT m.* From MyTable m INNER JOIN (SELECT minv_code, MAX([date]) AS [date] FROM myTable GROUP by minv_code) g ON m.minv_code = g.minv_code AND m.[date] = g.[date] ORDER BY minv_code ASC
select minv_code, alert_msg, alert_time from table1 u1 where alert_time = (select max(alert_time) from table1 u2 where u1.minv_code = u2.minv_code) order by minv_code
这篇关于如何从表中对数据进行排序以获取唯一值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文