银行id 客户代码 客户名称 原贷日期 贷款金额
1 001 张三 20080301 5000
1 001 张三 20080301 2000
2 001 张三 20080326 3000
3 001 张三 20080520 6000
4 002 李四 20080201 3000
5 003 王五 20080302 12000
5 003 王五 20080225 6000
5 003 王五 20080307 10000
6 004 张三 20090104 6000
7 005 赵柳 20090605 6000
8 005 赵柳 20090712 4000
9 006 韩伟 20090605 6000
。
要求：写一个SQL语句，筛选出 客户代码出现两次，并且对同一个客户代码，银行代码不同，且原贷日期在20070101以后的记录 感觉应该比较好弄，但是真正写起来，感觉确实不好写，高手，帮帮忙吧？
---------------------------------
-- Author: htl258(Tony)
-- Date : 2009-08-19 08:04:51
---------------------------------
--> 生成测试数据表:tbIf not object_id('[tb]') is null
Drop table [tb]
Go
Create table [tb]([银行id] int,[客户代码] int,[客户名称] nvarchar(2),[原贷日期] Datetime,[贷款金额] int)
Insert tb
Select 1,001,'张三','20080301',5000 union all
Select 1,001,'张三','20080301',2000 union all
Select 2,001,'张三','20080326',3000 union all
Select 3,001,'张三','20080520',6000 union all
Select 4,002,'李四','20080201',3000 union all
Select 5,003,'王五','20080302',12000 union all
Select 5,003,'王五','20080225',6000 union all
Select 5,003,'王五','20080307',10000 union all
Select 6,004,'张三','20090104',6000 union all
Select 7,005,'赵柳','20090605',6000 union all
Select 8,005,'赵柳','20090712',4000 union all
Select 9,006,'韩伟','20090605',6000
Go
--Select * from tb-->SQL查询如下:
select * from tb t where exists(select 1 from tb where 客户代码=t.客户代码 and 银行id<>t.银行id) and [原贷日期]>='20070101'
/*
银行id 客户代码 客户名称 原贷日期 贷款金额
----------- ----------- ---- ----------------------- -----------
1 1 张三 2008-03-01 00:00:00.000 5000
1 1 张三 2008-03-01 00:00:00.000 2000
2 1 张三 2008-03-26 00:00:00.000 3000
3 1 张三 2008-05-20 00:00:00.000 6000
7 5 赵柳 2009-06-05 00:00:00.000 6000
8 5 赵柳 2009-07-12 00:00:00.000 4000(6 行受影响)
*/
-->Author:wufeng4552【水族杰纶】
-->Date :2009-08-19 08:05:46
if not object_id('tempdb..#') is null
drop table tempdb..#
Go
Create table tempdb..#([银行id] int,[客户代码] nvarchar(3),[客户名称] nvarchar(2),[原贷日期] Datetime,[贷款金额] int)
Insert tempdb..#
select 1,N'001',N'张三','20080301',5000 union all
select 1,N'001',N'张三','20080301',2000 union all
select 2,N'001',N'张三','20080326',3000 union all
select 3,N'001',N'张三','20080520',6000 union all
select 4,N'002',N'李四','20080201',3000 union all
select 5,N'003',N'王五','20080302',12000 union all
select 5,N'003',N'王五','20080225',6000 union all
select 5,N'003',N'王五','20080307',10000 union all
select 6,N'004',N'张三','20090104',6000 union all
select 7,N'005',N'赵柳','20090605',6000 union all
select 8,N'005',N'赵柳','20090712',4000 union all
select 9,N'006',N'韩伟','20090605',6000
Go
select * from # t where exists(select 1 from # where 客户代码=t.客户代码 and 银行id<>t.银行id) and [原贷日期]>='20070101'
and (select count(*)from # where [客户代码]=t.[客户代码])=2
/*
银行id 客户代码 客户名称 原贷日期 贷款金额
----------- ---- ---- ----------------------- -----------
7 005 赵柳 2009-06-05 00:00:00.000 6000
8 005 赵柳 2009-07-12 00:00:00.000 4000(2 個資料列受到影響)
*/
-- Author: HEROWANG(让你望见影子的墙)
-- Date : 2009-08-19 08:17:57
---------------------------------
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
go
CREATE TABLE [tb] (银行id INT,客户代码 VARCHAR(3),客户名称 VARCHAR(4),原贷日期 DATETIME,贷款金额 INT)
INSERT INTO [tb]
SELECT 1,'001','张三','20080301',5000 UNION ALL
SELECT 1,'001','张三','20080301',2000 UNION ALL
SELECT 2,'001','张三','20080326',3000 UNION ALL
SELECT 3,'001','张三','20080520',6000 UNION ALL
SELECT 4,'002','李四','20080201',3000 UNION ALL
SELECT 5,'003','王五','20080302',12000 UNION ALL
SELECT 5,'003','王五','20080225',6000 UNION ALL
SELECT 5,'003','王五','20080307',10000 UNION ALL
SELECT 6,'004','张三','20090104',6000 UNION ALL
SELECT 7,'005','赵柳','20090605',6000 UNION ALL
SELECT 8,'005','赵柳','20090712',4000 UNION ALL
SELECT 9,'006','韩伟','20090605',6000;with wang as
(select * ,cnt=(select count(*) from tb where 客户代码=t.客户代码)
from tb t where exists(select 1 from tb where 客户代码=t.客户代码 and 银行id<>t.银行id))
select * from wang where cnt=2银行id 客户代码 客户名称 原贷日期 贷款金额 cnt
7 005 赵柳 2009-06-05 00:00:00.000 6000 2
8 005 赵柳 2009-07-12 00:00:00.000 4000 2