请用SQL语句实现:从TestDB数据表中查询出所有月份的发生额都比101科目相应月份的发生额高的科目。
请注意:TestDB中有很多科目,都有1-12月份的发生额。
AccID:科目代码,Occmonth:发生额月份,DebitOccur:发生额。
create table testdb(accid varchar(22),occmonth date ,debitoccur float);
insert into testdb values('101','2013-1-2',14.3);
insert into testdb values('102','2013-2-2',74.3);
insert into testdb values('103','2013-3-2',54.3);
insert into testdb values('104','2013-4-2',34.3);
insert into testdb values('105','2013-5-2',64.3);
select accid from testdb where debitoccur >(select debitoccur from testdb where accid ='103');
此题出的是有歧义的,不过大多数的 试题都会这么写,其实意思也很好理解
select a.*from
TestDB a
,
(select Occmonth,max(DebitOccur) Debit101ccur from TestDB where AccID='101' group by Occmonth) b
where
a.Occmonth=b.Occmonth and
a.DebitOccur>b.Debit101ccur
请注意:TestDB中有很多科目,都有1-12月份的发生额。
AccID:科目代码,Occmonth:发生额月份,DebitOccur:发生额。
create table testdb(accid varchar(22),occmonth date ,debitoccur float);
insert into testdb values('101','2013-1-2',14.3);
insert into testdb values('102','2013-2-2',74.3);
insert into testdb values('103','2013-3-2',54.3);
insert into testdb values('104','2013-4-2',34.3);
insert into testdb values('105','2013-5-2',64.3);
select accid from testdb where debitoccur >(select debitoccur from testdb where accid ='103');
此题出的是有歧义的,不过大多数的 试题都会这么写,其实意思也很好理解
select a.*from
TestDB a
,
(select Occmonth,max(DebitOccur) Debit101ccur from TestDB where AccID='101' group by Occmonth) b
where
a.Occmonth=b.Occmonth and
a.DebitOccur>b.Debit101ccur