sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 开发DBA >

【学习笔记】Oracle SQL语句 取出两张数据表某些项不重复的数据

时间:2016-07-10 10:59   来源:Oracle研究中心   作者:惜分飞   点击:

天萃荷净 两表连接,取出其中某些项不重复的数据,开发DBA反映需求,有两张数据表,一张为省表、一张为市表,要求只取每一个省中的一个市出来

数据库脚步如下

省表:

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

结果如:

 

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle SQL语句 取出两张数据表某些项不重复的数据

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/783.html

Oracle研究中心

关键词:

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

Oracle SQL语句

取出Oracle数据库两张数据表某些项不重复的数据