(2 行受影响State of Qatar*///利用存款和储蓄进程

假若有重复的字段为name,address,必要取得那五个字段独一的结果集
select identity(int,1,1) as autoid, * into #tmp from tablename
select min(autoid) as autoid into #tmp2 from #tmp group by
name,autoid
select * from #tmp where autoid in(select autoid from #tmp2)

–select * from sysreferences
–select object_name(constid),object_name(fkeyid) from sysreferences

— — (roy)生成

删去在此之前先用select语句查看要被删除的数据
select *
from 表 a
where exists(select * from 表 where  花费档案的次序=a.花费体系 and
时间>=dateadd(minute,-2,a.时间卡塔尔 and 时间<a.时间State of Qatar

–轻松的求差难题
alter proc p016
as
begin
  declare cur_1 cursor local for select * from a099 order by 1
        declare @id int,@qty int,
          @id1 int,@qty1 int,@diff int
  declare @tbl table(id int identity(1,1),qty int)
  open cur_1
  fetch cur_1 into @id ,@qty
  while @@fetch_status=0
  begin
    if @qty1<>0
    begin
      select @diff=@qty1-@qty
      select @qty=@qty1
      insert into @tbl(qty) values(@diff)
    end
    fetch cur_1 into @id1,@qty1
  end
  close cur_1  
  select * from @tbl
end

select distinct * into #tmp from tablenamedrop table tablenameselect
* into tablename from #tmpdrop table #tmp

delete from 表 a
where exists(select * from 表 where  花费档次=a.花费档期的顺序 and
时间>=dateadd(minute,-2,a.时间卡塔尔(قطر‎ and 时间<a.时间卡塔尔

exec p016

*/

代码

1、由表名求字段名
create proc up_008(@table varchar(20))
as
begin
        declare @sql varchar(99)
    select @sql=\’select name from syscolumns where id=object_id(\’
    select @sql=@sql+\’\’\’\’+@table+\’\’\’\’+\’)\’
    –select @sql
    exec(@SQL)
end

/*num name———– —-1 a2 b

举例该表供给删除重复的笔录(重复记录保留1条),能够按以下方法删除
select distinct * into #tmp from tablename
drop table tablename
select * into tablename from #tmp
drop table #tmp

4 求表中两行记录之差
—–求表中两条记下之差
create table a099
(
id int,
qty int
)

declare @max integer,@id integerdeclare cur_rows cursor local for
select 主字段,count(*) from 表名 group by 主字段 having count(*)
1open cur_rowsfetch cur_rows into @id,@maxwhile
@@fetch_status=0beginselect @max = @max -1set rowcount @maxdelete
from 表名 where 主字段 = @idfetch cur_rows into @id,@maxendclose
cur_rowsset rowcount 0

方法三

exec up_011

–查看结果select * from #t/*num name———– —-1 a2 b

删去重复数据sql语句
方法一

select * from a099
select * from a099 a,a099 b where a.id=b.id

Author

发表评论

电子邮件地址不会被公开。 必填项已用*标注