一个表中查询的问题？ - 编程论坛

#2

madpbpl2008-04-26 15:14

楼主是这个意思？
select id1 from A,B where A.姓名=B.姓名 and A.分类=B.分类

#3

飙马2008-04-26 16:56

不是这个意思，前面表述得不够清楚，我重新编辑了一下，你再看看。

#4

happynight2008-04-28 10:03

Create Table #A (id1 int,姓名 varchar(10),地址 varchar(10))
Create Table #B (id2 int,证件 varchar(10),分类 varchar(10))

INSERT INTO #A Values(1,'张三','测试')
INSERT INTO #A Values(1,'张三','测试')
INSERT INTO #A Values(2,'李四','测试')
--SELECT * FROM #A

INSERT INTO #B Values(1,'身份证','测试')
INSERT INTO #B Values(1,'身份证','测试')
INSERT INTO #B Values(2,'身份证','测试2')
--SELECT * FROM #B

SELECT id1,姓名,分类 FROM #A INNER JOIN #B ON #A.id1=#B.id2 GROUP BY id1,姓名,分类 Having Count(*)>=2

DROP TABLE #A
DROP TABLE #B

#5

provoke2008-04-29 01:51

select distinct id1 from A,B         --或select distinct id2--
where id1=id2 and id1 in (
    select id1 from A group by id1 having count(姓名)>=2)
and id2 in (
    select id2 from B group by id2 having count(分类)>=2)

#6

makebest2008-04-29 08:53

不知道A表和B表的主键是什么啊