mssql常用sql语句案例，亲测可用，复习必备

-------------------------循环例子---------------------------------------------------------------
declare @i int
set @i=1
while @i<=(select max(id) from dbo.Companyinfo)
begin
	
	set @i=@+1
end
-------------------------循环更新---------------------------------------------------------------
declare @i int,@count int,@manufacturer varchar(50),@sql nvarchar(50)
set @i=1
while @i<=944
begin
	select top 1 @manufacturer=manufacturer from mfr_brandid where id=@i
	select @count=count(1) from ProductInfo where manufacturer=@manufacturer
print @count
	update mfr_brandid set num=@count where id=@i
set @i=@i+1;
end
-------------------------游标例子---------------------------------------------------------------
declare @id int
declare cursor1 cursor for
select id from mb_member where email in(select email from products_buys where id in(17141,17145))
open cursor1
fetch next from cursor1 into @id
while @@fetch_status=0
begin
fetch next from cursor1 into @id 
end
close cursor1
-------------------------临时表---------------------------------------------------------------
create table #Tmp_ids
	(
		id int IDENTITY (1,1) not null, --创建列ID,并且每次新增一条记录就会加1
		WokNo                varchar(50),   
		primary key(ID)      --定义ID为临时表#Tmp的主键      
	);
-------------------------触发器只能执行一次的问题---------------------------------------------------------------
if exists(select flags from inserted where flags=1)
   begin
declare crs_xxmx scroll cursor	 /* 游标标识*/
for select dates,kmdm,jtotal,dtotal,[id] from inserted
where flags=1
open crs_xxmx
set @row=@@cursor_rows /* 得到记录集记录数*/
while @row >0
begin
fetch crs_xxmx into @dates ,@kmdm,@jtotal,@dtotal,@id

update kmmx set jtotal=k.jtotal-@jtotal,dtotal=k.dtotal-@dtotal
from kmmx k
where k.kmdm=@kmdm and month(k.dates)=month(@dates) 
and year(k.dates)=year(@dates)
set @row=@row-1
end 
close crs_xxmx
deallocate crs_xxmx
 	  end
--------------------------------------------------------------------------------------------------------------
select count(1) from dbo.products with(index(Products_MemberId),nolock)
create PROC [dbo].[sendemailmore]
AS
create table #emailmore(pid int identity(1,1),id int ,toemail varchar(250),adminid int,sen)
insert into #emailmore(id,email,adminid) values select 
--------------------------内连接更新语句---------------------------------------
update A SET A.type = B.type FROM talble1 A INNER JOIN table2 B ON A.id=B.id
--------------------------去重复语句---------------------------------------
delete from people
where peopleId in (select   peopleId from people group by   peopleId   having count(peopleId) > 1)
and rowid not in (select min(rowid) from   people group by peopleId having count(peopleId )>1)
--------------------------每日更新-------------------------------------
select id,adminid,createdate from products_buys where  adminid=0
select id,adminid from mb_member where  adminid=0
update products_buys set adminid=null where adminid=0
update mb_member set adminid=null where adminid=0
--------------------------sql生成随机-----------------------------------------------------------------------
declare @i int,@code varchar(50)
set @i=1
while @i<=500000
begin
	select @code=newid()
	insert into randomcode(code,id_isget) values(substring(@code,1,8),convert(varchar,@i)+'_0')
	set @i=@i+1
end
-----------------------公司信息表赋值随机字符------
declare @i int ,@code varchar(50),@id int 
set @i=1
while @i<=(select max(id) from dbo.CompanyInfo)
begin
	select top 1 @id=id,@code =code from dbo.randomcode where id_isget=convert(varchar,id)+'_0'
	update randomcode set id_isget=convert(varchar,id)+'_1' where id=@id
	update CompanyInfo set randomurl=@code where id=@i
	set @i=@i+1
	set @code=null
	set @id=null
end
-------------
exec sp_fulltext_database 'enable';
-----------------空字符null拼接
isnull(name,'')+id+'texss'
-------------------------------------------------删除url字段重复数据-------------------------------------------------
delete gil where ID not in (select max(ID) from gather_customers_alibaba_detail group by url)
--------------------------------------------------查看url字段重复情况-------------------------------------------------
SELECT COUNT(url) AS num,url
FROM detail
GROUP BY url
HAVING (COUNT(1) > 1)