27,579
社区成员
发帖
与我相关
我的任务
分享同一表两条记录同一字段做比较的问题
一张sql表中的最新两条记录里的两个数字类型字段对比，最后一条比上一条的值大则输出上升，一样大输出持平 比上一条小则输出下降 这个数据查询怎么写？
...全文
请发表友善的回复…
发表回复
LongRui888 2014-01-21
- 打赏
- 举报
基本上是这个意思，如果只想比较每个人最后两次的呢？最后只输出名字、最后一次结果和flag就行了，譬如
Name Result flag
001 92 下降
002 83 持平
003 88 下降[/quote]
修改了一下，是这样吗：
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([Id] int,[Name] varchar(3),[Result] int,[Date] datetime)
insert [tb]
select 1,'001',90,'2013-11-10' union all
select 2,'002',85,'2013-11-10' union all
select 3,'003',87,'2013-11-10' union all
select 4,'001',95,'2013-11-15' union all
select 5,'002',83,'2013-11-15' union all
select 6,'003',89,'2013-11-15' union all
select 7,'001',92,'2013-11-20' union ALL
select 8,'002',83,'2013-11-20' union all
select 9,'003',88,'2013-11-20'
go
;with t
as
(
select *,
ROW_NUMBER() over(partition by [Name] order by [Date] desc) rownum
from tb
)
select t1.Name,t1.Result,
case when t1.[Result] > t2.[Result] then '上升'
when t1.[Result] = t2.[Result] then '持平'
when t1.[Result] < t2.[Result] then '下降'
end flag
from t t1
left join t t2
on t1.Name = t2.Name and t1.rownum = t2.rownum - 1
and t2.rownum = 2
where t1.rownum = 1
/*
Name Result flag
001 92 下降
002 83 持平
003 88 下降
*/
玉面小飞龙_ly 2014-01-21
- 打赏
- 举报
基本上是这个意思，如果只想比较每个人最后两次的呢？最后只输出名字、最后一次结果和flag就行了，譬如
Name Result flag
001 92 下降
002 83 持平
003 88 下降
玉面小飞龙_ly 2014-01-21
- 打赏
- 举报
基本上是这个意思，如果只想比较每个人最后两次的呢？最后只输出名字、最后一次结果和flag就行了，譬如
Name Result flag
001 92 下降
002 83 持平
003 88 下降[/quote]
修改了一下，是这样吗：
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([Id] int,[Name] varchar(3),[Result] int,[Date] datetime)
insert [tb]
select 1,'001',90,'2013-11-10' union all
select 2,'002',85,'2013-11-10' union all
select 3,'003',87,'2013-11-10' union all
select 4,'001',95,'2013-11-15' union all
select 5,'002',83,'2013-11-15' union all
select 6,'003',89,'2013-11-15' union all
select 7,'001',92,'2013-11-20' union ALL
select 8,'002',83,'2013-11-20' union all
select 9,'003',88,'2013-11-20'
go
;with t
as
(
select *,
ROW_NUMBER() over(partition by [Name] order by [Date] desc) rownum
from tb
)
select t1.Name,t1.Result,
case when t1.[Result] > t2.[Result] then '上升'
when t1.[Result] = t2.[Result] then '持平'
when t1.[Result] < t2.[Result] then '下降'
end flag
from t t1
left join t t2
on t1.Name = t2.Name and t1.rownum = t2.rownum - 1
and t2.rownum = 2
where t1.rownum = 1
/*
Name Result flag
001 92 下降
002 83 持平
003 88 下降
*/
LongRui888 2014-01-20
- 打赏
- 举报
是这样吗：
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([Id] int,[Name] varchar(3),[Result] int,[Date] datetime)
insert [tb]
select 1,'001',90,'2013-11-10' union all
select 2,'002',85,'2013-11-10' union all
select 3,'003',87,'2013-11-10' union all
select 4,'001',95,'2013-11-15' union all
select 5,'002',83,'2013-11-15' union all
select 6,'003',89,'2013-11-15' union all
select 7,'001',92,'2013-11-20' union ALL
select 8,'002',83,'2013-11-20' union all
select 9,'003',88,'2013-11-20'
go
;with t
as
(
select *,
ROW_NUMBER() over(partition by [Name] order by [Date]) rownum
from tb
)
select t1.*,
case when t1.[Result] > t2.[Result] then '上升'
when t1.[Result] = t2.[Result] then '持平'
when t1.[Result] < t2.[Result] then '下降'
end flag
from t t1
left join t t2
on t1.Name = t2.Name and t1.rownum = t2.rownum + 1
/*
Id Name Result Date rownum flag
1 001 90 2013-11-10 00:00:00.000 1 NULL
4 001 95 2013-11-15 00:00:00.000 2 上升
7 001 92 2013-11-20 00:00:00.000 3 下降
2 002 85 2013-11-10 00:00:00.000 1 NULL
5 002 83 2013-11-15 00:00:00.000 2 下降
8 002 83 2013-11-20 00:00:00.000 3 持平
3 003 87 2013-11-10 00:00:00.000 1 NULL
6 003 89 2013-11-15 00:00:00.000 2 上升
9 003 88 2013-11-20 00:00:00.000 3 下降
*/
發糞塗牆 2014-01-20
- 打赏
- 举报
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2014-01-20 16:30:54
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
-- Dec 28 2012 20:23:12
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([Id] int,[Name] varchar(3),[Result] int,[Date] datetime)
insert [huang]
select 1,'001',90,'2013-11-10' union all
select 2,'002',85,'2013-11-10' union all
select 3,'003',87,'2013-11-10' union all
select 4,'001',95,'2013-11-15' union all
select 5,'002',83,'2013-11-15' union all
select 6,'003',89,'2013-11-15' union all
select 7,'001',92,'2013-11-20' union ALL
select 8,'002',83,'2013-11-20' union all
select 9,'003',88,'2013-11-20'
--------------开始查询--------------------------
select * from [huang]
DECLARE @n INT
SET @n=1
;with t
as(
select
px=ROW_NUMBER()over(partition by name order by [date] desc),
*
from [huang]
)
select
name,[date],Result from t a
where exists(select 1 from t b where a.name=b.name and a.px=b.px-@n and a.Result>b.Result)
and px=1
----------------结果----------------------------
/*
*/
