查询SQL语句请教

[a785126172]

在一张表里得如下数据
ITEM_CODE ITEM_CODE_D
ADHGD076B1 DBF5BR0105
DBF5BR0105 CBF5BR


如何写一个查询的SQL 显示成
ITEM_CODE ITEM_CODE_D
ADHGD076B1 CBF5BR
谢谢了

[勿勿]

max() 和min（）可以解决你的问题，如果你想要更精确的答案 请把你的要求写出来

[cd731107]

--可能不行，例举的数据不够
update tb set ITEM_CODE='ADHGD076B1'
where ITEM_CODE in (select ITEM_CODE_D from tb)

[todayst]

你小子写个存储过程循环去找，折到一阶再往表里插，有写好的东西，找你老大要

[liang145]

create table tb
(ITEM_CODE nvarchar(50),ITEM_CODE_D nvarchar(50))
insert tb
select 'ADHGD076B1','C4CEBR' union all
select 'ADHGD076B1','DBF5BR0105' union all
select 'ADHGD076B1','DFNAAR0380' union all
select 'ADHGD076B1','F024AR' union all
select 'ADHGD076B1','F210AR' union all
select 'ADHGD076B1','F326AR' union all
select 'DBF5BR0105','CBF5BR'


create function GetPid(@ITEM_CODE_D nvarchar(50))returns nvarchar(50)  --找出根父节点
as
begin
   declare @Pid nvarchar(50)
   declare @L int
   set @Pid=''
   set @L=1
   ;with T as (select @L as L,ITEM_CODE,ITEM_CODE_D from tb where ITEM_CODE_D=@ITEM_CODE_D
               union all
               select L=@L+1,t2.ITEM_CODE,t2.ITEM_CODE_D from T as t1 join tb as t2
               on t1.ITEM_CODE=t2.ITEM_CODE_D)
   select top 1 @Pid=ITEM_CODE from T order by L desc
   return @Pid
end

select dbo.GetPid(t1.ITEM_CODE_D) as ITEM_CODE,t1.ITEM_CODE_D from tb as t1
Left Join tb as t2 on t1.ITEM_CODE_D=t2.ITEM_CODE where t2.ITEM_CODE is null

--ITEM_CODE                                          ITEM_CODE_D
---------------------------------------------------- --------------------------------------------------
--ADHGD076B1                                         C4CEBR
--ADHGD076B1                                         DFNAAR0380
--ADHGD076B1                                         F024AR
--ADHGD076B1                                         F210AR
--ADHGD076B1                                         F326AR
--ADHGD076B1                                         CBF5BR
--
--(6 row(s) affected)

楼主素不素要这样？

[liang145]

#10寫錯了一點~~

create function GetPid(@ITEM_CODE_D nvarchar(50))returns nvarchar(50)  --找出根父节点
as
begin
   declare @Pid nvarchar(50)
   declare @L int
   set @Pid=''
   set @L=1
   ;with T as (select @L as L,ITEM_CODE,ITEM_CODE_D from tb where ITEM_CODE_D=@ITEM_CODE_D
               union all
               select L=L+1,t2.ITEM_CODE,t2.ITEM_CODE_D from T as t1 join tb as t2
               on t1.ITEM_CODE=t2.ITEM_CODE_D)
   select top 1 @Pid=ITEM_CODE from T order by L desc
   return @Pid
end

[一十七]

	DROP TABLE ta

	create table ta(ITEM_CODE varchar(20), ITEM_CODE_D varchar(20))
	insert ta
	select 'ADHGD076B1','DBF5BR0105' union all
	select 'DBF5BR0105','CBF5BR' 
	
 
	SELECT distinct  BB=(SELECT ITEM_CODE FROM ta where ITEM_CODE_D<>'CBF5BR'),AA=(SELECT ITEM_CODE_D FROM ta WHERE ITEM_CODE <> 'ADHGD076B1') FROM ta

[AcHerat]

select max(ITEM_CODE),max(ITEM_CODE_D)
from tb

--不对用 min

[AcHerat]

啊哦！看差，楼主这样子的层数多么？

1 2
2 3
3 4

显示1, 4

[a785126172]

可能意思没表达清楚吧，这样的话如果数据多的话就不行了
比如说：ITEM_CODE    ITEM_CODE_D
      ADHGD076B1     C4CEBR
      ADHGD076B1     DBF5BR0105
      ADHGD076B1     DFNAAR0380
      ADHGD076B1     F024AR
      ADHGD076B1     F210AR
      ADHGD076B1     F326AR
      DBF5BR0105     CBF5BR
我要实现所述功能：
比如说：ITEM_CODE    ITEM_CODE_D
      ADHGD076B1     C4CEBR
     -- ADHGD076B1     DBF5BR0105
      ADHGD076B1     DFNAAR0380
      ADHGD076B1     F024AR
      ADHGD076B1     F210AR
      ADHGD076B1     F326AR
      ADHGD076B1     CBF5BR

[cd731107]

create table ta(ITEM_CODE varchar(20), ITEM_CODE_D varchar(20))
insert ta
select 'ADHGD076B1','DBF5BR0105' union all
select 'DBF5BR0105','CBF5BR' 

select 
(select ITEM_CODE from ta where ITEM_CODE not in (select ITEM_CODE_D from ta)),
(select ITEM_CODE_D from ta where ITEM_CODE_D not in (select ITEM_CODE from ta))

/*
                     
-------------------- --------------------
ADHGD076B1           CBF5BS

(1 行受影响)

*/

[cd731107]

[Quote=引用 3 楼 a785126172 的回复:]
SQL code
可能意思没表达清楚吧，这样的话如果数据多的话就不行了
比如说：ITEM_CODE ITEM_CODE_D
ADHGD076B1 C4CEBR
ADHGD076B1 DBF5BR0105
ADHGD076B1 DFNAAR0380
ADHGD076B1 F024AR
AD……
[/Quote]

select * from tb where ITEM_CODE not in (select ITEM_CODE_D from tb)

[a785126172]

豆子那样只是屏蔽掉并不能替换数据啊

可能意思没表达清楚吧，这样的话如果数据多的话就不行了
比如说：ITEM_CODE ITEM_CODE_D
ADHGD076B1 C4CEBR
ADHGD076B1 DBF5BR0105 ADHGD076B1 DFNAAR0380
ADHGD076B1 F024AR
ADHGD076B1 F210AR
ADHGD076B1 F326AR
DBF5BR0105 CBF5BR
上述是源数据，请看仔细
如下是我想要的结果
我要实现所述功能：
比如说：ITEM_CODE ITEM_CODE_D
ADHGD076B1 C4CEBR
-- ADHGD076B1 DBF5BR0105 这条数据替换掉
ADHGD076B1 DFNAAR0380
ADHGD076B1 F024AR
ADHGD076B1 F210AR
ADHGD076B1 F326AR
ADHGD076B1 CBF5BR---将DBF5BR0105 替换成 ADHGD076B1