客户表 clientId,name,creatorId,startUseDate 1,AAA ,1,2013-09-29 2,BBB ,2,2013-09-29table B 销售表 id ,clientId,creatorId,createdate 1,1,1,2013-09-30 由于销售表只有A客户,且ceatorid都是1,则BBB是没有销售单据的客户
猜测一下是不是这样:SELECT * FROM A WHERE EXISTS ( SELECT 1 FROM b WHERE a.clientid=b.clientid AND b.createdate BETWEEN a.startUseDate AND GETDATE())
---------------------------------------------------------------- -- Author :DBA_Huangzj(發糞塗牆) -- Date :2013-09-30 16:57:05 -- Version: -- Microsoft SQL Server 2014 (CTP1) - 11.0.9120.5 (X64) -- Jun 10 2013 20:09:10 -- Copyright (c) Microsoft Corporation -- Enterprise Evaluation Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor) -- ---------------------------------------------------------------- --> 测试数据:[A] if object_id('[A]') is not null drop table [A] go create table [A]([clientId] int,[name] varchar(3),[creatorId] int,[startUseDate] datetime) insert [A] select 1,'AAA',1,'2013-09-29' union all select 2,'BBB',2,'2013-09-29' --> 测试数据:[B] if object_id('[B]') is not null drop table [B] go create table [B]([id] int,[clientId] int,[creatorId] int,[createdate] datetime) insert [B] select 1,1,1,'2013-09-30' --------------开始查询--------------------------SELECT * FROM A WHERE EXISTS ( SELECT 1 FROM b WHERE a.clientid=b.clientid AND b.createdate BETWEEN a.startUseDate AND GETDATE()) ----------------结果---------------------------- /* clientId name creatorId startUseDate ----------- ---- ----------- ----------------------- 1 AAA 1 2013-09-29 00:00:00.000 */
select * from A a where creatorId=@我 and not exists(select 1 from B b where creatorId=a.creatorId and clientId=a.clientId and createdate>=a.startUseDate) --开始使用时间(startUseDate)到现在为止,这个条件感觉没用,到现在为止,不就是所有数据么?
---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2013-09-30 17:05:05 -- Verstion: -- Microsoft SQL Server 2012 - 11.0.2100.60 (X64) -- Feb 10 2012 19:39:15 -- Copyright (c) Microsoft Corporation -- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) -- ---------------------------------------------------------------- --> 测试数据:[a] if object_id('[a]') is not null drop table [a] go create table [a]([clientId] int,[name] varchar(3),[creatorId] int,[startUseDate] datetime) insert [a] select 1,'AAA',1,'2013-09-29' union all select 2,'BBB',2,'2013-09-29' --> 测试数据:[b] if object_id('[b]') is not null drop table [b] go create table [b]([id] int,[clientId] int,[creatorId] int,[createdate] datetime) insert [b] select 1,1,1,'2013-09-30' --------------开始查询-------------------------- SELECT * FROM a WHERE NOT EXISTS(SELECT 1 FROM b WHERE clientId=a.clientId AND b.createdate BETWEEN a.startUseDate AND GETDATE()) ----------------结果---------------------------- /* clientId name creatorId startUseDate ----------- ---- ----------- ----------------------- 2 BBB 2 2013-09-29 00:00:00.000(1 行受影响) */
---------------------------------------------------------------- -- Author :DBA_Huangzj(發糞塗牆) -- Date :2013-09-30 16:57:05 -- Version: -- Microsoft SQL Server 2014 (CTP1) - 11.0.9120.5 (X64) -- Jun 10 2013 20:09:10 -- Copyright (c) Microsoft Corporation -- Enterprise Evaluation Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor) -- ---------------------------------------------------------------- --> 测试数据:[A] if object_id('[A]') is not null drop table [A] go create table [A]([clientId] int,[name] varchar(3),[creatorId] int,[startUseDate] datetime) insert [A] select 1,'AAA',1,'2013-09-29' union all select 2,'BBB',2,'2013-09-29' --> 测试数据:[B] if object_id('[B]') is not null drop table [B] go create table [B]([id] int,[clientId] int,[creatorId] int,[createdate] datetime) insert [B] select 1,1,1,'2013-09-30' --------------开始查询--------------------------SELECT * FROM A WHERE not EXISTS ( SELECT 1 FROM b WHERE a.[creatorId]=b.[creatorId] AND b.createdate BETWEEN a.startUseDate AND GETDATE()) ----------------结果---------------------------- /* clientId name creatorId startUseDate ----------- ---- ----------- ----------------------- 2 BBB 2 2013-09-29 00:00:00.000 */ 就近用哪个ID关联?
--找出A表我创建的客户中,开始使用时间(startUseDate)到现在为止,没有由我创建的销售记录的关联客户
clientId,name,creatorId,startUseDate
1,AAA ,1,2013-09-29
2,BBB ,2,2013-09-29table B 销售表
id ,clientId,creatorId,createdate
1,1,1,2013-09-30
由于销售表只有A客户,且ceatorid都是1,则BBB是没有销售单据的客户
FROM A
WHERE EXISTS (
SELECT 1 FROM b WHERE a.clientid=b.clientid
AND b.createdate BETWEEN a.startUseDate AND GETDATE())
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-09-30 16:57:05
-- Version:
-- Microsoft SQL Server 2014 (CTP1) - 11.0.9120.5 (X64)
-- Jun 10 2013 20:09:10
-- Copyright (c) Microsoft Corporation
-- Enterprise Evaluation Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据:[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([clientId] int,[name] varchar(3),[creatorId] int,[startUseDate] datetime)
insert [A]
select 1,'AAA',1,'2013-09-29' union all
select 2,'BBB',2,'2013-09-29'
--> 测试数据:[B]
if object_id('[B]') is not null drop table [B]
go
create table [B]([id] int,[clientId] int,[creatorId] int,[createdate] datetime)
insert [B]
select 1,1,1,'2013-09-30'
--------------开始查询--------------------------SELECT *
FROM A
WHERE EXISTS (
SELECT 1 FROM b WHERE a.clientid=b.clientid
AND b.createdate BETWEEN a.startUseDate AND GETDATE())
----------------结果----------------------------
/*
clientId name creatorId startUseDate
----------- ---- ----------- -----------------------
1 AAA 1 2013-09-29 00:00:00.000
*/
and not exists(select 1 from B b where creatorId=a.creatorId and clientId=a.clientId and createdate>=a.startUseDate)
--开始使用时间(startUseDate)到现在为止,这个条件感觉没用,到现在为止,不就是所有数据么?
-- Author :fredrickhu(小F,向高手学习)
-- Date :2013-09-30 17:05:05
-- Verstion:
-- Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
-- Feb 10 2012 19:39:15
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[a]
if object_id('[a]') is not null drop table [a]
go
create table [a]([clientId] int,[name] varchar(3),[creatorId] int,[startUseDate] datetime)
insert [a]
select 1,'AAA',1,'2013-09-29' union all
select 2,'BBB',2,'2013-09-29'
--> 测试数据:[b]
if object_id('[b]') is not null drop table [b]
go
create table [b]([id] int,[clientId] int,[creatorId] int,[createdate] datetime)
insert [b]
select 1,1,1,'2013-09-30'
--------------开始查询--------------------------
SELECT * FROM a WHERE NOT EXISTS(SELECT 1 FROM b WHERE clientId=a.clientId AND b.createdate BETWEEN a.startUseDate AND GETDATE())
----------------结果----------------------------
/* clientId name creatorId startUseDate
----------- ---- ----------- -----------------------
2 BBB 2 2013-09-29 00:00:00.000(1 行受影响)
*/
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-09-30 16:57:05
-- Version:
-- Microsoft SQL Server 2014 (CTP1) - 11.0.9120.5 (X64)
-- Jun 10 2013 20:09:10
-- Copyright (c) Microsoft Corporation
-- Enterprise Evaluation Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据:[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([clientId] int,[name] varchar(3),[creatorId] int,[startUseDate] datetime)
insert [A]
select 1,'AAA',1,'2013-09-29' union all
select 2,'BBB',2,'2013-09-29'
--> 测试数据:[B]
if object_id('[B]') is not null drop table [B]
go
create table [B]([id] int,[clientId] int,[creatorId] int,[createdate] datetime)
insert [B]
select 1,1,1,'2013-09-30'
--------------开始查询--------------------------SELECT *
FROM A
WHERE not EXISTS (
SELECT 1 FROM b WHERE a.[creatorId]=b.[creatorId]
AND b.createdate BETWEEN a.startUseDate AND GETDATE())
----------------结果----------------------------
/*
clientId name creatorId startUseDate
----------- ---- ----------- -----------------------
2 BBB 2 2013-09-29 00:00:00.000
*/
就近用哪个ID关联?