【正文】 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。 +t_name +39。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。)exec up_distinct 39。)insert into a_dist values(1,39。有沒(méi)有這樣的sql語(yǔ)句？============================== A:一個(gè)完整的解決方案：將重復(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作一個(gè)包含所有不重復(fù)記錄的表：select * into temp2 from [表名] where 標(biāo)志字段id in(select 標(biāo)志字段id from temp1)刪除重復(fù)表: delete [表名]恢復(fù)表：insert [表名] select * from temp2刪除臨時(shí)表: drop table temp1 drop table temp2 ================================ B: create table a_dist(id int,name varchar(20))insert into a_dist values(1,39。,3)要求查詢出來(lái)的結(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語(yǔ)句?。elect * from hard a where je in(select top 2 je from hard b where = order by je),7)insert into hard values(39。,4)insert into hard values(39。,6)insert into hard values(39。,5)insert into hard values(39。,4)insert into hard values(39。,9)insert into hard values(39。,2)insert into hard values(39。,4)insert into hard values(39。,3)insert into hard values(39。 查詢數(shù)據(jù)的最大排序問(wèn)題（只能用一條語(yǔ)句寫(xiě)）CREATE TABLE hard(qu char(11),co char(11),je numeric(3, 0))insert into hard values(39。39。39。 = 39。delete from 39。 where 39。 having count(*) 139。 ,count(*)from 39。id39。abc39。abc39。,true，那么簡(jiǎn)單：select * from tablename where identitycol between n and m 1100139。1073039。select * into n1 from orders select * into n2 from ordersselect * from n1 select * from n2添加主鍵，然后修改n1中若干字段的若干條alter table n1 add constraint pk_n1_id primary key(OrderID)alter table n2 add constraint pk_n2_id primary key(OrderID)select OrderID from(select * from n1 union select * from n2)a group by OrderID having count(*) 1應(yīng)該可以，而且將不同的記錄的ID顯示出來(lái)。 select str=str+39。,39。,39。,39。,39。YYYY/MM/DD39。YYYY/MM39。YYYY/MM39。 說(shuō)明：兩張關(guān)聯(lián)表，刪除主表中已經(jīng)在副表中沒(méi)有的信息delete from info where not exists(select * from infobz where =) 說(shuō)明：外連接查詢(表名1：a 表名2：b)select , , , , , from a LEFT OUT JOIN b ON = 說(shuō)明：復(fù)制表(只復(fù)制結(jié)構(gòu),源表名：a 新表名：b)select * into b from a where 11 說(shuō)明：拷貝表(拷貝數(shù)據(jù),源表名：a 目標(biāo)表名：b)insert into b(a, b, c)select d,e,f from b。 說(shuō)明：日程安排提前五分鐘提醒select * from 日程安排 where datediff(39。 說(shuō)明：SQL:SELECT , , , FROM TABLE1，(SELECT , , PREV_UPD_DATEFROM(SELECT NUM, UPD_DATE, INBOUND_QTY, STOCK_ONHANDFROM TABLE2WHERE TO_CHAR(UPD_DATE,39。))X，(SELECT NUM, UPD_DATE, STOCK_ONHANDFROM TABLE2WHERE TO_CHAR(UPD_DATE,39。)|| 39。)1 FROM Handle a)管理員39。管理員39。一般用戶39。一般用戶39。,39。下面的適用于雙方記錄一樣的情況，select * from n1 where orderid in(select OrderID from(select * from n1 union select * from n2)a group by OrderID having count(*) 1)至于雙方互不存在的記錄是比較好處理的刪除n1,n2中若干條記錄delete from n1 where orderID in(39。)delete from n2 where orderID in(39。)*************************************************************雙方都有該記錄卻不完全相同select * from n1 where orderid in(select OrderID from(select * from n1 union select * from n2)a group by OrderID having count(*) 1)unionn2中存在但在n1中不存的在10728,10730 select * from n1 where OrderID not in(select OrderID from n2)unionn1中存在但在n2中不存的在11000,11001 select * from n2 where OrderID not in(select OrderID from n1) 如何刪除一個(gè)表中重復(fù)的記錄？create table a_dist(id int,name varchar(20))insert into a_dist values(1,39。)insert into a_dist values(1,39。)exec up_distinct 39。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。 +t_name +39。 exec(sql)open cur_rows fetch 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。 + f_key+39。+t_name+39。+39。+ id +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。A39。A39。A39。A39。B39。B39。B39。C39。C39。C39。 求刪除重復(fù)記錄的sql語(yǔ)句？怎樣把具有相同字段的紀(jì)錄刪除，只留下一條。abc39。abc39。a_dist39。declare cur_rows cursor for select 39。 group by 39。delete from 39。 = 39。 where 39。39。39。a_dist39。select sql = sql + 39。+Subject+39。+Subject+39。 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)建一個(gè)合并的函數(shù)create function fmerg(id int)returns varchar(8000)as begindeclare str varchar(8000)set str=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 select objid = id from sysobjects where id = object_id(objname)select 39。User39。 怎么判斷出一個(gè)表的哪些字段不允許為空？select COLUMN_NAME from where IS_NULLABLE=39。U39。 未知列名查所有在不同表出現(xiàn)過(guò)的列名Select As tablename, As columnname From syscolumns s1, sysobjects o Where = And = 39。 SQL Server日期計(jì)算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))。 替換 成 39。)When 39。 Else 39。)as pbc_init, as F_DataType，CASE IsNull(TYPEPROPERTY(, 39。)WHEN 39。 + Cast( as varchar)END as F_Scale， as F_isNullAble FROM Syscolumns as A JOIN Systypes as TON( = AND = Object_id(39。)and( amp。IsConstraint39。 end)N39。, N39。)=1 then 39。 end)N39。))0 then 39。 end)N39。 N39。)as N39。),0)as N39。else 39。, isnull(,39。isnull(g.[value],