日期:2014-05-18  浏览次数:20572 次

求一条sql语句,找出余额没有变化的账户记录
表结构及测试数据
账号,日期,余额
001,20110101,100
002,20110101,200
003,20110101,100

001,20110102,100
002,20110102,210
003,20110102,150

001,20110103,100
002,20110103,220
003,20110103,100
.......
现在要找出某个时间段内,余额没有变化的那些账号(比如上面001账号),请大侠出手,谢了

------解决方案--------------------
SQL code

create table a(账号 varchar(10),日期 varchar(10),余额 int)
insert into a 
select '001','20110101',100
union all select 
'002','20110101',200
union all select 
'003','20110101',100
union all select 
'001','20110102',100
union all select 
'002','20110102',210
union all select 
'003','20110102',150
union all select 
'001','20110103',100
union all select 
'002','20110103',220
union all select 
'003','20110103',100
select * from a

select 账号
from a
where 日期 between '2011-01-01' and '2011-01-03'
group by 账号
having max(余额)=min(余额)

/*
001
*/

------解决方案--------------------
if OBJECT_ID('tbl')is not null
drop table tbl
go
create table tbl(
账号 varchar(10),
日期 varchar(10),
余额 int)
insert into tbl
select '001','20110101',100 union all 
select '002','20110101',200 union all
select '003','20110101',100 union all 
select '001','20110102',100 union all 
select '002','20110102',210 union all 
select '003','20110102',150 union all 
select '001','20110103',100 union all 
select '002','20110103',220 union all 
select '003','20110103',100

select 账号 from tbl where 日期 between '20110101' and '20110103'
group by 账号
having MAX(余额)=MIN(余额)
/*
结果表
账号
001
*/