请教一个SQLite SQL语句得到报表的问题
我需要修改一个程序 以前是用ACCESS作为数据库的，现在转用Sqlite
我建表的语句是
Create TABLE table1
[Sn] integer PRIMARY KEY ASC AUTOINCREMENT UNIQUE NOT NULL
,[Humidity] integer NOT NULL /*湿度*/
,[Temperature] integer NOT NULL /*温度*/
,[RecDate] datetime NOT NULL /*记录日期*/
,[RecTime] datetime NOT NULL /*记录时间*/
,[kufang] integer NOT NULL /*库房*/
);
我现在想从这个表中提取数据形成一个报表
报表格式如下
请问该怎么做？ 知道的请帮忙看看 谢谢了
我对数据库熟悉程度有限，估计对这个表还有很多欠缺，请大家多指点一下 谢谢了
------解决方案--------------------
用group by 比较容易实现。
- SQL code
sqlite> Create TABLE table1( ...> [Sn] integer PRIMARY KEY ASC AUTOINCREMENT UNIQUE NOT NULL ...> ,[Humidity] integer NOT NULL /*湿度*/ ...> ,[Temperature] integer NOT NULL /*温度*/ ...> ,[RecDate] datetime NOT NULL /*记录日期*/ ...> ,[RecTime] datetime NOT NULL /*记录时间*/ ...> ,[kufang] integer NOT NULL /*库房*/ ...> );sqlite>sqlite> select * from table1;2|10|20|2000-04-12|09:00:00|13|11|21|2000-04-12|11:00:00|14|12|22|2000-04-12|17:00:00|15|13|23|2000-04-12|15:00:00|16|14|24|2000-04-13|09:00:00|17|15|25|2000-04-13|01:00:00|1sqlite>sqlite> select kufang,RecDate, ...> sum((1/('09'-strftime('%H',RecTime)) is null)*Humidity) as Humidity9, ...> sum((1/('09'-strftime('%H',RecTime)) is null)*Temperature) as Temperature9, ...> sum((1/('11'-strftime('%H',RecTime)) is null)*Humidity) as Humidity11, ...> sum((1/('11'-strftime('%H',RecTime)) is null)*Temperature) as Temperature11, ...> sum((1/('15'-strftime('%H',RecTime)) is null)*Humidity) as Humidity15, ...> sum((1/('15'-strftime('%H',RecTime)) is null)*Temperature) as Temperature15, ...> sum((1/('17'-strftime('%H',RecTime)) is null)*Humidity) as Humidity17, ...> sum((1/('17'-strftime('%H',RecTime)) is null)*Temperature) as Temperature17 ...> from table1 ...> group by kufang,RecDate;1|2000-04-12|10|20|11|21|13|23|12|221|2000-04-13|14|24|0|0|0|0|0|0
------解决方案--------------------
上述代码假设你使用ACCESS数据库，如果不是，则用SUM(CASE WHEN)
select [kufang],[RecDate],
sum(case when strftime('%H',RecTime)='09' then 湿度 end),
sum(case when strftime('%H',RecTime)='09' then 温度 end)
group by [kufang],[RecDate]
其它的类似，没有看到记录及要求结果，贴出来看看