MySQL (C API)VC实例及代码下载

10.SQL语句技巧

1、一个SQL语句的问题:行列转换

select * from v_temp

上面的视图结果如下:

user_name role_name

-------------------------

系统管理员 管理员

feng 管理员

feng 一般用户

test 一般用户

想把结果变成这样:

user_name role_name

---------------------------

系统管理员 管理员

feng 管理员,一般用户

test 一般用户

===================

create table a_test(name varchar(20),role2 varchar(20))

insert into a_test values('李','管理員')

insert into a_test values('張','管理員')

insert into a_test values('張','一般用戶')

insert into a_test values('常','一般用戶')

create function join_str(@content varchar(100))

returns varchar(2000)

as

begin

declare @str varchar(2000)

set @str=''

select @str=@str+','+rtrim(role2) from a_test where [name]=@content

select @str=right(@str,len(@str)-1)

return @str

end

go

--调用：

select [name],dbo.join_str([name]) role2 from a_test group by [name]

--select distinct name,dbo.uf_test(name) from a_test

2、求助！快速比较结构相同的两表

结构相同的两表，一表有记录3万条左右，一表有记录2万条左右，我如何快速查找两表的不同记录？

============================

给你一个测试方法，从northwind中的orders表取数据。

select * into n1 from orders

select * into n2 from orders

select * 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

应该可以，而且将不同的记录的ID显示出来。

下面的适用于双方记录一样的情况，

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 ('10728','10730')

delete from n2 where orderID in ('11000','11001')

--*************************************************************

-- 双方都有该记录却不完全相同

select * from n1 where orderid in

(

select OrderID from (select * from n1

union

select * from n2) a group by OrderID having count(*) > 1

)

union

--n2中存在但在n1中不存的在10728,10730

select * from n1 where OrderID not in (select OrderID from n2)

union

--n1中存在但在n2中不存的在11000,11001

select * from n2 where OrderID not in (select OrderID from n1)

3、四种方法取表里n到m条纪录：

1).

select top m * into 临时表(或表变量) from tablename order by columnname -- 将top m笔插入

set rowcount n

select * from 表变量 order by columnname desc

2).

select top n * from

(select top m * from tablename order by columnname) a

order by columnname desc

3).如果tablename里没有其他identity列，那么：

select identity(int) id0,* into #temp from tablename

取n到m条的语句为：

select * from #temp where id0 >=n and id0 <= m

如果你在执行select identity(int) id0,* into #temp from tablename这条语句的时候报错,那是因为你的DB中间的select into/bulkcopy属性没有打开要先执行：

exec sp_dboption 你的DB名字,'select into/bulkcopy',true

4).如果表里有identity属性，那么简单：

select * from tablename where identitycol between n and m

5).怎么样删除一个表中重复的记录？

create table a_dist(id int,name varchar(20))

insert into a_dist values(1,'abc')

insert into a_dist values(1,'abc')

insert into a_dist values(1,'abc')

insert into a_dist values(1,'abc')

exec up_distinct 'a_dist','id'

select * from a_dist

create 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 = 'declare cur_rows cursor for select '+@f_key+' ,count(*) from ' +@t_name +' group by ' +@f_key +' having count(*) > 1'

exec(@sql)

open cur_rows

fetch cur_rows into @id,@max

while @@fetch_status=0

begin

select @max = @max -1

set rowcount @max

select @type = xtype from syscolumns where id=object_id(@t_name) and name=@f_key

if @type=56

select @sql = 'delete from '+@t_name+' where ' + @f_key+' = '+ @id

if @type=167

select @sql = 'delete from '+@t_name+' where ' + @f_key+' = '+''''+ @id +''''

exec(@sql)

fetch cur_rows into @id,@max

end

close cur_rows

deallocate cur_rows

set rowcount 0

end

select * from systypes

select * from syscolumns where id = object_id('a_dist')

 如果您对本文有任何疑问或者建议，请到讨论区发表您的意见: >> 论坛入口 <<