取出两张表中相同的数据 oracle,两表连接，取出其中某些项不重复的数据 – 提供7*24专业数据库(Oracle,SQL Server,MySQL等)恢复和Oracle技术服务@Tel:+86 1...

有省、市数据库表，现在要求只取每一个省中的一个市出来

数据库脚步如下

省表：

CREATE TABLE [dbo].[mem_province](

[id] [int] NOT NULL,

1 [nvarchar](6) NOT NULL,

[name] [nvarchar](40) NULL,

CONSTRAINT [PK_mem_province] PRIMARY KEY CLUSTERED

(

1 ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

市表：

CREATE TABLE [dbo].[mem_city](

[id] [int] NOT NULL,

1 [nvarchar](6) NOT NULL,

[name] [nvarchar](50) NULL,

[provinceId] [nvarchar](6) NULL,

CONSTRAINT [PK_mem_city] PRIMARY KEY CLUSTERED

(

1 ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

执行sql语句为：

SELECT *

FROM   (

SELECT a.id aid,

a.code acode,

a.name aname,

b.id bid,

b.code bcode,

b.name bname,

b.provinceId bprovinceId

FROM   mem_province a left   JOIN

mem_city b

on  a.code= b.provinceId

)c

WHERE  NOT   EXISTS (

SELECT 1

FROM   (SELECT a.id aid,

a.code acode,

a.name aname,

b.id bid,

b.code bcode,

b.name bname,

b.provinceId bprovinceId

FROM   mem_province a LEFT   JOIN

mem_city b

on  a.code= b.provinceId)e

WHERE  e.bid

AND  e.aname = c.aname

) ORDER BY aid

结果如：