在sqlserver中求一SQL语句;

快跑蜗牛哥 2008-01-24 10:53:53

已知表：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
...全文
176 11 打赏 收藏 转发到动态 举报
写回复
用AI写文章
11 条回复
切换为时间正序
请发表友善的回复…
发表回复
pt1314917 2008-01-24
  • 打赏
  • 举报
 回复
哦对了。我2楼的语句还忘记加条件过滤了。哈哈```
kk19840210 2008-01-24
  • 打赏
  • 举报
 回复 
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 行受影响)
pt1314917 2008-01-24
  • 打赏
  • 举报
 回复
float比较讨厌的
---------------
确实比较讨厌。我赞成。太多小数位了。。
昵称被占用了 2008-01-24
  • 打赏
  • 举报
 回复
float比较讨厌的
昵称被占用了 2008-01-24
  • 打赏
  • 举报
 回复 
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 行）
chuifengde 2008-01-24
  • 打赏
  • 举报
 回复 
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 行）

*/
yangjiexi 2008-01-24
  • 打赏
  • 举报
 回复 


/*

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

*/
yangjiexi 2008-01-24
  • 打赏
  • 举报
 回复 


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
pt1314917 2008-01-24
  • 打赏
  • 举报
 回复 


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
liangCK 2008-01-24
  • 打赏
  • 举报
 回复 
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 行）

*/
-狙击手- 2008-01-24
  • 打赏
  • 举报
 回复 
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 行）





*/
SQL语句基础教程
SQL语句基础教程
SQL Server超详细使用教程——从安装到编写SQL语句
到微软官网下载SQL Server Developer版本，现在的最新版本是SQL Server 2019 Developer。微软官网传送门：点击此处直达 下载完成之后，在文件夹中找到刚才下载的文件，双击打开，打开之后的界面如下图所示。 我们...
SQL Server语句创建表
在SQL Server 2014中直接新建表是默认的前缀dbo 而命名其他的模式需要使用SQL Server语句进行创建 下面将一步一步的进行演示，首先是创建一个数据库 然后创建模式在后面使用 根据创建的模式或者使用默认的...
mysql sqlserver 语句区别_数据库MySQL与SqlServer两者SQL语句的区别
项目迁移需要更换数据库，更换数据库时发现MySQL与...一、自增长列的插入：SQLServer中可以不为自动增长列插入值，MySQL中需要为自动增长列插入值。二、获取当前时间函数：SQLServer写法：getdate()MySQL写法：now()...
SQL Server创建表语句介绍
SQL Server创建表是最常见也是最常用的操作之一，下面就为您介绍SQL Server创建表的语句写法，供您参考，希望可以让您对SQL Server创建表方面有更深的认识。 USE suntest create table 仓库 ( 仓库编号 int , ...
MS-SQL Server

34,575

社区成员

254,585

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章