创建测试表
CREATE TABLE [t_demo] (
[name] [varchar] (16) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[birh] [datetime] NOT NULL ,
[grade] [decimal](5, 2) NOT NULL ,
[class] [decimal](5, 2) NULL
)
插入数据
INSERT INTO [t_demo]([name], [birh], [grade], [class])
VALUES('cy','19800202', '100','2');
INSERT INTO [t_demo]([name], [birh], [grade], [class])
VALUES('cy','19800305', '100','3');
INSERT INTO [t_demo]([name], [birh], [grade], [class])
VALUES('cy','19800506', '100','2');
INSERT INTO [t_demo]([name], [birh], [grade], [class])
VALUES('cy','19800301', '97','2');
INSERT INTO [t_demo]([name], [birh], [grade], [class])
VALUES('cy','19800401', '98','2');想得到以下查询结果,查询条件name='cy' and class='2' and brith='19800401'前后的grade
name,class,'19800401'前的grade,'19800401'后的grade
cy, 2, 97, 100
CREATE TABLE [t_demo] (
[name] [varchar] (16) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[birh] [datetime] NOT NULL ,
[grade] [decimal](5, 2) NOT NULL ,
[class] [decimal](5, 2) NULL
)
插入数据
INSERT INTO [t_demo]([name], [birh], [grade], [class])
VALUES('cy','19800202', '100','2');
INSERT INTO [t_demo]([name], [birh], [grade], [class])
VALUES('cy','19800305', '100','3');
INSERT INTO [t_demo]([name], [birh], [grade], [class])
VALUES('cy','19800506', '100','2');
INSERT INTO [t_demo]([name], [birh], [grade], [class])
VALUES('cy','19800301', '97','2');
INSERT INTO [t_demo]([name], [birh], [grade], [class])
VALUES('cy','19800401', '98','2');想得到以下查询结果,查询条件name='cy' and class='2' and brith='19800401'前后的grade
name,class,'19800401'前的grade,'19800401'后的grade
cy, 2, 97, 100
a.name,a.class,b.grade,c.grade
from
#t a,#t b,#t c
where
a.class='2' and a.brith='19800401'
and
a.id=b.id+1
and
a.id=c.id-1
and
a.name=b.name and a.name=c.name
select
name
,class
,(select top 1 grade from [t_demo] where name='cy' AND class=2 AND birh<'1980-04-01' order by birh desc) as Before_grade
,(select top 1 grade from [t_demo] where name='cy' AND class=2 AND birh>'1980-04-01' order by birh ) as After_grade
from [t_demo] where name='cy' AND class=2 AND birh='1980-04-01'
[name] [varchar] (16) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[birh] [datetime] NOT NULL ,
[grade] [decimal](5, 2) NOT NULL ,
[class] [decimal](5, 2) NULL
) INSERT INTO [t_demo]([name], [birh], [grade], [class])
VALUES('cy','19800202', '100','2');
INSERT INTO [t_demo]([name], [birh], [grade], [class])
VALUES('cy','19800305', '100','3');
INSERT INTO [t_demo]([name], [birh], [grade], [class])
VALUES('cy','19800506', '100','2');
INSERT INTO [t_demo]([name], [birh], [grade], [class])
VALUES('cy','19800301', '97','2');
INSERT INTO [t_demo]([name], [birh], [grade], [class])
VALUES('cy','19800401', '98','2');select id=identity(int,1,1),* into #t from t_demo where class='2' order by birh
--select * from #t
select
a.name,a.class,b.grade,c.grade
from
#t a,#t b,#t c
where
a.class='2' and a.birh='19800401'
and
a.id=b.id+1
and
a.id=c.id-1
and
a.name=b.name and a.name=c.name
drop table t_demo,#t
go
/*name class grade grade
---------------- --------------------------------------- --------------------------------------- ---------------------------------------
cy 2.00 97.00 100.00(1 行受影响)
*/
order by abs(DATEDIFF(DAY,'19800401',[birh]))
with cte as
(
select *,row=row_number() over(order by birh) from t_demo
)
select * from cte where
row=(
select row-1 from cte where name='cy' and class='2' and birh='19800401'
)
or
row=(
select row+1 from cte where name='cy' and class='2' and birh='19800401'
)
select
b.name,
b.class,
PreGrade=(select top 1 grade from [t_demo] as pre where pre.birh<b.birh order by birh desc),
PreGrade=(select top 1 grade from [t_demo] as pre where pre.birh>b.birh order by birh)
from [t_demo] as b
where b.name='cy' and b.class='2' and b.birh='19800401'
/*
name class PreGrade PreGrade
---------------- --------------------------------------- --------------------------------------- ---------------------------------------
cy 2.00 100.00 100.00
*/select
b.name,
b.class,
PreGrade=(select top 1 grade from [t_demo] as pre where pre.grade<b.grade order by grade desc),
PreGrade=(select top 1 grade from [t_demo] as pre where pre.grade>b.grade order by birh)
from [t_demo] as b
where b.name='cy' and b.class='2' and b.birh='19800401'
/*
name class PreGrade PreGrade
---------------- --------------------------------------- --------------------------------------- ---------------------------------------
cy 2.00 97.00 100.00*/
能麻烦你讲一下你写的SQL语句的意思吗?
新手,不大理解
首先是创建一个表t,按照日期排序,并且加了一列自加一的列,但是这列在执行完第一句语句的时候就已经初始化完毕了对吗?
然后这句from语句创建了3个表的对象,那是不是相当于创建了3个t表啊?
第一个where语句选出了a的id和name
再然后是不是去表b中查找id=a的id-1并且b的name=a的name的列啊?
去表b中查找的话不是 where b.id=a.id-1吗?a.id=b.id+1效果是一样的吗?
a.id=c.id-1也是一样。
但是还有一个问题啊,id既然是已经初始化完的,当查找b中id=a.id-1的时候列就已经固定了,此时条件b.name=a.name不一定成立怎么办?或者我的理解是b中的name和固定的id已经绑定了。
问题好多~麻烦楼主啦~谢谢谢谢