34,590
社区成员
发帖
与我相关
我的任务
分享
----------------------------------------------------------------
-- Author :TravyLee(物是人非事事休,欲语泪先流!)
-- Date :2012-11-07 09:05:30
-- Version:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 6.1 <X86> (Build 7600: )
--
----------------------------------------------------------------
--> 测试数据:[a]
if object_id('[a]') is not null drop table [a]
go
create table [a]([ID] varchar(3),[结果] int)
insert [a]
select '001',10 union all
select '002',12
--> 测试数据:[b]
if object_id('[b]') is not null drop table [b]
go
create table [b]([ID] varchar(3),[结果] int)
insert [b]
select '001',80 union all
select '002',90 union all
select '003',90
--> 测试数据:[c]
if object_id('[c]') is not null drop table [c]
go
create table [c]([ID] varchar(3),[结果] int)
insert [c]
select '002',50 union all
select '003',60
--> 测试数据:[d]
if object_id('[d]') is not null drop table [d]
go
create table [d]([ID] varchar(3),[结果] int)
insert [d]
select '002',50 union all
select '003',60 union all
select '004',60
go
--a表中结果大于10的,
--又在b表中结果大于80的id,
--又在c表中结果大于40,
--又在d表中结果大于等于50
select
[ID]
from
a
where
exists(select 1 from b where a.ID=b.ID and b.结果>=80)
and exists(select 1 from c where a.ID=c.ID and c.结果>40)
and exists(select 1 from d where a.ID=d.ID and d.结果>=50)
and a.结果>=10
----------------结果----------------------------
/*
ID
----
002
(1 行受影响)
*/
select ID from A where 结果>10
intersect
select ID from B where 结果>80
intersect
select ID from C where 结果>40
intersect
select ID from d where 结果>=50
select a.id
from a, b,c,d
where a.id=b.id and b.id=c.id and c.id=d.id
and a.结果>10 and b.结果>80 and c.结果>40 and d.结果>=50