常用Sql語句積累(二)

--根據使用者的手機號碼,尋找是否有授權的車輛.顯示車輛資訊
alter proc up_GetCars
 @vchMobile varchar(20)
 as
 select a.* from 
  carbaseinfo a
  inner join users b
  on a.mobileid = b.mobileid 
  where 
          --b.scanmobileid ='13706686388'
   b.scanmobileid = @vchMobile  --OK
   and exists(
    select 1 
    from users 
    where mobileid in
    
    (select scanmobileid
    from users
    where mobileid = a.mobileid) 
         )

   and exists
    (
    select 1 
    from UsersSq
    where mobile = a.mobileid and sqmobile = b.scanmobileid   
    )
   order by a.createtime 
go  

2.SqlServer的datatime型別用bigint來替換,這樣該日期在oracle中一樣使用.
select mobileid,convert(datetime,firsttime,120) from   Location_Users  where mobileid = '13454000201'
update  Location_Users set firsttime = convert(bigint,getdate(),120) where mobileid = '13454000201'

3.刪除日誌

DUMP TRANSACTION study WITH NO_LOG

壓縮日誌及資料庫檔案大小

/*--特別注意

請按步驟進行,未進行前面的步驟,請不要做後面的步驟
否則可能損壞你的資料庫.

一般不建議做第4,6兩步
第4步不安全,有可能損壞資料庫或丟失資料
第6步如果日誌達到上限,則以後的資料庫處理會失敗,在清理日誌後才能恢復.
--*/

--下面的所有庫名都指你要處理的資料庫的庫名

1.清空日誌
DUMP  TRANSACTION  庫名  WITH  NO_LOG   

2.截斷事務日誌：
BACKUP LOG 庫名 WITH NO_LOG

3.收縮資料庫檔案(如果不壓縮,資料庫的檔案不會減小
企業管理器--右鍵你要壓縮的資料庫--所有任務--收縮資料庫--收縮檔案
--選擇日誌檔案--在收縮方式裡選擇收縮至XXM,這裡會給出一個允許收縮到的最小M數,直接輸入這個數,確定就可以了
--選擇資料檔案--在收縮方式裡選擇收縮至XXM,這裡會給出一個允許收縮到的最小M數,直接輸入這個數,確定就可以了

也可以用SQL語句來完成
--收縮資料庫
DBCC SHRINKDATABASE(庫名)

--收縮指定資料檔案,1是檔案號,可以通過這個語句查詢到:select * from sysfiles
DBCC SHRINKFILE(1)

4.為了最大化的縮小日誌檔案(如果是sql 7.0,這步只能在查詢分析器中進行)
a.分離資料庫:
企業管理器--伺服器--資料庫--右鍵--分離資料庫

b.在我的電腦中刪除LOG檔案

c.附加資料庫:
企業管理器--伺服器--資料庫--右鍵--附加資料庫

此法將生成新的LOG，大小隻有500多K

或用程式碼： 
下面的示例分離 pubs，然後將 pubs 中的一個檔案附加到當前伺服器。

a.分離
EXEC sp_detach_db @dbname = '庫名'

b.刪除日誌檔案

c.再附加
EXEC sp_attach_single_file_db @dbname = '庫名', 
   @physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\庫名.mdf'

5.為了以後能自動收縮,做如下設定:
企業管理器--伺服器--右鍵資料庫--屬性--選項--選擇"自動收縮"

--SQL語句設定方式:
EXEC sp_dboption '庫名', 'autoshrink', 'TRUE'

6.如果想以後不讓它日誌增長得太大
企業管理器--伺服器--右鍵資料庫--屬性--事務日誌
--將檔案增長限制為xM(x是你允許的最大資料檔案大小)

--SQL語句的設定方式:
alter database 庫名 modify file(name=邏輯檔名,maxsize=20)

或
開啟資料庫屬性--選項，將模式改為--簡單
然後右鍵資料庫-所有任務-收縮資料庫。

遊標使用


declare @mobile varchar(32)
declare @service varchar(10)
declare @fee varchar(10)

DECLARE abc CURSOR FOR
select   mobileid,servicetype ,freecode from activeuser where ServiceType = '-xxx' and 
 mobileid not in (select usernumber from f_submit_temp where servicetype = '-xxx')

OPEN abc 
fetch next from abc 
into @mobile,@service,@fee
while (@@FETCH_STATUS = 0)
begin

 exec SP_ADDMobileMonthMuteSubmitMessage 88,@service,'0','099009','99999',@mobile,@mobile,''
 insert into f_submit_temp(usernumber,servicetype) values(@mobile,@service)
 waitfor delay '00:00:00.500'

fetch next from abc
into @mobile,@service,@fee
end
close abc
DEALLOCATE abc

table1構成: id,mobile (id是自動編號)
現在通過儲存過程來向表table1裡插入記錄:
儲存過程裡的輸入引數:mobiles 是多個mobile的集合(之間用逗號分開,如: "5678909876,34567889,12345678")
現在是想把mobiles裡的所有mobile(不在table1裡的)插入到table1裡.

這裡使用迴圈分割字串了....偶都不會....

謝謝了!
declare @str varchar(8000)
set @str = '5678909876,34567889,12345678'

...

while charindex(',',@str)>0
begin
    insert into mobiles select left(@str,charindex(',',@str)-1)
    where not exists(select 1 from mobiles where 列名=left(@str,charindex(',',@str)-1))
    set @str = stuff(@str,1,charindex(',',@str),'')
end

insert into mobiles select @str
where not exists(select 1 from mobiles where 列名=@str)

...
方法二
--建立函式
create function f_split(@str varchar(8000))
returns @tb table(num int)
as
begin
      while charindex(',',@str)>0
      begin
            insert @tb
            select left(@str,charindex(',',@str)-1)

            set @str=stuff(@str,1,charindex(',',@str),'')
      end
      
      insert @tb select @str
      return
end 
go

--測試
declare @s varchar(100)
select @s='1,2,3,4,5,6,7,8'
select * from f_split(@s)

通過表b來更新a表

--UPDATE LocationUser By LocationTypeUser   ;LocationUser   can't as a 
UPDATE LocationUser   
 SET User_AccessMode = b.servicecount
 from 
  
  (SELECT TypeUser_MobileId, COUNT(TypeUser_MobileId) AS 'servicecount' 
   FROM LocationTypeUser 
   WHERE (TypeUser_Status = 0) 
   GROUP BY TypeUser_MobileId
  ) as b 
 where User_Mobileid = b.TypeUser_MobileId

分組的欄位/where 欄位/select 欄位
---'where department' can have the column where is not exists in 'select departmen;but 'select department' can't hvae column where not in 'group departemnt' except statistic column

SELECT TypeUser_MobileId, COUNT(TypeUser_MobileId) AS 'servicecount' 
 FROM LocationTypeUser 
 WHERE (TypeUser_Status = 0) --TypeUser_Status OK
 GROUP BY TypeUser_MobileId

千萬數量級分頁儲存過程 **
http://blog.csdn.net/evafly920/archive/2006/03/03/614813.aspx
CREATE PROCEDURE CN5135_SP_Pagination
/*
***************************************************************
** 千萬數量級分頁儲存過程 **
***************************************************************
引數說明:
1.Tables :表名稱,檢視
2.PrimaryKey :主關鍵字
3.Sort :排序語句，不帶Order By 比如：NewsID Desc,OrderRows Asc
4.CurrentPage :當前頁碼
5.PageSize :分頁尺寸
6.Filter :過濾語句，不帶Where 
7.Group :Group語句,不帶Group By
效果演示：http://www.cn5135.com/_App/Enterprise/QueryResult.aspx
***************************************************************/
(
@Tables varchar(1000),
@PrimaryKey varchar(100),
@Sort varchar(200) = NULL,
@CurrentPage int = 1,
@PageSize int = 10,
@Fields varchar(1000) = '*',
@Filter varchar(1000) = NULL,
@Group varchar(1000) = NULL
)
AS
/*預設排序*/
IF @Sort IS NULL OR @Sort = ''
SET @Sort = @PrimaryKey
DECLARE @SortTable varchar(100)
DECLARE @SortName varchar(100)
DECLARE @strSortColumn varchar(200)
DECLARE @operator char(2)
DECLARE @type varchar(100)
DECLARE @prec int
/*設定排序語句.*/
IF CHARINDEX('DESC',@Sort)>0
BEGIN
SET @strSortColumn = REPLACE(@Sort, 'DESC', '')
SET @operator = '<='
END
ELSE
BEGIN
IF CHARINDEX('ASC', @Sort) = 0
SET @strSortColumn = REPLACE(@Sort, 'ASC', '')
SET @operator = '>='
END
IF CHARINDEX('.', @strSortColumn) > 0
BEGIN
SET @SortTable = SUBSTRING(@strSortColumn, 0, CHARINDEX('.',@strSortColumn))
SET @SortName = SUBSTRING(@strSortColumn, CHARINDEX('.',@strSortColumn) + 1, LEN(@strSortColumn))
END
ELSE
BEGIN
SET @SortTable = @Tables
SET @SortName = @strSortColumn
END
SELECT @type=t.name, @prec=c.prec
FROM sysobjects o 
JOIN syscolumns c on o.id=c.id
JOIN systypes t on c.xusertype=t.xusertype
WHERE o.name = @SortTable AND c.name = @SortName
IF CHARINDEX('char', @type) > 0
SET @type = @type + '(' + CAST(@prec AS varchar) + ')'
DECLARE @strPageSize varchar(50)
DECLARE @strStartRow varchar(50)
DECLARE @strFilter varchar(1000)
DECLARE @strSimpleFilter varchar(1000)
DECLARE @strGroup varchar(1000)
/*預設當前頁*/
IF @CurrentPage < 1
SET @CurrentPage = 1
/*設定分頁引數.*/
SET @strPageSize = CAST(@PageSize AS varchar(50))
SET @strStartRow = CAST(((@CurrentPage - 1)*@PageSize + 1) AS varchar(50))
/*篩選以及分組語句.*/
IF @Filter IS NOT NULL AND @Filter != ''
BEGIN
SET @strFilter = ' WHERE ' + @Filter + ' '
SET @strSimpleFilter = ' AND ' + @Filter + ' '
END
ELSE
BEGIN
SET @strSimpleFilter = ''
SET @strFilter = ''
END
IF @Group IS NOT NULL AND @Group != ''
SET @strGroup = ' GROUP BY ' + @Group + ' '
ELSE
SET @strGroup = ''
/*執行查詢語句*/
EXEC(
'
DECLARE @SortColumn ' + @type + '
SET ROWCOUNT ' + @strStartRow + '
SELECT @SortColumn=' + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
SET ROWCOUNT ' + @strPageSize + '
SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
'
)
GO
獲得自動編號

不好辦,只有用儲存過程來插入新記錄,如:
create procedure eosp_CreatebdAccVsCorp
(
@fDistFeeDeptID Int,
@fCoalCorpID Int
)
as
insert into bdAccVsCorps (FDistFeeDeptID, FCoalCorpID) values (@fDistFeeDeptID, @fCoalCorpID)
return @@identity
GO
或者
strSql="INSERT INTO ...; SELECT @@IDENTITY"

年月統計

select datepart(year,createtime) as 'year', datepart(month,createtime) as 'month',count(mobileid) as '數量'
from Media_answer
where createtime >= '2005-4-29' and accessnumber = '05555001'
group by datepart(year,createtime), datepart(month,createtime)
order by datepart(year,createtime), datepart(month,createtime)

select convert(varchar(7),createtime,120) as 'year-month',count(mobileid) as '數量'
from Media_answer
where createtime >= '2005-4-29' and accessnumber = '05555001'
group by convert(varchar(7),createtime,120)
order by convert(varchar(7),createtime,120)

特殊分組統計,小於某個值的為一組
select a.型別,a.年月,count(1) as '數量'
 from
  (
  select convert(varchar(7),createtime,120) as '年月',src_userid,
    case when count(src_userid)<=10 then '小於10' 
           when count(src_userid)>10 and count(src_userid)<=30 then '小於30' 
    when count(src_userid)>30 and count(src_userid)<=100 then '小於100' 
    when count(src_userid)>100 and count(src_userid)<500 then '小於500' 
    when count(src_userid)>500 and count(src_userid)<=1000 then '小於1000' 
    when count(src_userid)>1000 then '大於1000' 
    end as '型別'
   from his_deliver 
   where dst_userid ='05555' and UPPER(substring(message,1,2)) ='CX'
   AND CREATETIME>'2006-05-08'
   group by convert(varchar(7),createtime,120),src_userid
   --order by convert(varchar(7),createtime,120)
   --having count(src_userid)>10 and count(src_userid)<=30
  )  as a
 group by a.年月,a.型別
 order by a.型別

type 的值為0或1,province 和city 相同的按照type來統計price的和.
結果如下：
Provice city price1(type=0)的和,price2(type=1)的和
－－－－－－－－－－－－－－－－

CREATE TABLE [PriceStatistic] (
 [id] [int] IDENTITY (1, 1) NOT NULL ,
 [Province] [int] NULL ,
 [City] [int] NULL ,
 [Type] [int] NULL ,
 [Price] [int] NULL 
) ON [PRIMARY]
GO

select Province,City,
       Price1=sum(case type when 0 then Price else 0 end),
       Price2=sum(case type when 1 then Price else 0 end)
from PriceStatistic
group by Province,City

分組的問題:

DELETE FROM  TEST
DELeTE FROM Test2
DROP TABLE TEST
DROP TABLE TEST2
CREATE TABLE [dbo].[Test](
 [Id] [int] IDENTITY(1,1) NOT NULL,
 [Type] [int] NULL,
 [Type2] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
 [Name] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[Test2](
 [id] [int] IDENTITY(1,1) NOT NULL,
 [YearId] [int] NULL,
 [Type] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
 [Total] [int] NULL
) ON [PRIMARY]

INSERT Test (Type,Type2 ,Name)
 SELECT '1','A,B','Name2'
 union all
 SELECT '2','C,D,E','Name3'
INSERT Test2 (YearId,Type,Total)
 SELECT 1,'A',10
 union all
 SELECT 1,'B',10
 union all
 SELECT 2,'C',10
 union all
 SELECT 2,'D',30
 union all
 SELECT 2,'F',10
 union all
 SELECT 1,'B',10

按照 Test.Type,Test2.YearId分組,求Total的和.
如果 Type2.Type 是Test.Type裡的一部分，那麼此Test2的記錄就屬於Test.Type裡的一組(以逗號來判斷是否在裡面)

要求結果：
Test.Type  Test2.Year_id,Test2.Total
1                1                30       
2                2                40 
-----------------------------------------------
select * from test
select  * from test2

SELECT A.TYPE,B.YearId,sum(B.total)
 FROM Test AS A
 inner join Test2 AS B ON charIndex(','+B.Type+',',','+A.Type2+',')>0
 GROUP BY A.Type, B.YearId

--自動編號的表,暫時取消自動編號功能,方便插入指定編號的資料.
--允許將顯式值插入表的標識列中。
SET IDENTITY_INSERT sys_parameter_key ON 
insert into Datable1 (parameter_key_id,parameter_key,status) values (100,'HOTEL_PRICE','ENABLE')
insert into DatableChild1 (parameter_text,parameter_value,status,parameter_key_id) values ('HOTEL_PRICE','30','ENABLE',100)
SET IDENTITY_INSERT sys_parameter_key OFF


/*FULL JOIN 實現兩行轉一行.
每個Name對應兩個TYPE的記錄,現在需要一條記錄獲得一個name對應的type及相關資料
*/
--測試資料
CREATE TABLE [dbo].[Test](
 [ID] [int] IDENTITY(1,1) NOT NULL,
 [NAME] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
 [NameDate] [datetime] NULL,
 [TYPESTR] [varchar](10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
insert test
 select 'name1',getdate(),'type1' union all
 select 'name2',getdate(),'type1' union all
 select 'name3',getdate() ,'type1' union all
 select 'name1',getdate(),'type2' union all
 select 'name2',getdate(),'type2' union all
 select 'name3',getdate() ,'type2' union all
 select 'name4',getdate(),'type1' union all
 select 'name5',getdate() ,'type2'

--SQL
select a.name as name1 ,a.typestr as typestr1,a.namedate as namedate1,b.name as name2 ,b.typestr as typestr2,b.namedate as namedate2
from 
 (select * from test  where typestr = 'type1') as a  
 full join (select * from test  where typestr = 'type2') as b on a.name = b.name
--RESULT 
name1 type1 2006-12-28 11:32:06.437 name1 type2 2006-12-28 11:32:06.437
name2 type1 2006-12-28 11:32:06.437 name2 type2 2006-12-28 11:32:06.437
name3 type1 2006-12-28 11:32:06.437 name3 type2 2006-12-28 11:32:06.437
NULL NULL NULL name5 type2 2006-12-28 11:32:06.437
name4 type1 2006-12-28 11:32:06.437 NULL NULL NULL

--捕捉異常
BEGIN TRY
 select 1/0
    EXECUTE usp_ExampleProc
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() as ErrorNumber,
        ERROR_MESSAGE() as ErrorMessage,
  ERROR_LINE() as ERROR_LINE,
  ERROR_SEVERITY() as ERROR_SEVERITY,
  ERROR_STATE() as ERROR_STATE,
  ERROR_PROCEDURE() as ERROR_PROCEDURE;
END CATCH;

--獲得插入記錄的id(自增列)
declare @tbl table
(
  Id int identity(1,1) primary key not null,
    name varchar(10)
)
insert @tbl 
OUTPUT INSERTED.id
select 'name1'

--獲得某個欄位長度最長的記錄及其欄位長度
select city,len(city)
from city1
where not exists(select 1 from city1 as a where len(a.city)>len(city1.city))