DECLARE @TB TABLE(名称 NVARCHAR(5), 证件号 VARCHAR(5), 交易金额 INT, 标志 INT)
INSERT @TB
SELECT N'张三', '12345', 1100, 1 UNION ALL
SELECT N'张三', '12345', 2000, 1 UNION ALL
SELECT N'李四', '67899', 1000, 1 UNION ALL
SELECT N'赵五', '88888', 20000, 1 UNION ALL
SELECT N'a公司', '52abc', 50000, 2 UNION ALL
SELECT N'a公司', '52abc', 60000, 2 UNION ALL
SELECT N'a公司', '52abc', 10000, 2 UNION ALL
SELECT N'b公司', '88bbb', 11000, 2
SELECT * FROM @TB WHERE 标志=1 AND 交易金额>1000
UNION
SELECT * FROM @TB WHERE 标志=2 AND 交易金额>40000
UNION
SELECT A.* FROM @TB AS A JOIN (
SELECT 名称 FROM @TB WHERE 标志=2 GROUP BY 名称 HAVING SUM(交易金额)>100000) B
ON A.名称=B.名称
UNION
SELECT A.* FROM @TB AS A JOIN (
SELECT 名称 FROM @TB WHERE 标志=1 GROUP BY 名称 HAVING SUM(交易金额)>3000) B
ON A.名称=B.名称
/*
名称 证件号 交易金额 标志
----- ----- ----------- -----------
a公司 52abc 10000 2
a公司 52abc 50000 2
a公司 52abc 60000 2
张三 12345 1100 1
张三 12345 2000 1
赵五 88888 20000 1
*/
INSERT @TB
SELECT N'张三', '12345', 1100, 1 UNION ALL
SELECT N'张三', '12345', 2000, 1 UNION ALL
SELECT N'李四', '67899', 1000, 1 UNION ALL
SELECT N'赵五', '88888', 20000, 1 UNION ALL
SELECT N'a公司', '52abc', 50000, 2 UNION ALL
SELECT N'a公司', '52abc', 60000, 2 UNION ALL
SELECT N'a公司', '52abc', 10000, 2 UNION ALL
SELECT N'b公司', '88bbb', 11000, 2
SELECT * FROM @TB WHERE 标志=1 AND 交易金额>1000
UNION
SELECT * FROM @TB WHERE 标志=2 AND 交易金额>40000
UNION
SELECT A.* FROM @TB AS A JOIN (
SELECT 名称 FROM @TB WHERE 标志=2 GROUP BY 名称 HAVING SUM(交易金额)>100000) B
ON A.名称=B.名称
UNION
SELECT A.* FROM @TB AS A JOIN (
SELECT 名称 FROM @TB WHERE 标志=1 GROUP BY 名称 HAVING SUM(交易金额)>3000) B
ON A.名称=B.名称
/*
名称 证件号 交易金额 标志
----- ----- ----------- -----------
a公司 52abc 10000 2
a公司 52abc 50000 2
a公司 52abc 60000 2
张三 12345 1100 1
张三 12345 2000 1
赵五 88888 20000 1
*/
from tab a,(
select 名称,证件号,sum(交易金额) as 交易金额,标志
from tab
group by 名称,证件号,标志
) as b
where a.名称=b.名称
and a.证件号=b.证件号
and a.标志=b.标志
and (
(a.标志=1 --个人
and (a.交易金额 >1000 --单笔交易大于1000
or b.交易金额 >3000) --累计交易大于3000
)
or (a.标志=2 --公司
and (a.交易金额 >40000 --单笔交易大于40000
or b.交易金额 >100000) --累计交易大于100000
)
)
--> (让你望见影子的墙)生成测试数据,时间:2008-12-05
if not object_id('tb') is null
drop table tb
Go
Create table tb([名称] nvarchar(3),[证件号] nvarchar(5),[交易金额] int,[标志] int)
Insert tb
select N'张三',N'12345',1100,1 union all
select N'张三',N'12345',2000,1 union all
select N'李四',N'67899',1000,1 union all
select N'赵五',N'88888',20000,1 union all
select N'a公司',N'52abc',50000,2 union all
select N'a公司',N'52abc',60000,2 union all
select N'a公司',N'52abc',10000,2 union all
select N'b公司',N'88bbb',11000,2
Go
Select * from tbselect * from tb
where (交易金额>1000 and 标志=1) or (交易金额>40000 and 标志=2)
union
select * from tb t
where exists( select 1 from tb where 名称=t.名称 group by 名称,标志 having sum(交易金额)>3000 and 标志=1 or sum(交易金额)>100000 and 标志=2)a公司 52abc 10000 2
a公司 52abc 50000 2
a公司 52abc 60000 2
张三 12345 1100 1
张三 12345 2000 1
赵五 88888 20000 1
DECLARE @TB TABLE(名称 NVARCHAR(5), 证件号 VARCHAR(5), 交易金额 INT, 标志 INT)
INSERT @TB
SELECT N'张三', '12345', 1100, 1 UNION ALL
SELECT N'张三', '12345', 2000, 1 UNION ALL
SELECT N'李四', '67899', 1000, 1 UNION ALL
SELECT N'赵五', '88888', 20000, 1 UNION ALL
SELECT N'a公司', '52abc', 50000, 2 UNION ALL
SELECT N'a公司', '52abc', 60000, 2 UNION ALL
SELECT N'a公司', '52abc', 10000, 2 UNION ALL
SELECT N'b公司', '88bbb', 110000, 2select * from @TB where [标志]=1 and [交易金额]>1000 or [名称] in (
select [名称] from @TB where [标志]=1 group by [名称] having sum([交易金额])>3000
)
union all
select * from @TB where [标志]=2 and [交易金额]>40000 or [名称] in (
select [名称] from @TB where [标志]=2 group by [名称] having sum([交易金额])>100000
)/*
名称 证件号 交易金额 标志
----- ----- ----------- -----------
张三 12345 1100 1
张三 12345 2000 1
赵五 88888 20000 1
a公司 52abc 50000 2
a公司 52abc 60000 2
a公司 52abc 10000 2
b公司 88bbb 110000 2
*/
table
where 名称 in
(select 名称
from
(select 名称,SUM(交易金额) as 交易金额
from table
where 标志 =1
group by 名称
having SUM(交易金额)>3000
) AS AB
)
and 标志=1
and 交易金额>1000
unionselect * from
table
where 名称 in
(select 名称
from
(select 名称,SUM(交易金额) as 交易金额
from table
where 标志 =2
group by 名称
having SUM(交易金额)>100000
) AS AB
)
and 标志=2and 交易金额>40000
table
where 名称 in
(select 名称
from
(select 名称,SUM(交易金额) as 交易金额
from table
where 标志 =1
group by 名称
having SUM(交易金额)>3000
) AS AB
)
and 标志=1
or 交易金额>1000
union select * from
table
where 名称 in
(select 名称
from
(select 名称,SUM(交易金额) as 交易金额
from table
where 标志 =2
group by 名称
having SUM(交易金额)>100000
) AS AB
)
and 标志=2 or 交易金额>40000
(
name nvarchar(20),
cardno nvarchar(20),
price int,
int
)
insert into #TEST
select '张三','12345',1100,1 union all
select '张三','12345',2000,1 union all
select '李四','67899',1000,1 union all
select '赵五','88888',20000,1 union all
select 'a公司','52abc',50000,2 union all
select 'a公司','52abc',60000,2 union all
select 'a公司','52abc',10000,2 union all
select 'b公司','88bbb',11000,2
--select name,cardno,,SUM(price) AS price from #TEST group by name,cardno,select A.name, A.cardno,A.,A.price from #TEST A
INNER JOIN (select name,cardno,,SUM(price) AS price from #TEST group by name,cardno,) B
ON A.name=B.name AND A.=1 AND B.=1
where A.price>1000 OR B.price>3000
union all
select C.name, C.cardno,C.,C.price from #TEST C
INNER JOIN (select name,cardno,,SUM(price) AS price from #TEST group by name,cardno,) D
ON C.name=D.name and C.=D. AND C.=2 AND D.=2
where C.price>40000 OR D.price>100000
drop table #TEST