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表示時間段是一點
唉，只想到這個笨辦法唉