不知道这个SQL该怎么写，请指教

BurmeHill 2010-10-01 09:29:46
遇到一个问题，需要只用一条SQL得到数据，请各位高手指点：
表AA有列E，G，数据如下，_表示空格（不知道怎么发表格，空格总是被自动删除）
E___G
1___m
2___m
3___m
4___n

表BB有列F，H，数据如下
F___H
1___x
1___y
2___x
2___z
3___x
3___y
3___z
4___x
4___z

表CC有列I，K，数据如下
I___K
11__x
11__z
22__..
22__..
33__..

表中AA.E与BB.F是主外键关系，BB.H与CC.K没有关系，但是其中的值可能是一样的。

现已知 AA.G=m, CC.I=11, 想用一条SQL得到AA.E=2的结果。

注意：当BB.H和CC.K进行比较时，这两列的记录数和值需要完全一样才是符合要求的。也就是说链接BB和CC的时候只有F=2，4才是符合要求的数据，再通过已知的AA.G=m链接到AA得到AA.E=2的结果。
...全文
87 13 点赞 打赏 收藏 举报
写回复
13 条回复
切换为时间正序
当前发帖距今超过3年，不再开放新的回复
发表回复
claro 2010-10-02
没看懂，学习。
  • 打赏
  • 举报
 回复 赞
SQLCenter 2010-10-02
双向否定

--> 测试数据：#aa

if object_id('tempdb.dbo.#aa') is not null drop table #aa

create table #aa(E int, G varchar(8))

insert into #aa

select 1, 'm' union all

select 2, 'm' union all

select 3, 'm' union all

select 4, 'n'

--> 测试数据：#bb

if object_id('tempdb.dbo.#bb') is not null drop table #bb

create table #bb(F int, H varchar(8))

insert into #bb

select 1, 'x' union all

--select 1, 'y' union all

select 2, 'x' union all

select 2, 'z' union all

select 3, 'x' union all

select 3, 'y' union all

select 3, 'z' union all

select 4, 'x' union all

select 4, 'z'

--> 测试数据：#cc

if object_id('tempdb.dbo.#cc') is not null drop table #cc

create table #cc(I int, K varchar(8))

insert into #cc

select 11, 'x' union all

select 11, 'z' union all

select 22, '..' union all

select 22, '..' union all

select 33, '..'



-- 双向否定

select * from #aa a where G='m' and exists

(

	select 1 from #bb b where F=a.E

	and not exists (select 1 from #bb where F=b.F and H not in (select K from #cc where I=11))

	and not exists (select 1 from #cc where I=11 and K not in (select H from #bb where F=b.F))

)



/*

E           G

----------- --------

2           m

*/
  • 打赏
  • 举报
 回复 赞
SQLCenter 2010-10-02
[Quote=引用 5 楼 dawugui 的回复:]

--不考虑你有重复数据如下:
SQL code
表BB有列F，H，数据如下
F___H
1___x
1___x
1___y
2___x
2___x
2___z
3___x
3___y
3___z
4___x
4___z



--需要使用双重否定.
SQL code
create table aa(E int, G varchar(8))
insert into aa
select ……
[/Quote]

还不一样仅仅是 CC.K 包含 BB.H，不是全匹配

是需要双重否定，但不是单向，而是双向否定。
  • 打赏
  • 举报
 回复 赞
dawugui 2010-10-02
[Quote=引用 4 楼 burmehill 的回复:]
非常感谢各位的回复，但是

1楼和2楼都没有完全匹配BB和CC表。在构造BB表的时候，如果去掉下面红色的那一行

表BB有列F，H，数据如下
F___H
1___x
1___y
2___x
2___z
3___x
3___y
3___z
4___x
4___z

1楼和2楼的SQL都会返回两行数据
E G
----------- -
1 m
2 m


现在正在研究3楼的回复。虽然还没有得到答案，仍然非常感谢。[/Quote]

按照你的需求,如果去掉1___y这行数据,结果就应该是:

E G   

---

1 m   

2 m
  • 打赏
  • 举报
 回复 赞
BurmeHill 2010-10-02
[Quote=引用 12 楼 sqlcenter 的回复:]
引用 11 楼 burmehill 的回复:

请教一下SQLCenter同学, 你觉得是3楼count方法效率高还是8楼的双向否定效率高啊。如果三个表的数据量都有10万左右的话。

AA表主键为E，BB表F，H为联合主键，CC表主键为I.


这个我也拿不准，感觉应该是8楼，你实测一下。
[/Quote]


在数据库实际跑了一下，8楼的双向否定效率高，快了不只一点半点，有数量级的区别。看来我还要加强对not exists的学习。
  • 打赏
  • 举报
 回复 赞
SQLCenter 2010-10-02
[Quote=引用 11 楼 burmehill 的回复:]

请教一下SQLCenter同学, 你觉得是3楼count方法效率高还是8楼的双向否定效率高啊。如果三个表的数据量都有10万左右的话。

AA表主键为E，BB表F，H为联合主键，CC表主键为I.
[/Quote]

这个我也拿不准，感觉应该是8楼，你实测一下。
  • 打赏
  • 举报
 回复 赞
BurmeHill 2010-10-02
请教一下SQLCenter同学, 你觉得是3楼count方法效率高还是8楼的双向否定效率高啊。如果三个表的数据量都有10万左右的话。

AA表主键为E，BB表F，H为联合主键，CC表主键为I.
  • 打赏
  • 举报
 回复 赞
BurmeHill 2010-10-02
非常感谢你，SQLCenter同学，我觉得你3楼和8楼的回复应该都满足我的要求，我自己思考的结果和你3楼的类似但是没有你的简洁，我构造了3个子查询来实现count的比较。你在8楼的回复是我不怎么会用的not exists，我完全理解not exists的意思和用法，但是一旦用在复杂的SQL中我就有点拿不准，不自信 ，总害怕有点问题，所以一般情况下能不用就不用。

6楼的dawugui同学，按照我题目的要求“当BB.H和CC.K进行比较时，这两列的记录数和值需要完全一样才是符合要求的”，如果去掉1___y这行数据,结果仍然应该只有一行返回。SQLCenter同学很好地实现了这一点。

我结贴的时候准备把分都给SQLCenter同学。谢谢！
  • 打赏
  • 举报
 回复 赞
dawugui 2010-10-01
--不考虑你有重复数据如下:
表BB有列F，H，数据如下

F___H

1___x

1___x

1___y

2___x

2___x

2___z

3___x

3___y

3___z

4___x

4___z


--需要使用双重否定.
create table aa(E int, G varchar(8))

insert into aa

select 1, 'm' union all

select 2, 'm' union all

select 3, 'm' union all

select 4, 'n'

create table bb(F int, H varchar(8))

insert into bb

select 1, 'x' union all

select 1, 'y' union all

select 2, 'x' union all

select 2, 'z' union all

select 3, 'x' union all

select 3, 'y' union all

select 3, 'z' union all

select 4, 'x' union all

select 4, 'z'

create table cc(I int, K varchar(8))

insert into cc

select 11, 'x' union all

select 11, 'z' union all

select 22, '..' union all

select 22, '..' union all

select 33, '..'



select distinct t1.* from aa t1 , bb t2 where t1.e = t2.f and t1.g = 'm' and t2.f not in

(

select m.f from bb m where not exists (select 1 from 

(select bb.*,cc.* from bb , cc where bb.H = cc.K) n where n.k = m.h) 

)



/*

E           G        

----------- -------- 

2           m



（所影响的行数为 1 行）

*/





drop table aa , bb , cc
  • 打赏
  • 举报
 回复 赞
BurmeHill 2010-10-01
非常感谢各位的回复，但是

1楼和2楼都没有完全匹配BB和CC表。在构造BB表的时候，如果去掉下面红色的那一行

表BB有列F，H，数据如下
F___H
1___x
1___y
2___x
2___z
3___x
3___y
3___z
4___x
4___z

1楼和2楼的SQL都会返回两行数据
E G
----------- -
1 m
2 m


现在正在研究3楼的回复。虽然还没有得到答案，仍然非常感谢。
  • 打赏
  • 举报
 回复 赞
SQLCenter 2010-10-01
BB.H 和 CC.K 完全匹配的用这条

--> 测试数据：#aa

if object_id('tempdb.dbo.#aa') is not null drop table #aa

create table #aa(E int, G varchar(8))

insert into #aa

select 1, 'm' union all

select 2, 'm' union all

select 3, 'm' union all

select 4, 'n'

--> 测试数据：#bb

if object_id('tempdb.dbo.#bb') is not null drop table #bb

create table #bb(F int, H varchar(8))

insert into #bb

select 1, 'x' union all

select 1, 'y' union all

select 2, 'x' union all

select 2, 'z' union all

select 3, 'x' union all

select 3, 'y' union all

select 3, 'z' union all

select 4, 'x' union all

select 4, 'z'

--> 测试数据：#cc

if object_id('tempdb.dbo.#cc') is not null drop table #cc

create table #cc(I int, K varchar(8))

insert into #cc

select 11, 'x' union all

select 11, 'z' union all

select 22, '..' union all

select 22, '..' union all

select 33, '..'



-- bb.H 和 cc.K 完全匹配的用这条

select * from #aa a where G='m' and exists

(

    select 1 from #bb b left join (select K from #cc where I=11) c on a.E=b.F and b.H=c.K

    group by b.F having count(1)=count(c.K) and count(c.K)=(select count(1) from #cc where I=11)

)
  • 打赏
  • 举报
 回复 赞
喜-喜 2010-10-01
use test

go

if object_id('test.dbo.AA') is not null drop table AA

-- 创建数据表

create table AA

(

E int,

G char(2)

)

go

--插入测试数据

insert into AA select 1,'m'

union all select 2,'m'

union all select 3,'m'

union all select 4,'n'

go

if object_id('test.dbo.BB') is not null drop table BB

-- 创建数据表

create table BB

(

F int,

H char(2)

)

go

--插入测试数据

insert into BB select 1,'x'

union all select 1,'y'

union all select 2,'x'

union all select 2,'z'

union all select 3,'x'

union all select 3,'y'

union all select 3,'z'

union all select 4,'x'

union all select 4,'z'

go

if object_id('test.dbo.CC') is not null drop table CC

-- 创建数据表

create table CC

(

I int,

K char(2)

)

go

--插入测试数据

insert into CC select 11,'x'

union all select 11,'z'

union all select 22,'x'

union all select 22,'y'

union all select 33,'y'

go

--代码实现



select distinct a.*

from AA a join BB b

on a.G='m' and a.E=b.F and not exists(

	select 1

	from BB

	where F=b.F and H not in(

		select K

		from CC

		where CC.I=11

	)

)





/*测试结果



E G

-------

2 m



(1 行受影响)

*/
  • 打赏
  • 举报
 回复 赞
SQLCenter 2010-10-01
--> 测试数据：#aa

if object_id('tempdb.dbo.#aa') is not null drop table #aa

create table #aa(E int, G varchar(8))

insert into #aa

select 1, 'm' union all

select 2, 'm' union all

select 3, 'm' union all

select 4, 'n'

--> 测试数据：#bb

if object_id('tempdb.dbo.#bb') is not null drop table #bb

create table #bb(F int, H varchar(8))

insert into #bb

select 1, 'x' union all

select 1, 'y' union all

select 2, 'x' union all

select 2, 'z' union all

select 3, 'x' union all

select 3, 'y' union all

select 3, 'z' union all

select 4, 'x' union all

select 4, 'z'

--> 测试数据：#cc

if object_id('tempdb.dbo.#cc') is not null drop table #cc

create table #cc(I int, K varchar(8))

insert into #cc

select 11, 'x' union all

select 11, 'z' union all

select 22, '..' union all

select 22, '..' union all

select 33, '..'



-- 这条语句求出F=2,4

select * from #bb b where not exists (select 1 from #bb where F=b.F and H not in (select K from #cc where I=11))

/*

F           H

----------- --------

2           x

2           z

4           x

4           z

*/



-- 联合起来一条SQL

select * from #aa a where G='m' and exists

(

    select 1 from #bb b where a.E=b.F and not exists (select 1 from #bb where F=b.F and H not in (select K from #cc where I=11))

)

/*

E           G

----------- --------

2           m

*/
  • 打赏
  • 举报
 回复 赞
相关推荐
Sql server之sql注入篇 　关于sql注入的危害在这里不多做介绍了，相信大家也知道其中的厉害关系。这里有一些sql注入的事件大家感兴趣可以看一下 　防范sql注入的方法无非有以下几种： 　1.使用类型安全的SQL参数 　2.使用参数化输入...
SQL server 随机数函数 不过还是希望老鸟们多多指教了，现在切入正题： 随机函数：rand() 在查询分析器中执行：select rand()，可以看到结果会是类似于这样的随机小数：0.36361513486289558， 像这样的小数在实际应用中用得不多，一般要取...
springboot+mybatis+sqlserver 这是个人搭建的基础开发框架，比较简单，可以自由拓展。里面还包含了部分钉钉api,有问题请留言指教小弟
SQL Server中关于基数估计计算预估行数的一些方法探讨 我们下面通过一些例子来初略了解一下，下面测试案例仅供参考，如有不足或肤浅的地方，敬请指教！ 下面实验测试的环境主要为SQL Server 2014 SP2 （Standard Edition (64-bit)） 具体版本号为12.0.5000
一条sql查询多个sum 怎么才能把多个sum都取到呢，菜鸟请大神们指教一下
SqlHelper MSSQL数据库辅助类 自己写的，不足之处请大家指教 //这句代码即可实现查询Users表内所有内容 List<User> list = helper.ExecuteList("select * from users"); //查询ID=1的用户 User user = helper.ExecuteObject("select * from users where id = 1"); //参数化...
一个简单的sql记事本 就是一个相对比较简单的SQL存取的实力,注释比较全面,应该都能看懂,本人新手,如果有问题,希望大家多多指教!
SQL 数据导入选择源表和源视图时的错误提示，求指教
Sql优化的方向分析（新手上路，老司机请尽情指教） Sql优化我个人理解是要在三方面进行 第一、 在查询方面尽量让Sql能够使用到索引，但也要按照业务添加一定量的索引，索引过多也会引起性能变慢。 第二、 使用第三方做缓存，让查询尽量走缓存，像...在写sql进行查询时...
求大神指教 安装好了SQL Server 2017后发现没有运行的文件（就是打不开） 不知道打开哪个（第一次用2017版的）
SQL 注入攻击介绍与测试案例 本文由博主威威喵原创，请多支持与指教。 本文首发于此 博主：威威喵|博客主页：https://blog.csdn.net/smile_running SQL注入攻击 SQL 注入是利用某些系统没有对用户输入的数据进行充分的检查，而在用户...
SQL_无法绑定由多个部分组成的标识符 本博客谨记载个人SQL学习中遇到的问题和体会，如有错误，恳请大家指教& 当遇到报错，消息4104，无法绑定由多个部分组成的标识符 "xxx.xxx"时的解决办法， 个人遇到两种情况 1 没有使用好‘完全限定列名’： ...
log4j打印到控制台输出sql语句 log4j错误打印到控制台 并且输出sql语句
SQL Server2017 安装完成后找不到启动项解决方案 SQL Server2017 安装完成后找不到启动项解决方案
初用Hibernate4写了个BaseDao实现请多多指教 //log.info("只有在事务管理下，才能通过 getCurrentSession() 得到 session ，如果显示这个信息，则说明事务管理工作起作用了。"); } catch(HibernateException ex) { session = sessionfactory....
SQL IN 子查询返回多个值 下午遇到一个问题，IN子查询返回多个值。网上查了很多文档，资料，都没收获。问了技术群的同僚，竟然还被嘲笑了。IN 怎么可能匹配多个字段呢！个人印象中曾经在哪里见到过，所以就觉得不服气。自己慢慢地去试，试...
一种只写 SQL、做配置完成复杂业务系统开发的方法 一看这标题，你肯定会认为基本不可能，或者认为，不写代码最多只能做一些简单业务场景实现。 常规企业及应用开发基本过程 为了达成我们的目标，先来看看常规企业级应用开发的基本过程： 第一步，数据库建表建字段。...
CTF实验吧-简单的sql注入【SQL注入关键词绕过】 首先尝试性的试了一个1， 然后试了一下0'='0，将所有值输出看看 这也证明了‘’没有被题目给过滤掉 接下来开始尝试and语句准备试出其表名，按实验吧的习惯，一般都是存在flag表的flag字段中，不过还是尝试...
ssm+maven+mysql+sql server多数据源使用（新手上路，欢迎大神留言指教） 1.首先咱先分析需求，如下：通过java同时连接mysql和sql server数据库，使得两个数据库数据实现功能开发。（此方法不是用注解来实现的） 2.ssm框架的搭建（这个是基础，有太多大神的博客可以参考） 3.引入jar包，...
火车订票系统（mfc+sql）希望能对大家有所帮助 谢谢 请大家多多指教
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

12.1w+

社区内容

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2010-10-01 09:29
社区公告
暂无公告