select spare,day,H from consume_164
658 30 16
8657 30 16
3149 30 16
15649 30 16
15599 30 16
8823 31 8
22376 31 8
41191 31 17
7746 1 7
2630 1 7
4790 1 7 这批数据是强行恢复过来的~
现在需要导回数据库中~首先需要将spare转换
例:658 转 6.58
8657 转 86.57day,H 列
需要合并~成‘2009-10-30 16:00:00.000’没有月份数据,只有日~这批数据时10月30号~11月2号
658 30 16
8657 30 16
3149 30 16
15649 30 16
15599 30 16
8823 31 8
22376 31 8
41191 31 17
7746 1 7
2630 1 7
4790 1 7 这批数据是强行恢复过来的~
现在需要导回数据库中~首先需要将spare转换
例:658 转 6.58
8657 转 86.57day,H 列
需要合并~成‘2009-10-30 16:00:00.000’没有月份数据,只有日~这批数据时10月30号~11月2号
解决方案 »
- 如何把rtf文件保存在数据库中?
- 请求一句sql的解决方法,急~~~谢谢了
- ■■各位达人,看看这个 sql 语句怎么写啊? 在线等待ing ■■
- 在线等:如何将大批量xml文件分别解析并把数据存入SQL数据库的数据表中?如何在表中保存xml文件名?
- 把全部行 放在一起(有图)
- 求教:如何写SQL语句,两表有相同字段,要显示两表全部字段,相同的只显示一个。
- 为什么sql优化里面会建议不要过多用存储过程
- 求一sql语句?解决立即结贴!!!
- order by case when @OrderBy=1 then 'CreateDate desc' end 不起作用?
- 求每天 内部员工和非内部员工消费次数?
- 求一条SQL语句
- 赋值语句怎么写
from consume_164
--测试数据
declare @table table(spare int,day varchar(10),H varchar(10))
insert into @table
select 658,'30','16' union all
select 8657, '30' , '16' union all
select 3149 , '30' , '16' union all
select 15649 , '30' , '16' union all
select 15599 , '30' , '16' union all
select 8823 , '31' , '8' union all
select 22376 , '31' , '8' union all
select 41191 , '31' , '17' union all
select 7746 , '1' , '7' union all
select 2630 , '1' , '7' union all
select 4790 , '1' , '7'
--转换
select convert(dec(9,2),spare)/100 as spare,
case len(day) when 2 then dateadd(hh,convert(int,H),convert(datetime,'2009-10-'+day))
else dateadd(hh,convert(int,H),convert(datetime,'2009-11-0'+day)) end as date
from @table
--结果
----------------------
6.580000 2009-10-30 16:00:00.000
86.570000 2009-10-30 16:00:00.000
31.490000 2009-10-30 16:00:00.000
156.490000 2009-10-30 16:00:00.000
155.990000 2009-10-30 16:00:00.000
88.230000 2009-10-31 08:00:00.000
223.760000 2009-10-31 08:00:00.000
411.910000 2009-10-31 17:00:00.000
77.460000 2009-11-01 07:00:00.000
26.300000 2009-11-01 07:00:00.000
47.900000 2009-11-01 07:00:00.000
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-11-04 09:52:33
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([spare] int,[day] int,[h] int)
insert [tb]
select 658,30,16 union all
select 8657,30,16 union all
select 3149,30,16 union all
select 15649,30,16 union all
select 15599,30,16 union all
select 8823,31,8 union all
select 22376,31,8 union all
select 41191,31,17 union all
select 7746,1,7 union all
select 2630,1,7 union all
select 4790,1,7
--------------开始查询--------------------------
select
spare*0.01 as [price],
'2009-'+case when day>29 then '10-' else '11-' end +ltrim(DAY)+' '+LTRIM(H)+':00:00.000' as [time]
from
tb
----------------结果----------------------------
/*price time
--------------------------------------- -------------------------------------------
6.58 2009-10-30 16:00:00.000
86.57 2009-10-30 16:00:00.000
31.49 2009-10-30 16:00:00.000
156.49 2009-10-30 16:00:00.000
155.99 2009-10-30 16:00:00.000
88.23 2009-10-31 8:00:00.000
223.76 2009-10-31 8:00:00.000
411.91 2009-10-31 17:00:00.000
77.46 2009-11-1 7:00:00.000
26.30 2009-11-1 7:00:00.000
47.90 2009-11-1 7:00:00.000(11 行受影响)
*/