sql语句的常用逻辑处理

#table @table 的区别

#定义的表不drop不会释放

@定义的表会自动释放

创建#table有两种方式:

1.select * into #T from tablename

2.CREATE TABLE #tablename( 
coumnname1 NVARCHAR(36)
 )

创建@table也有两种方式用法同上

1.select * into @v from tablename

 2.DECLARE @V TABLE
            (
              columnname1 INT ,
              columnname 2 NVARCHAR(36)
            )

判断语句

DECLARE @flag int
SET @flag =0
if @flag=0
begin


PRINT('我是0');
end 
else if @flag=1
begin


PRINT('我是1');
end

用分组筛选批量重复

row_number over()生成的列可以作为查询条件。

实现方式：
在sqlserver下编写语句：
SELECT * FROM ( select     row_number() over(partition by ProjectName,BusinessModelName,City,mtime order by VersionStartTime DESC) as rownum ,* FROM (SELECT IndicatorValue,VersionStartTime,ProjectName,BusinessModelName,City,MONTH(StatisticalCycle) mtime  FROM  dbo.IncomeTargetAct WHERE PlanActDataType=2 AND StatisticalCycle >=CAST(CAST(YEAR(GETDATE()) AS VARCHAR(4)) as datetime)AND ValidStatus= 1 AND VersionEndTime IS NULL   AND OrganizationName=@OrgName ) AS T )AS at WHERE rownum = 1 

临时表绑定数据

 --写字楼计划监控  DECLARE @plan TABLE (Businessname [nvarchar](36), --PlanValue DECIMAL(18,4), --PlanTotalValue DECIMAL(18,4), --WarnValue DECIMAL(18,4) --)--绑定第一列INSERT INTO @plan  (column)select cname from table--绑定第二列  UPDATE   @plan  SET    PlanValue =(SELECT IndicatorValue FROM #T t WHERE t.bussinessmodename =Businessname AND t.Indicatorname='我')--绑定第三列  UPDATE   @plan  SET    PlanTotalValue =(SELECT IndicatorValue FROM #T t WHERE t.bussinessmodename =Businessname AND t.Indicatorname='实')  --绑定第四列  UPDATE   @plan  SET    WarnValue =(SELECT IndicatorValue FROM #T t WHERE t.bussinessmodename =Businessname AND t.Indicatorname='自')