在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
...全文
186 11 打赏 收藏 转发到动态 举报
AI 作业
写回复
用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 Server超详细使用教程——从安装到编写SQL语句
到微软官网下载SQL Server Developer版本，现在的最新版本是SQL Server 2019 Developer。微软官网传送门：点击此处直达 下载完成之后，在文件夹中找到刚才下载的文件，双击打开，打开之后的界面如下图所示。 我们选择自定义安装，之后再选择要安装的位置，再点击安装，如下图所示 点了安装之后会出现如下图所示的界面，我们需要等待它下载安装包，此过程等待的时间可能较长...
SQL Server语句创建表
今天介绍一下如何使用SQL Server语句创建表并添加数据 首先先了解一下表的模式，在数据库中根据模式进行分组避免表名称的冲突 在SQL Server 2014中直接新建表是默认的前缀dbo 而命名其他的模式需要使用SQL Server语句进行创建 下面将一步一步的进行演示，首先是创建一个数据库 然后创建模式在后面使用 根据创建的模式或者使用默认的模式名，进行创建表，语句如下图 下面解释一下句子的意思 看一下新建好的表 后面介绍如何在新表里面添加数据 ...
mysql sqlserver 语句区别_数据库MySQL与SqlServer两者SQL语句的区别
项目迁移需要更换数据库，更换数据库时发现MySQL与SqlServer的sql语句有一些区别，做下记录。项目迁移需要更换数据库，更换数据库时发现MySQL与SqlServer的sql语句有一些区别，做下记录。一、自增长列的插入：SQLServer中可以不为自动增长列插入值，MySQL中需要为自动增长列插入值。二、获取当前时间函数：SQLServer写法：getdate()MySQL写法：now()...
SQLServer：SQL语句中加中括号[ ]的含义
SQLServer：SQL语句中加中括号[ ]的含义 在操作SQLServer数据库的时候，经常能看到用中括号（[]）括住的字段，这些字段可能是表名或者字段名等等。那么为什么要加中括号呢？解释如下： 为了防止某些关键字在使用中引起歧义，如果加上中括号，则代表这是一个字段名，而不是关键字。例如：表中有一个字段叫select，而select是SQL中的一个关键字，如果直接写作《 select se...
SQL server 查询语句大全
在 SQL Server 中，查询语句是最常用的语句类型，用于从数据库中提取有用的信息。SQL Server 中常用的查询语句有 SELECT、FROM、WHERE、GROUP BY、HAVING 和 ORDER BY。
MS-SQL Server

34,838

社区成员

254,632

社区内容

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

加载中

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

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

+ 用AI写文章