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
Ïà¹ØÎĵµ£º
create PROCEDURE pagelist
@tablename nvarchar(50),
@fieldname nvarchar(50)='*',
@pagesize int output,--ÿҳÏÔʾ¼Ç¼ÌõÊý
@currentpage int output,--µÚ¼¸Ò³
@orderid nvarchar(50),--Ö÷¼üÅÅÐò
@sort int,--ÅÅÐò·½Ê½£¬1±íʾÉýÐò£¬0±íʾ½µÐòÅÅÁÐ
......
³£ÓÃ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, ......
Sql ServerÖеÄÈÕÆÚÓëʱ¼äº¯Êý
1. µ±Ç°ÏµÍ³ÈÕÆÚ¡¢Ê±¼ä
select getdate()
2. dateadd ÔÚÏòÖ¸¶¨ÈÕÆÚ¼ÓÉÏÒ»¶Îʱ¼äµÄ»ù´¡ÉÏ£¬·µ»ØÐ嵀 datetime Öµ
ÀýÈ磺ÏòÈÕÆÚ¼ÓÉÏ2Ìì
select dateadd(day,2,'2004-10-15') --·µ»Ø£º2004-10-17 00:00:00.000
3. datediff ·µ»Ø¿çÁ½¸öÖ ......
use Tempdb
go
if object_ID ( 'fn_ACITEncryption' ) is not null
drop function fn_ACITEncryption
go
create function fn_ACITEncryption
(
@Str nvarchar ( 4000), -- ¼ÓÃܵÄ×Ö·û´®
@Flag bit = 1, --1 ¡¢¼ÓÃÜ 0 ¡¢½âÃÜ
@Key nvarchar ( 50) -- ÃÜÎÄ
)
returns nvarchar ( 4000) ......