作者 主題: sql syntax  (閱讀 11706 次)

0 會員 與 1 訪客 正在閱讀本文。

小徒兒

  • 鑽研的研究生
  • *****
  • 文章數: 622
    • 檢視個人資料
sql syntax
« 於: 2005-08-18 09:00 »
SELECT         債券代號
FROM             BondPLInterest
WHERE         (債券代號 not in
                              (SELECT         債券代碼
                                FROM              BondInfo))

***找出各家公司最近的下單品項,日期最晚的
select distinct item_id, company_id, [Date] from items as tb1
where Date = (select Max([Date]) from items as tb2 where tb1.company_id = tb2.company_id)


***sum 加總的比較 要用having 代替where就行了

select
case when Buy - Sale<=0 then 0 else Buy-Sale end
 from
(select sum(AmtPrice) as Buy from TraderTrans
where ADate = '20051223'
and BS = 0) a,
(select sum(AmtPrice) as Sale from TraderTrans
where ADate = '20051223'
and BS = 1) b


SELECT productid, SUM(quantity) AS total_quantity
FROM [order details]
GROUP BY productid
HAVING SUM(quantity) > 1200



****cross join 將query 結果連成一行


select *  from
 (
SELECT '' as 成本,'' as 成本變動, '' as 市值,'' as 市值變動
 ) d

cross join

  (
SELECT 交易日期,sum((POWER(1 + 票面利率 / 100 / 每年複息次數, 每年複息次數) - 1) * 面額 / 365 * CASE WHEN OPOS='OS' then -1 else 1 end )    AS 單日利息收入
FROM BondPLInterest
where 交易日期=dbo.digiMingodate(getdate(),-2)
group by 交易日期 ) a

cross join
(SELECT sum((POWER(1 + 票面利率 / 100 / 每年複息次數, 每年複息次數) - 1) * 面額 / 365 * CASE WHEN OPOS='OS' then -1 else 1 end )    AS 單日利息收入
FROM BondPLInterest
where 交易日期>=cast(left(dbo.digiMingodate(getdate(),-2),4)+'01'  as int) and 交易日期<=cast(left(dbo.digiMingodate(getdate(),-2),4)+'31' as int)) b


cross join
(SELECT sum((POWER(1 + 票面利率 / 100 / 每年複息次數, 每年複息次數) - 1) * 面額 / 365 * CASE WHEN OPOS='OS' then -1 else 1 end )    AS 單日利息收入
FROM BondPLInterest
where 交易日期>=cast(left(dbo.digiMingodate(getdate(),-2),2)+'0101'  as int) and 交易日期<=cast(left(dbo.digiMingodate(getdate(),-2),2)+'1231' as int)) c

cross join
 (
SELECT 0  as 帳面未實現,0 as 年底未實現
 ) e

cross join
(SELECT sum((POWER(1 + 票面利率 / 100 / 每年複息次數, 每年複息次數) - 1) * 面額 / 365 * CASE WHEN OPOS='OS' then -1 else 1 end )    AS 總損益
FROM BondPLInterest
where 交易日期>=cast(left(dbo.digiMingodate(getdate(),-2),2)+'0101'  as int) and 交易日期<=cast(left(dbo.digiMingodate(getdate(),-2),2)+'1231' as int)
 ) f

cross join
(SELECT sum((POWER(1 + 票面利率 / 100 / 每年複息次數, 每年複息次數) - 1) * 面額 / 365 * CASE WHEN OPOS='OS' then -1 else 1 end )    AS 本年總損益
FROM BondPLInterest
where 交易日期>=cast(left(dbo.digiMingodate(getdate(),-2),2)+'0101'  as int) and 交易日期<=cast(left(dbo.digiMingodate(getdate(),-2),2)+'1231' as int)
 ) g

cross join
(SELECT sum((POWER(1 + 票面利率 / 100 / 每年複息次數, 每年複息次數) - 1) * 面額 / 365 * CASE WHEN OPOS='OS' then -1 else 1 end )    AS 本月總損益
FROM BondPLInterest
where 交易日期>=cast(left(dbo.digiMingodate(getdate(),-2),4)+'01'  as int) and 交易日期<=cast(left(dbo.digiMingodate(getdate(),-2),4)+'31' as int)) h

cross join
  (
SELECT sum((POWER(1 + 票面利率 / 100 / 每年複息次數, 每年複息次數) - 1) * 面額 / 365 * CASE WHEN OPOS='OS' then -1 else 1 end )    AS 單日利息收入
FROM BondPLInterest
where 交易日期=dbo.digiMingodate(getdate(),-2)
group by 交易日期 ) i






***正負號 相加

sum((POWER(1 + 票面利率 / 100 / 每年複息次數, 每年複息次數) - 1) * 面額 / 365 * CASE WHEN OPOS="OP" then 1 else -1 end )    AS 單日利息收入



***
SELECT         每年複息次數, 債券代號, (POWER(1 + 票面利率 / 100 / 每年複息次數, 每年複息次數) - 1) * 面額 / 365 AS Expr1, 票面利率, 利率型態
FROM             BondPLInterest



***
SELECT         TraderStock.ADate, TraderStock.StockId AS Expr3, TraderStock.Price AS Expr4,
                          TraderStock.AvgCost, TraderStock.Qty AS Expr5, StkProfit.TradeProfit,
                          TraderStock.Price * TraderStock.Qty - TraderStock.Amt AS Expr1
FROM             TraderStock INNER JOIN
                          StkProfit ON TraderStock.ADate = StkProfit.ADate AND
                          TraderStock.TraderId = StkProfit.TraderId AND
                          TraderStock.StockId = StkProfit.StockId
WHERE         (TraderStock.ADate = dbo.fn_digidate(GETDATE(), 0)) AND
                          (TraderStock.TraderId = '02')




--dbschema

sELECT         COLUMN_NAME,
 case
when Data_type='decimal' then  DATA_TYPE   + '(' + cast(numeric_precision as varchar)+ ',' + cast(numeric_scale as varchar) +')'
 when Data_type='numeric' then  DATA_TYPE   + '(' + cast(numeric_precision as varchar)+ ',' + cast(numeric_scale as varchar) +')'  when DATA_TYPE !='varchar'  then DATA_TyPE  else DATA_TYPE   + '(' + cast(CHARACTER_MAXIMUM_LENGTH as varchar)+')' end, *
FROM             VAR .INFORMATION_SCHEMA.COLUMNS
WHERE         TABLE_NAME = N'RiskBackTesttingReport'


SELECT * FROM  sysproperties  WHERE  id = (select id from sysobjects where name='BondYieldHistory' )






--index
IF not EXISTS (SELECT name FROM sysindexes
         WHERE name = 'TraderGroupindex')
CREATE
  INDEX [TraderGroupindex] ON [dbo].[TraderGroup] ([GroupId], [GroupName])



-- 印出某個規則、預設值或未加密預存程序、使用者定義函數、觸發程序、或檢視的文字
EXEC sp_helptext 'employee_insupd'


-- 顯示資料庫物件相依性的相關資訊
EXEC sp_depends OrdersNotShipped

-- 列出資料庫中的所有 Stored Procedure
EXEC sp_stored_procedures

-- 執行 Stored Procedure
EXEC OrdersNotShipped

-- 刪除 Stored Procedure
DROP PROCEDURE OrdersNotShipped


-- 刪除 NestDemo
IF EXISTS(SELECT * FROM sysobjects WHERE xtype='P' AND name='NestDemo')
   DROP PROCEDURE NestDemo
GO

***execute 可以執行純量值、使用者自訂函數、系統程序、使用者自訂的預存程序或延伸預存程序
代碼: [選擇]

execute sp_executesql
          N'SELECT * FROM Northwind.dbo.Customers WHERE Country=@country AND City=@city',
          N'@country varchar(50), @city varchar(50)',
          @country = 'France',
 @city = 'Paris'






代碼: [選擇]

CREATE  function fn_digidate(@input datetime,@offset int)
RETURNS varchar(8)
BEGIN
declare @output as varchar(8)
select  @output=( datepart(yy,@input))*10000+(datepart(mm,@input))*100+(datepart(dd,@input))
select @output=dbo.fn_getworkday(@output,@offset)
return @output
end



***將執行 stored procedure 的結果 塞到table裡
INSERT INTO CustomerOrdersSummary
   EXEC OrderSummary


***
SELECT DATEPART(m, 0), DATEPART(d, 0), DATEPART(yy, 0)

***
SELECT DATENAME(month, getdate()) AS 'Month Name'

以下為結果集:

Month Name                    
------------------------------
February                      



比對是否有漏資料
代碼: [選擇]

SELECT date FROM [VaR].[dbo].[fn_getWorkDaysListbetween2]('20050630','20050728')
where date not in
 
(SELECT   BaseDate
                           FROM              BondYieldHistory
                           WHERE          (BaseDate LIKE '%200507%')
                           GROUP BY   BaseDate)




function 結果後 join
代碼: [選擇]

SELECT         *
FROM             (SELECT         BaseDate AS date
                           FROM              BondYieldHistory
                           WHERE          (BaseDate LIKE '%200507%')
                           GROUP BY   BaseDate) a INNER JOIN
                              (SELECT         date
                                FROM              [VaR].[dbo].[fn_getWorkDaysListbetween2]('20050630',
                                                           '20050730') fn_getWorkDaysListbetween2) b ON
                          a.[date] = b.[date]



代碼: [選擇]

create TRIGGER TestTable_InsteadOfinsert ON TestTable_View
INSTEAD OF INSERT
AS
DECLARE @city varchar(50)
SET @city = (SELECT City FROM inserted)
IF @city = '台中市'
BEGIN
--raiserror('test',16,-1)
--insert into TestTable (inserted.[Name], ins.Sex) from inserted
INSERT INTO testTable
SELECT Name,Sex FROM inserted
END
ELSE

BEGIN

raiserror('tet',16,-1)
INSERT INTO testTable2
SELECT Name,Sex FROM inserted


-- insert into TestTable2  values(ins.[Name], ins.Sex)from inserted

END
GO








EXEC sp_helptext testtriggertable
EXEC sp_helptrigger testtriggertable

***限制特定欄位不能修改
USE Northwind

GO

CREATE TRIGGER Employee_Update

       ON Employees

       FOR UPDATE

AS

IF UPDATE (EmployeeID)

BEGIN

       RAISERROR ('Transaction cannot be processed.\

       ***** Employee ID number cannot be modified.', 10, 1)

       ROLLBACK TRANSACTION

END






USE pubs
IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'reminder' AND type = 'TR')
   DROP TRIGGER reminder
GO
CREATE TRIGGER reminder
ON titles
FOR INSERT, UPDATE
AS RAISERROR (50009, 16, 10)





***enable trigger
ALTER TABLE trig_example ENABLE TRIGGER trig1

***disable trigger
ALTER TABLE trig_example DISABLE TRIGGER trig1

代碼: [選擇]


CREATE TRIGGER tNewOrderInsert
ON 訂單資料
WITH ENCRYPTION
FOR INSERT
AS
IF (SELECT 訂購數量 FROM inserted ) > 100
EXEC xp_sendmail 'jhhsu','有人下了一筆大訂單'






USE pubs
IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'reminder' AND type = 'TR')
   DROP TRIGGER reminder
GO
CREATE TRIGGER reminder
ON titles
FOR INSERT, UPDATE, DELETE
AS
   EXEC master..xp_sendmail 'MaryM',
      'Don''t forget to print a report for the distributors.'
GO




***inline table function

USE pubs
GO
CREATE FUNCTION SalesByStore (@storeid varchar(30))
RETURNS TABLE
AS
RETURN (SELECT title, qty
      FROM sales s, titles t
      WHERE s.stor_id = @storeid and
      t.title_id = s.title_id)




代碼: [選擇]

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

ALTER  PROCEDURE getTop  AS

begin

delete from vote3
declare @columnname as varchar(40),@sqlstring as varchar(100)
--exec getTop

DECLARE abc CURSOR FOR
SELECT COLUMN_NAME
FROM vote.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'vote'
OPEN abc
FETCH NEXT FROM abc into @columnname
WHILE (@@FETCH_STATUS = 0 )
BEGIN


set @sqlstring= 'insert vote3 SELECT ''' + @columnname+''', [' + @columnname+ ']  FROM [vote].[dbo].[vote]'
execute(@sqlstring)

FETCH NEXT FROM abc into @columnname
END

CLOSE abc

DEALLOCATE abc

select * from vote3

end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO






要索引: where, order by,group by, having, compute(by),join on, a=3, a>9, group by, sorted order
不要索引:城市,性別,text,ntext, image

***keep the data pre-sorted

***Use =,>,M,>=,<=,Between
***Avoid <>,!=, !>,!<,NOT EXISTS, NOT IN
***Avoid using leadind wildcards %

***查詢 顯示評估的執行計畫



***disable all
ALTER TABLE Employees2
NOCHECK CONSTRAINT ALL


***check the constraint is disable or not, 1=disabled
SELECT OBJECTPROPERTY(OBJECT_ID('CK_Orders_City'), 'cnstIsDisabled')

***re-enable the constraint
ALTER TABLE Employees2
CHECK CONSTRAINT PK_doc_exe_column_b


***disable constraint can not use on the primary key
***so please drop it then add it

ALTER TABLE Employees2
drop CONSTRAINT PK_doc_exe_column_b


***drop index can not delete the index with primary key constraint
drop Index Employees2.PK_doc_exe_column_b

***disable constraint
ALTER TABLE Employees2
NOCHECK CONSTRAINT CK_Orders_City


insert Employees2
select * from Employees2

exec sp_helpconstraint  Employees2
exec sp_helpindex  Employees

***刪除constraint
ALTER TABLE Scores
DROP CONSTRAINT FK_Scores_Students

***唯一
ALTER TABLE doc_exc ADD column_b VARCHAR(20) NULL
   CONSTRAINT exb_unique UNIQUE NONCLUSTERED (column_b)


***primary key
ALTER TABLE doc_exe ADD
CONSTRAINT PK_doc_exe_column_b PRIMARY KEY CLUSTERED(column_b)



***
ALTER TABLE Employees2
WITH NOCHECK   -- 加這個可以不要檢查現有的資料
ADD CONSTRAINT CK_Orders_City CHECK (City ='NewYork')


***
ALTER TABLE doc_exd WITH NOCHECK
ADD CONSTRAINT exd_check CHECK (column_a > 1),

--預設值
column_e DECIMAL(3,3)
CONSTRAINT column_e_default
DEFAULT .081,

--電話號碼
column_d VARCHAR(16) NULL
CONSTRAINT column_d_chk
CHECK
(column_d IS NULL OR
column_d LIKE "[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]" OR
column_d LIKE
"([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]"),





use Northwind
CREATE TABLE Orders
(
   OrderID      int   IDENTITY(1,1)    NOT NULL,
   CustomerName   varchar(30)      NOT NULL   DEFAULT 'None',
   ProductName   varchar(30)      NOT NULL   CONSTRAINT DF_Orders_ProductName DEFAULT('Something'),
   Quantity   int         NOT NULL   DEFAULT 0,
   UnitPrice   int         NOT NULL   CONSTRAINT DF_Orders_UnitPrice DEFAULT(0)
)


--此範例顯示了 authors 資料表的所有條件約束。

USE pubs
EXEC sp_helpconstraint authors

-- 刪除 CONSTRAINT
ALTER TABLE Employees
DROP CONSTRAINT PK_Employees

***constraint info
SELECT *
FROM Northwind.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'Customers'

SELECT * FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS
SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS






***
use Northwind
if exists (select * from dbo.sysobjects where name ='Customersbcp')
drop table Customersbcp

go
select *  into Customersbcp from Customers



***change dbowner
use VaR
EXEC sp_changedbowner 'sa'

***guid

use Sample
CREATE TABLE Members
(
   MemberID      uniqueidentifier   NOT NULL   DEFAULT   NEWID(),
   MemberName   varchar(30)      NOT NULL,
)

-- 新增一筆資料,看看 uniqueidentifier 欄位由 NEWID() 函數所產生的值
INSERT INTO Members(MemberName) VALUES('王小明')
SELECT * FROM Members

-- 刪除表格
DROP TABLE Members

***IDENTITY

USE Sample
SELECT * FROM INFORMATION_SCHEMA.TABLES

--IDENT_SEED初始
--IDENT_INCR增量
--IDENT_CURRENT現今的值
SELECT TABLE_NAME, IDENT_SEED(TABLE_NAME) AS IDENT_SEED,
         IDENT_INCR(TABLE_NAME) AS IDENT_INCR,
         IDENT_CURRENT(TABLE_NAME) AS IDENT_CURRENT
FROM INFORMATION_SCHEMA.TABLES
WHERE IDENT_SEED(TABLE_NAME) IS NOT NULL

-- 設定 IDENTITY_INSERT = ON ,讓我們可以直接指定 IDENTITY 欄位的值
--暫時停用自動參數
SET IDENTITY_INSERT Orders ON

INSERT INTO Orders(OrderID, CustomerName, ProductName, UnitPrice, Quantity)
VALUES(100, '王小明', '筆記型電腦', 50000, 3)



***新增欄位,加入預設值
alter table Orders
ADD OrderDAte datetime DEFAULT getdate()
GO

ALTER table Orders
ALTER  Column ShipAddress varchar(500) not null
go




create Table dbo.Categories
(CategoryID int IDENTITY(1,1) not NULL,
--COLLATE
CategoryName nvarchar(15) not null,
Description ntext,
Picture Image)

create table orders
(OrderID int IDENTITY(1,1) NOT NULL,
UnitPrice int,
Quantity int,
Total as UnitPrice * Quantity
)




GRANT SELECT ON Customers TO guest
REVOKE SELECT ON Customers TO guest
DENY SELECT ON Customers TO guest
REVOKE SELECT ON Customers TO guest


SELECT DB_NAME() AS 資料庫名稱
SELECT USER_ID() AS 使用者名稱
EXEC sp_helpserver

***
DECLARE @dbname varchar(30), @tblname varchar(30), @sql varchar(200)
SET @dbname = 'Northwind'
SET @tblname = 'Customers'
SET @sql = 'SET NOCOUNT ON;use ' + @dbname + ';SELECT * FROM ' + @tblname   -- 不傳回筆數統計
print @sql
EXECUTE(@sql)


***xml

select 1 AS Tag, NULL as Parent, CustomerID as [Customers!1!ID], CompanyName as [Customers!1!Name], null as [Orders!2!OrderID],
null as [Products!3!ProductID], null as [Products!3!ProductName]
from Customers
where CustomerID in ('BERGS','BOLID')

union

select 2, 1, Orders.CustomerID, null, Orders.OrderID, null, null
from Orders
inner join Customers on Customers.CustomerID = Orders.CustomerID
where Orders.CustomerID in ('BERGS','BOLID')

union

select 3, 2, Customers.CustomerID, null, od.OrderID, Products.ProductID, Products.ProductName
from Products
inner join [Order Details] od on od.ProductId = Products.ProductID
inner join Orders on Orders.OrderId = od.OrderId
inner join Customers on Customers.CustomerID = Orders.CustomerID
where Customers.CustomerID in ('BERGS','BOLID')

order by [Customers!1!ID] DESC, [Orders!2!OrderID], [Products!3!ProductID]

for xml explicit


***

ALTER DATABASE Northwind SET READ_WRITE
EXEC sp_dboption Northwind

ALTER DATABASE Northwind SET AUTO_SHRINK ON
EXEC sp_dboption Northwind

ALTER DATABASE Northwind SET AUTO_SHRINK OFF
EXEC sp_dboption Northwind


***log 大小
DBCC SQLPERF(LOGSPACE)

use Sample
DBCC SHRINKFILE(File1, 10)
DBCC SHRINKFILE(File1, 10, NOTRUNCATE)
DBCC SHRINKFILE(File1, 10, TRUNCATEONLY)   
DBCC SHRINKFILE(File1, EMPTYFILE)      


***
use master
DROP DATABASE Sample
GO






***
datatype:
    *Integer:
    *Exact numeric
    *Approximate numeric: float
    *Monetary

Binary:
     *varbin
     *Image

Character:
      *Text


自訂型態:
n: unicode
var: variante

Exec sp_addtype city, 'nvarchar(15)',null
Exec sp_addtype region, 'nvarchar(15)',null


exec sp_droptype region










***移除
alter database Sample
remove file File2
go
alter database Sample
remove file File3

go
alter database Sample
remove filegroup group1


***加入第三個
ALTER DATABASE Sample
   add file (Name='File3',FileName='C:\Sample3.ndf', Size=6mb,MAXsize=10MB,filegrowth=10%)
to filegroup Group1


***加入第二個
ALTER DATABASE Sample
   add file (Name='File2',FileName='C:\Sample2.ndf', Size=6mb,MAXsize=10MB,filegrowth=10%)

***檢視
exec sp_helpdb Sample
exec sp_helpfile 'File2'

***

***改變大小
ALTER DATABASE Sample
   Modify file (Name='SampleLog', Size=6mb)


***filegroup

use Sample
exec sp_helpfilegroup

alter database Sample
ADD filegroup OrderHistoryGroup

alter database Sample
remove filegroup OrderHistoryGroup


use Sample
exec sp_helpfilegroup

-----------------------------------

exec sp_spaceused customers

SELECT DATABASEPROPERTYEX('Northwind','Status')
SELECT DATABASEPROPERTYEX('Northwind', 'Collation')

以下為結果集:

------------------------------
SQL_Latin1_General_CP1_CS_AS




這一範例可讓 pubs 資料庫成為唯讀。

USE master
EXEC sp_dboption 'pubs', 'read only', 'TRUE'


exec sp_dboption
exec sp_dboption Northwind
exec sp_helpdb Northwind
alter database Sample SET READ_ONLY
alter database Sample SET READ_WRITE
exec sp_dboption Sample 'read only','on'



***查詢資料庫是否存在,建立資料庫
if exists(SELECT         *
FROM             sysdatabases where name='Sample')

Drop DataBase Sample

GO

use master
create Database Sample
on PRIMARY( Name=File1,FileName='C:\Sample.mdf',
     size=10MB,MaxSize=15MB,
   FileGrowth=20%)

LOG ON(Name=SampleLog,FileName='C:\Sample.ldf',
Size=3MB,MaxSize=5MB,FileGrowth=1MB)


***



VaR=Zalpha*W*(w'Σw)^(1/2)

代碼: [選擇]

IF EXISTS (SELECT         *
                        FROM             sprcd
                        WHERE         date1 = '20060420')
    SELECT         1 ELSE
                                    SELECT         0


SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO





alter        procedure getReturnLogPerTrader(
  @dt varchar(8))
AS
BEGIN

--exec getReturnLogPerTrader '20060320'
declare @TraderID as varchar(10),@offset as integer
set @offset =1

SELECT         SPRCDALL.ID as Stockid  into #a1
FROM             SPRCDALL INNER JOIN
                          TraderStock ON SPRCDALL.ID = TraderStock.StockId
WHERE         (TraderStock.ADate = @dt)
GROUP BY  SPRCDALL.ID, TraderStock.ADate, TraderStock.StockId
ORDER BY  COUNT(*) DESC, TraderStock.StockId


   DECLARE abc CURSOR FOR
   select TraderId from trader order by GroupId desc
   OPEN abc

   FETCH NEXT FROM abc into @TraderID


   WHILE (@@FETCH_STATUS = 0 )
   BEGIN

   SELECT       Price * Qty AS [position], StockId, TraderId
   into #a3
   FROM             TraderStock
   WHERE         (ADate = @dt) AND (TraderId = @TraderID)

   /**
   *****************************************
   將資料達成stockid 及部位兩個 欄位,並將其塞在#rightpane裡面

   ******************************************
   **/
   
   if @offset =1
   begin
   /**
   *****************************************
   初始化JOINME,左邊的TABLE
   ******************************************
   **/
      select #a1.StockId,#a3.position into #joinme from #a1 left join #a3 on  #a1.StockId = #a3.StockId

   /**
   *****************************************
   清掉#a3,以供下次使用
   ******************************************
   **/
      drop table #a3
   end
   ELSE
   begin
   /**
   *****************************************
   產生RIGHTPANE,右邊的TABLE
   ******************************************
   **/
      select  #a1.StockId,#a3.position  into #rightpane from #a1     left join #a3 on  #a1.StockId = #a3.StockId

   /**
   *****************************************
   將左右合併結果存至暫存容器
   ******************************************
   **/

      select     #joinme.*,#rightpane.*   into #usemethendrop  from  #joinme  left join #rightpane  on #joinme.StockId=#rightpane.StockId

   /**
   *****************************************
   JOINME清空,將暫存容器重新定義為左邊JOINME TABLE
   ******************************************
   **/
   
      drop table #joinme
      select  *  into #joinme from #usemethendrop

   /**
   *****************************************
   清掉#usemethendrop,以供下次使用
   ******************************************
   **/
      drop table #usemethendrop

   end

   FETCH NEXT FROM abc into @TraderID

   set @offset =@offset +1   
   END
End


select * from #joinme


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

[/code]

MCDBA class notes

代碼: [選擇]



CREATE  FUNCTION fn_stock
(
  @dt varchar(8)
)
RETURNS @stock table(adate_ varchar(8), stockid_ varchar(10), stockname_ varchar(20), market_ varchar(1), closeprice_ numeric(8, 2), capital_ numeric(12, 8))
AS
BEGIN

  INSERT @stock
    SELECT stk.ADate, stk.StockId, StockName, CASE WHEN LEN(stk.StockId) = 5 THEN 'U' ELSE Market END, ClosePrice, Capital
      FROM vw_stk stk
        JOIN (
              SELECT MAX(ADate) ADate, StockId
                FROM vw_stk
               WHERE (ADate <= @dt) OR (@dt = '')
               GROUP BY StockId
             ) tmp
          ON stk.ADate = tmp.ADate AND stk.StockId = tmp.StockId

  RETURN
END





代碼: [選擇]



SELECT         TraderStock.StockId, TraderStock.StockName, TraderStock.Amt AS 部位總成本,
                          TraderStock.Price * TraderStock.Qty AS 部位總市值,
                          TraderStock.Amt - TraderStock.Price * TraderStock.Qty AS 未實?#123;損益,
                          (TraderStock.Amt - TraderStock.Price * TraderStock.Qty)
                          / TraderStock.Amt AS 報酬率, STCRI.TCRI
FROM             TraderStock INNER JOIN
                          STCRI ON TraderStock.StockId = STCRI.INDUSTRY
WHERE         (TraderStock.ADate = '20060320') AND (TraderStock.StockId = '1565') AND
                                             
(  ((STCRI.YYMM + STCRI.INDUSTRY) IN
                                                    (SELECT         MAX(YYMM) + INDUSTRY
                                                      FROM              STCRI  where TCRI!=''
                                                      GROUP BY   INDUSTRY))

)


代碼: [選擇]


SELECT         YYMM, INDUSTRY, TCRI
FROM             STCRI
WHERE         ((YYMM + INDUSTRY) IN
                              (SELECT         MAX(YYMM) + INDUSTRY
                                FROM              STCRI
                                GROUP BY   INDUSTRY))
ORDER BY  INDUSTRY



取出所有的field
代碼: [選擇]

create    PROCEDURE GetTableFieldString
(
 @TableName varchar(30)
)
 AS

BEGIN
--exec GetTableFieldString PerStockRisk
declare @fieldname as varchar(50)
declare @string as varchar(4000)

set @string =''
DECLARE abc CURSOR FOR

select c.name as fieldname  from syscolumns c left join systypes t on c.xtype=t.xtype  where id in(select id from sysobjects where name=@TableName) order by colorder

OPEN abc

WHILE (@@FETCH_STATUS = 0 )
BEGIN
FETCH NEXT FROM abc into @fieldname

set @string=@string+@fieldname+','

END

CLOSE abc

DEALLOCATE abc

set @string=Ltrim(Rtrim(@string))

select Len(@string)
if Len(@string)>0
BEGIN
set @string=Left(@string,Len(@string)-1)
END

select @string
print @string

end


代碼: [選擇]


CREATE FUNCTION fn_NewRegion ( @myinput nvarchar(30) )
RETURNS nvarchar(30)
BEGIN
IF @myinput IS NULL
SET @myinput = ’Not Applicable‘
RETURN @myinput
END



代碼: [選擇]

SELECT plant_id, common_name, price
FROM plants
WHERE CONTAINS( *, '"English Thyme"' )



代碼: [選擇]


SELECT Description
FROM Categories
WHERE CategoryName <> 'Seafood'
AND CONTAINS(Description, 'sauces AND seasonings')


代碼: [選擇]

UPDATE products
SET unitprice = (unitprice * 1.1)



代碼: [選擇]

INSERT customers
SELECT substring(firstname, 1, 3)
+ substring (lastname, 1, 2)
,lastname, firstname, title, address, city
,region, postalcode, country, homephone, NULL
FROM employees



代碼: [選擇]

INSERT customers
(customerid, companyname, contactname, contacttitle
,address, city, region, postalcode, country, phone
,fax)
VALUES ('PECOF', 'Pecos Coffee Company', 'Michael Dunn'
,'Owner', '1900 Oak Street', 'Vancouver', 'BC'
,'V3F 2K1', 'Canada', '(604) 555-3392'
,'(604) 555-7293')


代碼: [選擇]

SELECT productid, orderid
,quantity
FROM orderhist
ORDER BY productid, orderid
COMPUTE SUM(quantity)




代碼: [選擇]

SELECT orderid, quantity
 FROM [order details]
 WHERE orderid in ( 11075, 11076 )
 ORDER BY orderid
 COMPUTE SUM(quantity) BY orderid
 COMPUTE SUM(quantity)
 COMPUTE AVG(quantity)


代碼: [選擇]


SELECT co.isbn, co.copy_no, co.on_loan
      ,ti.title, it.translation, it.cover
 FROM copy co
 INNER JOIN title AS ti
  ON co.title_no = ti.title_no
 INNER JOIN item AS it
  ON co.isbn = it.isbn
 WHERE co.isbn IN ( 1, 500, 1000)
 ORDER BY co.isbn


代碼: [選擇]

SELECT d.adult_member_no, a.expr_date, d.No_Of_Children
 FROM adult AS a
 INNER JOIN (         SELECT adult_member_no, COUNT(*) AS No_Of_Children
          FROM juvenile
          GROUP BY adult_member_no
          HAVING COUNT(*)>3
         ) AS d   ON a.member_no = d.adult_member_no


代碼: [選擇]

SELECT DISTINCT firstname, lastname, isbn, fine_paid FROM member AS m
 INNER JOIN loanhist AS lh  ON m.member_no = lh.member_no
 WHERE lh.fine_paid = (SELECT MAX(fine_paid) FROM loanhist)


代碼: [選擇]

SELECT t.title_no, title, l.isbn      ,count(*) AS 'Total Reserved'
 FROM title AS t
 INNER JOIN loan AS l  ON t.title_no = l.title_no
 INNER JOIN reservation AS r
  ON r.isbn = l.isbn WHERE r.isbn IN
      ( SELECT isbn
         FROM reservation
         GROUP BY isbn
         HAVING COUNT(*)>50 )  AND  l.copy_no < 5
 GROUP BY t.title_no, title, l.isbn



代碼: [選擇]


SELECT member_no, lastname
 FROM member AS m





代碼: [選擇]

SELECT companyname
FROM customers
WHERE companyname LIKE '%Restaurant%'


LIKE '_en'
LIKE '[CK]%'  --c or k
LIKE '[S-V]ing'  --from s to v
LIKE 'M[^c]%'  --from M then not c

代碼: [選擇]

SELECT companyname, country
FROM suppliers
WHERE country IN ('Japan', 'Italy')



代碼: [選擇]

SELECT companyname, fax
FROM suppliers
WHERE fax IS NULL



代碼: [選擇]

SELECT member_no, fine_assessed
 FROM loanhist
 WHERE (fine_assessed BETWEEN $8.00 AND $9.00)



代碼: [選擇]

SELECT  LOWER(firstname + middleinitial
              +SUBSTRING(lastname, 1, 2) ) AS email_name
 FROM member
 WHERE lastname = 'anderson'


代碼: [選擇]

SELECT 'The title is: ' + title + ', title number ' +
        CONVERT(char(6),title_no)
FROM title




grouping 顯示 1為group,顯示0為非 group
代碼: [選擇]


SELECT productid
      ,GROUPING(productid)
      ,orderid
      ,GROUPING(orderid)
      ,SUM(quantity) AS total_quantity
 FROM [order details]
 WHERE productid = 50
 GROUP BY productid, orderid
  WITH CUBE
 ORDER BY productid, orderid


代碼: [選擇]

SELECT orderid, quantity
 FROM [order details]
 WHERE orderid in ( 11075, 11076 )
 ORDER BY orderid
 COMPUTE SUM(quantity) BY orderid




SELECT LastName, Title, HireDate, Notes
 FROM employees
 WHERE FREETEXT (notes, 'cold and toast')


SELECT LastName, Title, HireDate, Notes
 FROM employees
 WHERE CONTAINS(notes, '"St. Andrews" NEAR() "Scotland"')  


代碼: [選擇]

SELECT lastname, employeeid
FROM employees AS e
WHERE EXISTS ( SELECT * FROM orders AS o
WHERE e.employeeid = o.employeeid
AND o.orderdate = ’9/5/1997’ )



***=inner join
代碼: [選擇]

SELECT DISTINCT t1.type
FROM titles AS t1
WHERE t1.type IN
(SELECT t2.type
FROM titles AS t2
WHERE t1.pub_id <> t2.pub_id)


全文檢索
代碼: [選擇]

SELECT
DATABASEPROPERTY('Northwind','IsFullTextEnabled')

USE northwind
SELECT fulltextserviceproperty('IsFullTextInstalled')
GO

--?#93;定為全文檢索
exec sp_fulltext_database 'enable'

exec sp_help_fulltext_catalogs

--建立catelogue
exec sp_fulltext_catalog 'Cat_Customers', 'create'

exec sp_fulltext_table 'Customers','create','Cat_Customers','PK_Customers'

exec sp_helpindex 'Customers'

exec sp_help_fulltext_tables

exec sp_fulltext_column 'Customers','CompanyName','add'
exec sp_help_fulltext_columns

exec sp_fulltext_table 'Customers','activate'
exec sp_fulltext_catalog 'Cat_Customers','start_full'


找出索引健constraint 名稱
代碼: [選擇]

exec sp_helpindex 'Customers'


代碼: [選擇]

SELECT T.orderid, T.customerid
FROM ( SELECT orderid, customerid
FROM orders ) AS T


insert from select
代碼: [選擇]

INSERT customers
SELECT substring (firstname, 1, 3)
+ substring (lastname, 1, 2)
,lastname, firstname, title, address, city
,region, postalcode, country, homephone, NULL
FROM employees


代碼: [選擇]

DELETE FROM [order details]
WHERE orderid IN (
SELECT orderid
FROM orders
WHERE orderdate = ’4/14/1998’
)



代碼: [選擇]

UPDATE savings
SET balance = balance - 100
WHERE custid = 78910



代碼: [選擇]
BEGIN TRANSACTION
UPDATE savings
SET balance = balance - 100
WHERE custid = 78910
IF @@ERROR <> 0
BEGIN
RAISERROR (’Error, transaction not completed!’, 16, -1)
ROLLBACK TRANSACTION
END
UPDATE checking
SET balance = balance + 100
WHERE custid = 78910
IF @@ERROR <> 0
BEGIN
RAISERROR (’Error, transaction not completed!’, 16, -1)
ROLLBACK TRANSACTION
END
COMMIT TRANSACTION
Example





代碼: [選擇]

DELETE FROM [order details]
FROM orders AS o
INNER JOIN [order details] AS od
ON o.orderid = od.orderid
WHERE orderdate = ’4/14/1998’


***temp table

代碼: [選擇]

select FirstName,LastName into #temp2 from Employees

use northwind

insert #temp2
select CustomerID,Country From customers
where country is not null


代碼: [選擇]

USE master
GO
EXEC sp_attach_db @dbname = N'SBS_OLTP',
   @filename1 = N'C:\Microsoft Press\SQL DTS SBS\Databases\SBS_OLTP.mdf',
   @filename2 = N'C:\Microsoft Press\SQL DTS SBS\Databases\SBS_OLTP.ldf'



find out constraint and drop contstraint

代碼: [選擇]

--Creates relational tables in the SBS_OLAP database to support the Analysis Services Sales cube
USE SBS_OLAP
GO
if exists (select * from dbo.sysobjects where id = object_id(N'dbo.FK_SalesCustomer') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE dbo.SalesFact DROP CONSTRAINT FK_SalesCustomer
GO
if exists (select * from dbo.sysobjects where id = object_id(N'dbo.FK_SalesProduct') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE dbo.SalesFact DROP CONSTRAINT FK_SalesProduct
GO
if exists (select * from dbo.sysobjects where id = object_id(N'dbo.FK_SalesTime') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE dbo.SalesFact DROP CONSTRAINT FK_SalesTime
GO
if exists (select * from dbo.sysobjects where id = object_id(N'dbo.TimeDim') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table dbo.TimeDim
GO
if exists (select * from dbo.sysobjects where id = object_id(N'dbo.CustomerDim') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table dbo.CustomerDim
GO
if exists (select * from dbo.sysobjects where id = object_id(N'dbo.ProductDim') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table dbo.ProductDim
GO
if exists (select * from dbo.sysobjects where id = object_id(N'dbo.SalesFact') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table dbo.SalesFact
GO
if exists (select * from dbo.sysobjects where id = object_id(N'dbo.CustomerStage') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table dbo.CustomerStage
GO
if exists (select * from dbo.sysobjects where id = object_id(N'dbo.ProductStage') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table dbo.ProductStage
GO
if exists (select * from dbo.sysobjects where id = object_id(N'dbo.SalesStage') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table dbo.SalesStage
GO
CREATE TABLE dbo.TimeDim
(
TimeKey int IDENTITY (1, 1) NOT NULL PRIMARY KEY CLUSTERED
,FullDateCode datetime NOT NULL
,DayOfMonth tinyint NOT NULL
,DayOfYear smallint NOT NULL
,DayFullName varchar (9)  NOT NULL
,WeekNumber tinyint NOT NULL
,MonthFullName varchar (9)  NOT NULL
,MonthNumber tinyint NOT NULL
,Quarter tinyint NOT NULL
,CalendarYear smallint NOT NULL
)
GO
CREATE TABLE dbo.CustomerDim
(
CustomerKey int IDENTITY (1, 1) NOT NULL PRIMARY KEY CLUSTERED
,CustomerCode nchar (5) NOT NULL
,CustomerName nvarchar (80)  NULL
,BillAddress nvarchar (120)  NULL
,BillCity nvarchar (30)  NULL
,BillRegion nvarchar (30)  NULL
,BillPostalCode nvarchar (20)  NULL
,BillCountry nvarchar (30)  NULL
,ShipAddress nvarchar (120)  NULL
,ShipCity nvarchar (30)  NULL
,ShipRegion nvarchar (30)  NULL
,ShipPostalCode nvarchar (20)  NULL
,ShipCountry nvarchar (30)  NULL
)
GO
CREATE TABLE dbo.ProductDim
(
ProductKey int IDENTITY (1, 1) NOT NULL PRIMARY KEY CLUSTERED
,ProductCode int NOT NULL
,ProductName nvarchar (80)  NOT NULL
,ReorderLevel smallint NULL
,ObsoleteFlag char (1)  NULL
,CategoryName nvarchar (30)  NOT NULL
,CategoryDescription nvarchar (70)  NULL
,UnitPrice money NOT NULL
,QuantityPerUnit nvarchar (40)  NULL
)
GO
CREATE TABLE dbo.CustomerStage
(
CustomerCode nchar (5)  NOT NULL ,
CustomerName nvarchar (80)  NULL ,
BillAddress nvarchar (120)  NULL ,
BillCity nvarchar (30)  NULL ,
BillRegion nvarchar (30)  NULL ,
BillPostalCode nvarchar (20)  NULL ,
BillCountry nvarchar (30)  NULL ,
ShipAddress nvarchar (120)  NULL ,
ShipCity nvarchar (30)  NULL ,
ShipRegion nvarchar (30)  NULL ,
ShipPostalCode nvarchar (20)  NULL ,
ShipCountry nvarchar (30)  NULL ,
)
GO
CREATE TABLE dbo.ProductStage
(
ProductCode int NOT NULL ,
ProductName nvarchar (80)  NOT NULL ,
ReorderLevel smallint NULL ,
ObsoleteFlag char (1)  NULL ,
CategoryName nvarchar (30)  NOT NULL ,
CategoryDescription nvarchar (70)  NULL ,
UnitPrice money NOT NULL ,
QuantityPerUnit nvarchar (40)  NULL ,
)
GO
CREATE TABLE dbo.SalesStage
(
ProductCode int NOT NULL ,
CustomerCode nvarchar (5)  NOT NULL ,
OrderDate datetime NULL ,
QuantitySales smallint NOT NULL ,
AmountSales money NOT NULL
)
GO
CREATE TABLE dbo.SalesFact
(
ProductKey int NOT NULL CONSTRAINT FK_SalesProduct FOREIGN KEY (ProductKey)
REFERENCES dbo.ProductDim (ProductKey)
,CustomerKey int NOT NULL CONSTRAINT FK_SalesCustomer FOREIGN KEY (CustomerKey)
REFERENCES dbo.CustomerDim (CustomerKey)
,TimeKey int NOT NULL CONSTRAINT FK_SalesTime FOREIGN KEY (TimeKey)
REFERENCES dbo.TimeDim (TimeKey)
,QuantitySales smallint NULL
,AmountSales money NULL  
)
GO

resource from SQL server 2000 DTS step by step , Carl Rabeler


***
當distinct 時 只針對某個欄位作,如果要distinct兩個欄位,需要用group by 哪兩個欄位

***
當recordcount =-1, 而其實裡面有資料,要注意那個co


***

CREATE FUNCTION fn_NewRegion ( @myinput nvarchar(30) )
  RETURNS nvarchar(30)
BEGIN
  IF @myinput IS NULL
   SET @myinput = 'Not Applicable'

  RETURN @myinput
END
GO

SELECT LastName, City
      ,dbo.fn_NewRegion(Region) AS Region
      ,Country
 FROM Employees
GO


***
SELECT *
 FROM UnpaidFinesView

***
CREATE VIEW dbo.UnpaidFinesView (Member, TotalUnpaidFines)
AS
SELECT member_no, (sum(fine_assessed-fine_paid))
 FROM loanhist
 GROUP BY member_no
 HAVING SUM(fine_assessed-fine_paid) > 0

***

UPDATE products
 SET unitprice = unitprice + 2
 FROM products
 INNER JOIN suppliers
  ON products.supplierid = suppliers.supplierid
 WHERE suppliers.country = 'USA'

***debug print
if exists(select * from Northwind.dbo.Customers
               where CustomerId = 'ALFKI')
       Print 'Need to update Customer Record ALFKI'
   else
       Print 'Need to add Customer Record ALFKI'



***
USE northwind
DELETE orders
 WHERE DATEDIFF(MONTH, shippeddate, GETDATE()) >= 6
GO

***
USE northwind
INSERT shippers (companyname, Phone)
VALUES ('Fitch & Mather', DEFAULT)
***

alter table employees  NOCHECK CONSTRAINT pk_Employees

***刪除重複資料
use var


select distinct ID, DATE1, OPEN1,HIGH1,LOW1,CLOSE1,VOL1,AMT1,RRR1,RO1,STK_A1,MV11,BID_W1,OFFER_W1, RRRB1,MV21,AMT21,TRN_W1,PER_W1,TEJPER1,TAIL into #tmptable  from sprcd  ORDER BY ID , DATE1 desc

truncate table sprcd
insert into sprcd select * from #tmptable


***
use northwind
Grant SELECT ON Products To public

**
CREATE TABLE customer
(cust_id int, company varchar(40), contact varchar(30), phone char(12) )


***
DECLARE @v1 int
SET @v1 = 0
WHILE @v1 < 100
BEGIN
SELECT @v1 = (@v1 + 1)
SELECT @v1
END


***

SELECT *
FROM OPENQUERY
(OracleSvr, 'SELECT name, id FROM owner.titles')

***

DECLARE @n tinyint
SET @n = 5
IF (@n BETWEEN 4 and 6)
BEGIN
WHILE (@n > 0)
BEGIN
SELECT @n AS 'Number'
,CASE
WHEN (@n % 2) = 1
THEN ‘ODD'
ELSE ‘EVEN'
END AS 'Type'
SET @n = @n - 1
END
END
ELSE
PRINT ‘NO ANALYSIS‘
GO

***

***找出field name
create   PROCEDURE GetTableField
(
 @TableName varchar(30)
)
 AS

BEGIN
select c.name  from syscolumns c left join systypes t on c.xtype=t.xtype  where id in(select id from sysobjects where name=@TableName) order by colorder
end

***找出重複的並刪除
代碼: [選擇]

SELECT         *
INTO #tmptable
FROM             SASTK1

WHERE         ((ID7 + EXDATE) IN
                              (SELECT         ID7 + EXDATE AS Expr1
                                FROM              SASTK1
                                GROUP BY   ID7, EXDATE
                                HAVING          (COUNT(*) > 1)))

ORDER BY  ID7, EXDATE
go

delete  from  SASTK1

WHERE         ((ID7 + EXDATE) IN
                              (SELECT         ID7 + EXDATE AS Expr1
                                FROM              SASTK1
                                GROUP BY   ID7, EXDATE
                                HAVING          (COUNT(*) > 1)))



go


DECLARE abc CURSOR FOR

SELECT         *
from #tmptable

declare @flag as varchar(1)
set @flag='1'

OPEN abc

WHILE (@@FETCH_STATUS = 0 )
BEGIN
FETCH NEXT FROM abc
     if @flag ='1'

    begin  
               DELETE  from #tmptable WHERE CURRENT OF abc
          set @flag ='2'
    end

    else
   begin
       set  @flag ='1'
   end

END

CLOSE abc

DEALLOCATE abc


insert  SASTK1 select * from #tmptable





***找出兩個key field 為唯一值但是出現兩次的資料列,ID7及EXDATE為key field

代碼: [選擇]

SELECT         *
FROM             SASTK1
WHERE         ((ID7 + EXDATE) IN
                              (SELECT         ID7 + EXDATE AS Expr1
                                FROM              SASTK1
                                GROUP BY   ID7, EXDATE
                                HAVING          (COUNT(*) > 1)))
ORDER BY  ID7, EXDATE



***三個同一個dbschema 整合成一個table

代碼: [選擇]

CREATE TABLE  [#returntable] (
[ID] [char] (7) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL ,
[DATE1] [char] (8) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL ,
[OPEN1] [char] (8) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[HIGH1] [char] (8) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[LOW1] [char] (8) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[CLOSE1] [char] (8) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[VOL1] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[AMT1] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[RRR1] [char] (7) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[RO1] [char] (7) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[STK_A1] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[MV11] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[BID_W1] [char] (7) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[OFFER_W1] [char] (7) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[RRRB1] [char] (7) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[MV21] [char] (7) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[AMT21] [char] (7) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[TRN_W1] [char] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[PER_W1] [char] (7) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[TEJPER1] [char] (7) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[TAIL] [char] (1) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
) ON [PRIMARY]


insert into [#returntable]
SELECT         *
FROM             SOPRCD
UNION ALL
SELECT         *
FROM             SRPRCD
UNION ALL
SELECT         *
FROM             SPRCD

select * from [#returntable]





**當isnull不能用時
代碼: [選擇]

if exists(SELECT  sum(TraderStock.Amt)
FROM             TraderStock INNER JOIN
                          Stk ON TraderStock.ADate = Stk.ADate AND
                          TraderStock.StockId = Stk.StockId INNER JOIN
                          Trader ON TraderStock.TraderId = Trader.TraderId INNER JOIN
                          TraderGroup ON Trader.GroupId = TraderGroup.GroupId
WHERE         (TraderStock.ADate =@dt ) AND (Stk.Market = 'T') AND
                          (TraderGroup.GroupName = '承銷部')
group by TraderGroup.GroupName)

begin
SELECT  sum(TraderStock.Amt)
FROM             TraderStock INNER JOIN
                          Stk ON TraderStock.ADate = Stk.ADate AND
                          TraderStock.StockId = Stk.StockId INNER JOIN
                          Trader ON TraderStock.TraderId = Trader.TraderId INNER JOIN
                          TraderGroup ON Trader.GroupId = TraderGroup.GroupId
WHERE         (TraderStock.ADate =@dt ) AND (Stk.Market = 'T') AND
                          (TraderGroup.GroupName = '承銷部')
group by TraderGroup.GroupName

end
else
begin

select 0

end




-----------------------------------
得出以交易來判斷筆數

SELECT '20060208' Date1,
dbo.fn_getworkday('20060208', 1) Date2,  
isnull(t.Market,0) Market,
isnull(Sum(ACount) ,0) count_,
isnull(SUM(SAmt),0) Amt,
isnull(ROUND(SUM(SAmt) * 0.003,
0, 1),0) Tax
FROM (SELECT stk.Market_ Market, ts.StockId, ts.SAmt, Count(*) ACount
FROM TraderStock ts
INNER JOIN
                                                      Trader ON ts .TraderId = Trader.TraderId INNER JOIN
                                                      TraderGroup ON Trader.GroupId = TraderGroup.GroupId
LEFT JOIN dbo.fn_stock('2006208') stk
ON stk.stockid_ = ts.StockId
Left Join TraderTrans c
On stk.stockid_ = c.StockId
WHERE     TraderGroup.GroupId = 'G88' and  ts.ADate = '20060208' and c.ADate = '20060208'
And c.BS = 1
AND ts.SAmt > 0 AND stk.Market_='T'
Group by ts.StockId, stk.Market_, ts.SAmt) t

GROUP BY Market


-----------------------------------

得出以股票代號判斷筆數
SELECT '20060109' Date1, dbo.fn_getworkday('20060109', 1) Date2, isnull(Market,0) Market, isnull(COUNT(*) ,0) count_, isnull(SUM(SAmt),0) Amt, isnull(ROUND(SUM(SAmt) * 0.003, 0, 1),0) Tax FROM (SELECT CASE WHEN market_ = 'U' THEN 'O' ELSE market_ END Market, SAmt FROM TraderStock ts LEFT JOIN dbo.fn_stock('20060109') stk ON stk.stockid_ = ts.StockId WHERE ADate = '20060109' AND SAmt > 0 AND Market_='T') t GROUP BY Market

------------------------------------
SELECT         (ISNULL(SUM(AFQTY * PRICE),
                          0) )/18
FROM             iacksum
WHERE         (MTYPE = 'O') AND (LEFT(CREATED_DATE, 6) = '200601') AND
                          (DESP = '00000000')

---------------------------------
***insert 加入多筆

INSERT INTO 客戶
                          (客戶編號, 客戶名稱, 聯絡人, 性別)
SELECT         客戶編號 + 100 AS Expr1, 客戶名稱, 聯絡人, 性別
FROM             客戶
WHERE         (客戶名稱 = '大發書店')
-------------------------------

insert into ermwst
    (dcomno,edyear,edseq,patno)
 select  "2860","94","8",patno
    from cngwst
    where dcomno = "2860"
    and   edyear = 94
    and   edseq  = 9


***update

update sms.miprs
set sms.miprs.chkno  =(select chkno
                          from mistk
                       where dcomno = 6258
                       and  sms.miprs.stkno = sms.mistk.stkno)
where sms.miprs.dcomno = 6258




***設定使用者
SETUSER 'excel'

***給予應用程式腳色
->(Revoke Select)Customers <--Select<--Erp[應用程式角色]

***設定使用者
exec sp_setapprole 'erp','123'


***unique 要查出某個欄位有無重複值
select  field,count( * ) from table1 group by field having count( * ) >1

***無條件捨去小數點
SELECT FLOOR(123.45), FLOOR(-123.45), FLOOR($123.45)
先減掉0.5 再 round

***四捨五入
SELECT ROUND(123.9994, 0)

***無條件進位
SELECT CEILING($123.45), CEILING($-123.45), CEILING($0.0)


***query出來的結果 要成為.txt且為固定長度
代碼: [選擇]

文字就  
CAST( field1 as char( n ))

補空白,有小數的數字就
select  right('                   '+ convert( varchar(20), EmployeeID),30) as employid FROM Employees

補0, 整數就..
right('00000000' | field1,n)

n=長度



***為查詢結果編寫序號
代碼: [選擇]



select identity(int) id0,[#tmpimat].BHNO,
COMPANYNAME =
CASE rtrim(Ltrim([#TMPIMAT].BHNO))

         WHEN '5180' THEN '營業部'
         WHEN '518!'  THEN '法人部'
         WHEN '5181' THEN '板橋分公司'
         WHEN '5182' THEN '台中分公司'
         WHEN '5183' THEN '高雄分公司'
         WHEN '5184' THEN '敦南分公司'
         WHEN '5185' THEN '五權分公司'
         WHEN '5186' THEN '南京分公司'
         WHEN '5187' THEN '台南分公司'
         WHEN '5188' THEN '嘉義分公司'
         WHEN '5189' THEN '桃園分公司'
      END,
[#tmpimat].CSEQ,cumb.SNAME as sname, [#tmpimat].CREATED_DATE,[#tmpimat].imatSalesAmount ,
[#tmpiack].iackSalesAmount,( [#tmpimat].imatSalesAmount/[#tmpiack].iackSalesAmount)*100 AS pnt
into [#returntable]
 from [#tmpimat]
 inner join [#tmpiack] ON
 [#tmpiack].BHNO = [#tmpimat].BHNO and [#tmpimat].CSEQ=[#tmpiack].CSEQ and [#tmpimat].CREATED_DATE =[#tmpiack].CREATED_DATE
INNER JOIN
                          cumb ON [#tmpimat].cseq = cumb.cseq  and  [#tmpimat].bhno=cumb.bhno
order by imatSalesAmount desc

select * from [#returntable]



sql function syntax



TRUNCATE TABLE authors
從資料表中移除所有的資料列,且不對個別資料列的刪除做記錄。


判斷然後取代
代碼: [選擇]

SELECT         CMSMV.MV001, CMSMV.MV002, CMSMV.MV004 AS EXPR1,
                          CMSME.ME002 AS EXPR3, CASE LEFT(CMSMV.MV005, 1)
                          WHEN 'S' THEN '業務員' ELSE CMSMV.MV005 END AS EXPR2
FROM             CMSMV INNER JOIN
                          CMSME ON CMSMV.MV004 = CMSME.ME001
WHERE         (CMSME.ME002 LIKE '%高雄%' OR
                          CMSME.ME002 LIKE '%嘉義%') AND (CMSMV.MV022 = '' OR
                          CMSMV.MV022 IS NULL)
ORDER BY  CMSME.ME001, CMSMV.MV005




代碼: [選擇]


CREATE  FUNCTION cutovertime(
@?#91;班時分 varchar(4),
@whatyouwant varchar(5)
)
RETURNS numeric(4,2) AS  
BEGIN

declare @a as numeric(4,2)
declare @b as numeric(4,2)
declare @c as numeric(4,2)
declare @x as numeric(4,2)

select @x=cast(datediff(n,'00:00', left(@?#91;班時分,2)+':'+Right(@?#91;班時分,2)) as float)/60 --TG007

if @x> 2  
begin
   select @x=@x-2
   select @a=2

if @x>2
begin
            select @x=@x-2
select @b=2
select @c=@x
end
else
begin
select @b=@x
select @c=0
end


end
else
begin
   select @a=@x
   select @b=0
   select @c=0
end

if @whatyouwant='TG011' return @a
if @whatyouwant='TG012' return @b
if @whatyouwant='TG013' return @c

return 0

END


判斷並給值
代碼: [選擇]

declare @IneedMoney as numeric(4,2)
select @IneedMoney=cast(@?#91;班時分 as numeric(4,2))-@轉補修時數


    INSERT INTO PALTG (TG001,TG002,TG003,TG004,TG005,TG006,TG007,TG008,TG009,TG010,TG011,TG012,TG013,TG014,TG015,TG016,TG017,TG018)
 
     VALUES (
     @employID,  --'(1) TG001
     @?#91;班日期 , --(2) TG002 迴圈所在日
     @?#91;班班別,  --(3) TG003 為?#91;班班別
     substring(@起始時分,12,2) + substring(@起始時分,15,2), --(4) TG004
     substring(@截止時分,12,2) + substring(@截止時分,15,2),  --(5) TG005
     '0' + cast(cast(@?#91;班時分 as float) * 100 as char(3)), --(6) TG006
     cast(@?#91;班時分 as numeric(4,2)), --TG007
     --cast('2.5' as numeric(4,2)), --TG007
     @備註,  --(9) TG008
     @確?#123;碼,  --(10) TG009
     '000' + CAST(@@ROWCOUNT+1 AS VARCHAR(1)), --(by@rowcount)TG010為自動產生的序號
--     dbo.cutovertime(cast(@?#91;班時分 as numeric(4,2))-@轉補修時數,'TG011'),
--     dbo.cutovertime(cast(@?#91;班時分 as numeric(4,2))-@轉補修時數,'TG012'),
--     dbo.cutovertime(cast(@?#91;班時分 as numeric(4,2))-@轉補修時數,'TG013'),
 [color=darkred]case when @IneedMoney>2 then 2 else @IneedMoney end,
 case when @IneedMoney>4 then 2 else @IneedMoney-2 end,
 case when @IneedMoney>4 then @IneedMoney-4 else 0 end,[/color]     @確?#123;日期,
     @確?#123;者, --(11) TG015            
     @轉補修,  --(12) TG016
     @轉補修時數, --TG017
     @簽核狀態碼 --(14)TG018
     )



判斷並抓值

小徒兒

  • 鑽研的研究生
  • *****
  • 文章數: 622
    • 檢視個人資料
基礎
« 回覆 #1 於: 2006-03-16 19:59 »
***update lock

**由輕到重
--read uncommitted
--read committed (default)
--repeateable read
--serializable



  | HOLDLOCK
        | NOLOCK
        | PAGLOCK
        | READCOMMITTED
        | READPAST
        | READUNCOMMITTED
        | REPEATABLEREAD
        | ROWLOCK
        | SERIALIZABLE
        | TABLOCK
        | TABLOCKX
        | UPDLOCK
        | XLOCK

xlock =exclusive lock

***

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
GO
BEGIN TRANSACTION
SELECT * FROM publishers
SELECT * FROM authors
...
COMMIT TRANSACTION


***
set lock_timeout


***建立有預設值的stored procedure
CREATE PROCEDURE dbo.AddCustomer
    @CustomerID   nchar(5),
   @CompanyName   nvarchar(40),
   @ContactName   nvarchar(30) = NULL,
   @ContactTitle   nvarchar(30) = NULL,
   @Address   nvarchar(60) = NULL,
   @City      nvarchar(15) = NULL,
   @Region      nvarchar(15) = NULL,
   @PostalCode   nvarchar(10) = NULL,
   @Country   nvarchar(15) = NULL,
   @Phone      nvarchar(24) = NULL,
   @Fax      nvarchar(24) = NULL
AS
   INSERT INTO Customers(   CustomerID,
            CompanyName,
            ContactName,
            ContactTitle,
            Address,
            City,
            Region,
            PostalCode,
            Country,
            Phone,
            Fax   )
             VALUES(   @CustomerID,
            @CompanyName,
            @ContactName,
            @ContactTitle,
            @Address,
            @City,
            @Region,
            @PostalCode,
            @Country,
            @Phone,
            @Fax   )

GO

-- sp有預設值的參數可以省略
EXEC AddCustomer 'test', 'Peter Lee'


回傳值耶
EXEC GetTotalSales '1997/1/1', '1997/1/31', @ans OUTPUT
print @ans
GO

-- 改用 return 值
ALTER PROCEDURE dbo.GetTotalSales
   @BeginDate datetime,
   @EndDate datetime
AS
   DECLARE @result int
   SELECT @result = SUM(od.Quantity * od.UnitPrice)
   FROM [Order Details] od
   INNER JOIN Orders o ON o.OrderID = od.OrderID
   WHERE OrderDate BETWEEN @BeginDate AND @EndDate
   return @result
GO

-- 執行 Stored Procedure,並取回 return 值
DECLARE @ans int
EXEC @ans = GetTotalSales '1997/1/1', '1997/1/31'
print @ans









***
此範例將建立使用 Microsoft OLE DB Provider for DB2的連結伺服器,名為 DB2。

EXEC sp_addlinkedserver
   @server='DB2',
   @srvproduct='Microsoft OLE DB Provider for DB2',
   @catalog='DB2',
   @provider='DB2OLEDB',
   @provstr='Initial Catalog=PUBS;Data Source=DB2;HostCCSID=1252;Network Address=XYZ;Network Port=50000;Package Collection=admin;Default Schema=admin;'

????lock





CREATE INDEX AAAA ON Customers(Region, City, CompanyName)
DROP INDEX Customers.AAAA


SET SHOWPLAN_TEXT OFF
SET SHOWPLAN_ALL ON

SET STATISTICS TIME OFF
SET STATISTICS TIME ON

SET STATISTICS PROFILE ON

SET STATISTICS IO ON


SELECT * FROM
OPENROWSET('SQLOLEDB', '172.16.200.168';'sa';'','SELECT * FROM Northwind.dbo.Orders')


SELECT * FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'C:\Northwind.mdb';'Admin';'', 'SELECT * FROM 客戶')

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
   'c:\MSOffice\Access\Samples\northwind.mdb';'admin';'',
   Customers)


代碼: [選擇]

SELECT c.公司名稱, o.OrderDate FROM
OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'C:\Northwind.mdb';'Admin';'', 'SELECT * FROM 客戶') c
INNER JOIN
OPENROWSET('SQLOLEDB', '172.16.200.210';'sa';'','SELECT * FROM Northwind.dbo.Orders') o
ON o.CustomerID = c.客戶編號



***
select *
FROM OPENDATASOURCE(
          'SQLOLEDB',
          'Data Source=ServerName;User ID=MyUID;Password=MyPass'
                   ).Northwind.dbo.Categories

***加linked server
EXEC sp_addlinkedserver 'OracleSvr',
   'Oracle 7.3',
   'MSDAORA',
   'ORCLDB'


GO
此範例將建立使用 Microsoft OLE DB Provider for SQL Server 的連結伺服器,名為 SEATTLESales。
eXEC sp_addlinkedserver
    'SEATTLESales',
    N'SQL Server'


此範例將利用 OLE DB Provider for SQL Server 在 SQL Server 的執行個體上建立連結伺服器 S1_instance1。
EXEC    sp_addlinkedserver    @server='S1_instance1', @srvproduct='',
                                @provider='SQLOLEDB', @datasrc='S1\instance1'



***
EXEC sp_addlinkedserver
   @server = 'SEATTLE Mktg',
   @provider = 'Microsoft.Jet.OLEDB.4.0',
   @srvproduct = 'OLE DB Provider for Jet',
   @datasrc = 'C:\MSOffice\Access\Samples\Northwind.mdb'
GO


***
-- oracle To use named parameters:
EXEC sp_addlinkedserver
   @server = 'LONDON Mktg',
   @srvproduct = 'Oracle',
   @provider = 'MSDAORA',
   @datasrc = 'MyServer'
GO



***使用dns
-- To use named parameters:
EXEC sp_addlinkedserver
   @server = 'SEATTLE Payroll',
   @provider = 'MSDASQL',
   @datasrc = 'LocalServer'




----使用連結字串加入linked server
EXEC sp_addlinkedserver
   @server = 'LONDON Payroll',
   @provider = 'MSDASQL',
   @provstr = 'DRIVER={SQL Server};SERVER=MyServer;UID=sa;PWD=;'



***Excel 工作表資料行及資料列。接著可將範圍名稱引用為分散式查詢中的資料表名稱。

EXEC sp_addlinkedserver 'ExcelSource',
   'Jet 4.0',
   'Microsoft.Jet.OLEDB.4.0',
   'c:\MyData\DistExcl.xls',
   NULL,
   'Excel 5.0'


select * from [excelsource]...Dailyreport$


EXEC sp_serveroption 'ExcelSource', 'data access', true
EXEC sp_helpserver 'ExcelSource'


-- 傳回特定遠端資料表的索引資訊。
EXEC sp_indexes '172.16.200.168'


-- 傳回指定連結伺服器中的資料庫目錄清單
EXEC sp_catalogs '172.16.200.168'


-- 傳回與指定連結伺服器中的資料表有關的資料表資訊
EXEC sp_tables_ex
   @table_server = '172.16.200.158',
   @table_catalog = 'Northwind'

-- 傳回指定連結伺服器資料表的資料行資訊
EXEC sp_columns_ex
   @table_server = '172.16.200.168',
   @table_catalog = 'Northwind',
   @table_schema = 'dbo',
   @table_name = 'Customers'


-- 針對指定的遠端資料表,傳回主索引鍵資料行,每一索引鍵資料行傳回一列EXEC sp_primarykeys
   @table_server = '172.16.200.168',
   @table_catalog = 'Northwind',
   @table_schema = 'dbo',
   @table_name = 'Customers'

-- 列出關聯索引資訊
EXEC sp_foreignkeys
   @table_server = '172.16.200.168',
   @pktab_catalog = 'Northwind',
   @pktab_name = 'Customers'


--query 遠端server
SELECT * FROM
OPENQUERY([172.16.200.168], 'SELECT * FROM Northwind.dbo.Customers')


---







EXEC sp_linkedservers
SELECT * FROM [172.16.200.168].Northwind.dbo.Customers
EXEC sp_dropserver '172.16.200.168'
EXEC sp_linkedservers











代碼: [選擇]

select * from Customers for XML Auto
select * from Customers for XML RAW




代碼: [選擇]

declare @page as int,@pagesize as int
set @page=3
set @pagesize=5

DECLARE mycursor SCROLL CURSOR FOR SELECT * FROM Customers
OPEN mycursor

--找出the first row of page 4 number
DECLARE @loc int SET @loc = @page * @pagesize + 1

FETCH ABSOLUTE @loc FROM mycursor
DECLARE @no int

--every page has 5 rows,every time catch
SET @no = @pagesize - 1
WHILE @@FETCH_STATUS = 0 AND @no > 0
BEGIN
FETCH NEXT FROM mycursor

SET @no = @no - 1
END
CLOSE mycursor
DEALLOCATE mycursor



***cursor
FETCH FIRST、FETCH LAST、FETCH NEXT、FETCH PRIOR、FETCH ABSOLUTE(n) 和往下擠比 FETCH RELATIVE(n)

***linkedserver /右鍵/property/security local login / remote password


exec sp_helpserver
exec sp_addlinkedserver '10.10.1.103'
select * From OpenQuery ([10.10.1.103],'select * from Northwind.dbo.Customers')

select * from [10.10.1.103].northwind.dbo.customers

exec sp_dropserver '10.10.1.103'


代碼: [選擇]


select * ,Quantity * UnitPrice,

case
when Quantity>=5  and Quantity <10 then '打八折'
when Quantity>=10 then '打五折'
when Quantity<5 then '原價'
end
as  打折,
case
when Quantity>=5  and Quantity <10 then (Quantity*UnitPrice)*0.8
when Quantity>=10 then (Quantity*UnitPrice)*0.5
when Quantity<5 then (Quantity*UnitPrice)
end
as  打折後

from [order details]


代碼: [選擇]

select ProductName, UnitPrice,
CASE

         WHEN UnitPrice<100 THEN '便宜'
         WHEN UnitPrice>=100 and UnitPrice<300  THEN '普通'
         WHEN UnitPrice>=300 THEN '很貴'
      END as 評等 from Products



declare @intx as int,@inty as int, @intz as int,@stringfinal as varchar(1000)
set @intx=2

while @intx <=9
begin
set @inty =1
set @stringfinal=''
   while @inty <=9
   begin

   set @stringfinal=@stringfinal+'   '+ cast(@intx as varchar(2)) +' x  ' +cast(@inty as varchar(2)) +' = ' + cast(@inty*@intx as varchar(4))

   set @inty=@inty+1
  end
   print @stringfinal
set @intx=@intx+1

end[/code]


代碼: [選擇]

SELECT 'ANSI:' AS Region,
CONVER(varchar(30),GETDATE(),102) AS Style

UNION
SELECT 'European:' , Convert(varchar(30), Getdate(),111) as Style





@@identity
@@error

vw_
sp_
fn_

server.db.owner.object




sub query

代碼: [選擇]

SELECT         *
FROM             (SELECT         Products.ProductName, [Order Details].ProductID,
                                                      SUM([Order Details].Quantity) AS TotalQty,
                                                      SUM(([Order Details].UnitPrice * [Order Details].Quantity)
                                                      * (1 - [Order Details].Discount)) AS TotalSales
                           FROM              [Order Details] INNER JOIN
                                                      [Order Details] [Order Details_1] ON
                                                      [Order Details].ProductID = [Order Details_1].ProductID INNER JOIN
                                                      Products ON [Order Details].ProductID = Products.ProductID AND
                                                      [Order Details_1].ProductID = Products.ProductID
                           GROUP BY   [Order Details].ProductID, Products.ProductName) T
WHERE         (TotalSales >= 30000)
ORDER BY  TotalSales DESC





*** sub query
SELECT *
 FROM ( SELECT orderid, customerid
         FROM orders )  AS T




***依撈出資料比數,動態產生相對樣數量column的 recordset
代碼: [選擇]


SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


--drop table   [#returntable]
ALTER        procedure getReturnLog (@dt  varchar(8),@markettype char(1))
as

declare @firsttable as varchar(10)
declare @offset as  integer
begin

--exec getReturnLog '20060320',''
set @dt ='20060315'
declare @sqlstring as varchar(2000)
declare @sqlstring2 as varchar(2000)
declare @sqlstring3 as varchar(2000)
declare @sqlstring4 as varchar(6000)
declare @secondtable as varchar(30),@Stockid varchar(10)


set @sqlstring=''
set @sqlstring4=''
set @sqlstring2=''
set @offset =1
set @markettype=''



--   將三個table union在一起
CREATE TABLE  [#returntable] (
   [ID] [char] (7) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL ,
   [DATE1] [char] (8) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL ,
   [RRRB1] [char] (7) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
) ON [PRIMARY]

---


insert into [#returntable]
SELECT    ID,DATE1,RRRB1
FROM             SOPRCD
UNION ALL
SELECT    ID,DATE1,RRRB1    
FROM             SRPRCD
UNION ALL
SELECT    ID,DATE1,RRRB1
FROM             SPRCD

---將持有部位的股票代號製造出#tmptalbe @#a1-@an

DECLARE abc CURSOR FOR

select Stockid from dbo.TraderStock where ADate=@dt  and len(Rtrim(Stockid))=4 group by StockId

OPEN abc

WHILE (@@FETCH_STATUS = 0 )
BEGIN
FETCH NEXT FROM abc
into @Stockid

set @Stockid = cast(@Stockid as varchar)

    IF @markettype ='T'
    begin
      set @sqlstring4='select RRRB1,Date1 into #a' + cast(@offset as varchar) + ' from  [#returntable]   where ID=''' +@Stockid + ''''
    end

    IF @markettype ='O'
    begin
      set @sqlstring4='select RRRB1,Date1 into #a' + cast(@offset as varchar) + ' from  [#returntable]   where ID=''' +@Stockid + ''''
    end

    IF @markettype ='R'
    begin
      set @sqlstring4='select RRRB1,Date1 into #a' + cast(@offset as varchar) + ' from  [#returntable]   where ID=''' +@Stockid + ''''
    end

     IF @markettype=''
    begin
      set @sqlstring4=@sqlstring4 + '          select isnull(RRRB1,0) as '''+@Stockid + ''' ,Date1 into #a' + cast(@offset as varchar) + ' from  [#returntable]   where ID=''' +@Stockid + ''''
    end

    set @offset =@offset+1
END

CLOSE abc

DEALLOCATE abc

select @sqlstring4

set @offset =1

select TraderStock.Stockid from dbo.TraderStock
where TraderStock.ADate=@dt  and len(Rtrim(TraderStock.Stockid)) =4 group by  TraderStock.StockId

declare @rowcount as integer
set @rowcount =@@rowcount

/**
select @rowcount
set @rowcount =3

**/

DECLARE def CURSOR FOR
select Stockid from dbo.TraderStock where ADate=@dt  and len(Rtrim(Stockid))=4 group by StockId
OPEN def
FETCH NEXT FROM def
into @Stockid

WHILE (@@FETCH_STATUS = 0 )
BEGIN

--select '?#93;了幾次' +cast(@offset as varchar) +  cast(@Stockid as varchar)

set @firsttable ='#a'  + cast(@offset as varchar)
set @secondtable ='#a'  + cast(@offset+1 as varchar)
set @sqlstring=  @sqlstring+', '+@firsttable +'.[' +@Stockid +']'
set @sqlstring2= @sqlstring2 + ' left  join ' + @secondtable +' on ' +  @secondtable +'.Date1 =' +'#a1' +'.Date1'
set @offset =@offset+1


FETCH NEXT FROM def
into @Stockid

END

CLOSE def

DEALLOCATE def

set @sqlstring =Right(@sqlstring,Len(@sqlstring)-1)

set @sqlstring3=
'select ' +@sqlstring + ' from #a1'
+
@sqlstring2

select @sqlstring3

select @sqlstring4 = @sqlstring4+ '            '+ @sqlstring3

execute(@sqlstring4)

/**
declare @dt as varchar(8)
set @dt ='20060314'

select Stockid from dbo.TraderStock where ADate=@dt group by StockId
select @@rowcount

**/

end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO




---------------

USE master
EXECUTE xp_cmdshell 'dir *.exe'

---------------------
client 端要用nextrecordset
next dataset


SELECT productid, orderid, quantity
FROM orderhist
ORDER BY productid, orderid
COMPUTE SUM(quantity) BY productid
COMPUTE SUM(quantity)

----------找出與平均的差

SELECT title, price
      ,(SELECT AVG(price) FROM titles) AS average
      ,price-(SELECT AVG(price) FROM titles) AS difference
 FROM titles
 WHERE type='popular_comp'

----------找出買product id 23 超過 20件的客戶

SELECT orderid, customerid
 FROM orders AS or1
 WHERE 20 < (SELECT quantity
             FROM [order details] AS od
             WHERE or1.orderid = od.orderid
              AND od.productid = 23)

---------找出每個產品曾發生過的最大訂貨量
先找出所有的產品編號 及數量

SELECT DISTINCT productid, quantity
 FROM [order details] AS ord1
 WHERE quantity = ( SELECT MAX(quantity)
                     FROM [order details] AS ord2
                     WHERE ord1.productid = ord2.productid )


----------
USE northwind
SELECT productid, orderid, SUM(quantity) AS total_quantity
FROM orderhist
GROUP BY productid, orderid
WITH ROLLUP
ORDER BY productid, orderid

-----------
SELECT productid, orderid, SUM(quantity) AS total_quantity
FROM orderhist
GROUP BY productid, orderid
WITH CUBE
ORDER BY productid, orderid

---***用來判斷是否是累加直
SELECT productid, GROUPING (productid),
       orderid, GROUPING (orderid),
       SUM(quantity) AS total_quantity
FROM orderhist
GROUP BY productid, orderid
WITH CUBE
ORDER BY productid, orderid



---
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
      WHERE TABLE_NAME = 'employees')
   DROP TABLE employees


----------------
IF EXISTS (SELECT * FROM orders
WHERE customerid = 'frank')
PRINT '*** Customer cannot be deleted ***'
ELSE
BEGIN
DELETE customers WHERE customerid = 'frank'
PRINT '*** Customer deleted ***'
END


------------------
SELECT CompanyName, ContactName, Phone, Fax
FROM Customers
WHERE CompanyName LIKE N'%snabbk&ouml;p'

***N =unicode data

------------------

SELECT *
FROM finances
WHERE description LIKE 'gs_' ESCAPE 'S'


SELECT * FROM T_CLIENT WHERE CLI_ENSEIGNE LIKE '%#_%' ESCAPE '#'

--------------------------


USE library
DECLARE @dbname varchar(30), @tablename varchar(30)
SET @dbname = 'northwind'
SET @tablename = 'products'

EXECUTE
   ('USE ' + @dbname + ' SELECT productname FROM ' + @tablename)
GO

----------------------------

select 'Price=' + cast(UnitPrice as varchar) as price from products

--------------------------
EXECUTE ('SET NOCOUNT ON '+ 'SELECT lastname, reportsto FROM employees WHERE reportsto IS NULL')

---------------------------
USE northwind
SELECT orderid, customerid
FROM orders
WHERE orderdate < '8/1/96'
GO
----------------------------

USE northwind
SELECT productid, productname, supplierid, unitprice
FROM products
WHERE   (productname LIKE 'T%' OR productid = 46) AND
      (unitprice > 16.00)
------------------------------
SELECT companyname, country
FROM suppliers
WHERE country IN ('Japan', 'Italy')

-----------------------------------------

SELECT TOP 5 orderid, productid, quantity
FROM [order details]
ORDER BY quantity DESC

------------------------------------------

SELECT productid, SUM(quantity) AS total_quantity
FROM orderhist
GROUP BY productid
HAVING SUM(quantity) >=30

----------------------------------------
SELECT productid, SUM(quantity) AS total_quantity
FROM [order details]
GROUP BY productid
HAVING SUM(quantity) > 1200

小徒兒

  • 鑽研的研究生
  • *****
  • 文章數: 622
    • 檢視個人資料
update
« 回覆 #2 於: 2007-12-10 14:07 »
判斷並抓值
update

UPDATE    Customer2
SET              ContactRegion = 'Israel'
FROM         Customer2
WHERE     contactLocation = 'Yokneam'


UPDATE    Customer2
SET              ContactRegion = 'Korea'
FROM         Customer2

WHERE ContactCompany=' Electronics'


SELECT     Customer2.*
FROM         Customer2
WHERE     (ContactCompany LIKE '% LION%')


update Customer2
set ContactRegion = "Japan"
FROM         Customer2
where contactLocation="Yonezawa"


SELECT     Customer2.*, ContactLocation AS Expr1
FROM         Customer2
ORDER BY ContactLocation DESC

***alter procedure 不看空值

alter PROCEDURE filteroutthedata AS


select distinct * from vwFromCustomer2 where

len(isnull(vwFromCustomer2.ContactCompany ,' ') )>1 and
len(isnull(vwFromCustomer2.ContactRegion ,' ') )>1 and
len(isnull(vwFromCustomer2.ContactLocation ,' ') )>1

order by ContactRegion asc,ContactCompany asc
--exec filteroutthedata



micmic3

  • 俺是博士!
  • *****
  • 文章數: 1693
    • 檢視個人資料
回覆: sql syntax
« 回覆 #3 於: 2007-12-11 11:23 »
最近也為了這樣的事情傷腦筋
但是不能用 sub query.......
---
mysql 4.0 不能用 sub query..........orz

小徒兒

  • 鑽研的研究生
  • *****
  • 文章數: 622
    • 檢視個人資料
new sql syntax for sql 2008 (Tech Ed 2008 會後筆記)
« 回覆 #4 於: 2008-10-02 15:02 »
SQL 2007

*Tool
   Microsoft SQL Server Management Studio


Merge – when not match
Group by setting ( union)
Declare type as table\

? Table value parameters (only in function and sp)
database engine
Create clustered index

Day time saving (系統定義)
?? by system server now() or by DB now() à aging calculate
    how to solve the question?


2008 enforce dependence

every time to build the view, drop table will update the dependency

sys.sm_sql_referencing_entities (‘dbo.Mystocks’, ‘Object’);

grouping set (
group by control, province, City

using cube to replace group by contry, province, city

T-SQL delighters

***Table value contracture through value clause

Insert into values(‘Jone Doe’, ‘425-14349’)
Select * from values(1,1,) as c(custid, companyname, phone, address);
***object dependencies
sys.sql_expression_dependencies
sys.dm_sql_referenced_entitites
sys.dm_sql_referencing_entities

***object dependencies

***DDL Trigger Enhancements
enhanced (data definition language) DDL events to include all DDP operations
a.   stored procedures like sp_rename
b.   language DDL like full-text, security DDL
Persisting event group
c.   No expansion of groups
d.   CLR integration
e.   **CLR Enhancemnt
f.   1. user defined type
i.   support for large instances
ii.   user define aggregates
1.   support for large serialization
2.   multi column input
g.   Table value function

Globalization
1.   Align with windows vista collations
2.   adding windows new collations in SQL server 2008
3.   adding new versions to existing windows collations
4.   Adding new versions to existing windows collations with signigicant changes
a.   Chnese tawan

**A better store for semi structure data
hierarchyID
a.   Tree view, Stored arbitrary hierarchies of data and efficiently query them
b.   Optimize hieratical or vertical
large udts
c.   
sparse columns
d.   indexer
e.   harepoint or AD attribute all is empty, most fields in empty, difficult to indexing

wide tables
f.   
filtered indices
g.   Active or inactive user
h.   We must indexing to all the users, every query to go through all the users
i.   Only pick up certain columns to do the filter
j.   Create the index toward certain column with true value
k.   Column put into “simplified Chinese””and “Traditional Chinese” to build index through the filter indices

? can many index build on different filtering : yes

-   Report , simple integration
-   Every one all can use GIS, 空間資料
-   2008 JDBC
***Spatial Goes Mainstream
Extend SQL server with types

**Deprecation
Deprecation stages
a.   Announcement/Warning
b.   Final support/Removal
c.   Future deprecation


用batch, collation 資料作定義Select

1. type as daytime offset
2. now( )- daytime offset(region)

clustering, filtering ß> filtering index ,index view, maturlized view

?? timestamp (sys daytime) oracle already timezone 存進去
絕對時間 timezone

 


***above resource is from Microsoft Howard H. Yin