sql中的分段列表
表：用户号码，登录时间
显示 ：每日登录各时间段的登录人数，和每天登录人数
if isnull(object_id('#tb'),'')=''
drop table #tb
CREATE TABLE #tb(列名1 varchar(12),时间 datetime)
INSERT INTO #tb
SELECT '03174190188','2009-11-01 07:17:39.217' UNION ALL
SELECT '015224486575','2009-11-01 08:01:17.153' UNION ALL
SELECT '013593006926','2009-11-12 08:04:46.560' UNION ALL
SELECT '013593006926','2009-11-12 07:05:46.560' UNION ALL
SELECT '013599584239','2009-11-22 08:53:27.763' UNION ALL
SELECT '013911693526','2009-11-23 08:53:51.683' UNION ALL
SELECT '013846472440','2009-11-23 08:54:57.233' UNION ALL
SELECT '013990353697','2009-11-24 08:55:25.077' UNION ALL
SELECT '013990353697','2009-11-25 08:56:01.327' UNION ALL
SELECT '013945594843','2009-11-26 08:57:02.233' UNION ALL
SELECT '013990353697','2009-11-27 08:57:29.700' UNION ALL
SELECT '013916597421','2009-11-28 08:59:49.390' UNION ALL
SELECT '03916995857','2009-11-29 09:11:05.607' UNION ALL
SELECT '015097712001','2009-11-30 09:13:50.293'
--SQL查询如下:
DECLARE @minDate datetime,@maxDate datetime;
SELECT @minDate = '2009-11-1',@maxDate = '2009-12-01';
select convert(char(10),dateadd(d,number,@minDate),120),
sum(case when convert(char(8),时间,108) between '00:00' and '01:00' then 1 else 0 end) as '00:00~01:00',
sum(case when convert(char(8),时间,108) between '01:00' and '02:00' then 1 else 0 end) as '01:00~02:00',
sum(case when convert(char(8),时间,108) between '02:00' and '03:00' then 1 else 0 end) as '02:00~03:00',
sum(case when convert(char(8),时间,108) between '03:00' and '04:00' then 1 else 0 end) as '03:00~04:00',
sum(case when convert(char(8),时间,108) between '04:00' and '05:00' then 1 else 0 end) as '04:00~05:00',
sum(case when convert(char(8),时间,108) between '05:00' and '06:00' then 1 else 0 end) as '05:00~06:00',
sum(case when convert(char(8),时间,108) between '06:00' and '07:00' then 1 else 0 end) as '06:00~07:00',
sum(case when convert(char(8),时间,108) between '07:00' and '08:00' then 1
相关文档：
string str = System.Configuration.ConfigurationManager.AppSettings["strconn"];
string sqlpwd = "select password from bg_user where username='" + username + "'";
MySqlConnection conn = new MySqlConnection(str);
MySqlCommand cmd=new MySqlCommand(sqlpwd,conn);
MySqlDataAdapter adr = new MySqlDataA ......
常用SQL查询：
1、查看表空间的名称及大小
select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size
from dba_tablespaces t, dba_data_files d
where t.tablespace_name = d.tablespace_name
group by t.tablespace_name;
2、查看表空间物理文件的名称及大小
select tablespace_name, file_id, ......
left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录
right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录
inner join(等值连接) 只返回两个表中联结字段相等的行
举例如下：
--------------------------------------------
表A记录如下：
aID aNum
1 a ......
1: 删除LOG (删除log文件，附加时自动重建log文件，以此方式来清空日志)
1) 分离数据库 企业管理器－＞服务器－＞数据库－＞右键－＞分离数据库
2) 删除LOG文件
3) 附加数据库 企业管理器－＞服务器－＞数据库－＞右键－＞附加数据库
此法生� ......
SQL按照日、周、月、年统计数据 收藏
文章参考：http://www.cnblogs.com/wenbhappy/archive/2008/07/02/1233660.html
如:
表:consume_record
字段:consume (money类型)
date (datetime类型)
请问怎么写四条sql语句分别按日,按周,按月,按季统计消费总量.
如:1月 1200� ......
