现有表:
id,bm
1 123
1 456
1 765
2 456
2 788
3 123
3 999
4 555
4 9991、当输入123或其中任意个bm字段内容条件查找时把所以的全找出来,规则是123 通过id 为1 把456 ,765找到,通过456和2组中的456关联把2组的所有找到,同过123 把3组中的999关联到4组。
2、能否通过sql 把这组数字整理成
id,bm
5 123
5 456
5 765
5 788
5 999
5 555
id,bm
1 123
1 456
1 765
2 456
2 788
3 123
3 999
4 555
4 9991、当输入123或其中任意个bm字段内容条件查找时把所以的全找出来,规则是123 通过id 为1 把456 ,765找到,通过456和2组中的456关联把2组的所有找到,同过123 把3组中的999关联到4组。
2、能否通过sql 把这组数字整理成
id,bm
5 123
5 456
5 765
5 788
5 999
5 555
先谢谢你
select DISTINCT bm from 表
where id in ( select DISTINCT id from 表 where bm
in( select bm from 表
where id in (select id from 表 where
id = '123' )))
drop table tb
gocreate table tb
(
id int ,
bm int
)insert into tb(id,bm) values(1 ,123)
insert into tb(id,bm) values(1 ,456)
insert into tb(id,bm) values(1 ,765)
insert into tb(id,bm) values(2 ,456)
insert into tb(id,bm) values(2 ,788)
insert into tb(id,bm) values(3 ,123)
insert into tb(id,bm) values(3 ,999)
insert into tb(id,bm) values(4 ,555)
insert into tb(id,bm) values(4 ,999)select * from tb where id in (select id from tb where bm = 123)drop table tid bm
----------- -----------
1 123
1 456
1 765
3 123
3 999(所影响的行数为 5 行)-----------------------------------------------------------------------------------select id = 5,bm from (select distinct bm as bm from tb) tid bm
----------- -----------
5 123
5 456
5 555
5 765
5 788
5 999
-------------------------------------------------------------------------------------select a.id , b.bm from
(select max(id) + 1 as id from tb) a,
(select distinct bm as bm from tb) bid bm
----------- -----------
5 123
5 456
5 555
5 765
5 788
5 999(所影响的行数为 6 行)
set @bm='123' --查询条件declare @t table (
id int,bm varchar(10)
)insert @t
select id,bm
from tablename
where bm=@bmwhile exists (
select 1
from tablename a,@t t
where ((a.id=t.id and a.bm<>t.bm) or (a.id<>t.id and a.bm=t.bm))
and not exists (select 1 from @t where id=a.id and bm=a.bm)
)
insert @t
select a.id,a.bm
from tablename a,@t t
where ((a.id=t.id and a.bm<>t.bm) or (a.id<>t.id and a.bm=t.bm))
and not exists (select 1 from @t where id=a.id and bm=a.bm)select distinct 5 as id,bm
from @t
(
id int ,
bm varchar(10)
)insert into @tablename(id,bm) values(1 ,123)
insert into @tablename(id,bm) values(1 ,456)
insert into @tablename(id,bm) values(1 ,765)
insert into @tablename(id,bm) values(2 ,456)
insert into @tablename(id,bm) values(2 ,788)
insert into @tablename(id,bm) values(3 ,123)
insert into @tablename(id,bm) values(3 ,999)
insert into @tablename(id,bm) values(4 ,555)
insert into @tablename(id,bm) values(4 ,999)declare @bm varchar(10)
set @bm='123' --查询条件declare @t table (
id int,bm varchar(10)
)insert @t
select id,bm
from @tablename
where bm=@bmwhile exists (
select 1
from @tablename a,@t t
where ((a.id=t.id and a.bm<>t.bm) or (a.id<>t.id and a.bm=t.bm))
and not exists (select 1 from @t where id=a.id and bm=a.bm)
)
insert @t
select a.id,a.bm
from @tablename a,@t t
where ((a.id=t.id and a.bm<>t.bm) or (a.id<>t.id and a.bm=t.bm))
and not exists (select 1 from @t where id=a.id and bm=a.bm)select distinct 5 as id,bm
from @t--结果
id bm
----------- ----------
5 123
5 456
5 555
5 765
5 788
5 999(所影响的行数为 6 行)
declare @a table(id int,bm int)
insert into @a
select 1,123
union all
select 1,456
union all
select 1,765
union all
select 2,456
union all
select 2,788
union all
select 3,123
union all
select 3,999
union all
select 4,555
union all
select 4,999declare @b table(bm int)
declare @n int
insert into @b
select 123
select @n=count(1)
from
(select distinct * from @a)twhile (select count(1) from @b)<@n
begin
insert into @b
select Bm
from @a
where id in(select id from @a where bm in(select * from @b))
end
select distinct max(a.id)+1 as id,b.bm
from @a a,@b b
group by b.bm