[转载]精妙SQL语句介绍----(4)

求删除重复记录的sql语句？ T~Z@8tQ"  
怎样把具有相同字段的纪录删除，只留下一条。 ?6[%g+*6M  
例如，表test里有id,name字段 g7nlic  
如果有name相同的记录 只留下一条，其余的删除。 Jb>_-?eN  
name的内容不定，相同的记录数不定。 #qPUAj~%  
有没有这样的sql语句？ t@A*!u8  
============================== \/|W0fY!  
A:一个完整的解决方案： \*4FNd(Cj  
将重复的记录记入temp1表: 6 1E  
select [标志字段id],count(*) into temp1 from [表名] f iO  
group by [标志字段id] ^ Yd+RA  
having count(*)>1 Jd[+T%HY  
2、将不重复的记录记入temp1表: M.WTvVl1  
insert temp1 select [标志字段id],count(*) from [表名] group by [标志字段id] having count(*)=1 b 4E+q  
3、作一个包含所有不重复记录的表： j_d#JU<[%W  
select * into temp2 from [表名] where 标志字段id in(select 标志字段id from temp1) I4d]dfJ  
4、删除重复表: A4/$uYdo$  
delete [表名] :L8Q(_@~>  
5、恢复表： Z^K:H:OBW  
insert [表名] select * from temp2 K"j\5,#  
6、删除临时表:  
drop table temp1 #(X6*O  
drop table temp2 '9w'7?(&#  
================================ 6k 
B: i}jS J_6  
create table a_dist(id int,name varchar(20)) q[(^Og   
insert into a_dist values(1,'abc') j:'rA'/ +  
insert into a_dist values(1,'abc') wKyj>.[  
insert into a_dist values(1,'abc') bv"mi?>&S  
insert into a_dist values(1,'abc') >^:9x1|%fl  
exec up_distinct 'a_dist','id' zufnC?e3  
select * from a_dist gVIZ^.Go  
create procedure up_distinct(@t_name varchar(30),@f_key varchar(30)) $w{nJ^y  
--f_key表示是分组字段﹐即主键字段 $Z|"x1@  
as j <~W  
begin jg&^ Hg:(  
declare @max integer,@id varchar(30) ,@sql varchar(7999) ,@type integer rT:>U>w  
select @sql = 'declare cur_rows cursor for select '+@f_key+' ,count(*) from ' +@t_name +' group by ' +@f_key +' having count(*) > 1' 6S\5/9=  
exec(@sql) *b8M] v  
open cur_rows GcB|Ktod  
fetch cur_rows into @id,@max 
9$L^IG  
while @@fetch_status=0 WmQ\Nl'M  
begin EI\9:rkW=  
select @max = @max -1 ~ ?UO  
set rowcount @max VuwDRPH/  
select @type = xtype from syscolumns where id=object_id(@t_name) and name=@f_key {US]HX  
if @type=56 \I+}Cf/s  
select @sql = 'delete from '+@t_name+' where ' + @f_key+' = '+ @id [2
tN  
if @type=167 G\1f!!wn  
select @sql = 'delete from '+@t_name+' where ' + @f_key+' = '+''''+ @id +'''' #/]y_* 0  
exec(@sql) n5!1*Of` _  
fetch cur_rows into @id,@max DVUKcYS6N  
end I06+j  
close cur_rows T}jJ4 D&d  
deallocate cur_rows $zB=wH n  
set rowcount 0 !X3dzp3  
end j4[I 
select * from systypes } t Xx%!  
select * from syscolumns where id = object_id('a_dist') 01y %F  
行列转换--普通 } 674eO"  
假设有张学生成绩表(CJ)如下 0%i;
NNO  
Name Subject Result o ,&k(|8  
张三 语文 80 pL;Jb4*  
张三 数学 90 H5#THIYT?[  
张三 物理 85 %V94noG  
李四 语文 85 G[ b\ &}"  
李四 数学 92 ?lY1KkH  
李四 物理 82 MmgpdwM? {  
想变成 )w'R_jO L  
姓名 语文 数学 物理 W9"xF-%aD  
张三 80 90 85 /e '(K  
李四 85 92 82 .%U-Mc,#  
declare @sql varchar(4000) P =r;m t  
set @sql = 'select Name' N4T71if8  
select @sql = @sql + ',sum(case Subject when '''+Subject+''' then Result end) ['+Subject+']' :*]T!ImB  
from (select distinct Subject from CJ) as a cX"2zc  
select @sql = @sql+' from test group by name' 6>#b}nxvu  
exec(@sql) PO$jm'  
行列转换--合并 uy+FF:  
有表A, x-iP5Z{w  
id pid Koz"(EtmAD  
1 1 E[_(pZ%6  
1 2 Y&3p.

i  
1 3 `t3!Jui  
2 1 ) ^g<  
2 2 $ N\+R  
3 1 PD$A.d:9  
如何化成表B: Sik=/5  
id pid o4^eraZ  
1 1,2,3 +6q0R{  
2 1,2 f;k /rz  
3 1 1$  
创建一个合并的函数 zo+=OCvS  
create function fmerg(@id int) #d{Iz#  
returns varchar(8000) ?[<)nYl,LP  
as kIcj HL!  
begin .j 1SLp  
declare @str varchar(8000) #Don;gc CT  
set @str='' W&H+&x*  
select @str=@str+','+cast(pid as varchar) from 表A where id=@id ^%x\O:p  
set @str=right(@str,len(@str)-1) Cb@aZ b'  
return(@str) xjR?r- bm  
End =cfZW9  
go }adXia2  
--调用自定义函数得到结果 4%fVpN'62v  
select distinct id,dbo.fmerg(id) from 表A avZ88"SA  
如何取得一个数据表的所有列名 5=5Xpi  
方法如下：先从SYSTEMOBJECT系统表中取得数据表的SYSTEMID,然后再SYSCOLUMN表中取得该数据表的所有列名。 Lp62I3$'F  
SQL语句如下： ?5[on?]62@  
declare @objid int,@objname char(40) )C?AQ6]ua  
set @objname = 'tablename' \% B8-~  
select @objid = id from sysobjects where id = object_id(@objname) 3G!c!`*  
select 'Column_name' = name from syscolumns where id = @objid order by colid o]gJn|3O  
或 e!vW$Yv(  
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME ='users' ED;=6rT  
通过SQL语句来更改用户的密码 IkU %
i"  
修改别人的,需要sysadmin role #u*^ue,  
EXEC sp_password NULL, 'newpassword', 'User' A}NY6'FE-  
如果帐号为SA执行EXEC sp_password NULL, 'newpassword', sa H_yO35-  
怎么判断出一个表的哪些字段不允许为空？ 4
\R=QD  
select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where IS_NULLABLE='NO' and TABLE_NAME=tablename Gv".@'  
如何在数据库里找到含有相同字段的表？ Qq 
a. 查已知列名的情况 &]yB;  
SELECT b.name as TableName,a.name as columnname {lg)833  
From syscolumns a INNER JOIN sysobjects b `ckPxEEh  
ON a.id=b.id |6yz @  
AND b.type='U' T%|:'|n}  
AND a.name='你的字段名字' (OdnO,v>_  
未知列名查所有在不同表出现过的列名 lIQU9f  
Select o.name As tablename,s1.name As columnname =MQ}:I"Z>  
From syscolumns s1, sysobjects o )I+hLe8E  
Where s1.id = o.id #*u@o1 (  
And o.type = 'U' I,&C 7mID  
And Exists ( :& F;@,  
Select 1 From syscolumns s2 3B's^=O<  
Where s1.name = s2.name ob * ;d  
And s1.id <> s2.id |9D&@c[ H  
) mK8buukVS