例如设备有 A1,A2,A3,A4 其中A4没加油 现有一表数据如下
设备 加油量 加油日期
A1 10 2009-10-1 A2 2 2009-10-2 A3 3 2009-10-1 现在想得到10月份每天每台设备的加油汇总表，如下 日期
设备 1 2 3 4 5 。 31 1-31 18-17 说明（18-17是指上月18号到17号数据的合计）
A1 10 10 。。A2 2 2 。。A3 3 3 。。A4 0 。。日统计 13 2 15 。。
存储过程参数 如 ‘2009-08’ 关键是怎么取出最后一列的合计，即上月18号到这月17号的合计
请高手帮忙，分不够再加
设备 加油量 加油日期
A1 10 2009-10-1 A2 2 2009-10-2 A3 3 2009-10-1 现在想得到10月份每天每台设备的加油汇总表，如下 日期
设备 1 2 3 4 5 。 31 1-31 18-17 说明（18-17是指上月18号到17号数据的合计）
A1 10 10 。。A2 2 2 。。A3 3 3 。。A4 0 。。日统计 13 2 15 。。
存储过程参数 如 ‘2009-08’ 关键是怎么取出最后一列的合计，即上月18号到这月17号的合计
请高手帮忙，分不够再加
前面用case when ..取本月的统计数据，最后一列case when 时间>=trunc(add_months(时间,-1))+18 and...来筛选或者两表连接，分别取1-31日和18-17日的数据，统计结果通过设备来连接
create or replace procedure PRO_OIL_COST_DAILY(dtMonth in varchar2,MYCUR OUT SYS_REFCURSOR)
IS
BEGIN
OPEN MYCUR FOR
select
dept_name 部门,
decode(dept_name,null,'总计',asset_spec_name) 设备类型,
decode(decode(dept_name,null,'总计',asset_spec_name),null,'部门日消耗累计','总计',null,nvl(asset_no,'日消耗累计')) 设备号,
sum(decode(to_char(add_oil_date, 'DD'), '01', oil_number, null)) "1",
sum(decode(to_char(add_oil_date, 'DD'), '02', oil_number, null)) "2",
sum(decode(to_char(add_oil_date, 'DD'), '03', oil_number, null)) "3",
sum(decode(to_char(add_oil_date, 'DD'), '04', oil_number, null)) "4",
sum(decode(to_char(add_oil_date, 'DD'), '05', oil_number, null)) "5",
sum(decode(to_char(add_oil_date, 'DD'), '06', oil_number, null)) "6",
sum(decode(to_char(add_oil_date, 'DD'), '07', oil_number, null)) "7",
sum(decode(to_char(add_oil_date, 'DD'), '08', oil_number, null)) "8",
sum(decode(to_char(add_oil_date, 'DD'), '09', oil_number, null)) "9",
sum(decode(to_char(add_oil_date, 'DD'), '10', oil_number, null)) "10",
sum(decode(to_char(add_oil_date, 'DD'), '11', oil_number, null)) "11",
sum(decode(to_char(add_oil_date, 'DD'), '12', oil_number, null)) "12",
sum(decode(to_char(add_oil_date, 'DD'), '13', oil_number, null)) "13",
sum(decode(to_char(add_oil_date, 'DD'), '14', oil_number, null)) "14",
sum(decode(to_char(add_oil_date, 'DD'), '15', oil_number, null)) "15",
sum(decode(to_char(add_oil_date, 'DD'), '16', oil_number, null)) "16",
sum(decode(to_char(add_oil_date, 'DD'), '17', oil_number, null)) "17",
sum(decode(to_char(add_oil_date, 'DD'), '18', oil_number, null)) "18",
sum(decode(to_char(add_oil_date, 'DD'), '19', oil_number, null)) "19",
sum(decode(to_char(add_oil_date, 'DD'), '20', oil_number, null)) "20",
sum(decode(to_char(add_oil_date, 'DD'), '21', oil_number, null)) "21",
sum(decode(to_char(add_oil_date, 'DD'), '22', oil_number, null)) "22",
sum(decode(to_char(add_oil_date, 'DD'), '23', oil_number, null)) "23",
sum(decode(to_char(add_oil_date, 'DD'), '24', oil_number, null)) "24",
sum(decode(to_char(add_oil_date, 'DD'), '25', oil_number, null)) "25",
sum(decode(to_char(add_oil_date, 'DD'), '26', oil_number, null)) "26",
sum(decode(to_char(add_oil_date, 'DD'), '27', oil_number, null)) "27",
sum(decode(to_char(add_oil_date, 'DD'), '28', oil_number, null)) "28",
sum(decode(to_char(add_oil_date, 'DD'), '29', oil_number, null)) "29",
sum(decode(to_char(add_oil_date, 'DD'), '30', oil_number, null)) "30",
sum(decode(to_char(add_oil_date, 'DD'), '31', oil_number, null)) "31",
sum(nvl(oil_number,0)) "1-31日"
from
(
select a.asset_no,o.oil_number,o.add_oil_date,nvl(d.dept_name,a.dept_id) dept_name,s.asset_spec_name
from asset a
left join equipment_add_oil o on a.asset_id=o.asset_id
left join code_dept d on d.dept_id=a.dept_id
inner join asset_spec s on s.asset_spec_id=a.asset_spec_id
where (a.is_drop <> '是' or a.is_drop is null)
)tb
where (to_char(add_oil_date,'yyyy-MM') = dtMonth or add_oil_date is null)
group by rollup(dept_name,asset_spec_name,asset_no);
END PRO_OIL_COST_DAILY;
只差18-17的合计数据
/*====================================================*/
-- Author: Ken Wong
-- Create date: 2009-12-17 10:44:49
-- Description:
/*====================================================*/
/*
dbo.proc_trans_makedata '@table
id day starttime overtime name 1 20091202 9:00 16:00 张三
'
*/
/*
例子描述问题：
表 t1 , 内容: --------------------------------------------------------------
id day starttime overtime name 1 20091202 9:00 16:00 张三
----------------------------------------------------------------- 查询条件：20091129 - 20091205， 写一条 SQL 语句得到下面的表： ------------------------------------------------------------------------------------
时间 星期日 星期一 星期二 星期三 星期四 星期五 星期六
8:00-9:00 null null null null null null null
9:00-10:00 null null null 张三 null null null
10:00-11:00 null null null 张三 null null null
11:00-12:00 null null null 张三 null null null
13:00-14:00 null null null 张三 null null null
14:00-15:00 null null null 张三 null null null
15:00-16:00 null null null 张三 null null null
16:00-17:00 null null null null null null null
17:00-18:00 null null null null null null null */
--> 测试数据：@table
declare @table table([id] int,[day] varchar(10),[starttime] varchar(10),[overtime] varchar(10),[name] varchar(10))
insert @table
select 1,'20091202', '09:00','16:00','张三'
declare @begdate datetime,@enddate datetime
select @begdate = '20091129',@enddate = '20091205'select t.[date],t.[time],u.[name] into #temp from
(
select convert(varchar(10),dateadd(hour,number,@begdate),112) as [date],
convert(varchar(10),dateadd(hour,number,@begdate),108) + '-'
+convert(varchar(10),dateadd(hour,number+1,@begdate),108) as [time],
null as [name]
from master.dbo.spt_values
where type = 'P'
and dateadd(hour,number,@begdate) <= dateadd(hour,18,@enddate)
and convert(varchar(10),dateadd(hour,number,@begdate),108) >= '08:00'
and convert(varchar(10),dateadd(hour,number,@enddate),108) <= '18:00'
) t left join
(
select convert(varchar(10),dateadd(hour,r.number,@begdate),112) as [date],
convert(varchar(10),dateadd(hour,number,@begdate),108) + '-'
+convert(varchar(10),dateadd(hour,number+1,@begdate),108) as [time],
h.name
from master.dbo.spt_values r ,@table h
where type = 'P'
and convert(varchar(10),dateadd(hour,number,@begdate),108) >= h.[starttime]
and convert(varchar(10),dateadd(hour,number,@enddate),108) <= h.[overtime]
and convert(varchar(10),dateadd(hour,r.number,@begdate),112) = h.[day]
) u
on t.[date] = u.[date] and t.[time] = u.[time]--select * from #tempdeclare @sql varchar(8000)
select @sql = ''select @sql = @sql + ',max(case [date] when '+[date]+' then name else null end) as ['+ltrim(datename(weekday,[date]))+']'
from (select distinct [date] from #temp) tselect @sql = 'select [time] '+ @sql + ' from #temp group by [time]'--print @sqlexec(@sql)drop table #temp
sum(case when add_oil_date>=trunc(add_months(add_oil_date,-1))+18 and add_oil_date<trunc(add_months(add_oil_date,0))+18 then oil_number else 0 end) "18-17"试试看？