解决方案 »
- SQL 排课查询
- 请求数据库高手能进群交流搜索这方面的数据库知道谢谢群号:17371752
- [求助 请问 SQL Server 2000 怎样在 Windows Server 2003 SP1 中安装????
- 大量图片如何存储?
- 微软工程师对SQL Server中可不可以指定某些语句不记录到日志问题的回复
- 简单的SQL问题?
- Why not the result what I wanted??
- 怎么搞的?关于SQL SERVER 2000的一个问题
- 存储过程中,要用到游标的话是不是不可以用EXECUTE执行SQL语句,帮我看一下源代码!
- access插入10W条记录花了很长时间。
- sql server2000 发布快照时无法访问
- 急!谁来帮帮我完成这个查询语句?
select
1 as id,0 as pid,'AO1-YM-001' as lev4,'NAME1' as name
into #Test
union all
select
2 as id,0 as pid,'AO1-YM-001-001' as lev4,'NAME2' as name
union all
select
3 as id,0 as pid,'AO1-YM-001-002' as lev4,'NAME3' as name
union all
select
4 as id,0 as pid,'AO1-YM-002' as lev4,'NAME4' as name
union all
select
5 as id,0 as pid,'AO1-YM-002-001' as lev4,'NAME5' as name
union all
select
6 as id,0 as pid,'AO1-YM-002-002' as lev4,'NAME6' as nameupdate #Test
set pid = isnull((select id from #Test a where a.lev4 = left(#Test.lev4,len(a.lev4)) and a.lev4 <> #Test.lev4),0)select * from #Testdrop table #Test/*
id pid lev4 name
----------- ----------- -------------- -----
1 0 AO1-YM-001 NAME1
2 1 AO1-YM-001-001 NAME2
3 1 AO1-YM-001-002 NAME3
4 0 AO1-YM-002 NAME4
5 4 AO1-YM-002-001 NAME5
6 4 AO1-YM-002-002 NAME6
*/
set a.pid=b.id
from tb a,tb b
where a.lev4 like b.lev4+'%' and a.lev4!=b.lev4
子查询返回的值多于一个。当子查询跟随在 =、!=、<、<=、>、>= 之后,或子查询用作表达式时,这种情况是不允许的。
语句已终止。
提示这个错误,上午的时候我也是出现这个
set pid = isnull(Test.pid,0)
from #Test
left join (
select #Test.id,(select top 1 id from #Test a where a.lev4 = left(#Test.lev4,len(a.lev4)) and a.lev4 <> #Test.lev4) as pid
from #Test
) Test
on Test.id = #Test.id
id pid lev4 name
1 0 AO1-YM-001 NAME1
2 0 AO1-YM-001-001 NAME2
3 0 AO1-YM-001-002 NAME3
4 0 AO1-YM-002 NAME4
5 0 AO1-YM-002-001 NAME5
6 0 AO1-YM-002-002 NAME6
7 0 AO1-YM NAME6
8 0 AO1 NAME6 结果
id pid lev name
1 7 AO1-YM-001 NAME1
2 1 AO1-YM-001-001 NAME2
3 1 AO1-YM-001-002 NAME3
4 7 AO1-YM-002 NAME4
5 4 AO1-YM-002-001 NAME5
6 4 AO1-YM-002-002 NAME6
7 8 AO1-YM NAME6
8 0 AO1 NAME6
不好意思,应该是这个样子,能帮忙改下吗?
源数据
id pid lev4 name
1 0 AO1-YM-001 NAME1
2 0 AO1-YM-001-001 NAME2
3 0 AO1-YM-001-002 NAME3
4 0 AO1-YM-002 NAME4
5 0 AO1-YM-002-001 NAME5
6 0 AO1-YM-002-002 NAME6
7 0 AO1-YM NAME6
8 0 AO1 NAME6 结果
id pid lev name
1 7 AO1-YM-001 NAME1
2 1 AO1-YM-001-001 NAME2
3 1 AO1-YM-001-002 NAME3
4 7 AO1-YM-002 NAME4
5 4 AO1-YM-002-001 NAME5
6 4 AO1-YM-002-002 NAME6
7 8 AO1-YM NAME6
8 0 AO1 NAME6
不好意思,应该是这个样子,能帮忙改下吗
set T.pid=T.Gid from
(select a.*,(select id from #test where lev4=(select max(lev4) from #test where a.lev4 like lev4+'%' and lev4<>a.lev4)) as Gid from #Test a) T
where t.Gid is not null select * from #Test
select
1 as id,0 as pid,'AO1-YM-001' as lev4,'NAME1' as name
into #Test
union all
select
2 as id,0 as pid,'AO1-YM-001-001' as lev4,'NAME2' as name
union all
select
3 as id,0 as pid,'AO1-YM-001-002' as lev4,'NAME3' as name
union all
select
4 as id,0 as pid,'AO1-YM-002' as lev4,'NAME4' as name
union all
select
5 as id,0 as pid,'AO1-YM-002-001' as lev4,'NAME5' as name
union all
select
6 as id,0 as pid,'AO1-YM-002-002' as lev4,'NAME6' as name
union all
select
7 as id,0 as pid,'AO1-YM' as lev4,'NAME7' as name
union all
select
8 as id,0 as pid,'AO1' as lev4,'NAME8' as nameupdate #Test
set pid = isnull(Test.pid,0)
from #Test
left join (
select #Test.id,(select top 1 id from #Test a where a.lev4 = left(#Test.lev4,len(a.lev4)) and a.lev4 <> #Test.lev4) as pid
from #Test
) Test
on Test.id = #Test.idselect * from #Testdrop table #Test/*
id pid lev4 name
----------- ----------- -------------- -----
1 7 AO1-YM-001 NAME1
2 1 AO1-YM-001-001 NAME2
3 1 AO1-YM-001-002 NAME3
4 7 AO1-YM-002 NAME4
5 4 AO1-YM-002-001 NAME5
6 4 AO1-YM-002-002 NAME6
7 8 AO1-YM NAME7
8 0 AO1 NAME8
*/