MS SQL 實用語法(一)

隨機産生0或1
SELECT DATENAME ( millisecond , GETDATE ( ) )%2

四捨五入某數，並取整數
Select ROUND(748.58, 0)

撈行號的技巧

USE   PUBS   
  GO   
  --------------------------------------------------------------------------   
  SELECT   
          (SELECT   COUNT(1)   FROM   authors   a2   where   a2.au_id   <=   a1.au_id)   AS   ROWNUM,   
          *   
  FROM   authors   a1   
  --------------------------------------------------------------------------   
  SELECT   
          COUNT(a1.au_id),   
          a1.au_id,   
          a1.au_lname,   
          a1.au_fname,   
          a1.phone,   
          a1.address,   
          a1.city,   
          a1.state,   
          a1.zip,   
          a1.contract   
  FROM   authors   a1,   authors   a2   
  WHERE   a1.au_id   <=   a2.au_id  [>=]
  GROUP   BY   a1.au_id,   
                    a1.au_lname,   
                    a1.au_fname,   
                    a1.phone,   
                    a1.address,   
                    a1.city,   
                    a1.state,   
                    a1.zip,   
                    a1.contract   
  ORDER   BY   a1.au_id   DESC   [ASC]
----------------------------------------------------------------
  select   row   =   (select   count(1)   from   a   y   where   y.id   <=   x.id   ),   
                x.id,   
                x.num1,   
                x.num2,   
                z.cnt   
  from   a   x     
  join   (   select   num1,num2,count(id)   from   a   z   group   by   num1,num2)     
  on   x.num1   =   z.num1   and   x.num2   =   z.num2;  
--------------------------------------------------------------------

轉日期格式
datetime to char(8) 如 yyyy-mm-dd hh:mi:ss.mmm -> yyyymmdd
Select CONVERT( nvarchar(8),getDate(),112) as Today

檢查資料型態

-- if exists, drop the table we need for the demo
IF EXISTS (SELECT *
           FROM   INFORMATION_SCHEMA.TABLES
           WHERE  TABLE_SCHEMA = USER
             AND  TABLE_NAME = 'T_VEHICULE')
   DROP TABLE T_VEHICULE
-- create it
CREATE TABLE T_VEHICULE
(VHC_ID         INTEGER NOT NULL PRIMARY KEY,
 VHC_ID_FATHER  INTEGER FOREIGN KEY REFERENCES T_VEHICULE (VHC_ID),
 VHC_NAME       VARCHAR(16))
-- populate
INSERT INTO T_VEHICULE VALUES (1, NULL, 'ALL')
INSERT INTO T_VEHICULE VALUES (2, 1, 'SEA')
--see the column data type
SELECT  TOP 2  SQL_VARIANT_PROPERTY(VHC_ID,'BaseType') VHC_ID,
 SQL_VARIANT_PROPERTY(VHC_ID_FATHER,'BaseType') VHC_ID_FATHER,
 SQL_VARIANT_PROPERTY(VHC_NAME,'BaseType') VHC_NAME
FROM      T_VEHICULE