测试数据如下
零售户代码为111的数据符合要求
想要把它查询出来
月份 零售户代码 购进量
1 111 3
1 111 2
1 111 1
1 111 2
2 111 3
2 111 4
2 111 5
3 111 1
3 111 1
3 111 2
4 111 3
4 111 3
5 111 2
5 111 1
5 111 2
6 111 3
6 111 4
7 111 5
8 111 1
9 111 1
10 111 2
10 111 3
11 111 2
12 111 31 222 0
1 222 0
1 222 0
1 222 0
2 222 0
2 222 0
2 222 0
3 222 0
3 222 0
3 222 0
4 222 3
4 222 3
5 222 2
5 222 1
5 222 2
6 222 3
6 222 4
7 222 5
8 222 1
9 222 1
10 222 2
10 222 3
11 222 2
12 222 3
零售户代码为111的数据符合要求
想要把它查询出来
月份 零售户代码 购进量
1 111 3
1 111 2
1 111 1
1 111 2
2 111 3
2 111 4
2 111 5
3 111 1
3 111 1
3 111 2
4 111 3
4 111 3
5 111 2
5 111 1
5 111 2
6 111 3
6 111 4
7 111 5
8 111 1
9 111 1
10 111 2
10 111 3
11 111 2
12 111 31 222 0
1 222 0
1 222 0
1 222 0
2 222 0
2 222 0
2 222 0
3 222 0
3 222 0
3 222 0
4 222 3
4 222 3
5 222 2
5 222 1
5 222 2
6 222 3
6 222 4
7 222 5
8 222 1
9 222 1
10 222 2
10 222 3
11 222 2
12 222 3
a.*
from
tb a,
(select 零售户代码,sum(购进量) as 购进量 from tb group by 零售户代码)b
where
a.月份-b.月份>=10
and
b.购进量>15
and
a.零售户代码=b.零售户代码
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-03-15 21:47:47
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([月份] int,[零售户代码] int,[购进量] int)
insert [tb]
select 1,111,3 union all
select 1,111,2 union all
select 1,111,1 union all
select 1,111,2 union all
select 2,111,3 union all
select 2,111,4 union all
select 2,111,5 union all
select 3,111,1 union all
select 3,111,1 union all
select 3,111,2 union all
select 4,111,3 union all
select 4,111,3 union all
select 5,111,2 union all
select 5,111,1 union all
select 5,111,2 union all
select 6,111,3 union all
select 6,111,4 union all
select 7,111,5 union all
select 8,111,1 union all
select 9,111,1 union all
select 10,111,2 union all
select 10,111,3 union all
select 11,111,2 union all
select 12,111,3 union all
select 1,222,0 union all
select 1,222,0 union all
select 1,222,0 union all
select 1,222,0 union all
select 2,222,0 union all
select 2,222,0 union all
select 2,222,0 union all
select 3,222,0 union all
select 3,222,0 union all
select 3,222,0 union all
select 4,222,3 union all
select 4,222,3 union all
select 5,222,2 union all
select 5,222,1 union all
select 5,222,2 union all
select 6,222,3 union all
select 6,222,4 union all
select 7,222,5 union all
select 8,222,1 union all
select 9,222,1 union all
select 10,222,2 union all
select 10,222,3 union all
select 11,222,2 union all
select 12,222,3
--------------开始查询--------------------------
declare @t int
set @t=1
select
distinct a.零售户代码
from
tb a,
(select 零售户代码,sum(购进量) as 购进量 from tb group by 零售户代码)b
where
a.月份-@t>=10
and
b.购进量>15
and
a.零售户代码=b.零售户代码
----------------结果----------------------------
/*零售户代码
-----------
111
222(2 行受影响)
*/
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-03-15 21:47:47
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([月份] int,[零售户代码] int,[购进量] int)
insert [tb]
select 1,111,3 union all
select 1,111,2 union all
select 1,111,1 union all
select 1,111,2 union all
select 2,111,3 union all
select 2,111,4 union all
select 2,111,5 union all
select 3,111,1 union all
select 3,111,1 union all
select 3,111,2 union all
select 4,111,3 union all
select 4,111,3 union all
select 5,111,2 union all
select 5,111,1 union all
select 5,111,2 union all
select 6,111,3 union all
select 6,111,4 union all
select 7,111,5 union all
select 8,111,1 union all
select 9,111,1 union all
select 10,111,2 union all
select 10,111,3 union all
select 11,111,2 union all
select 12,111,3 union all
select 1,222,0 union all
select 1,222,0 union all
select 1,222,0 union all
select 1,222,0 union all
select 2,222,0 union all
select 2,222,0 union all
select 2,222,0 union all
select 3,222,0 union all
select 3,222,0 union all
select 3,222,0 union all
select 4,222,3 union all
select 4,222,3 union all
select 5,222,2 union all
select 5,222,1 union all
select 5,222,2 union all
select 6,222,3 union all
select 6,222,4 union all
select 7,222,5 union all
select 8,222,1 union all
select 9,222,1 union all
select 10,222,2 union all
select 10,222,3 union all
select 11,222,2 union all
select 12,222,3
--------------开始查询--------------------------
select
a.零售户代码
from
(select count(月份) as 月份 ,零售户代码 from tb group by 零售户代码)a,
(select 零售户代码,sum(购进量) as 购进量 from tb group by 零售户代码)b
where
b.购进量>=15
and
a.月份>=10
and
a.零售户代码=b.零售户代码
----------------结果----------------------------
/*零售户代码
-----------
111
222(2 行受影响)
*/
select a.*
from tb a,
(select 零售户代码 from tb group by 零售户代码 having COUNT(*)>15 and COUNT(distinct 月份) >=10) b
where a.零售户代码=b.零售户代码
select 零售户代码 from tb group by 零售户代码 having COUNT(*)>15 and COUNT(distinct 月份) >=10
零售户代码
-----------
111
222(2 行受影响)好像是都满足
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([月份] int,[零售户代码] int,[购进量] int)
insert [tb]
select 1,111,3 union all
select 1,111,2 union all
select 1,111,1 union all
select 1,111,2 union all
select 2,111,3 union all
select 2,111,4 union all
select 2,111,5 union all
select 3,111,1 union all
select 3,111,1 union all
select 3,111,2 union all
select 4,111,3 union all
select 4,111,3 union all
select 5,111,2 union all
select 5,111,1 union all
select 5,111,2 union all
select 6,111,3 union all
select 6,111,4 union all
select 7,111,5 union all
select 8,111,1 union all
select 9,111,1 union all
select 10,111,2 union all
select 10,111,3 union all
select 11,111,2 union all
select 12,111,3 union all
select 1,222,0 union all
select 1,222,0 union all
select 1,222,0 union all
select 1,222,0 union all
select 2,222,0 union all
select 2,222,0 union all
select 2,222,0 union all
select 3,222,0 union all
select 3,222,0 union all
select 3,222,0 union all
select 4,222,3 union all
select 4,222,3 union all
select 5,222,2 union all
select 5,222,1 union all
select 5,222,2 union all
select 6,222,3 union all
select 6,222,4 union all
select 7,222,5 union all
select 8,222,1 union all
select 9,222,1 union all
select 10,222,2 union all
select 10,222,3 union all
select 11,222,2 union all
select 12,222,3
---查询---
select a.*
from tb a
join
(select 零售户代码 from tb where 购进量>0 group by 零售户代码 having count(distinct 月份)>=10) b
on a.零售户代码=b.零售户代码
join
(select 零售户代码 from tb group by 零售户代码 having count(购进量)>15) c
on a.零售户代码=c.零售户代码---结果---
月份 零售户代码 购进量
----------- ----------- -----------
1 111 3
1 111 2
1 111 1
1 111 2
2 111 3
2 111 4
2 111 5
3 111 1
3 111 1
3 111 2
4 111 3
4 111 3
5 111 2
5 111 1
5 111 2
6 111 3
6 111 4
7 111 5
8 111 1
9 111 1
10 111 2
10 111 3
11 111 2
12 111 3(24 行受影响)
from tb a,
(select 零售户代码 from tb where 购进量>0 group by 零售户代码 having sum([购进量])>15 and COUNT(distinct 月份)>10 )b
where a.零售户代码=b.零售户代码
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-03-15 21:47:47
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([月份] int,[零售户代码] int,[购进量] int)
insert [tb]
select 1,111,3 union all
select 1,111,2 union all
select 1,111,1 union all
select 1,111,2 union all
select 2,111,3 union all
select 2,111,4 union all
select 2,111,5 union all
select 3,111,1 union all
select 3,111,1 union all
select 3,111,2 union all
select 4,111,3 union all
select 4,111,3 union all
select 5,111,2 union all
select 5,111,1 union all
select 5,111,2 union all
select 6,111,3 union all
select 6,111,4 union all
select 7,111,5 union all
select 8,111,1 union all
select 9,111,1 union all
select 10,111,2 union all
select 10,111,3 union all
select 11,111,2 union all
select 12,111,3 union all
select 1,222,0 union all
select 1,222,0 union all
select 1,222,0 union all
select 1,222,0 union all
select 2,222,0 union all
select 2,222,0 union all
select 2,222,0 union all
select 3,222,0 union all
select 3,222,0 union all
select 3,222,0 union all
select 4,222,3 union all
select 4,222,3 union all
select 5,222,2 union all
select 5,222,1 union all
select 5,222,2 union all
select 6,222,3 union all
select 6,222,4 union all
select 7,222,5 union all
select 8,222,1 union all
select 9,222,1 union all
select 10,222,2 union all
select 10,222,3 union all
select 11,222,2 union all
select 12,222,3
--------------开始查询--------------------------
select
a.*
from
tb a,
(select 零售户代码,sum(购进量) as 购进量, count(distinct 月份) as 月份 from tb group by 零售户代码)b
where
b.购进量>=15
and
b.月份>=10
and
a.零售户代码=b.零售户代码
----------------结果----------------------------
/*
(48 行受影响)
月份 零售户代码 购进量
----------- ----------- -----------
1 111 3
1 111 2
1 111 1
1 111 2
2 111 3
2 111 4
2 111 5
3 111 1
3 111 1
3 111 2
4 111 3
4 111 3
5 111 2
5 111 1
5 111 2
6 111 3
6 111 4
7 111 5
8 111 1
9 111 1
10 111 2
10 111 3
11 111 2
12 111 3
1 222 0
1 222 0
1 222 0
1 222 0
2 222 0
2 222 0
2 222 0
3 222 0
3 222 0
3 222 0
4 222 3
4 222 3
5 222 2
5 222 1
5 222 2
6 222 3
6 222 4
7 222 5
8 222 1
9 222 1
10 222 2
10 222 3
11 222 2
12 222 3(48 行受影响)
*/