sql总结

1.sql语句获取表中某个字段，并按照逗号分隔显示

--拼接条件DECLARE @strWhereStation NVARCHAR(4000);SET @strWhereStation = ( SELECT DISTINCT                                    STUFF(( SELECT ''''+ ','+''''                                                    + [StationName]                                            FROM    [Data].[BasicProcessInfo]                                                    WITH ( NOLOCK )                                            WHERE   [Enable] = 'OK'                                          FOR                                            XML PATH('')                                          ), 1, 1, '') AS jzhw                             FROM   [Data].[BasicProcessInfo] t                           );    IF(LEN(@strWhereStation)>0)      begin          SET @strWhereStation=RIGHT(@strWhereStation,LEN(@strWhereStation)-1) +''''    END      PRINT @strWhereStation;

2. sql逗号分隔字符串转换为表。

DECLARE @ApplyIDs NVARCHAR(MAX)      --工艺卡模板IDSET @ApplyIDs='1,2,3,4,5,6'SET @ApplyIDs=@ApplyIDs+',';--拆分申请模板ID 并插入临时表DECLARE @temp1 TABLE(ApplyID int );WITH    tb1AS ( SELECT @ApplyIDs as  ApplyID,CHARINDEX(',',@ApplyIDs) AS STA ,CHARINDEX(',', @ApplyIDs) AS LENSUNION ALLSELECT@ApplyIDs as  ApplyID,CHARINDEX(',', @ApplyIDs,STA + 1) AS STA ,CHARINDEX(',',@ApplyIDs,STA + 1) - STA- 1 AS LENSFROMtb1 AS tb1_2WHERE( STA <> 0 ))INSERT  INTO @temp1SELECTSUBSTRING(@ApplyIDs, STA - LENS, LENS) AS ApplyIDFROM  tb1 AS tb1_1WHERE ( STA <> 0 ) AND ApplyID = @ApplyIDs;--查询结果表SELECT * FROM @temp1

3.sql批量修改，删除

DECLARE @ApplyIDs NVARCHAR(MAX)      --工艺卡模板IDSET @ApplyIDs='1,2,3,4,5,6'--修改UPDATE  dbo.ApplyTemplateDataSET     Status = 1 ,        CheckDate = GETDATE()WHERE (charindex(','+RTRIM(ApplyID)+',' , ','+@ApplyIDs+',')>0)  ; --删除DELETE  dbo.ApplyTemplateDataWHERE (charindex(','+RTRIM(ApplyID)+',' , ','+@ApplyIDs+',')>0)  ;