看不明白你是怎么来计算的,按dt大小排序,如输入m 就找出m,n..不明白你找数据的规律是什么
计算方式是怎么样的
计算方式是怎么样的
解决方案 »
- sql操作两个数据库之间数据的变换后的结果异常
- 如何在数据库对一条语句进行统计?
- SQL2005存储过程样式
- 1000000行数据的表,如何快速得到第500000行到500200行之间的数据?
- 求批量修改语句
- 一个很简单但我不会的问题
- 附加数据库提示错误823
- 一个常用的分页存储过程p_splitpage 在sql2000上正常显示,但在sql2005上显示不出结果集?
- 关于使用like?
- 我想在sql server在full text search中去查询多国的语言,请问怎么办?
- 谁能看出这一句sql的问题,我佩服你,还给你加分
- 某数据库有多个"文件组",而每个"文件组"又有多个"数据文件",如何将该表建到某"文件组"下的"数据文件"下?谢谢!!
snold snnew dt
a b 1
m n 2
b c 3
c d 4
n 5 --snnew里为空
d e 6
e 7 --snnew里为空就是两者之间在替换,如第一条是用b换a,第一条是用c换b, 我想把所有相换的记录都找出来!
declare @t table(snold char(1),snnew char(1),dt int)
insert into @t(snold,snnew,dt)
select 'a','b',1
union all select 'm','n',2
union all select 'b','c',3
union all select 'c','d',4
union all select 'n',null,5
union all select 'd','e',6
union all select 'e',null,7--测试
declare @v char(1)
set @v='b'create table #t(bt int,sn char(1))
declare @i int
set @i=1
insert into #t select @i,@vwhile not exists(select 1 from #t where sn is null)
begin
set @i=@i+1
insert into #t
select @i,sn
from (
select sn=snold from @t
where snnew in (select sn from #t where bt=@i-1)
union
select sn=snnew from @t
where snold in (select sn from #t where bt=@i-1)
) a
endselect snold from @t where snold in (select sn from #t) order by dtdrop table #t--返回
snold
--------
a
b
c
d
e(所影响的行数为 5 行)
as
create table #t(bt int,sn char(1))
…………
drop table #t
returns @tb table (snold char(2))
as
begin
insert @tb select snold from tb where snold=@snold
while @@rowcount > 0
insert @tb select snold from tb
where (snnew in (select snold from @tb)
and snold not in (select snold from @tb)) return
end
谢谢!谁能帮我修改一下,谢谢!
-- SQL Server 2005 中的树形数据处理示例
-- 作者: 邹建(引用请保留此信息)
-- 2005.07
-- =====================================================-- =====================================================
-- 创建测试数据
-- =====================================================
if exists (select * from dbo.sysobjects where id = object_id(N'[tb]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [tb]
GO--示例数据
create table [tb]([id] int PRIMARY KEY,[pid] int,name nvarchar(20))
INSERT [tb] SELECT 1,0,N'中国'
UNION ALL SELECT 2,0,N'美国'
UNION ALL SELECT 3,0,N'加拿大'
UNION ALL SELECT 4,1,N'北京'
UNION ALL SELECT 5,1,N'上海'
UNION ALL SELECT 6,1,N'江苏'
UNION ALL SELECT 7,6,N'苏州'
UNION ALL SELECT 8,7,N'常熟'
UNION ALL SELECT 9,6,N'南京'
UNION ALL SELECT 10,6,N'无锡'
UNION ALL SELECT 11,2,N'纽约'
UNION ALL SELECT 12,2,N'旧金山'
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_cid]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_cid]
GO
-- =====================================================
-- 查询指定id的所有子
-- 邹建 2005-07(引用请保留此信息)-- 调用示例/*--调用(查询所有的子)
SELECT A.*,层次=B.[level]
FROM [tb] A,f_cid(2)B
WHERE A.[id]=B.[id]
--*/
-- =====================================================
CREATE FUNCTION f_cid(@id int)
RETURNS TABLE
AS
RETURN(
WITH ctb([id],[level])
AS(
SELECT [id],1 FROM [tb]
WHERE [pid]=@id
UNION ALL
SELECT A.[id],B.[level]+1
FROM [tb] A,ctb B
WHERE A.[pid]=B.[id])
SELECT * FROM ctb
--如果只显示最明细的子(下面没有子),则将上面这句改为下面的
--SELECT * FROM ctb A
--WHERE NOT EXISTS(
--SELECT 1 FROM [tb] WHERE [pid]=A.[id])
)
GO--调用(查询所有的子)
SELECT A.*,层次=B.[level]
FROM [tb] A,f_cid(2)B
WHERE A.[id]=B.[id]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_pid]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_pid]
GO
-- =====================================================
-- 查询指定id的所有父
-- 邹建 2005-07(引用请保留此信息)-- 调用示例/*--调用(查询所有的父)
SELECT A.*,层次=B.[level]
FROM [tb] A,[f_pid](2)B
WHERE A.[id]=B.[id]
--*/
-- =====================================================
CREATE FUNCTION [f_pid](@id int)
RETURNS TABLE
AS
RETURN(
WITH ptb([id],[level])
AS(
SELECT [pid],1 FROM [tb]
WHERE [id]=@id
AND [pid]<>0
UNION ALL
SELECT A.[pid],B.[level]+1
FROM [tb] A,ptb B
WHERE A.[id]=B.[id]
AND [pid]<>0)
SELECT * FROM ptb
)
GO--调用(查询所有的父)
SELECT A.*,层次=B.[level]
FROM [tb] A,[f_pid](7)B
WHERE A.[id]=B.[id]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_id]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_id]
GO
-- =====================================================
-- 级别及排序字段(树形分级显示)
-- 邹建 2005-07(引用请保留此信息)-- 调用示例/*--调用实现树形显示--调用函数实现分级显示
SELECT N'|'+REPLICATE('-',B.[level]*4)+A.name
FROM [tb] A,f_id()B
WHERE a.[id]=b.[id]
ORDER BY b.sid--当然,这个也可以根本不用写函数,直接排序即可
WITH stb([id],[level],[sid])
AS(
SELECT [id],1,CAST(RIGHT(10000+[id],4) as varchar(8000))
FROM [tb]
WHERE [pid]=0
UNION ALL
SELECT A.[id],B.[level]+1,B.sid+RIGHT(10000+A.[id],4)
FROM [tb] A,stb B
WHERE A.[pid]=B.[id])
SELECT N'|'+REPLICATE('-',B.[level]*4)+A.name
FROM [tb] A,stb B
WHERE a.[id]=b.[id]
ORDER BY b.sid
--*/
-- =====================================================
CREATE FUNCTION f_id()
RETURNS TABLE
AS
RETURN(
WITH stb([id],[level],[sid])
AS(
SELECT [id],1,CAST(RIGHT(10000+[id],4) as varchar(8000))
FROM [tb]
WHERE [pid]=0
UNION ALL
SELECT A.[id],B.[level]+1,B.sid+RIGHT(10000+A.[id],4)
FROM [tb] A,stb B
WHERE A.[pid]=B.[id])
SELECT * FROM stb
)
GO--调用函数实现分级显示
SELECT N'|'+REPLICATE('-',B.[level]*4)+A.name
FROM [tb] A,f_id()B
WHERE a.[id]=b.[id]
ORDER BY b.sid
GO-- =====================================================
-- 直接查询的应用实例
-- =====================================================-- =====================================================
-- 1. 每个叶子结点的 FullName
-- =====================================================
WITH stb([id],[FullName],[pid],[flag])
AS(
SELECT [id],CAST(RTRIM([name]) as nvarchar(4000)),[pid],1
FROM [tb] A
WHERE NOT EXISTS(
SELECT 1 FROM [tb]
WHERE [pid]=A.[id])
UNION ALL
SELECT A.[id],RTRIM(B.[name])+'/'+A.[FullName],B.[pid],A.flag+1
FROM stb A,[tb] B
WHERE A.[pid]=B.[id])
SELECT [id],[FullName] FROM stb A
WHERE NOT EXISTS(
SELECT * FROM stb
WHERE [id]=A.[id]
AND flag>A.flag)
ORDER BY [id]
GO-- =====================================================
-- 2. 每个结点的 FullName
-- =====================================================
WITH stb([id],[FullName],[pid],[flag])
AS(
SELECT [id],CAST(RTRIM([name]) as nvarchar(4000)),[pid],1
FROM [tb]
UNION ALL
SELECT A.[id],RTRIM(B.[name])+'/'+A.[FullName],B.[pid],A.flag+1
FROM stb A,[tb] B
WHERE A.[pid]=B.[id])
SELECT [id],[FullName] FROM stb A
WHERE NOT EXISTS(
SELECT * FROM stb
WHERE [id]=A.[id]
AND flag>A.flag)
ORDER BY [id]
GO-- =====================================================
-- 3. 树形显示数据
-- =====================================================
WITH stb([id],[level],[sid])
AS(
SELECT [id],1,CAST(RIGHT(10000+[id],4) as varchar(8000))
FROM [tb]
WHERE [pid]=0
UNION ALL
SELECT A.[id],B.[level]+1,B.sid+RIGHT(10000+A.[id],4)
FROM [tb] A,stb B
WHERE A.[pid]=B.[id])
SELECT N'|'+REPLICATE('-',B.[level]*4)+A.name
FROM [tb] A,stb B
WHERE a.[id]=b.[id]
ORDER BY b.sid
GO-- =====================================================
-- 4. 检查不规范的数据
-- =====================================================
WITH chktb([id],[pid],[level],[Path],[Flag])
AS(
SELECT [id],[pid],1,
CAST([id] as varchar(8000)),
CASE WHEN [id]=[pid] THEN 1 ELSE 0 END
FROM [tb]
UNION ALL
SELECT A.[id],B.[pid],B.[level]+1,
CAST(B.[Path]+' > '+RTRIM(A.[id]) as varchar(8000)),
CASE WHEN A.[id]=B.[pid] THEN 1 ELSE 0 END
FROM [tb] A,chktb B
WHERE A.[pid]=B.[id]
AND B.[Flag]=0)
SELECT * FROM chktb
WHERE [Flag]=1
ORDER BY [Path]
GO-- =====================================================
-- 5. 查询结点的所有子结点数
-- =====================================================
WITH sumtb([id],[level])
AS(
SELECT [pid],1
FROM [tb] A
WHERE [pid]<>0
UNION ALL
SELECT A.[pid],B.[level]+1
FROM [tb] A,sumtb B
WHERE A.[id]=B.[id]
AND A.[pid]<>0)
SELECT A.[id],ChildCounts=COUNT(b.[id])
FROM [tb] A
LEFT JOIN sumtb B
ON A.[id]=B.[id]
GROUP BY A.[id]
GO
-- =====================================================
-- 3. 树形显示数据
-- =====================================================
WITH stb([id],[level],[sid])
AS(
SELECT [id],1,CAST(RIGHT(10000+[id],4) as varchar(8000))
FROM [tb]
WHERE [pid]=0
UNION ALL
SELECT A.[id],B.[level]+1,B.sid+RIGHT(10000+A.[id],4)
FROM [tb] A,stb B
WHERE A.[pid]=B.[id])
SELECT N'|'+REPLICATE('-',B.[level]*4)+A.name,A.[id]
FROM [tb] A,stb B
WHERE a.[id]=b.[id]
ORDER BY b.sid
GO
-- =====================================================
-- 6. 查询结点的所有父结点数
-- =====================================================
WITH sumtb([id],[level],[ParentCounts])
AS(
SELECT [id],1,0
FROM [tb] A
WHERE [pid]=0
UNION ALL
SELECT A.[id],B.[level]+1,B.[ParentCounts]+1
FROM [tb] A,sumtb B
WHERE A.[pid]=B.[id])
SELECT * FROM sumtb
order by [ID]
GO
你的函数里是不可以用到INSERT这样的命令的,还是建议用一棵树的存储过程
create table t(snold char(1),snnew char(1),dt int)
insert into t(snold,snnew,dt)
select 'a','b',1
union all select 'm','n',2
union all select 'b','c',3
union all select 'c','d',4
union all select 'n',null,5
union all select 'd','e',6
union all select 'e',null,7
go--创建存储过程,(@old恒等于@new,只是为处理方便,一个向前递归,一个向后递归)
create proc p(@old char(1),@new char(1),@r varchar(20) output)
as
if @new='#'
begin
declare @t table(sn char(1))
declare @i int
set @i=len(@r)-len(replace(@r,',',''))
while @i>0
begin
insert into @t select substring(@r,@i*2-1,1)
set @i=@i-1
end
select a.* from @t a join t b on a.sn=b.snold order by b.dt
end else
begin
if exists(select 1 from t where snnew=@old)
begin
select @r=isnull(@r,@old)+','+snold,@old=snold from t where snnew=@old
exec p @old,@new,@r output
end
else
begin
select @r=isnull(@r,@new)+','+isnull(snnew,'#'),@new=isnull(snnew,'#') from t where snold=@new
exec p '',@new,@r output
end
end
go--测试
declare @c varchar(20)
exec p 'e','e',@c outputdrop table t
drop proc p--返回
sn
----
a
b
c
d
e