程式碼回收桶: SQL

顯示具有 SQL 標籤的文章。顯示所有文章

7月 18, 2015

sql用while迴圈



declare @rt nvarchar(4)
declare @rr nvarchar(4)
declare @uu int
set @uu=0
while @uu < 22 --(select count(單位代碼) from View_LiveUnit where 單位代碼 < 0242)
begin
--print @uu
select top 1 @rr=單位代碼 from View_LiveUnit order by 單位代碼 --where 單位代碼 > @rr
select top 1 @rt=單位代碼 from View_LiveUnit where 單位代碼 > @rr order by 單位代碼
print @rt
set @uu+=1
end

7月 17, 2015

sql 指標的使用方法



declare @p3 nvarchar(4);
DECLARE cs CURSOR FAST_FORWARD for select 單位代碼 from View_LiveUnit where 單位代碼 < 0242;
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
open cs;
FETCH NEXT FROM cs INTO @p3;
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO [HR].[dbo].[HR_UnitDivisionCode]
([單位代碼]
,[科別代碼]
,[生效日期]
,[總分行別]
,[科別名稱]
)
VALUES
(@p3,531,getdate(),0,'作業組'

)
INSERT INTO [HR].[dbo].[HR_UnitDivisionCode]
([單位代碼]
,[科別代碼]
,[生效日期]
,[總分行別]
,[科別名稱]
)
VALUES
(@p3,532,getdate(),0,'業務組'

)
FETCH NEXT FROM cs INTO @p3;
END

CLOSE cs;
DEALLOCATE cs;
END

10月 25, 2014

免用SQL的SqlDataAdapter的新增修改和刪除



        Dim dt As New DataTable
        Using cn As New SqlConnection(conn)
            Dim rr$ = "select * from city where id=@id"

            Dim cmds As New SqlDataAdapter(rr, cn)
            cmds.SelectCommand.Parameters.AddWithValue("id", 8)
            cmds.Fill(dt)
            Dim cb As New SqlCommandBuilder(cmds)
            '新增
            'Dim dr As DataRow = dt.NewRow
            'dr("id") = 9
            'dr("pp") = "hh"
            'dt.Rows.Add(dr)
            'dt.Rows.Add(8, "ert")'偷懶用
            '修改
            'dt.Rows(0)("city") = "666"'偷懶用
            'For Each dr As DataRow In dt.Select("id=" & 8)
            '    dr("kk") = "hello"
            'Next
            '刪除
            'dt.Rows(0).Delete()'偷懶用
            'For Each dr As DataRow In dt.Select("id=" & 8)
            '    dr.Delete()
            'Next
            cmds.Update(dt)
            cmds.Dispose()
        End Using
        GridView1.DataSource = dt
        GridView1.DataBind()

10月 21, 2013

SQL-stored procedure

ALTER PROCEDURE [dbo].[spss] 
    -- Add the parameters for the stored procedure here
    @c2 nvarchar(50),
    @c3 nvarchar(50) output
    --DECLARE @oo nchar(20) = 'Transaction1';
AS
BEGIN TRY
BEGIN TRAN m1
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    -- SET NOCOUNT ON;
    Insert into stu(stu,c1,c2) values(121,'hello',@c2)
    update the set t1='www' where the = 102
    -- Insert statements for procedure here
    set @c3 = @c2
    SELECT * FROM [stu]
    
COMMIT TRAN m1
return 999
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
     ROLLBACK TRAN m1
     -- Return the error information.
  DECLARE @ErrorMessage nvarchar(4000),  @ErrorSeverity int;
  SELECT @ErrorMessage = ERROR_MESSAGE(),@ErrorSeverity = ERROR_SEVERITY();
  RAISERROR(@ErrorMessage, @ErrorSeverity, 1);
END CATCH

2月 26, 2011

新增
不加欄位名稱必須填入所有值可用NULL或default
INSERT [ad].[dbo].[trade] VALUES(3,'susan',default)

正常都加欄位名比較方便
INSERT [ad].[dbo].[trade]
(id,name)
VALUES(4,'eric')

另外是插入別的資料表的值
INSERT [ad].[dbo].[trade]
(id,name)
select id,name
from trade
where id < 3
order by id
不喜歡用...因為欄位順序和類型要一樣

修改
UPDATE [ad].[dbo].[trade]
SET [id] +=1
WHERE name='jack'

修改特定範圍
UPDATE [ad].[dbo].[trade]
SET [id] +=1
from trade c inner join class p on
c.id = p.age
WHERE c.id = 4

刪除
DELETE FROM [ad].[dbo].[trade]
WHERE id NOT IN (select id from trade)
也可以特定範圍或是用子查詢

查詢
select count(*) AS count from trade
where 1=0 false
group by ALL id 全部分組
HAVING count(*) > 5 事後篩選
order by id DESC

select distinct name 移除重複
into #class 插入暫存資料表
from trade
where id = 1

TOP
declare @per float
set @per = 1
select top (@per) PERCENT *
from ad.dbo.trade

資料正規化
SELECT *
from trade
pivot (count(type) for type in ([girl])) as b
把欄位內容轉成欄位名稱 UNPIVOT則相反
unpivot (typevalue for type in ([girl])) as b

找出存在於trade卻沒有再class
select name from trade
EXCEPT
select name from class
欄位數目要相同

select name from trade
INTERSECT
select name from class
同時符合2個資料表

SELECT grouping(name),SUM(id),name
FROM [ad].[dbo].[trade]
group by name
with cube 統計總數

SELECT name,SUM(id)
FROM [ad].[dbo].[trade]
group by name
with rollup 階層查詢依據group by的順序

declare @error int
begin tran 顯性交易開始

if(@error <> 0) goto error

commit tran 釋放資源
error: 副程式寫法
if @error <> 0
begin
rollback transaction
end

轉型

convert(char(4),2005)
cast(name as char(4))

次序函數

row_number() over (order by name)

當科系不同時會重新計數
row_number() over (partition by 科系 order by name)

rank() over (order by name)
欄位內容若相同則排名一樣且會跳過下一號
dense_rank() over (order by name)
這個不會跳過下一號

系統函數
@@rowcount 傳回影響資料筆數

create table #t4 創造一個暫存表
newid() 產生一組識別值

條件處理

case 欄位名稱
when 'boy' then 'roomA'
when 'girl' then 'roomB'
else 'roomC'
end 會議室

if @temp = 1
begin

end
else
begin

end

if not exists
(select * from trade where ccolor = 'pink')
真表示沒有粉紅色

while (select count(*) from trade) > 0
begin

end

waitfor delay '00:00:10'
begin
等10秒後才執行
end

waitfor time '19:05:00'
等到晚上7點5分才執行

指標的用法
declare @fi int
declare sp cursor 宣告
for select id from ad.dbo.trade where id = 3

open sp 開啟

fetch next
from sp
into @fi

print @fi

close sp 關閉
deallocate sp 釋放

@床位1 nvarchar(10),
@學號1 nvarchar(10)

錯誤處理
begin try
end try
begin catch
rollback
end catch

2月 20, 2011

SELECT INTO跟INSERT INTO的差別

SELECT INTO可以into進去暫存的table也可以into實體table，
INSERT INTO只能into實體的table。
實體：SELECT ～ INTO table2 FROM table1
虛擬：SELECT ～ INTO #table2 FROM table1

也就是說，如果使用SELECT INTO
後面的table2如果本來就有，他就直接塞資料；
如果本來就沒有table2，他會新建一個table2，再塞資料。

加了#的意義就是，into進去後，
把Query關掉，重新開啟一個Query，table2就不見了，等於是暫存性的。
當然假如不加#的話，table2就會一直保留著。

另外SELECT INTO如果是實體用法，可以等同於：
INSERT INTO table2 (.....)
SELECT ～ FROM table1

這兩個都可以下WHERE條件。

還有一點，以SELECT 'a' id, 100 cost INTO table3這段而言，
它會自動新建兩個欄位，一個叫id、一個叫cost，
還會自動判別出，id欄位是字串、cost欄位是數值。

附註：以上測試環境是Microsoft SQL Server Service Manager 8.0

1月 29, 2011

SQL 錯誤處理機制

--使用錯誤處理機制
BEGIN TRY
BEGIN TRAN
DECLARE @ApplyNo int

SET @ApplyNo = @@IDENTITY

--INSERT INTO 床位申請明細_團體
INSERT INTO dbo.床位申請明細_團體(申請序號, 床位)
VALUES (@ApplyNo, @床位1);

INSERT INTO dbo.床位申請明細_團體(申請序號, 床位)
VALUES (@ApplyNo, @床位2);

COMMIT TRAN
END TRY
BEGIN CATCH
IF (@@ERROR > 0 AND @@ROWCOUNT <> 1)
ROLLBACK
ELSE
COMMIT
RETURN @@ERROR
END CATCH

5月 11, 2010

SQL 日期函數


SELECT DATEPART(year, GETDATE()) --抓年
SELECT DATEPART(month, GETDATE()) --抓月
SELECT DATEPART(day, GETDATE()) --抓日

DATEDIFF(Day, GETDATE(), 租期止日) AS 相差天數

CONVERT(varchar(12), getdate(), 111) 轉為 yy/mm/dd

CONVERT(varchar(12), getdate(), 105) 轉為 yy-mm-dd

其他:

select CONVERT(varchar, getdate(), 120 )
2004-09-12 11:06:08

select replace(replace(replace(CONVERT(varchar, getdate(), 120 ),'-',''),' ',''),':','')
20040912110608

select CONVERT(varchar(12) , getdate(), 111 )
2004/09/12

select CONVERT(varchar(12) , getdate(), 112 )
20040912

select CONVERT(varchar(12) , getdate(), 102 )
2004.09.12

select CONVERT(varchar(12) , getdate(), 101 )
09/12/2004

select CONVERT(varchar(12) , getdate(), 103 )
12/09/2004

select CONVERT(varchar(12) , getdate(), 104 )
12.09.2004

select CONVERT(varchar(12) , getdate(), 106 )
12 09 2004

select CONVERT(varchar(12) , getdate(), 107 )
09 12, 2004

select CONVERT(varchar(12) , getdate(), 108 )
11:06:08

select CONVERT(varchar(12) , getdate(), 109 )
09 12 2004 1

select CONVERT(varchar(12) , getdate(), 110 )
09-12-2004

select CONVERT(varchar(12) , getdate(), 113 )
12 09 2004 1

select CONVERT(varchar(12) , getdate(), 114 )
11:06:08.177

5月 23, 2009

統計表


SELECT     申請網路, CASE WHEN 申請網路 = '1' THEN '校內學術網路' WHEN 申請網路 = '2' THEN '八人用光纖' ELSE '四人用光纖' END AS 類型名稱, 
                      SUM(tCount) AS 人數
FROM         (SELECT     dbo.床位申請_團體.申請網路, COUNT(*) AS tCount
                       FROM          dbo.床位申請_團體 INNER JOIN
                                              dbo.床位申請明細_團體 ON dbo.床位申請_團體.申請序號 = dbo.床位申請明細_團體.申請序號
                       GROUP BY dbo.床位申請_團體.申請網路
                       UNION ALL
                       SELECT     申請網路, COUNT(*) AS tCount
                       FROM         dbo.床位申請明細_個人
                       GROUP BY 申請網路) AS tmp
GROUP BY 申請網路

3月 11, 2009

sp charindex


declare @tCount as int
 --declare @學號 nvarchar(50)
 --SET @學號 = '55,5'
 
 SELECT  @tCount = Count(*)
 from 
 (
  SELECT [學號] FROM [床位申請明細_個人]
  UNION ALL
  SELECT [學號] FROM [床位申請明細_團體]
 ) AS TMP_TABLE
    --where  [學號]=@學號 
 where CHARINDEX([學號],@學號) > 0
 --開必殺技=無敵
 select @tCount AS tCount 
 
 return @tCount;

程式碼回收桶