一个数据库查询的问题，请大家帮帮忙！
单位表：id,name
加油表：单位id,单位name,车辆号码，加油数量，加油时间
查询结果如下：
单位一 当日加油数量 累计加油数量 单位二 当日加油数量 累计加油数量
001 100 1080 101 200 2000
002 200 3000 102 540 8124
请大家帮忙看看这个查询应该怎么写，用一条语句是不是写不出来啊，如果用视图的话应该怎么写，谢谢！
查询的时候根据时间查询。
[解决办法]
--> 测试数据：@tbdeclare @tb table([id] int,[teamid] int,[team] varchar(6),[carcode] varchar(5),[shuliang] numeric(7,4),[shijian] datetime)insert @tbselect 1,1,'厂一队','00001',100.0000,'2012-6-12 0:00:00' union allselect 2,1,'厂一队','00001',200.0000,'2012-6-13 0:00:00' union allselect 3,2,'厂二队','00002',300.0000,'2012-6-12 0:00:00' union allselect 4,2,'厂二队','00002',400.0000,'2012-6-13 0:00:00' union allselect 5,1,'厂一队','00002',100.0000,'2012-6-12 0:00:00' union allselect 6,1,'厂一队','00002',200.0000,'2012-6-13 0:00:00' union allselect 7,3,'厂三队','00010',500.0000,'2012-6-12 0:00:00'--SQL语句--得到每个team,carcode的当天及累计数量SELECT rowid=ROW_NUMBER() OVER(PARTITION BY teamid,team ORDER BY teamid), teamid,team,carcode, SUM(CASE WHEN shijian >= CONVERT(varchar(10),GETDATE(),120) AND shijian < CONVERT(varchar(10),DATEADD(day,1,GETDATE()),120) THEN shuliang ELSE 0 END) AS CurrentDay, SUM(shuliang) AS total INTO #TMPFROM @tbGROUP BY teamid,team,carcode;--得到一共有多少个team,并生成列字符串DECLARE @s varchar(MAX);SET @s='';SELECT @s=@s+',MAX(CASE WHEN teamid='+RTRIM(teamid)+' THEN carcode ELSE '''' END) AS ['+team+']' +',MAX(CASE WHEN teamid='+RTRIM(teamid)+' THEN RTRIM(CAST(CurrentDay AS numeric(12,2))) ELSE '''' END) AS [' + team+'_当天]' +',MAX(CASE WHEN teamid='+RTRIM(teamid)+' THEN RTRIM(CAST(total AS numeric(12,2))) ELSE '''' END) AS [' + team+'_累计]'FROM #TMPGROUP BY teamid,teamORDER BY teamid;--执行SET @s='SELECT '+STUFF(@s,1,1,'')+' FROM #TMP GROUP BY rowid';PRINT @s;EXEC(@s);--删除临时表DROP TABLE #TMP;/*厂一队 厂一队_当天 厂一队_累计 厂二队 厂二队_当天 厂二队_累计 厂三队 厂三队_当天 厂三队_累计00001 200.00 300.00 00002 400.00 700.00 00010 0.00 500.0000002 200.00 300.00 */
