这个sql怎么写啊？

[emilchan6k]

--找出A表我创建的客户中，开始使用时间（startUseDate）到现在为止，没有该客户由我创建的销售记录的客户

table A,客户表

clientId,name,creatorId,startUseDate

table B 销售表
id ,clientId,creatorId,createdate

[發糞塗牆]

好像好拗口，给点数据和期待结果吧

[emilchan6k]

修改一下描述，应该是： --找出A表我创建的客户中，开始使用时间（startUseDate）到现在为止，没有由我创建的销售记录的关联客户

[emilchan6k]

客户表 clientId,name,creatorId,startUseDate 1,AAA ,1,2013-09-29 2,BBB ,2,2013-09-29 table 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
*/

[kunshan]

左连接 即可

[spiritofdragon]

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）到现在为止，这个条件感觉没用，到现在为止，不就是所有数据么？

[--小F--]

----------------------------------------------------------------
-- 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关联？