求一条sql语句,找出余额没有变化的账户记录,该如何解决
求一条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账号),请大侠出手,谢了
------解决方案--------------------
表结构及测试数据
账号,日期,余额
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
*/