在sqlserver中求一SQL语句;

Question

已知表：XingShiLiCheng    
资料名称                类型         长度
CarMark             varchar       20
topDate             datetime      8
CarLiCheng          float         8
eg:表中数据：CarMark        topDate                           CarLiCheng       
S05261         2007/12/29 10:26:24                3081.6
S05261         2007/12/29 11:20:20                3181.6
S05261         2007/12/29 15:26:24                3181.6
S05261         2007/12/29 18:26:24                3181.6
S05261         2007/12/29 20:26:24                3281.8
S22126         2007/12/29 10:26:24                2081.6
S22126         2007/12/29 13:26:24                2081.6
S22126         2007/12/29 15:26:24                2081.6
另有获得的值top1=2007/01/29 02:21:29、tup2=2008/01/30 02:21:29
求一SQL语句满足当top1<=topDate<=tup2时，可得到
CarMark    topDate1                topDate2           CarLiCheng1   CarLiCheng2     CarLi
S05261     2007/12/29 10:26:24   2007/12/29 20:26:24    3081.6       3281.8         200.2
S22126     2007/12/29 10:26:24   2007/12/29 15:26:24    2081.6       2081.6           0

Answer 1

declare   @tab2   table   
( 
人数   int, 
时间   datetime 
) insert   into   @tab2   select   1, '2005-12-1       14:12:16 '       
insert   into   @tab2   select   2, '2005-12-1       14:22:16 '       
insert   into   @tab2   select   3, '2005-12-1       14:47:16 '       
insert   into   @tab2   select   11, '2005-12-1       14:36:16 '       
insert   into   @tab2   select   12, '2005-12-2       10:22:16 '       
insert   into   @tab2   select   3, '   2005-12-2       10:23:16 '   
--解决方法 
select   min(时间)   as   起始时间,dateadd(mi,5,min(时间))   as   终止时间,平均人数=avg(人数)   from   
( 
select   *,col=datediff(mi,(select   min(时间)   from   @tab2   c   where   datediff(day,c.时间,b.时间)=0),时间)/5       from   @tab2   b 
)   a 
group   by   convert(varchar(10),时间,120),col 
order   by   1 /*
起始时间                     终止时间                           平均人数        
---------------- ---------------------- ----------- 
2005-12-01 14:12:16.000         2005-12-01 14:17:16.000         1
2005-12-01 14:22:16.000        2005-12-01 14:27:16.000          2
2005-12-01 14:36:16.000         2005-12-01 14:41:16.000         11
2005-12-01 14:47:16.000        2005-12-01 14:52:16.000          3
2005-12-02 10:22:16.000        2005-12-02 10:27:16.000          7（所影响的行数为 5 行）
*/

Answer 2

create table eg(CarMark varchar(20),topdate datetime,CarLiCheng float)
insert into eg select 'S05261','2007/12/29 10:26:24','3081.6'
insert into eg select 'S05261','2007/12/29 11:20:20','3181.6'
insert into eg select 'S05261','2007/12/29 15:26:24','3181.6'
insert into eg select 'S05261','2007/12/29 18:26:24','3181.6'
insert into eg select 'S05261','2007/12/29 20:26:24','3281.8'
insert into eg select 'S22126','2007/12/29 10:26:24','2081.6'
insert into eg select 'S22126','2007/12/29 13:26:24','2081.6'
insert into eg select 'S22126','2007/12/29 15:26:24','2081.6'
select c.CarMark,c.topdate topdate1,d.topdate topdate2,c.CarLiCheng CarLiCheng1,d.CarLiCheng CarLiCheng2,d.CarLiCheng-c.CarLiCheng CarLi
from 
(select * from eg a where not exists(select 1 from eg where CarMark=a.CarMark and  topdate<a.topdate))c,
(select * from eg b where not exists(select 1 from eg where  CarMark=b.CarMark and  topdate>b.topdate))d
where c.CarMark=d.CarMark

Answer 3

declare @tb table(car varchar(20),topdate datetime,carlicheng float)
insert into @tb
select 'S05261','2007/12/29   10:26:24',3081.6
union
select 'S05261','2007/12/29   11:20:20',3181.6
union
select 'S05261','2007/12/29   15:20:24',3181.6
union
select 'S05261','2007/12/29   18:20:24',3181.6
union
select 'S05261','2007/12/29   20:26:24',3281.6
union
select 'S22126','2007/12/29   10:26:24',2081.6
union
select 'S22126','2007/12/29   13:26:24',2081.6
union
select 'S22126','2007/12/29   15:26:24',2081.6
select car,min(topdate) top1date,max(topdate) top2date into #aa from @tb
group by carselect c.car,c.top1date,c.top2date,
a.carlicheng as carlicheng1,b.carlicheng as carlicheng2,
b.carlicheng-a.carlicheng as carli
from @tb a,@tb b,#aa c
where a.car=b.car
and a.car=c.car
and a.topdate=c.top1date
and b.topdate=c.top2date

Answer 4

declare @a table(CarMark varchar(20),topDate datetime ,CarLiCheng float(8))
insert @a select 'S05261','2007/12/29 10:26:24',3081.6 
union all select 'S05261','2007/12/29 11:20:20',3181.6 
union all select 'S05261','2007/12/29 15:26:24',3181.6 
union all select 'S05261','2007/12/29 18:26:24',3181.6 
union all select 'S05261','2007/12/29 20:26:24',3281.8 
union all select 'S22126','2007/12/29 10:26:24',2081.6 
union all select 'S22126','2007/12/29 13:26:24',2081.6 
union all select 'S22126','2007/12/29 15:26:24',2081.6 select *,
CarLiCheng1=(select top 1 CarLiCheng from @a where CarMark=aa.CarMark and topDate=topDate1),
        CarLiCheng2=(select top 1 CarLiCheng from @a where CarMark=aa.CarMark and topDate=topDate2),
CarLi=(select top 1 CarLiCheng from @a where CarMark=aa.CarMark and topDate=topDate2)-
      (select top 1 CarLiCheng from @a where CarMark=aa.CarMark and topDate=topDate1)
from(
select  CarMark,
topDate1=Min(topDate),
topDate2=Max(topDate)
from @a a where topDate between '2007/01/29 02:21:29' and '2008/01/30 02:21:29 ' group by CarMark
)aa--result
/*
CarMark              topDate1                                               topDate2                                               CarLiCheng1              CarLiCheng2              CarLi                    
-------------------- ------------------------------------------------------ ------------------------------------------------------ ------------------------ ------------------------ ------------------------ 
S05261               2007-12-29 10:26:24.000                                2007-12-29 20:26:24.000                                3081.6001                3281.8                   200.19995
S22126               2007-12-29 10:26:24.000                                2007-12-29 15:26:24.000                                2081.6001                2081.6001                0.0（所影响的行数为 2 行）
*/

Answer 5

declare @XingShiLiCheng table (
CarMark       varchar(20) ,
topDate       datetime,
CarLiCheng      float
)
insert @XingShiLiCheng select
'S05261',     '2007/12/29 10:26:24',         3081.6 
union all select
'S05261',     '2007/12/29 11:20:20',         3181.6 
union all select
'S05261',     '2007/12/29 15:26:24',         3181.6 
union all select
'S05261',     '2007/12/29 18:26:24',         3181.6 
union all select
'S05261',     '2007/12/29 20:26:24',         3281.8 
union all select
'S22126',     '2007/12/29 10:26:24',         2081.6 
union all select
'S22126',     '2007/12/29 13:26:24',         2081.6 
union all select
'S22126',     '2007/12/29 15:26:24',         2081.6 declare @top1 datetime
set @top1='2007/01/29 02:21:29'
declare @tup2 datetime
set @tup2='2008/01/30 02:21:29'
 
select 
a.CarMark,a.topDate as topDate1,b.topDate as topDate2,a.CarLiCheng as CarLiCheng1,b.CarLiCheng as CarLiCheng2,b.CarLiCheng-a.CarLiCheng as CarLi 
from @XingShiLiCheng a,@XingShiLiCheng b
where a.CarMark=b.CarMark
and a.topDate between @top1 and @tup2
and b.topDate between @top1 and @tup2
and not exists (select 1 from @XingShiLiCheng where CarMark=a.CarMark and topDate<a.topDate
and topDate between @top1 and @tup2
)
and not exists (select 1 from @XingShiLiCheng where CarMark=b.CarMark and topDate>b.topDate
and topDate between @top1 and @tup2
)-- 结果
CarMark              topDate1                                               topDate2                                               CarLiCheng1                                           CarLiCheng2                                           CarLi                                                 
-------------------- ------------------------------------------------------ ------------------------------------------------------ ----------------------------------------------------- ----------------------------------------------------- ----------------------------------------------------- 
S05261               2007-12-29 10:26:24.000                                2007-12-29 20:26:24.000                                3081.5999999999999                                    3281.8000000000002                                    200.20000000000027
S22126               2007-12-29 10:26:24.000                                2007-12-29 15:26:24.000                                2081.5999999999999                                    2081.5999999999999                                    0.0（所影响的行数为 2 行）

Answer 6

float比较讨厌的 
---------------
确实比较讨厌。我赞成。太多小数位了。。

Answer 7

create table XingShiLiCheng(CarMark varchar(20),topdate datetime,carlicheng float)insert into XingShiLiCheng values('S05261','2007/12/29   10:26:24',3081.6)
insert into XingShiLiCheng values('S05261','2007/12/29   11:20:20',3181.6)
insert into XingShiLiCheng values('S05261','2007/12/29   15:26:24',3181.6)
insert into XingShiLiCheng values('S05261','2007/12/29   18:26:24',3181.6)
insert into XingShiLiCheng values('S05261','2007/12/29   20:26:24',3281.8)
insert into XingShiLiCheng values('S22126','2007/12/29   10:26:24',2081.6)
insert into XingShiLiCheng values('S22126','2007/12/29   13:26:24',2081.6)
insert into XingShiLiCheng values('S22126','2007/12/29   15:26:24',2081.6)select b.CarMark,topdate1=b.topdate,topdate2=c.topdate,carlicheng1=b.carlicheng,carlicheng2=c.carlicheng,carlicheng=(c.carlicheng-b.carlicheng) from
(select * from XingShiLiCheng a where not exists (select 1 from XingShiLiCheng where car=a.car and topdate<a.topdate and topdate between '2007/01/29   02:21:29' and '2008/01/30   02:21:29'))b
,(select * from XingShiLiCheng a where not exists (select 1 from XingShiLiCheng where car=a.car and topdate>a.topdate and topdate between '2007/01/29   02:21:29' and '2008/01/30   02:21:29'))c
where b.car=c.car
CarMark              topdate1                topdate2                carlicheng1            carlicheng2            carlicheng
-------------------- ----------------------- ----------------------- ---------------------- ---------------------- ----------------------
S05261               2007-12-29 10:26:24.000 2007-12-29 20:26:24.000 3081.6                 3281.8                 200.2
S22126               2007-12-29 10:26:24.000 2007-12-29 15:26:24.000 2081.6                 2081.6                 0(2 行受影响)

Answer 8

declare @tb table(car varchar(20),topdate datetime,carlicheng float)
insert into @tb
select 'S05261','2007/12/29   10:26:24',3081.6
union
select 'S05261','2007/12/29   11:20:20',3181.6
union
select 'S05261','2007/12/29   15:20:24',3181.6
union
select 'S05261','2007/12/29   18:20:24',3181.6
union
select 'S05261','2007/12/29   20:26:24',3281.6
union
select 'S22126','2007/12/29   10:26:24',2081.6
union
select 'S22126','2007/12/29   13:26:24',2081.6
union
select 'S22126','2007/12/29   15:26:24',2081.6select a.car,a.topdate,b.topdate,a.carlicheng,b.carlicheng,a.carlicheng - b.carlichengfrom (select * from @tb a 
      where not exists(select 1 from @tb where car = a.car and topdate > a.topdate)) a
left join 
     (select * from @tb c 
      where not exists(select 1 from @tb where car = c.car and topdate < c.topdate)) b
on a.car = b.car/*
car              topdate                                                topdate                                                carlicheng                                            carlicheng                                                                                                  
-------------------- ------------------------------------------------------ ------------------------------------------------------ ----------------------------------------------------- ----------------------------------------------------- ----------------------------------------------------- 
S05261               2007-12-29 20:26:24.000                                2007-12-29 10:26:24.000                                3281.5999999999999                                    3081.5999999999999                                    200.0
S22126               2007-12-29 15:26:24.000                                2007-12-29 10:26:24.000                                2081.5999999999999                                    2081.5999999999999                                    0.0（所影响的行数为 2 行）
*/