A. 怎么查询某个数据库中的某些表共有多少条数据 sqlserver2005
SELECT a.name, b.rows
FROM sysobjects a inner JOIN sysindexes b
ON b.id = a.id
WHERE a.xtype = 'U' AND b.indid IN (0,1)
U表示用户表，0和1过滤一些记录
详细请参考查询出来的结果。
如果仅要某些表，在WHERE后加上a.name in (表名1，表名2等）
B. sqlserver: insert into table values ('11','aa', ltrim(rtrim('aaa ')))
这个你看一下 列的数据类型
如果是 varchar 的。 应该是没有空格的
但是如果是 char 类型的， 会自动补上空格
例如有个字段 a 数据类型为 char(5)
你插入了 'aaa'
那么最后 a 的内容， 就是 'aaa '
因为 char 类型的， 不填写满的话， 就会自动补满长度。
C. SQLServer表的大小与表占硬盘空间大小有什么区别，应该怎么去查这两项性能
表的大小一般指表的行数，占用硬盘空间大小就比较复杂了，可以有以下几个参数：
分配空间，数据占用空间及索引占用空间。
执行
EXEC alltablecount
即可显示当前数据库所有表资源占用情况。
执行前，请前建立以下表，和两个存储过程：
1、先建立表：HY_SPACE
CREATE TABLE [HY_SPACE] (
[name] [nvarchar] (128) COLLATE Chinese_PRC_CI_AS NULL ,
[rows] [char] (11) COLLATE Chinese_PRC_CI_AS NULL ,
[reserved] [varchar] (18) COLLATE Chinese_PRC_CI_AS NULL ,
[data] [varchar] (18) COLLATE Chinese_PRC_CI_AS NULL ,
[index_size] [varchar] (18) COLLATE Chinese_PRC_CI_AS NULL ,
[unused] [varchar] (18) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
2、建立统计存储过程：
create procere HYspaceused
@objname nvarchar(776) = null,
@updateusage varchar(5) = false
as
declare @idint
declare @typecharacter(2)
declare@pagesint
declare @dbname sysname
declare @dbsize dec(15,0)
declare @logsize dec(15)
declare @bytesperpagedec(15,0)
declare @pagesperMBdec(15,0)
create table #spt_space
(
rowsint null,
reserveddec(15) null,
datadec(15) null,
indexpdec(15) null,
unuseddec(15) null
)
if @updateusage is not null
begin
select @updateusage=lower(@updateusage)
if @updateusage not in ('true','false')
begin
raiserror(15143,-1,-1,@updateusage)
return(1)
end
end
if @objname IS NOT NULL
begin
select @dbname = parsename(@objname, 3)
if @dbname is not null and @dbname <> db_name()
begin
raiserror(15250,-1,-1)
return (1)
end
if @dbname is null
select @dbname = db_name()
select @id = null
select @id = id, @type = xtype
from sysobjects
where id = object_id(@objname)
if @id is null
begin
raiserror(15009,-1,-1,@objname,@dbname)
return (1)
end
if not exists (select * from sysindexes
where @id = id and indid < 2)
if @type in ('P ','D ','R ','TR','C ','RF')
begin
raiserror(15234,-1,-1)
return (1)
end
else if @type = 'V '
begin
raiserror(15235,-1,-1)
return (1)
end
else if @type in ('PK','UQ')
begin
raiserror(15064,-1,-1)
return (1)
end
else if @type = 'F '
begin
raiserror(15275,-1,-1)
return (1)
end
end
if @updateusage = 'true'
begin
if @objname is null
dbcc updateusage(0) with no_infomsgs
else
dbcc updateusage(0,@objname) with no_infomsgs
print ' '
end
set nocount on
if @id is null
begin
select @dbsize = sum(convert(dec(15),size))
from dbo.sysfiles
where (status & 64 = 0)
select @logsize = sum(convert(dec(15),size))
from dbo.sysfiles
where (status & 64 <> 0)
select @bytesperpage = low
from master.dbo.spt_values
where number = 1
and type = 'E'
select @pagesperMB = 1048576 / @bytesperpage
select database_name = db_name(),
database_size =
ltrim(str((@dbsize + @logsize) / @pagesperMB,15,2) + ' MB'),
'unallocated space' =
ltrim(str((@dbsize -
(select sum(convert(dec(15),reserved))
from sysindexes
where indid in (0, 1, 255)
)) / @pagesperMB,15,2)+ ' MB')
print ' '
insert into #spt_space (reserved)
select sum(convert(dec(15),reserved))
from sysindexes
where indid in (0, 1, 255)
select @pages = sum(convert(dec(15),dpages))
from sysindexes
where indid < 2
select @pages = @pages + isnull(sum(convert(dec(15),used)), 0)
from sysindexes
where indid = 255
update #spt_space
set data = @pages
update #spt_space
set indexp = (select sum(convert(dec(15),used))
from sysindexes
where indid in (0, 1, 255))
- data
update #spt_space
set unused = reserved
- (select sum(convert(dec(15),used))
from sysindexes
where indid in (0, 1, 255))
select reserved = ltrim(str(reserved * d.low / 1024.,15,0) +
' ' + 'KB'),
data = ltrim(str(data * d.low / 1024.,15,0) +
' ' + 'KB'),
index_size = ltrim(str(indexp * d.low / 1024.,15,0) +
' ' + 'KB'),
unused = ltrim(str(unused * d.low / 1024.,15,0) +
' ' + 'KB')
from #spt_space, master.dbo.spt_values d
where d.number = 1
and d.type = 'E'
end
else
begin
insert into #spt_space (reserved)
select sum(reserved)
from sysindexes
where indid in (0, 1, 255)
and id = @id
select @pages = sum(dpages)
from sysindexes
where indid < 2
and id = @id
select @pages = @pages + isnull(sum(used), 0)
from sysindexes
where indid = 255
and id = @id
update #spt_space
set data = @pages
update #spt_space
set indexp = (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and id = @id)
- data
update #spt_space
set unused = reserved
- (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and id = @id)
update #spt_space
set rows = i.rows
from sysindexes i
where i.indid < 2
and i.id = @id
INSERT INTO HY_SPACE
select name = object_name(@id),
rows = convert(char(11), rows),
reserved = ltrim(str(reserved * d.low / 1024.,15,0) +
' ' + 'KB'),
data = ltrim(str(data * d.low / 1024.,15,0) +
' ' + 'KB'),
index_size = ltrim(str(indexp * d.low / 1024.,15,0) +
' ' + 'KB'),
unused = ltrim(str(unused * d.low / 1024.,15,0) +
' ' + 'KB')
from #spt_space, master.dbo.spt_values d
where d.number = 1
and d.type = 'E'
RETURN
end
return (0)
GO
3、主存储过程
create procere alltablecount as
declare @name varchar(100)
TRUNCATE TABLE HY_SPACE
declare tablecur cursor for select name from sysobjects where xtype= 'u '
create table #tablecount(tablename varchar(100),reccount int)
open tablecur
fetch next from tablecur into @name
while @@fetch_status!=-1
begin
exec ('insert into #tablecount select tablename='''+@name+''',reccount=count(1) from '+@name+' ')
EXEC HYspaceused @NAME
fetch next from tablecur into @name
end
close tablecur
deallocate tablecur
select * from #tablecount order by reccount desc
select * from HY_SPACE
GO
D. sqlserver数据库中如何将每条数据开头的空格去掉
可以同时使用，ltrim() / rtrim()去除字段两头的空格：
PS.
update 表名 set ltrim(rtrim(字段));
E. 谁总结下sqlserver数据库优化知识的详解和具体操作最优方案！
给你一些我常用的脚本！至于方案，我觉得完全在于积累经验！
--前10名其他等待类型
SELECT TOP 10 *
from sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC
SELECT *FROM sys.dm_os_wait_stats WHERE wait_type like 'PAGELATCH%'
OR wait_type like 'LAZYWRITER_SLEEP%'
--CPU的压力
SELECT scheler_id, current_tasks_count, runnable_tasks_count
FROM sys.dm_os_schelers
WHERE scheler_id < 255
--表现最差的前10名使用查询
SELECT TOP 10 ProcereName = t.text,
ExecutionCount = s.execution_count,
AvgExecutionTime = isnull ( s.total_elapsed_time / s.execution_count, 0 ),
AvgWorkerTime = s.total_worker_time / s.execution_count,
TotalWorkerTime = s.total_worker_time,
MaxLogicalReads = s.max_logical_reads,
MaxPhysicalReads = s.max_physical_reads,
MaxLogicalWrites = s.max_logical_writes,
CreationDateTime = s.creation_time,
CallsPerSecond = isnull ( s.execution_count / datediff ( second , s.creation_time, getdate ()), 0 )
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t ORDER BY
s.max_physical_reads DESC
SELECT SUM(signal_wait_time_ms) AS total_signal_wait_time_ms总信号等待时间 ,
SUM(wait_time_ms - signal_wait_time_ms) AS resource_wait_time_ms资源的等待时间,
SUM(signal_wait_time_ms) * 1.0 / SUM (wait_time_ms) * 100 AS [signal_wait_percent信号等待％],
SUM(wait_time_ms - signal_wait_time_ms) * 1.0 / SUM (wait_time_ms) * 100 AS [resource_wait_percent资源等待％]
FROM sys.dm_os_wait_stats
--一个信号等待时间过多对资源的等待时间那么你的CPU是目前的一个瓶颈。
--查看进程所执行的SQL语句
if (select COUNT(*) from master.dbo.sysprocesses) > 500
begin
select text,CROSS APPLY master.sys.dm_exec_sql_text(a.sql_handle) from master.sys.sysprocesses a
end
select text,a.* from master.sys.sysprocesses a
CROSS APPLY master.sys.dm_exec_sql_text(a.sql_handle)
where a.spid = '51'
dbcc inputbuffer(53)
with tb
as
(
select blocking_session_id,
session_id,db_name(database_id) as dbname,text from master.sys.dm_exec_requests a
CROSS APPLY master.sys.dm_exec_sql_text(a.sql_handle)
),
tb1 as
(
select a.*,login_time,program_name,client_interface_name,login_name,cpu_time,memory_usage*8 as 'memory_usage(KB)',
total_scheled_time,reads,writes,logical_reads
from tb a inner join master.sys.dm_exec_sessions b
on a.session_id=b.session_id
)
select a.*,connect_time,client_tcp_port,client_net_address from tb1 a inner join master.sys.dm_exec_connections b on a.session_id=b.session_id
--当前进程数
select * from master.dbo.sysprocesses
order by cpu desc
--查看当前活动的进程数
sp_who active
--查询是否由于连接没有释放引起CPU过高
select * from master.dbo.sysprocesses
where spid> 50
and waittype = 0x0000
and waittime = 0
and status = 'sleeping '
and last_batch < dateadd(minute, -10, getdate())
and login_time < dateadd(minute, -10, getdate())
--强行释放空连接
select 'kill ' + rtrim(spid) from master.dbo.sysprocesses
where spid> 50
and waittype = 0x0000
and waittime = 0
and status = 'sleeping '
and last_batch < dateadd(minute, -60, getdate())
and login_time < dateadd(minute, -60, getdate())
--查看当前占用 cpu 资源最高的会话和其中执行的语句(及时CPU)
select spid,cmd,cpu,physical_io,memusage,
(select top 1 [text] from ::fn_get_sql(sql_handle)) sql_text
from master..sysprocesses order by cpu desc,physical_io desc
--查看缓存中重用次数少，占用内存大的查询语句（当前缓存中未释放的）--全局
SELECT TOP 100 usecounts, objtype, p.size_in_bytes,[sql].[text]
FROM sys.dm_exec_cached_plans p OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) sql
ORDER BY usecounts,p.size_in_bytes desc
SELECT top 25 qt.text,qs.plan_generation_num,qs.execution_count,dbid,objectid
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) as qt
WHERE plan_generation_num >1
ORDER BY qs.plan_generation_num
SELECT top 50 qt.text AS SQL_text ,SUM(qs.total_worker_time) AS total_cpu_time,
SUM(qs.execution_count) AS total_execution_count,
SUM(qs.total_worker_time)/SUM(qs.execution_count) AS avg_cpu_time,
COUNT(*) AS number_of_statements
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
GROUP BY qt.text
ORDER BY total_cpu_time DESC --统计总的CPU时间
--ORDER BY avg_cpu_time DESC --统计平均单次查询CPU时间
-- 计算可运行状态下的工作进程数量
SELECT COUNT(*) as workers_waiting_for_cpu,s.scheler_id
FROM sys.dm_os_workers AS o
INNER JOIN sys.dm_os_schelers AS s
ON o.scheler_address=s.scheler_address
AND s.scheler_id<255
WHERE o.state='RUNNABLE'
GROUP BY s.scheler_id
--表空间大小查询
create table #tb(表名 sysname,记录数 int,保留空间 varchar(100),使用空间 varchar(100),索引使用空间 varchar(100),未用空间 varchar(100))
insert into #tb exec sp_MSForEachTable 'EXEC sp_spaceused ''?'''
select * from #tb
go
SELECT
表名,
记录数,
cast(ltrim(rtrim(replace(保留空间,'KB',''))) as int)/1024 保留空间MB,
cast(ltrim(rtrim(replace(使用空间,'KB',''))) as int)/1024 使用空间MB,
cast(ltrim(rtrim(replace(使用空间,'KB',''))) as int)/1024/1024.00 使用空间GB,
cast(ltrim(rtrim(replace(索引使用空间,'KB',''))) as int)/1024 索引使用空间MB,
cast(ltrim(rtrim(replace(未用空间,'KB',''))) as int)/1024 未用空间MB
FROM #tb
WHERE cast(ltrim(rtrim(replace(使用空间,'KB',''))) as int)/1024 > 0
--order by 记录数 desc
ORDER BY 使用空间MB DESC
DROP TABLE #tb
--查询是否由于连接没有释放引起CPU过高
select * from master.dbo.sysprocesses
where spid> 50
and waittype = 0x0000
and waittime = 0
and status = 'sleeping '
and last_batch < dateadd(minute, -10, getdate())
and login_time < dateadd(minute, -10, getdate())
--强行释放空连接
select 'kill ' + rtrim(spid) from master.dbo.sysprocesses
where spid> 50
and waittype = 0x0000
and waittime = 0
and status = 'sleeping '
and last_batch < dateadd(minute, -60, getdate())
and login_time < dateadd(minute, -60, getdate())
----查看当前占用 cpu 资源最高的会话和其中执行的语句(及时CPU)
select spid,cmd,cpu,physical_io,memusage,
(select top 1 [text] from ::fn_get_sql(sql_handle)) sql_text
from master..sysprocesses order by cpu desc,physical_io desc
----查看缓存中重用次数少，占用内存大的查询语句（当前缓存中未释放的）--全局
SELECT TOP 100 usecounts, objtype, p.size_in_bytes,[sql].[text]
FROM sys.dm_exec_cached_plans p OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) sql
ORDER BY usecounts,p.size_in_bytes desc
SELECT top 25 qt.text,qs.plan_generation_num,qs.execution_count,dbid,objectid
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) as qt
WHERE plan_generation_num >1
ORDER BY qs.plan_generation_num
SELECT top 50 qt.text AS SQL_text ,SUM(qs.total_worker_time) AS total_cpu_time,
SUM(qs.execution_count) AS total_execution_count,
SUM(qs.total_worker_time)/SUM(qs.execution_count) AS avg_cpu_time,
COUNT(*) AS number_of_statements
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
GROUP BY qt.text
ORDER BY total_cpu_time DESC --统计总的CPU时间
--ORDER BY avg_cpu_time DESC --统计平均单次查询CPU时间
-- 计算可运行状态下的工作进程数量
SELECT COUNT(*) as workers_waiting_for_cpu,s.scheler_id
FROM sys.dm_os_workers AS o
INNER JOIN sys.dm_os_schelers AS s
ON o.scheler_address=s.scheler_address
AND s.scheler_id<255
WHERE o.state='RUNNABLE'
GROUP BY s.scheler_id
SELECT creation_time N'语句编译时间'
,last_execution_time N'上次执行时间'
,total_physical_reads N'物理读取总次数'
,total_logical_reads/execution_count N'每次逻辑读次数'
,total_logical_reads N'逻辑读取总次数'
,total_logical_writes N'逻辑写入总次数'
, execution_count N'执行次数'
, total_worker_time/1000 N'所用的CPU总时间ms'
, total_elapsed_time/1000 N'总花费时间ms'
, (total_elapsed_time / execution_count)/1000 N'平均时间ms'
,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) N'执行语句'
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
where SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) not like '%fetch%'
ORDER BY total_elapsed_time / execution_count DESC;
F. SqlServer函数的字符串函数
1 字符串函数
1.1 长度与分析用
datalength(Char_expr) 返回字符串包含字符数,但不包含后面的空格
substring(expression,start,length) 不多说了,取子串
right(char_expr,int_expr) 返回字符串右边int_expr个字符
1.2 字符操作类
upper(char_expr) 转为大写
lower(char_expr) 转为小写
space(int_expr) 生成int_expr个空格
replicate(char_expr,int_expr)复制字符串int_expr次
reverse(char_expr) 反转字符串
stuff(char_expr1,start,length,char_expr2) 将字符串char_expr1中的从
start开始的length个字符用char_expr2代替
ltrim(char_expr) rtrim(char_expr) 取掉空格
ascii(char) char(ascii) 两函数对应,取ascii码,根据ascii码取字符
1.3 字符串查找
charindex(char_expr,expression) 返回char_expr的起始位置
patindex(%pattern%,expression) 返回指定模式的起始位置,否则为0
G. sqlserver数据库文件大小
第一种方法(较简单，看的有些吃力)：
exec sp_MSforeachtable "exec sp_spaceused '?'"
第二种方法(较复杂，但看的比较清楚，原作者不详)：
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tablespaceinfo]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
create table tablespaceinfo --创建结果存储表
(nameinfo varchar(50) ,
rowsinfo int , reserved varchar(20) ,
datainfo varchar(20) ,
index_size varchar(20) ,
unused varchar(20) )
delete from tablespaceinfo --清空数据表
declare @tablename varchar(255) --表名称
declare @cmdsql varchar(500)
DECLARE Info_cursor CURSOR FOR
select o.name
from dbo.sysobjects o where OBJECTPROPERTY(o.id, N'IsTable') = 1
and o.name not like N'#%%' order by o.name
OPEN Info_cursor
FETCH NEXT FROM Info_cursor
INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
if exists (select * from dbo.sysobjects where id = object_id(@tablename) and OBJECTPROPERTY(id, N'IsUserTable') = 1)
execute sp_executesql
N'insert into tablespaceinfo exec sp_spaceused @tbname',
N'@tbname varchar(255)',
@tbname = @tablename
FETCH NEXT FROM Info_cursor
INTO @tablename
END
CLOSE Info_cursor
DEALLOCATE Info_cursor
GO
--itlearner注：显示数据库信息
sp_spaceused @updateusage = 'TRUE'
--itlearner注：显示表信息
select *
from tablespaceinfo
order by cast(left(ltrim(rtrim(reserved)) , len(ltrim(rtrim(reserved)))-2) as int) desc
第三种方法：
select object_name(id) tablename,8*reserved/1024 reserved,rtrim(8*dpages/1024)+'Mb' used,8*(reserved-dpages)/1024 unused,8*dpages/1024-rows/1024*minlen/1024 free,
rows,* from sysindexes
where indid=1
order by reserved desc
H. 从SQLServer里面取出数据，去掉头尾的空格
利用trim（）函数就可以了啊。
trim分三种：ltrim、rtrim、trim。
ltrim是去除数据左边的空白；
rtrim是去除数据右边的空白；
trim是去除数据前后的空白。
例如：trim（" 你好 "）
输出结果是"你好"；
ltrim（" 你好 "）
输出结果是" 你好"；
rtrim（" 你好 "）
输出结果是"你好 "。
I. 学习sqlserver的条件
首先我觉得楼主不必拿学历妄自菲薄，我们公司有个同事，也只有中专学历，但是自己非常爱钻研，尤其在数据库方面，给公司解决了很多技术问题，现在这个同事已经是总监职位了。
SQL Server 我从毕业就开始接触，到现在已经10年了，虽然不是专业的，但是在实际使用中相关的开发问题还是能做到一一解决的。感觉要提高，只要本着多看，多写，多记的原则，就可以。
楼主是做维护工作的，我的理解，做维护的，应该可以自己抽出一些时间的。建议楼主多去CSDN论坛转转，上论坛的好处是问题比较多，很多问题你现在可能没遇到，看到别人的解决方法就学会了，那以后你自己如果遇到的话，就简单了。另外还有一个好处是你有不明白的，有人可以帮你解答。
有问题多提问，别人提的问题多看，不要着急，一口吃不了胖子，一点一点来，时间一长，你就会发现其实问题就这么多，转来转去也转不出什么花样。当然学习的时候，因为很多问题是固定的，所以要善于整理一些问题，还有很多东西，是要记下来的，先记下来，才有可能去做深一步的理解和分析。有问题一定要做到理解，一问到底，当然也不能钻牛角尖。
增删改查的语法比较固定，一点一点的看，关键字就那么几个，一天能弄懂一个，全部弄懂也用不了一个月。你说的另外的同事看语句一看就明白，那说明他对关键字的理解是非常好的，知道每个关键字是干啥的。
另外还有一点，上论坛的时候，对于别人提出的问题，如果看起来比较简单一点的，那就尝试去回答，回答的慢不要紧，回答错了也不要紧，关键是给你自己一个动手的机会。每天坚持写一个语句，那水平应该提高的很快。
上面说了这么多，可能也是比较空，具体还是要楼主自己慢慢体会。
我在csdn用户也是 coolingpipe ，最近不是总泡在上面了，但是有空还是回上去看看，欢迎加我好友，有问题请留言。
J. sqlserver的datetime查询
SELECT COUNT(*)/datediff(hour,'2009-06-15 00:00:00','2009-06-28 00:00:00')
from a
where b >= '2009-06-15 00:00:00'and b <= '2009-06-28 00:00:00'
如上是查询6月15日到28日期间，平均每小时记录的数据数目
datediff(hour,'2009-06-15 00:00:00','2009-06-28 00:00:00' --取得日期区间的小时数
------------------------------------------
哦，是我读题不仔细唉，呵呵。
这样的话就复杂了。
你不急的话我明天有空写个预存程序来解决这个问题
------------------------------------------
1.先建立日期时间构造预存程序，用来传回作时间段查询的两个时间
create PROC [dbo].[sp_fordate]
@year char(4),
@month char(2),
@day char(2),
@hour char(2),
@date1 varchar(12) output,
@date2 char(20) output,
@date3 char(20) output
as
begin
set @date1 = @year + '-' + ltrim(rtrim(@month)) + '-' +rtrim(@day)
set @date2 = @date1 + space(1) + ltrim(rtrim(@hour)) + ':00:00'
set @hour = @hour +1
set @date3 = @date1 + space(1) + ltrim(rtrim(@hour)) + ':00:00'
set @hour = @hour -1
set @date1 = @date1 + '-'+ @hour
end
2.在这一个预存程序里用循环跑出一个用union关联的超长sql语句执行。需要传入四个值，年份（2009四位格式），月份，日期（若日期传入为空自动取1），小时（输入1表示1点到2点时段，以此类推）
create proc riqi
@year char(4),
@month char(2),
@day char(2),
@hour char(2)
as
BEGIN
declare @SQL varChar(4000)
if @day is null or @day = ''
begin
set @day = 1
end
declare @date1 char(12)
declare @date2 char(20)
declare @date3 char(20)
exec sp_fordate @year,@month,@day,@hour,@date1 output,@date2 output,@date3 output
set @sql = 'select count(*) as 数目, '''+ @date1 + ''' as 日期时间 from a where b >= ''' + @date2 + '''' + ' and b<= ''' + @date3 + ''''
set @day = @day + 1
while @day <= 31
BEGIN
exec sp_fordate @year,@month,@day,@hour,@date1 output,@date2 output,@date3 output
set @sql = @sql +char(10)+char(13)+ ' union all ' +char(10)+char(13)+ 'select count(*), '''+ @date1 + ''' from a where b >= ''' + @date2 + '''' + ' and b<= ''' + @date3 + ''''
set @day = @day + 1
print @day
if @@error <> ''
BEGIN
break
END
ELSE
CONTINUE
END
exec(@sql)
END
测试结果如下
数目 日期时间
----------- ------------
3 2009-10-1-1
2 2009-10-2-1
1 2009-10-3-1
最后的-1表示时间段是一点
唉，只想到这个笨办法唉