请教多表查询？ - 编程论坛

#2

xu20002007-02-05 23:36

select 姓名 from 表1，表2，表3.....
where 表1.姓名=表2.姓名 and 表3.姓名=表1.姓名

#3

zuoguangxin2007-02-05 23:38

那么问题2和问题3,如何解？

#4

xu20002007-02-05 23:40

同样的查询。

#5

zuoguangxin2007-02-05 23:49

太麻烦了，如果我有多个表，暂时不确定可能是4个或5个或50个，要用户具体选择，只想找基中两个表或超过两个表有相同的姓名，怎解？

#6

bygg2007-02-06 08:57

2 楼的不就是方法吗？

#7

Kendy1234562007-02-06 10:39

以下是引用zuoguangxin在2007-2-5 23:49:28的发言：
太麻烦了，如果我有多个表，暂时不确定可能是4个或5个或50个，要用户具体选择，只想找基中两个表或超过两个表有相同的姓名，怎解？

创造一个临时表

create table #Name
(
Name varchar(20)
)

Insert into #Name
select 姓名 from 表1
union all
Select 姓名 from 表2
....
union all
Select 姓名 from 表50
...
有1万零一个表就写1万个unin all

然后
Select 姓名= name, 相同次数 = count(*) from #Name group by name

然后前台代码来判断哪些出现2次哪些出现3次,4次,5次的...

#8

Kendy1234562007-02-06 11:36

增强一下功能给出姓名出现次数的同时给出在哪些表中出现

drop table #Name
create table #Name
(
Name varchar(20),
tbl varchar(10)
)

Insert into #Name
select
'A','表4'
union all
select 'A','表5'
union all
select 'V','表1'
union all
select 'V','表2'
union all
select 'V','表5'
union all
select 'C','表6'
union all
select 'A','表7'
union all
select 'V','表8'

delete #name

drop table #1
create table #1
(
ID int identity(1,1),
Name varchar(100),
cnt int,
tbl varchar(10),
MaxID int,
MinID int
)

insert into #1
select a.Name,b.cnt,a.tbl,0,0 from #name a join (
select Name, count(*) as cnt from #name group by name) b
on a.name = b.name

update #1 set MaxID = b.MaxID from #1 a
join (select Name, Max(ID) as MaxID from #1 group by Name) b
on a.name = b.name
update #1 set MinID = b.MinID from #1 a
join (select Name, Min(ID) as MinID from #1 group by Name) b
on a.name = b.name

select * from #1 where ID <> MaxID and cnt<>1
while @@RowCount <> 0
begin
Update #1 set tbl = a.tbl+','+b.tbl from #1 a join (select * from #1) b
on a.Name = b.Name and a.ID = a.MinID and b.ID = b.MaxID
and a.cnt <>1
delete #1 where ID = MaxID and MaxID <> MinID
update #1 set MaxID = b.MaxID from #1 a
join (select Name, Max(ID) as MaxID from #1 group by Name) b
on a.name = b.name
update #1 set MinID = b.MinID from #1 a
join (select Name, Min(ID) as MinID from #1 group by Name) b
on a.name = b.name
select * from #1 where ID <> MaxID and cnt <>1
end

select 姓名 ='Name', 出现次数 = cnt,出现在哪些表中 = tbl from #1