解决方案 »
- 求高手简化sql语句
- 有请小梁或其他高手,sql 2000 如何得到某月的第N个星期的星期一及星期日的日期?
- 视图问题
- 跟数据库干上了!! 事务复制中又出现一个错误!原因我应该找到了!同步的表名非法!例如表名为a-a
- 过程需要参数 '@statement' 为 'ntext/nchar/nvarchar' 类型。 问题出在那?
- 传奇私服中的*.DB文件如何修改?
- 在SQL Server中怎样建立服务启动账户?
- SQL7中,用Enterprise manage编辑数据,为删除行记录命令不能用(被Disable?))
- 请问在WINDOWS 2000高级服务器版中安装了SQL server7.0当我要改变WINDOS2000的超级用户口令时,要启动SQL SERVER 7.0出错,请各位大虾帮帮忙!谢谢
- 水平有限,真被搞晕了,两个关系表之间的检测
- 求个sql语句
- 这样的记录该怎么select ?
看看我的文章
http://blog.csdn.net/dotnetstudio/article/details/10109497
select 'A',3000,'2014-07-08' union
select 'A',300,'2014-08-08' union
select 'A',200,'2014-09-08' union
select 'B',3100,'2014-07-08' union
select 'B',310,'2014-08-08' union
select 'B',200,'2014-09-08' union
select 'C',3200,'2014-07-08' union
select 'C',320,'2014-08-08' union
select 'D',3300,'2014-07-08' union
select 'D',330,'2014-08-08' union
select 'E',3400,'2014-07-08' union
select 'E',340,'2014-08-08')
,TC AS(
SELECT *,CASE WHEN WAGETIME!=(SELECT MIN(WAGETIME) FROM tb WHERE A.Id=ID)
THEN wage*-1 ELSE wage END NEW FROM TB A
)
SELECT ID,wage,(SELECT SUM(NEW) FROM TC WHERE A.wagetime>=wagetime AND A.Id=ID)WAGE1,wagetime
FROM TC A
ORDER BY ID,3 DESC
SELECT ROW_NUMBER()OVER (PARTITION BY ID ORDER BY WAGETIME) AS XH,* FROM tb
)
SELECT ID,WAGE,CASE WHEN XH=1 THEN WAGE
ELSE (SELECT WAGE FROM CTE B WHERE XH=1 AND A.ID=B.ID)-(SELECT SUM(C.WAGE) FROM CTE C WHERE C.XH<>1 AND C.XH<=A.XH AND C.ID=A.ID) END AS WAGE1,WAGETIME
FROM CTE A
with cte(id,wage,wagetime,rid) as
(
select *,rid = row_number() over(partition by id order by wagetime) from [ttt]
)
,
cte1(id,wage,wagetime,rid,tt)
as
(select ID ,wage,wagetime,rid,(select SUM(case when rid = 1 then wage else -1*wage end) from cte where id = a.id and rid <=a.rid)
from cte a
)
select * from cte1
ELSE (SELECT WAGE FROM (SELECT ROW_NUMBER()OVER (PARTITION BY ID ORDER BY WAGETIME) AS XH,* FROM tb) B WHERE XH=1 AND A.ID=B.ID)-(SELECT SUM(C.WAGE) FROM (SELECT ROW_NUMBER()OVER (PARTITION BY ID ORDER BY WAGETIME) AS XH,* FROM tb) C WHERE C.XH<>1 AND C.XH<=A.XH AND C.ID=A.ID) END AS WAGE1,WAGETIME
FROM (SELECT ROW_NUMBER()OVER (PARTITION BY ID ORDER BY WAGETIME) AS XH,* FROM tb) A