34,838
社区成员




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 carmark=a.carmark 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 carmark=a.carmark and topdate>a.topdate and topdate between '2007/01/29 02:21:29' and '2008/01/30 02:21:29'))c
where b.carmark=c.carmark
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 行受影响)
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 行)
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 行)
*/
/*
S05261 2007-12-29 10:26:24.000 2007-12-29 20:26:24.000 3081.6 3281.6 200
S22126 2007-12-29 10:26:24.000 2007-12-29 15:26:24.000 2081.6 2081.6 0
*/
declare @tb table(carmark 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 carmark,min(topdate) top1date,max(topdate) top2date into #aa from @tb
group by carmark
select c.carmark,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.carmark=b.carmark
and a.carmark=c.carmark
and a.topdate=c.top1date
and b.topdate=c.top2date
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
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 行)
*/
declare @tb table(carmark 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 a.carmark,a.topdate,b.topdate,a.carlicheng,b.carlicheng,a.carlicheng - b.carlicheng
from (select * from @tb a
where not exists(select 1 from @tb where carmark = a.carmark and topdate > a.topdate)) a
left join
(select * from @tb c
where not exists(select 1 from @tb where carmark = c.carmark and topdate < c.topdate)) b
on a.carmark = b.carmark
/*
carmark 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 行)
*/