sqlserver 常用操作

我不是女神ヾ 2023-10-06 10:54 38阅读 0赞

*  根据某个字段统计重复数

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

*  一次性查询多条数据使用游标遍历操作多个sql语句

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;

*  提取日期中的年月日及其他格式  
    提取年：datepart(yy,getdate())  
    提取月：datepart(mm,getdate())  
    提取日：datepart(dd,getdate())

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