• 全部
  • 基础类
  • 应用实例
  • 新技术前沿

如何选出这样的数据？

fleamboy 2005-10-07 10:25:32
时间 费用 已交 累计欠费
2005-5 20 0 20
2005-6 35 0 55
2005-7 25 80 0
2005-8 20 0 20
2005-9 25 0 45

如何选出欠费的月份数据，就是之中8、9月的两条数据，而不选出5、6月的数据，也就是选出交清费用之后的所有欠费记录！！！！
...全文
106 点赞 收藏 12
写回复
12 条回复
切换为时间正序
当前发帖距今超过3年，不再开放新的回复
发表回复
fleamboy 2005-10-07
--结果
1 2005 08 20.0000 .0000 20.0000
1 2005 09 25.0000 .0000 45.0000
2 2005 07 25.0000 .0000 25.0000
2 2005 08 20.0000 .0000 40.0000
2 2005 09 25.0000 .0000 75.0000
2 2004 07 25.0000 .0000 25.0000
2 2004 08 20.0000 .0000 40.0000
2 2004 09 25.0000 .0000 75.0000

这个结果不是我要得，应为对于客户2来说，在2005-6月交清费用后之前的应该不算欠费了，所有2004年的数据不应该有.
 回复 赞
jxdjxd1111 2005-10-07
还有什么问题吗
 回复 赞
jxdjxd1111 2005-10-07
create table #a(id int, Year varchar(7),month varchar(5), Money1 money, Money2 money, Money3 money);
insert into #a values(1,'2005','05', 20, 0, 20);
insert into #a values(1,'2005','06', 35, 0, 55);
insert into #a values(1,'2005','07', 25, 80, 0);
insert into #a values(1,'2005','08', 20, 0, 20);
insert into #a values(1,'2005','09', 25, 0, 45);
insert into #a values(2,'2005','05', 20, 0, 20);
insert into #a values(2,'2005','06', 35, 55, 0);
insert into #a values(2,'2005','07', 25, 0, 25);
insert into #a values(2,'2005','08', 20, 0, 40);
insert into #a values(2,'2005','09', 25, 0, 75);
insert into #a values(2,'2004','05', 20, 0, 20);
insert into #a values(2,'2004','06', 35, 55, 0);
insert into #a values(2,'2004','07', 25, 0, 25);
insert into #a values(2,'2004','08', 20, 0, 40);
insert into #a values(2,'2004','09', 25, 0, 75);


select a.id,a.Year,month,money1,money2,money3
from #a a
where month>(select month from #a where id=a.id and money3=0 and year=a.year )



drop table #a

--结果
1 2005 08 20.0000 .0000 20.0000
1 2005 09 25.0000 .0000 45.0000
2 2005 07 25.0000 .0000 25.0000
2 2005 08 20.0000 .0000 40.0000
2 2005 09 25.0000 .0000 75.0000
2 2004 07 25.0000 .0000 25.0000
2 2004 08 20.0000 .0000 40.0000
2 2004 09 25.0000 .0000 75.0000


 回复 赞
fleamboy 2005-10-07
to： jxdjxd1111(qqq)，你这样的话我想如果存在2004年的数据会有问题的啊！

另外如果加入客户信息呢，该如何得到所有用户的欠费信息？

客户ID 年 月 费用 已交 累计欠费
1 2005 5 20 0 20
1 2005 6 35 0 55
1 2005 7 25 80 0
1 2005 8 20 0 20
1 2005 9 25 0 45
2 2005 5 20 0 20
2 2005 6 35 55 0
2 2005 7 25 0 25
2 2005 8 20 0 45
2 2005 9 25 0 70
 回复 赞
jxdjxd1111 2005-10-07
create table #a(Year varchar(7),month varchar(5), Money1 money, Money2 money, Money3 money);
insert into #a values('2005','05', 20, 0, 20);
insert into #a values('2005','06', 35, 0, 55);
insert into #a values('2005','07', 25, 80, 0);
insert into #a values('2005','08', 20, 0, 20);
insert into #a values('2005','09', 25, 0, 45);


select Year,month,money1,money2,money3
from #a
where month>(select month from #a where money3=0 )

drop table #a

--结果
--year month money1 money2 money3
--2005 08 20.0000 .0000 20.0000
--2005 09 25.0000 .0000 45.0000
 回复 赞
jxdjxd1111 2005-10-07
create table #a(Year varchar(7),month varchar(5), Money1 money, Money2 money, Money3 money);
insert into #a values('2005','05', 20, 0, 20);
insert into #a values('2005','06', 35, 0, 55);
insert into #a values('2005','07', 25, 80, 0);
insert into #a values('2005','08', 20, 0, 20);
insert into #a values('2005','09', 25, 0, 45);

--declare @YearMonth varchar(7)
select a.Year,a.month,money1,money2,money3
from #a a
where month>(select month from #a where money3=0 )

drop table #a

结果

2005 08 20.0000 .0000 20.0000
2005 09 25.0000 .0000 45.0000

 回复 赞
dutguoyi 2005-10-07
樓主太嬾了，呵呵，借用sunjian_qi(sonne) 的代碼
create table #a(Year varchar(4),Month varchar(2), Money1 money, Money2 money, Money3 money);
insert into #a values('2005','05', 20, 0, 20);
insert into #a values('2005','06', 35, 0, 55);
insert into #a values('2005','07', 25, 80, 0);
insert into #a values('2005','08', 20, 0, 20);
insert into #a values('2005','09', 25, 0, 45);

declare @YearMonth varchar(7)
select @YearMonth=Max(convert(varchar,Year+Month)) from #a where Money3=0
select * from #a where Year+Month>@YearMonth
drop table #a
////////////////////
2005 08 20.0000 .0000 20.0000
2005 09 25.0000 .0000 45.0000
回复 赞
dutguoyi 2005-10-07
同意 iwl()
 回复 赞
fleamboy 2005-10-07
刚才没有写清楚，我得年月是分开的字符！
年 月 费用 已交 累计欠费
2005 5 20 0 20
2005 6 35 0 55
2005 7 25 80 0
2005 8 20 0 20
2005 9 25 0 45
 回复 赞
sunjian_qi 2005-10-07
时间应该就是年月吗？是字符串型的？如果是这样，至少应该是yyyy-MM的样式。

create table #a(YearMonth varchar(7), Money1 money, Money2 money, Money3 money);
insert into #a values('2005-05', 20, 0, 20);
insert into #a values('2005-06', 35, 0, 55);
insert into #a values('2005-07', 25, 80, 0);
insert into #a values('2005-08', 20, 0, 20);
insert into #a values('2005-09', 25, 0, 45);

declare @YearMonth varchar(7)
select @YearMonth=MAX(YearMonth) from #a where Money3=0
select * from #a where YearMonth>@YearMonth

drop table #a

结果：
YearMonth Money1 Money2 Money3
--------- --------------------- --------------------- ---------------------
2005-08 20.0000 .0000 20.0000
2005-09 25.0000 .0000 45.0000
 回复 赞
iwl 2005-10-07
select * from tablename where 时间 >(select max(时间 ) from tablename where 累计欠费=0)
 回复 赞
jxdjxd1111 2005-10-07
这样就可以了
create table #a(id int, Year varchar(7),month varchar(5), Money1 money, Money2 money, Money3 money);
insert into #a values(1,'2005','05', 20, 0, 20);
insert into #a values(1,'2005','06', 35, 0, 55);
insert into #a values(1,'2005','07', 25, 80, 0);
insert into #a values(1,'2005','08', 20, 0, 20);
insert into #a values(1,'2005','09', 25, 0, 45);
insert into #a values(2,'2005','05', 20, 0, 20);
insert into #a values(2,'2005','06', 35, 55, 0);
insert into #a values(2,'2005','07', 25, 0, 25);
insert into #a values(2,'2005','08', 20, 0, 40);
insert into #a values(2,'2005','09', 25, 0, 75);
insert into #a values(2,'2004','05', 20, 0, 20);
insert into #a values(2,'2004','06', 35, 55, 0);
insert into #a values(2,'2004','07', 25, 0, 25);
insert into #a values(2,'2004','08', 20, 0, 40);
insert into #a values(2,'2004','09', 25, 0, 75);
insert into #a values(3,'2004','05', 20, 0, 20);
insert into #a values(3,'2004','06', 35, 55, 0);
insert into #a values(3,'2004','07', 25, 0, 25);
insert into #a values(3,'2004','08', 20, 0, 40);
insert into #a values(3,'2004','09', 25, 0, 75);



select a.id,a.Year,month,money1,money2,money3
from #a a
where month>(select month from #a where id=a.id and money3=0 and year=a.year ) and
year=(select max(year) from #a where id=a.id and money3=0)


drop table #a


1 2005 08 20.0000 .0000 20.0000
1 2005 09 25.0000 .0000 45.0000
2 2005 07 25.0000 .0000 25.0000
2 2005 08 20.0000 .0000 40.0000
2 2005 09 25.0000 .0000 75.0000
3 2004 07 25.0000 .0000 25.0000
3 2004 08 20.0000 .0000 40.0000
3 2004 09 25.0000 .0000 75.0000
 回复 赞
发帖
MS-SQL Server
创建于2007-09-28

3.3w+

社区成员

25.4w+

社区内容

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2005-10-07 10:25
社区公告
暂无公告