sqlserver 常用操作

select column1,count(1) total from Table1 WHERE condition1=xxx GROUP BY column1 HAVING count(1)>1

select sum(total) total from (
select column1,count(1) total from Table1 WHERE condition1=xxx GROUP BY column1 HAVING count(1)>1
) t

SELECT * from (SELECT column1, column2, ROW_NUMBER()OVER(PARTITION BY column1,column2 ORDER BY column3) AS RN FROM Table1 where condition1=xxx) t WHERE t.RN>1

DELETE t from (SELECT column1, column2, ROW_NUMBER()OVER(PARTITION BY column1,column2 ORDER BY column3) AS RN FROM Table1 where condition1=xxx) t WHERE t.RN>1

DECLARE @recordNo INT,@id INT
  DECLARE rs CURSOR LOCAL SCROLL FOR
  select DISTINCT top 10 id from Table1 where condition1=xxx
  OPEN rs
  FETCH NEXT FROM rs INTO @id
  WHILE @@FETCH_STATUS = 0
  BEGIN                   
    update Table2 set column1=1,column2='xxx',updateTime=GetDate() where id=@id;
    
    SELECT @recordNo=max(recordNo)+1 FROM Table2;
    
    insert into Table2 (recordNo,tb1_id,column1,column2,createTime) select top 1 @recordNo,@id,column1,column2,GetDate() FROM Table2 WHERE tb1_id=@id;
    
    update Table3 set idVal=@recordNo where idName='Table2';
    
    UPDATE Table1 set column1='xxx',column2=xxx,updateTime=GetDate() where id=@id;    
    
  FETCH NEXT FROM rs INTO @id
  END
  CLOSE rs;

//按月统计数量
select datepart(mm,shijian) MONTH,count(1) from tb where shijian > '2020-01-01' GROUP BY datepart(mm,shijian) order by MONTH