充值表:
充值卡号 充值额 充值时间
6565767 45 2008-11-5
6568716 35 2001-5-5
6568716 20 2005-4-1
6873546 58 2004-10-9
2563465 25 1998-1-5
6876796 15 1986-7-5
6873546 58.5 2004-10-9
6876796 12.3 2003-7-5
消费表:
消费卡号 消费额 余额 消费时间
2563465 0.6 24.4 1998-1-6
2563465 0.3 24.1 1998-1-7
2563465 0.8 23.3 1998-1-9
2563465 0.1 23.2 1998-1-12
6568716 1.5 53.5 2005-8-7
6568716 5.2 48.3 2005-9-5
6568716 6.3 42 2008-1-4求sql语句,比如6568716一共充值是55,和消费表里的此卡的最大余额53.5比较,比较结果为消费卡号
充值卡号 充值额 充值时间
6565767 45 2008-11-5
6568716 35 2001-5-5
6568716 20 2005-4-1
6873546 58 2004-10-9
2563465 25 1998-1-5
6876796 15 1986-7-5
6873546 58.5 2004-10-9
6876796 12.3 2003-7-5
消费表:
消费卡号 消费额 余额 消费时间
2563465 0.6 24.4 1998-1-6
2563465 0.3 24.1 1998-1-7
2563465 0.8 23.3 1998-1-9
2563465 0.1 23.2 1998-1-12
6568716 1.5 53.5 2005-8-7
6568716 5.2 48.3 2005-9-5
6568716 6.3 42 2008-1-4求sql语句,比如6568716一共充值是55,和消费表里的此卡的最大余额53.5比较,比较结果为消费卡号
select a.充值卡号,(a.充值额-b.余额 ) as 比较结果 from (
(select 充值卡号,sum(充值额) as 充值额 from 充值表 group by 充值卡号) a inner join
(select 消费卡号,max(余额) as 余额 from 消费表 group by 消费卡号) b on
a充值卡号.=b.消费卡号) t
-- Author :SQL77(只为思齐老)
-- Date :2010-01-13 21:06:28
-- Version:
-- Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
-- Aug 6 2000 00:57:48
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[充值表]
if object_id('[充值表]') is not null drop table [充值表]
go
create table [充值表]([充值卡号] int,[充值额] numeric(3,1),[充值时间] datetime)
insert [充值表]
select 6565767,45,'2008-11-5' union all
select 6568716,35,'2001-5-5' union all
select 6568716,20,'2005-4-1' union all
select 6873546,58,'2004-10-9' union all
select 2563465,25,'1998-1-5' union all
select 6876796,15,'1986-7-5' union all
select 6873546,58.5,'2004-10-9' union all
select 6876796,12.3,'2003-7-5'
--> 测试数据:[消费表]
if object_id('[消费表]') is not null drop table [消费表]
go
create table [消费表]([消费卡号] int,[消费额] numeric(2,1),[余额] numeric(3,1),[消费时间] datetime)
insert [消费表]
select 2563465,0.6,24.4,'1998-1-6' union all
select 2563465,0.3,24.1,'1998-1-7' union all
select 2563465,0.8,23.3,'1998-1-9' union all
select 2563465,0.1,23.2,'1998-1-12' union all
select 6568716,1.5,53.5,'2005-8-7' union all
select 6568716,5.2,48.3,'2005-9-5' union all
select 6568716,6.3,42,'2008-1-4'
--------------开始查询--------------------------select 充值卡号,SUM(充值额)AS 充值额 from [充值表] GROUP BY 充值卡号
select 消费卡号,MAX(余额)AS 余额 from [消费表] GROUP BY 消费卡号
----------------结果----------------------------
/* (所影响的行数为 8 行)
(所影响的行数为 7 行)充值卡号 充值额
----------- ----------------------------------------
2563465 25.0
6565767 45.0
6568716 55.0
6873546 116.5
6876796 27.3(所影响的行数为 5 行)消费卡号 余额
----------- -----
2563465 24.4
6568716 53.5(所影响的行数为 2 行)
*/
from [充值表]
group by [充值卡号]select [消费卡号],max([余额]) as [余额]
from [消费表]
group by [消费卡号]
select 消费卡号,MAX(余额)AS 余额 from [消费表] GROUP BY 消费卡号现在要求比较MAX(余额)>SUM(充值额)超过100的所有卡号也就是说正常情况下sum(充值额)一定比max(余额)要大,但现在我要的是max(余额)值比sum(充值额)值大于100的反常情况,查询结果是列出这样的反常卡号
from [消费表] t
group by [消费卡号]
having max([余额]) - (select sum([充值额])
from [充值表] where [充值卡号]=t.[消费卡号]) > 100
-- Author :SQL77(只为思齐老)
-- Date :2010-01-13 21:06:28
-- Version:
-- Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
-- Aug 6 2000 00:57:48
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[充值表]
if object_id('[充值表]') is not null drop table [充值表]
go
create table [充值表]([充值卡号] int,[充值额] numeric(3,1),[充值时间] datetime)
insert [充值表]
select 6565767,45,'2008-11-5' union all
select 6568716,35,'2001-5-5' union all
select 6568716,20,'2005-4-1' union all
select 6873546,58,'2004-10-9' union all
select 2563465,25,'1998-1-5' union all
select 6876796,15,'1986-7-5' union all
select 6873546,58.5,'2004-10-9' union all
select 6876796,12.3,'2003-7-5'
--> 测试数据:[消费表]
if object_id('[消费表]') is not null drop table [消费表]
go
create table [消费表]([消费卡号] int,[消费额] numeric(2,1),[余额] numeric(3,1),[消费时间] datetime)
insert [消费表]
select 2563465,0.6,24.4,'1998-1-6' union all
select 2563465,0.3,24.1,'1998-1-7' union all
select 2563465,0.8,23.3,'1998-1-9' union all
select 2563465,0.1,23.2,'1998-1-12' union all
select 6568716,1.5,53.5,'2005-8-7' union all
select 6568716,5.2,48.3,'2005-9-5' union all
select 6568716,6.3,42,'2008-1-4'
--------------开始查询--------------------------
SELECT A.充值卡号 FROM
(
select 充值卡号,SUM(充值额)AS 充值额 from [充值表] GROUP BY 充值卡号
)AS A,
(
select 消费卡号,MAX(余额)AS 余额 from [消费表] GROUP BY 消费卡号
)AS BWHERE B.余额-A.充值额>100