--Model DIFF Type Week
--ASL1000 -0.0001 DOWN 17
--ASL1000 0 ENGR 17
--ASL1000 0.1671 IDLE 17
--ASL1000 0 PM 17
--ASL1000 0 ENGR 18
--ASL1000 0.1671 IDLE 18
--ASL1000 0 PM 18
--ASL1000 0 DOWM 18--如图,
--我想求出, 同一model ,同一 type 下--(第18周 的 DIFF的值),减 (第17周 的 DIFF的值)--考虑到,有可能,求出的值,还在和其他的表 合并,连差
高手,赐教
解决方案 »
- 把一个字段设置成text类型,把一段带有下划线的文字加进去后,为什么显示出来的文字不带下划线??
- 执行的连接字符串出错,求解.
- 怎样把 存有1000条数据的表,分成两个表:一个表存有700条数据,一个表存300条
- sql 的问题2
- 请问? SQL 语句 , 移动加权求成本
- 急!急!急!急!在WINDOWS XP如何成功安装SQL SERVER 2000数据库,在线等待?
- ASP怎么连接上sql server?
- WIN2000 高级服务器版 和SQL SERVER 企业版的价格是多少?
- 请教:怎样检测一个临时数据库是否存在?
- EXCEL表内容导入到mysql数据库出错
- 怎么用sql语句把数据库中的二进制数据密码修改了
- 存储过程,给参数设置默认值,报错
go
--> -->
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([Model] nvarchar(7),[DIFF] decimal(18,4),[Type] nvarchar(4),[Week] int)
Insert #T
select N'ASL1000',-0.0001,N'DOWN',17 union all
select N'ASL1000',0,N'ENGR',17 union all
select N'ASL1000',0.1671,N'IDLE',17 union all
select N'ASL1000',0,N'PM',17 union all
select N'ASL1000',0,N'ENGR',18 union all
select N'ASL1000',0.1671,N'IDLE',18 union all
select N'ASL1000',0,N'PM',18 union all
select N'ASL1000',0,N'DOWM',18
Go
Select a.[Model], a.[DIFF],a.[Type],a.[Week],
[DIFF2]=a.[DIFF]-ISNULL(b.[DIFF],0)
from #T AS a
LEFT JOIN #T AS b ON a.[Model]=b.[Model] AND a.[Type]=b.[Type] AND a.[Week]=b.[Week]+1
/*
Model DIFF Type Week DIFF2
ASL1000 -0.0001 DOWN 17 -0.0001
ASL1000 0.0000 ENGR 17 0.0000
ASL1000 0.1671 IDLE 17 0.1671
ASL1000 0.0000 PM 17 0.0000
ASL1000 0.0000 ENGR 18 0.0000
ASL1000 0.1671 IDLE 18 0.0000
ASL1000 0.0000 PM 18 0.0000
ASL1000 0.0000 DOWM 18 0.0000
*/
from tb group by Model,Tpye
--瞎写的,错了勿怪
Select isnull(A.Model,B.Model) as Model,
isnull(A.Type,B.Type) as Type,
isnull(A.DIFF,0) - isnull(B.DIFF,0) as DIFF
(select Model , DIFF , Type , Week
from T
where week=18) as Afull join (select Model , DIFF, Type , Week
from T
where week=17) as B
on A.Model=B.Model and A.Type=B.Type
from tb group by Model,[type]
/*
Model,Type,DIFF
ASL1000,DOWM,0.0000
ASL1000,DOWN,0.0001
ASL1000,ENGR,0.0000
ASL1000,IDLE,0.0000
ASL1000,PM,0.0000
AND a.[Week]=b.[Week]+1如何理解
WEEK 下面。包含 17,18,2个周
是如何区分 2个表 代表 2个周的
a.[Model], a.[DIFF],a.[Type],a.[Week],
[DIFF2]=a.[DIFF]-ISNULL(b.[DIFF],0)
from
#T AS a ---第一次用#t
LEFT JOIN
#T AS b ---第二次用#t 意思就是#t调用两次 自己与自己连接 后面的连接条件不一样
ON
a.[Model]=b.[Model] AND a.[Type]=b.[Type] AND a.[Week]=b.[Week]+1