SQL/T-SQL实例参考-1

CASE

,D.[Score] B_Score
,'Distince'=
  CASE 
    WHEN C.Score > D.Score THEN C.[Score] - D.[Score]
    WHEN C.Score < D.Score THEN D.[Score] - C.[Score]
    ELSE 0
  END 
FROM [tbiz_AssScoreAction] C
INNER JOIN [tbiz_AssScoreAction] D
ON C.QuestionID =D.QuestionID AND (C.RelationID -30) = D.RelationID
WHERE C.RelationID=30 AND C.ProjectID=65 AND C.PersonID=2430
--等价语法
,D.[Score] - C.Score Ta_Zi
,CASE 
    WHEN C.[Score] > D.[Score] THEN C.[Score] - D.[Score]
    WHEN C.[Score] < D.[Score] THEN D.[Score] - C.[Score]
    ELSE 0
END AS Distince  

 CASE 判断 NULL的情况

CASE WHEN [ReadState] IS NULL OR [ReadState]=0  THEN '未读' ELSE '已读'  END AS ReadStateName

 

SELECT CASE Type_No
    WHEN 2 THEN Customer_No
    WHEN 4 THEN Customer_No
    WHEN 10 THEN Customer_No
    WHEN 11 THEN Customer_No
    WHEN 3 THEN Organization_Name
    WHEN 5 THEN Organization_Name
    END AS Customer_No
FROM    [Lx_Finance_Transfer_ForNC]

 

SELECT
    A.TESTID,
    A.SCALEID,
    B.NAME,
    B.COUNT,
    B.ASSTIME,
    A.STATE,
CASE
    WHEN A.MATCH IS NULL THEN
    0 ELSE CAST (A.MATCH AS NUMERIC ( 26, 2 )) 
END AS MATCH 
FROM
    TD_TEST_INFO A
    JOIN ASS_SCALE B ON A.SCALEID= B.SCALEID

 

JOIN

SELECT T.Id,T.pingZhengNum FROM Lx_Finance_Transfer_ForNC T
LEFT JOIN Lx_Finance_Flow_ForNC F ON T.Id=F.PingZhengId

 

LEFT JOIN

解读:尽管主档表T只有1条记录,但是在分档表中有2条记录与之关联,所以SELECT结果集有2条.

SELECT T.Id T_Id,T.pingZhengNum,F.* FROM Lx_Finance_Transfer_ForNC T
INNER JOIN Lx_Finance_Flow_ForNC F ON T.Id=F.PingZhengId
-- 效果和LEFT JOIN 一样

 RIGHT JOIN

解读:T表从RIGHT链接F表,SQL引擎会保留F表中所有记录

自连接

SELECT  A.* ,
        B.RelationName ,
        B.Score T_Score ,
        B.ID T_ID
FROM    [tbiz_AssScoreWeidu] A
        INNER JOIN [tbiz_AssScoreWeidu] B 
        ON A.WeiduNo = B.WeiduNo AND A.RelationID = B.RelationID + 30
WHERE   A.ProjectID = 65
        AND A.PersonID = 2430
        AND ( A.RelationID = 30 OR B.RelationID = 0 )
        AND A.WeiduNo = 'C211-13'

 自链接+WHERE条件

SELECT  A.DictItemID ,
        B.ItemName ParentItemName ,
        A.ItemType ,
        A.ItemName ,
        A.ItemCode ,
        A.CreatedBy ,
        A.IsDelete
FROM    tcfg_DictItem A
        LEFT JOIN tcfg_DictItem B ON B.DictItemID = A.ParentID
WHERE   A.ParentID != 0

一对多关系JOIN一行

SELECT * 
FROM (
   SELECT A.Id
      ,A.PingZhengNum
      ,A.CRT_Date
      ,A.[Enabled]
      ,B.Id FlowId
      ,B.Type_No_Name
      ,B.VouchType
      ,ROW_NUMBER() OVER
      (
        PARTITION BY A.Id 
        ORDER BY  B.CRT_Date
      )
      AS rn
      FROM [Lx_Finance_Transfer_ForNC] A LEFT OUTER JOIN dbo.Lx_Finance_Flow_ForNC B 
      ON A.Id=B.PingZhengId
) m
WHERE rn=1

解读:主档表记录多,分档表记录少,参考

方案二

SELECT A.Id
        ,A.PingZhengNum
        ,A.CRT_Date
        ,A.[Enabled]
        ,B.Id FlowId
        ,B.Type_No_Name
        ,B.VouchType
FROM [Lx_Finance_Transfer_ForNC] A
OUTER APPLY
(
SELECT TOP 1 *
FROM Lx_Finance_Flow_ForNC C
WHERE PingZhengId = A.Id
ORDER BY CRT_Date DESC 
) B

ROW_NUMBER() OVER(....) AS Xxx

SELECT  [PersonID] ,
        ROW_NUMBER() OVER
        (
            ORDER BY A.Id
        ) AS RowID
FROM [tbiz_AssScore] A

简单的说row_number()从1开始，为每一条"分组"记录返回一个数字,分组对应关键词PARTITION BY XXX,为可选关键词

当出现GROUP BY 子句时,GROUP BY会影响到ROW_NUMBER()中的字段,如下面的第5行

SELECT  [PersonID] ,
        AVG([Score]) AVG_Score,
        ROW_NUMBER() OVER
        (
            ORDER BY A.PersonID
        ) AS RowID
FROM [tbiz_AssScore] A
GROUP BY PersonID
ORDER BY  AVG_Score

PARTITION BY 子句 + ROW_NUMBER() OVER(....) AS rn

PARTITION BY 理解成"分组"

SELECT * FROM(
    SELECT C.[ID]
      ,D.ID BID
      ,C.[ProjectID]
      ,C.[PersonID]
      ,C.[QuestionID]
      ,C.[QuestionName]
      ,C.[WeiduNo]
      ,C.[WeiduNAME]
      ,C.[RelationID]
      ,C.[RelationName]
      ,C.[Score]
      ,D.RelationName B_RelationName
      ,D.[Score] B_Score
      ,D.[Score] - C.Score Ta_Zi
      ,ROW_NUMBER() OVER
      (
        PARTITION BY D.ID,C.[ProjectID],C.[PersonID],C.[QuestionID]
        ORDER BY  C.[ID] 
      )
      AS rn
      FROM [tbiz_AssScoreAction] C
      INNER JOIN [tbiz_AssScoreAction] D
      ON C.QuestionID =    D.QuestionID AND (C.RelationID -30) = D.RelationID
      WHERE C.RelationID=30 AND C.ProjectID=65 AND C.PersonID=2430
) m WHERE rn = 1  ORDER BY  Ta_Zi

DECLARE

DECLARE @MedicalInstitutionID VARCHAR(max) SET @MedicalInstitutionID='SYS20130228000000012'
DECLARE @ResidentCardID VARCHAR(max) SET @ResidentCardID=NULL
DECLARE @Name VARCHAR(max) SET @Name=NULL
DECLARE @InDateLeft DATETIME SET @InDateLeft=NULL
DECLARE @InDateRight DATETIME SET @InDateRight=NULL
DECLARE @AcceptsOperatorID VARCHAR(max) SET @AcceptsOperatorID=NULL
DECLARE @AcceptsOperatorName VARCHAR(max) SET @AcceptsOperatorName=NULL
DECLARE @IsPrint INT SET @IsPrint=null

ALERT

ALTER TABLE [tbiz_person] ADD Position [nvarchar](40);

--1.查询EmailSendState约束
select name from sysobjects 
where parent_obj in(select id from sysobjects where name='tbiz_person') AND name Like 'DF__tbiz_pers__Email%'

--2.将那个约束名写在CONSTRAINT 后面 如 DF__tbiz_pers__Email__1367E606
ALTER TABLE [tbiz_person] DROP CONSTRAINT 约束名
--3.删除EmailSendState 字段, 转移到[tbiz_emailPostDetail] 的 IsSend 字段
ALTER TABLE [tbiz_person] DROP EmailSendState;

 怎样查询约束

SQL/T-SQL实例参考-2