create table c_storage (serial varchar(200)) insert c_storage select '20051202104824000113' union all select '20051202104824000112'select serial from ( select serial from c_storage union all select '20051202104824000112' union all select '20051202104824000273' union all select '20051202104824000379' union all select '20051202104824000466' union all select '20051202104824000591' union all select '20051202104824000675' union all select '20051202104824000764' union all select '20051202104824000844' union all select '20051202104824000901' union all select '20051202104824001056') a group by serial having count(serial)>=2drop table c_storage老兄,没有这个概念吧!你想想,是不是你的serial varchar(200)没有留到足够的长度喃?
serial -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 20051202104824000112(所影响的行数为 1 行) 这是我的结果!
union all select '20051202104824000273' 有255条左右时就出现下面错误:服务器: 消息 8621,级别 17,状态 1,行 1 内部查询处理器错误: 在查询优化过程中,查询处理器用尽了堆栈空间。
create table c_storage (serial varchar(200)) insert c_storage select '20051202104824000113' union all select '20051202104824000112'declare @t table (col varchar(20)) insert @t select '20051202104824000112' union all select '20051202104824000273' union all select '20051202104824000379' union all select '20051202104824000466' union all select '20051202104824000591' union all select '20051202104824000675' union all select '20051202104824000764' union all select '20051202104824000844' union all select '20051202104824000901' union all select '20051202104824001056'select serial from ( select serial from c_storage union all select * from @t ) a group by serial having count(serial)>=2drop table c_storage20051202104824000112(所影响的行数为 1 行)建立了个变量表@t来联合!
union all select '20051202104824000273' union all select '20051202104824000379' union all select '20051202104824000466' union all select '20051202104824000591' union all select '20051202104824000675' union all select '20051202104824000764' union all select '20051202104824000844' union all select '20051202104824000901' union all select '20051202104824001056'不知如何写成一个存储过程,把上面这些语句合在一起,作为一个变量,从储存过程返回重复的记录?这样的话,我可以在程序中执行存储过程就可以了。谢谢!
create PROCEDURE CP_morethantwoserial @tmpsql char(8000) --最长只能8000?再长怎么解决?改为text 可不可以? AS set nocount on declare @t table (col varchar(20))insert @t @tmpsql --出错,怎么办?select serial from ( select serial from c_storage union all select * from @t ) a group by serial having count(serial)>=2谢谢!
如果超过8000那么用多个变量来连接!请仔细研究!create table t (col varchar(30))insert t select '20051202104824000273' union all select '20051202104824000379' gocreate proc p_go @tablename sysname, @sql1 varchar(8000), @sql2 varchar(8000) as begin create table #(col varchar(30)) declare @s varchar(8000) set @s='insert #'+' '+@sql1+' '+@sql2 exec(@s) set @s=' select col from ( select col from '+@tablename+' union all select * from # ) a group by col having count(col)>=2' exec(@s)drop table # end goexec p_go 't', 'select ''20051202104824000273'' union all select ''20051202104824000379'' union all select ''20051202104824000466'' union all select ''20051202104824000591'' union all select ''20051202104824000675'' union all select ''20051202104824000764'' union all select ''20051202104824000844'' union all select ''20051202104824000901'' union all select ''20051202104824001056''', 'union all select ''20051202104824000591'' union all select ''20051202104824000675''' drop proc p_go drop table t col ------------------------------ 20051202104824000273 20051202104824000379 20051202104824000591 20051202104824000675(所影响的行数为 4 行)
(serial varchar(200))
insert c_storage
select '20051202104824000113' union all
select '20051202104824000112'select serial from (
select serial from c_storage union all
select '20051202104824000112'
union all select '20051202104824000273'
union all select '20051202104824000379'
union all select '20051202104824000466'
union all select '20051202104824000591'
union all select '20051202104824000675'
union all select '20051202104824000764'
union all select '20051202104824000844'
union all select '20051202104824000901'
union all select '20051202104824001056') a group by serial having count(serial)>=2drop table c_storage老兄,没有这个概念吧!你想想,是不是你的serial varchar(200)没有留到足够的长度喃?
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
20051202104824000112(所影响的行数为 1 行)
这是我的结果!
有255条左右时就出现下面错误:服务器: 消息 8621,级别 17,状态 1,行 1
内部查询处理器错误: 在查询优化过程中,查询处理器用尽了堆栈空间。
(serial varchar(200))
insert c_storage
select '20051202104824000113' union all
select '20051202104824000112'declare @t table
(col varchar(20))
insert @t
select '20051202104824000112'
union all select '20051202104824000273'
union all select '20051202104824000379'
union all select '20051202104824000466'
union all select '20051202104824000591'
union all select '20051202104824000675'
union all select '20051202104824000764'
union all select '20051202104824000844'
union all select '20051202104824000901'
union all select '20051202104824001056'select serial from (
select serial from c_storage
union all
select * from @t
) a group by serial having count(serial)>=2drop table c_storage20051202104824000112(所影响的行数为 1 行)建立了个变量表@t来联合!
union all select '20051202104824000379'
union all select '20051202104824000466'
union all select '20051202104824000591'
union all select '20051202104824000675'
union all select '20051202104824000764'
union all select '20051202104824000844'
union all select '20051202104824000901'
union all select '20051202104824001056'不知如何写成一个存储过程,把上面这些语句合在一起,作为一个变量,从储存过程返回重复的记录?这样的话,我可以在程序中执行存储过程就可以了。谢谢!
@tmpsql char(8000) --最长只能8000?再长怎么解决?改为text 可不可以?
AS
set nocount on
declare @t table
(col varchar(20))insert @t @tmpsql --出错,怎么办?select serial from (
select serial from c_storage
union all
select * from @t
) a group by serial having count(serial)>=2谢谢!
(col varchar(30))insert t
select '20051202104824000273' union all
select '20051202104824000379'
gocreate proc p_go
@tablename sysname,
@sql1 varchar(8000),
@sql2 varchar(8000)
as
begin
create table #(col varchar(30))
declare @s varchar(8000)
set @s='insert #'+' '+@sql1+' '+@sql2
exec(@s)
set @s='
select col from (
select col from '+@tablename+'
union all
select * from #
) a group by col having count(col)>=2'
exec(@s)drop table #
end
goexec p_go 't',
'select ''20051202104824000273''
union all select ''20051202104824000379''
union all select ''20051202104824000466''
union all select ''20051202104824000591''
union all select ''20051202104824000675''
union all select ''20051202104824000764''
union all select ''20051202104824000844''
union all select ''20051202104824000901''
union all select ''20051202104824001056''',
'union all select ''20051202104824000591''
union all select ''20051202104824000675'''
drop proc p_go
drop table t
col
------------------------------
20051202104824000273
20051202104824000379
20051202104824000591
20051202104824000675(所影响的行数为 4 行)