#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='自')