如何选出这样的数据? select * from tablename where 时间 >(select max(时间 ) from tablename where 累计欠费=0) 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 时间应该就是年月吗?是字符串型的?如果是这样,至少应该是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=0select * from #a where YearMonth>@YearMonthdrop table #a结果:YearMonth Money1 Money2 Money3 --------- --------------------- --------------------- --------------------- 2005-08 20.0000 .0000 20.00002005-09 25.0000 .0000 45.0000 樓主太嬾了,呵呵,借用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=0select * from #a where Year+Month>@YearMonthdrop table #a////////////////////2005 08 20.0000 .0000 20.0000 2005 09 25.0000 .0000 45.0000 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 awhere month>(select month from #a where money3=0 ) drop table #a结果2005 08 20.0000 .0000 20.00002005 09 25.0000 .0000 45.0000 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 to: jxdjxd1111(qqq),你这样的话我想如果存在2004年的数据会有问题的啊!另外如果加入客户信息呢,该如何得到所有用户的欠费信息? 客户ID 年 月 费用 已交 累计欠费1 2005 5 20 0 201 2005 6 35 0 551 2005 7 25 80 01 2005 8 20 0 201 2005 9 25 0 452 2005 5 20 0 202 2005 6 35 55 02 2005 7 25 0 252 2005 8 20 0 452 2005 9 25 0 70 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 awhere 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.00001 2005 09 25.0000 .0000 45.00002 2005 07 25.0000 .0000 25.00002 2005 08 20.0000 .0000 40.00002 2005 09 25.0000 .0000 75.00002 2004 07 25.0000 .0000 25.00002 2004 08 20.0000 .0000 40.00002 2004 09 25.0000 .0000 75.0000 这样就可以了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 awhere 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 #a1 2005 08 20.0000 .0000 20.00001 2005 09 25.0000 .0000 45.00002 2005 07 25.0000 .0000 25.00002 2005 08 20.0000 .0000 40.00002 2005 09 25.0000 .0000 75.00003 2004 07 25.0000 .0000 25.00003 2004 08 20.0000 .0000 40.00003 2004 09 25.0000 .0000 75.0000 如何在同一个表中查询两次? access查询的问题 SQL语句 能实现吗? SQL Server 安装程序遇到以下错误: 版本字符串部分太短或太长。。 如何插入或更新带单引号的数据 定时备份数据库的存储过程 希望高人帮助!!!非常急切! 怎么在tablename是一个参数的表中取字段值? 远程无法访问有固定IP的SQL Server数据库服务器 关于数据库设计,绝对有挑战性 如何判斷某字符在字符串中的個數? select语句的一个问题
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>@YearMonthdrop table #a结果:
YearMonth Money1 Money2 Money3
--------- --------------------- --------------------- ---------------------
2005-08 20.0000 .0000 20.0000
2005-09 25.0000 .0000 45.0000
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
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
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
客户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
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
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