【文章內(nèi)容簡介】 639。,7)insert into hard values(39。C39。,39。239。,3)要求查詢出來的結(jié)果如下：qu co jeA 6 9 A 2 4 B 3 6 B 2 5 C 6 7 C 3 4就是要按qu分組，每組中取je最大的前2位！而且只能用一句sql語句?。elect * from hard a where je in(select top 2 je from hard b where = order by je) 求刪除重復(fù)記錄的sql語句？怎樣把具有相同字段的紀(jì)錄刪除，只留下一條。例如，表test里有id,name字段如果有name相同的記錄 只留下一條，其余的刪除。name的內(nèi)容不定，相同的記錄數(shù)不定。有沒有這樣的sql語句？============================== A:一個完整的解決方案：將重復(fù)的記錄記入temp1表: select [標(biāo)志字段id],count(*)into temp1 from [表名] group by [標(biāo)志字段id] having count(*)1將不重復(fù)的記錄記入temp1表: insert temp1 select [標(biāo)志字段id],count(*)from [表名] group by [標(biāo)志字段id] having count(*)=1作一個包含所有不重復(fù)記錄的表：select * into temp2 from [表名] where 標(biāo)志字段id in(select 標(biāo)志字段id from temp1)刪除重復(fù)表: delete [表名]恢復(fù)表：insert [表名] select * from temp2刪除臨時表: drop table temp1 drop table temp2 ================================ B: create table a_dist(id int,name varchar(20))insert into a_dist values(1,39。abc39。)insert into a_dist values(1,39。abc39。)insert into a_dist values(1,39。abc39。)insert into a_dist values(1,39。abc39。)exec up_distinct 39。a_dist39。,39。id39。select * from a_distcreate procedure up_distinct(@t_name varchar(30),@f_key varchar(30))f_key表示是分組字段﹐即主鍵字段 as begin declare @max integer,@id varchar(30),@sql varchar(7999),@type integer select @sql = 39。declare cur_rows cursor for select 39。+@f_key+39。 ,count(*)from 39。 +@t_name +39。 group by 39。 +@f_key +39。 having count(*) 139。 exec(@sql)open cur_rowsfetch cur_rows into @id,@max while @@fetch_status=0 beginselect @max = @max1 set rowcount @maxselect @type = xtype from syscolumns where id=object_id(@t_name)and name=@f_key if @type=56 select @sql = 39。delete from 39。+@t_name+39。 where 39。 + @f_key+39。 = 39。+ @id if @type=167 select @sql = 39。delete from 39。+@t_name+39。 where 39。 + @f_key+39。 = 39。+39。39。39。39。+ @id +39。39。39。39。 exec(@sql)fetch cur_rows into @id,@max endclose cur_rows deallocate cur_rows set rowcount 0 endselect * from systypes select * from syscolumns where id = object_id(39。a_dist39。) 行列轉(zhuǎn)換普通假設(shè)有張學(xué)生成績表(CJ)如下Name Subject Result 張三 語文 80 張三 數(shù)學(xué) 90 張三 物理 85 李四 語文 85 李四 數(shù)學(xué) 92 李四 物理 82想變成姓名 語文 數(shù)學(xué) 物理張三 80 90 85 李四 85 92 82declare @sql varchar(4000)set @sql = 39。select Name39。select @sql = @sql + 39。,sum(case Subject when 39。39。39。+Subject+39。39。39。 then Result end)[39。+Subject+39。]39。from(select distinct Subject from CJ)as aselect @sql = @sql+39。 from test group by name39。 exec(@sql)行列轉(zhuǎn)換合并有表A, id pid 1 1 1 2 1 3 2 1 2 2 3 1如何化成表B: id pid 1 1,2,3 2 1,2 3 1創(chuàng)建一個合并的函數(shù)create function fmerg(@id int)returns varchar(8000)as begindeclare @str varchar(8000)set @str=39。39。select @str=@str+39。,39。+cast(pid as varchar)from 表A where id=@id set @str=right(@str,len(@str)1)return(@str)End go調(diào)用自定義函數(shù)得到結(jié)果select distinct id,(id)from 表A 如何取得一個數(shù)據(jù)表的所有列名方法如下：先從SYSTEMOBJECT系統(tǒng)表中取得數(shù)據(jù)表的SYSTEMID,然后再SYSCOLUMN表中取得該數(shù)據(jù)表的所有列名。SQL語句如下：declare @objid int,@objname char(40)set @objname = 39。tablename39。select @objid = id from sysobjects where id = object_id(@objname)select 39。Column_name39。 = name from syscolumns where id = @objid order by colid或SELECT * FROM WHERE TABLE_NAME =39。users39。 通過SQL語句來更改用戶的密碼修改別人的,需要sysadmin roleEXEC sp_password NULL, 39。newpassword39。, 39。User39。如果帳號為SA執(zhí)行EXEC sp_password NULL, 39。newpassword39。, sa 怎么判斷出一個表的哪些字段不允許為空？select COLUMN_NAME from where IS_NULLABLE=39。NO39。 and TABLE_NAME=tablename 如何在數(shù)據(jù)庫里找到含有相同字段的表？SELECT as TableName, as columnname From syscolumns a INNER JOIN sysobjects b ON = AND =39。U39。AND =39。你的字段名字39。 未知列名查所有在不同表出現(xiàn)過的列名Select As tablename, As columnname From syscolumns s1, sysobjects o Where = And = 39。U39。 And Exists（Select 1 From syscolumns s2 Where = And ) 查詢第xxx行數(shù)據(jù)假設(shè)id是主鍵：select * from(select top xxx * from yourtable)aa where not exists(select 1 from(select top xxx1 * from yourtable)bb where =)如果使用游標(biāo)也是可以的fetch absolute [number] from [cursor_name] 行數(shù)為絕對行數(shù) SQL Server日期計算SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)SELECT DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)SELECT dateadd(ms,3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))SELECT dateadd(ms,3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))SELECT dateadd(ms,3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))select DATEADD(wk, DATEDIFF(wk,0, dateadd(dd,6datepart(day,getdate()),getdate())), 0)SELECT dateadd(ms,3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0))。 獲取表結(jié)構(gòu)[把 39。sysobjects39。 替換 成 39。tablename39。 即可]SELECT CASE IsNull(, 39。39。)When 39。39。 Then 39。39。 Else 39。*39。End as IsPK，Object_Name()as t_name, as c_name，IsNull(SubString(, 1, 254), 39。39。)as pbc_init, as F_DataType，CASE IsNull(TYPEPROPERTY(, 39。Scale39。), 39。39。)WHEN 39。39。 Then Cast( as varchar)ELSE Cast( as varchar)+ 39。,39。 + Cast( as varchar)END as F_Scale， as F_isNullAble FROM Syscolumns as A JOIN Systypes as TON( = AND = Object_id(39。sysobjects39。))LEFT JOIN(SysIndexes as I JOIN Syscolumns as A1ON( = and = object_id(39。sysobjects39。)and( amp。 0x800)= 0x800 AND ON( = AND = index_col(39。sysobjects39。, , ))LEFT JOIN SysComments as MON( = and ObjectProperty(, 39。IsConstraint39。)= 1)ORDER BY ASC 提取數(shù)據(jù)庫內(nèi)所有表的字段詳細(xì)說明的SQL語句SELECT(case when =1 then else 39。39。 end)N39。表名39。, N39。字段序號39。, N39。字段名39。，(case when COLUMNPROPERTY(,39。IsIdentity39。)=1 then 39。39。else 39。39。 end)N39。標(biāo)識39。，(case when(SELECT count(*)FROM sysobjects WHERE(name in(SELECT name FROM sysindexesWHERE(id = )AND(indid in(SELECT indidFROM sysindexkeysWHERE(id = )AND(colid in(SELECT colid FROM syscolumnsWHERE(id = )AND(name = )))))))AND(xtype = 39。PK39。))0 then 39。39。 else 39。39。 end)N39。主鍵39。, N39。類型39。， N39。占用字節(jié)數(shù)39。，COLUMNPROPERTY(,39。PRECISION39。)as N39。長度39。，isnull(COLUMNPROPERTY(,39。Scale39。),0)as N39。小數(shù)位數(shù)39。,(case when =1 then 39。39。else 39。39。 end)N39。允許空39。, isnull(,39。39。)N39。默認(rèn)值39。，isnull(g.[value],39。39。)AS N39。字段說明39。 FROM syscolumns a left join systypes b on = inner join sysobjects don = and =39。U39。 and 39。dtproperties39。 left join sysments e on =left join sysprop