where条件应该在子查询里(select 帐户姓名 from dbo.零售交易流水_保税新开户where datediff(day,a.开户日期,b.开户日期) between 0 and 2 and b.帐户姓名 is not null group by 帐户姓名 having count(帐户姓名)>=3 )
--本人想法简单不知道是这个意思否select 帐号,姓名,开户日期 from tb a where exists(select 1 from tb where a.姓名=b.姓名 and datediff(d,a.开户日期,开户日期)=3 group by 姓名 having count(姓名)>=3 )
-------------------------------------------------------------------------- -- Author : htl258(Tony) -- Date : 2010-04-22 00:27:09 -- 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 5.1 <X86> (Build 2600: Service Pack 2) -------------------------------------------------------------------------- --> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL DROP TABLE [tb] GO CREATE TABLE [tb]([帐号] NVARCHAR(10),[姓名] NVARCHAR(10),[开户日期] DATETIME) INSERT [tb] SELECT '001',N'王名','20090109' UNION ALL SELECT '002',N'张方','20090201' UNION ALL SELECT '123',N'张方','20090201' UNION ALL SELECT '345',N'张方','20090201' UNION ALL SELECT '342',N'张方','20090203' UNION ALL SELECT '456',N'王名','20090103' UNION ALL SELECT '453',N'张方','20090501' UNION ALL SELECT '432',N'张方','20090502' UNION ALL SELECT '356',N'张方','20090503' UNION ALL SELECT '351',N'张方','20090503' UNION ALL SELECT '578',N'刘二','20090503' UNION ALL SELECT '570',N'刘二','20090504' UNION ALL SELECT '340',N'刘二','20090509' GO --SELECT * FROM [tb]-->SQL查询如下: select * from tb where 姓名 in( select 姓名 from tb t where exists( select 1 from tb a where 姓名=t.姓名 and abs(datediff(dd,开户日期,t.开户日期))=1 having count(1)>=3)) /* 帐号 姓名 开户日期 ---------- ---------- ----------------------- 002 张方 2009-02-01 00:00:00.000 123 张方 2009-02-01 00:00:00.000 345 张方 2009-02-01 00:00:00.000 342 张方 2009-02-03 00:00:00.000 453 张方 2009-05-01 00:00:00.000 432 张方 2009-05-02 00:00:00.000 356 张方 2009-05-03 00:00:00.000 351 张方 2009-05-03 00:00:00.000(8 行受影响) */
-------------------------------------------------------------------------- -- Author : htl258(Tony) -- Date : 2010-04-24 00:08:41 -- 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 5.1 <X86> (Build 2600: Service Pack 2) -- Blog : http://blog.csdn.net/htl258 -------------------------------------------------------------------------- --> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL DROP TABLE [tb] GO CREATE TABLE [tb]([帐号] NVARCHAR(10),[姓名] NVARCHAR(10),[开户日期] DATETIME) INSERT [tb] SELECT '001',N'王名','20090109' UNION ALL SELECT '002',N'张方','20090201' UNION ALL SELECT '123',N'张方','20090201' UNION ALL SELECT '345',N'张方','20090201' UNION ALL SELECT '342',N'张方','20090203' UNION ALL SELECT '456',N'王名','20090103' UNION ALL SELECT '453',N'张方','20090501' UNION ALL SELECT '432',N'张方','20090502' UNION ALL SELECT '356',N'张方','20090503' UNION ALL SELECT '351',N'张方','20090503' UNION ALL SELECT '578',N'刘二','20090503' UNION ALL SELECT '570',N'刘二','20090504' UNION ALL SELECT '340',N'刘二','20090509' UNION ALL SELECT '789',N'张方','20090509' UNION ALL SELECT '560',N'张方','20090520' UNION ALL SELECT '765',N'张方','20090505' UNION ALL SELECT '098',N'张方','20090505' GO --SELECT * FROM [tb]-->SQL查询如下: select * from tb t where exists( select 1 from tb where 姓名=t.姓名 and abs(datediff(dd,开户日期,t.开户日期))<=3 having count(1)>=3) /* 帐号 姓名 开户日期 ---------- ---------- ----------------------- 002 张方 2009-02-01 00:00:00.000 123 张方 2009-02-01 00:00:00.000 345 张方 2009-02-01 00:00:00.000 342 张方 2009-02-03 00:00:00.000 453 张方 2009-05-01 00:00:00.000 432 张方 2009-05-02 00:00:00.000 356 张方 2009-05-03 00:00:00.000 351 张方 2009-05-03 00:00:00.000 765 张方 2009-05-05 00:00:00.000 098 张方 2009-05-05 00:00:00.000(10 行受影响) */
and b.帐户姓名 is not null
group by 帐户姓名
having count(帐户姓名)>=3 )
from tb a
where exists(select 1 from tb where a.姓名=b.姓名 and datediff(d,a.开户日期,开户日期)=3 group by 姓名 having count(姓名)>=3 )
-- Author : htl258(Tony)
-- Date : 2010-04-22 00:27:09
-- 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 5.1 <X86> (Build 2600: Service Pack 2)
--------------------------------------------------------------------------
--> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([帐号] NVARCHAR(10),[姓名] NVARCHAR(10),[开户日期] DATETIME)
INSERT [tb]
SELECT '001',N'王名','20090109' UNION ALL
SELECT '002',N'张方','20090201' UNION ALL
SELECT '123',N'张方','20090201' UNION ALL
SELECT '345',N'张方','20090201' UNION ALL
SELECT '342',N'张方','20090203' UNION ALL
SELECT '456',N'王名','20090103' UNION ALL
SELECT '453',N'张方','20090501' UNION ALL
SELECT '432',N'张方','20090502' UNION ALL
SELECT '356',N'张方','20090503' UNION ALL
SELECT '351',N'张方','20090503' UNION ALL
SELECT '578',N'刘二','20090503' UNION ALL
SELECT '570',N'刘二','20090504' UNION ALL
SELECT '340',N'刘二','20090509'
GO
--SELECT * FROM [tb]-->SQL查询如下:
select * from tb
where 姓名 in(
select 姓名 from tb t
where exists(
select 1 from tb a
where 姓名=t.姓名
and abs(datediff(dd,开户日期,t.开户日期))=1
having count(1)>=3))
/*
帐号 姓名 开户日期
---------- ---------- -----------------------
002 张方 2009-02-01 00:00:00.000
123 张方 2009-02-01 00:00:00.000
345 张方 2009-02-01 00:00:00.000
342 张方 2009-02-03 00:00:00.000
453 张方 2009-05-01 00:00:00.000
432 张方 2009-05-02 00:00:00.000
356 张方 2009-05-03 00:00:00.000
351 张方 2009-05-03 00:00:00.000(8 行受影响)
*/
-- Author : htl258(Tony)
-- Date : 2010-04-24 00:08:41
-- 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 5.1 <X86> (Build 2600: Service Pack 2)
-- Blog : http://blog.csdn.net/htl258
--------------------------------------------------------------------------
--> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([帐号] NVARCHAR(10),[姓名] NVARCHAR(10),[开户日期] DATETIME)
INSERT [tb]
SELECT '001',N'王名','20090109' UNION ALL
SELECT '002',N'张方','20090201' UNION ALL
SELECT '123',N'张方','20090201' UNION ALL
SELECT '345',N'张方','20090201' UNION ALL
SELECT '342',N'张方','20090203' UNION ALL
SELECT '456',N'王名','20090103' UNION ALL
SELECT '453',N'张方','20090501' UNION ALL
SELECT '432',N'张方','20090502' UNION ALL
SELECT '356',N'张方','20090503' UNION ALL
SELECT '351',N'张方','20090503' UNION ALL
SELECT '578',N'刘二','20090503' UNION ALL
SELECT '570',N'刘二','20090504' UNION ALL
SELECT '340',N'刘二','20090509' UNION ALL
SELECT '789',N'张方','20090509' UNION ALL
SELECT '560',N'张方','20090520' UNION ALL
SELECT '765',N'张方','20090505' UNION ALL
SELECT '098',N'张方','20090505'
GO
--SELECT * FROM [tb]-->SQL查询如下:
select * from tb t
where exists(
select 1 from tb
where 姓名=t.姓名
and abs(datediff(dd,开户日期,t.开户日期))<=3
having count(1)>=3)
/*
帐号 姓名 开户日期
---------- ---------- -----------------------
002 张方 2009-02-01 00:00:00.000
123 张方 2009-02-01 00:00:00.000
345 张方 2009-02-01 00:00:00.000
342 张方 2009-02-03 00:00:00.000
453 张方 2009-05-01 00:00:00.000
432 张方 2009-05-02 00:00:00.000
356 张方 2009-05-03 00:00:00.000
351 张方 2009-05-03 00:00:00.000
765 张方 2009-05-05 00:00:00.000
098 张方 2009-05-05 00:00:00.000(10 行受影响)
*/
您太厉害了,谢谢
abs(datediff(dd,开户日期,t.开户日期))<=3
没想到,很巧