几个sql小知识

一、查询效率分析语句

/*
set statistics io on;
set statistics time on;
*/

 

二、 查出重复数据

    select [GUID] from @Tab 
   where [GUID] in (select [GUID] from @Tab group by [GUID] having count([GUID])>1)
   order by [GUID]

 

 

三、一个常用的想不来的语句且已被遗忘

 

UPDATE a SET a.SaleZQ = b.OrganizationName
FROM @WaitCheckImei a,EmoAsp.dbo.TB_ASP_Organization b,EmoAsp.dbo.TB_ASP_RetailStore c,EmoAsp.dbo.TB_ASP_Organization d
WHERE  LEFT(d.OrganizationCode, 12) = b.OrganizationCode AND c.RetailStoreID = a.RetailStoreID

四、when case的 嵌套

select A.Oid,A.Nick as Buyer,A.Content,A.Result,@SellerNick as SellerNick,A.AddTime ,case b.Result
                                                                                                             when 'good'        then '好评'
                                                                                                             when 'bad'         then '差评'
                                                                                                             when 'neutral'     then '中评'
                                                                                                                                else '未审核'
                                                                                                              end as Result
                                                                                                           ,case
                                                                                                           when b.oid is null or b.SellerNick is null then '未审核'
                                                                                                                                                else '审核'
                                                                                                      end as IsRate
                        from

 

五、高效分页的语句（2005以上版本）

  select *
from
(
select row_number() over(order by guid) as RowNum, TableName.*
from TableName) t
where
t.RowNum between 1120000 and 1120022

六、设置测试表

create Table 
		T_Product
		(Product_ID int
		,Product_Name nvarchar(50))

declare  @Product_ID int
declare @Product_Name nvarchar(50)
set @Product_ID=1
set @Product_Name=''
while @product_ID<30000
begin
	set @Product_ID=@Product_ID+1
	set @Product_Name=('win'+cast(@Product_ID as nvarchar(10)))
	insert into T_Product
				(Product_ID
				,Product_Name)
			select @Product_ID,@Product_Name
end

 

把一个数据库里面的表的数据复制到另一个数据库相同的表里面

insert into DB..abc select * from abc --其中abc为当前数据库的表

row_number（） 函数的使用

select top 100 row_number() over （order by guid）as row_name ，* from T_Tname

其中row_number函数用来生成行号，over是按照具体的类的顺序

7、查看sql server的所有数据库
 select name from master.dbo.sysdatabases