高分求select语句

Question

1.  select a.cardid,name,sum(drivermile) sum_drivermile 
      from a,b 
        where a.carid=b.carid 
           group by a.carid order by sum_drivermile;
2. select a.cardid,name,sum(drivermile) sum_drivermile 
      from a,b 
        where a.carid=b.carid and sum_drivermile<900
           group by a.carid order by sum_drivermile;

Answer 1

SQL> select a.cardid,name,sum(drivermile) sum_drivermile
  2        from a,b
  3          where a.cardid=b.cardid
  4             group by a.cardid,a.name order by sum_drivermile
  5  /    CARDID NAME                                                         SUM_DRIVERMILE
---------- ------------------------------------------------------------ --------------
       101 小李                                                                    800
       102 小王                                                                   1000SQL>

Answer 2

SQL> select * from (
  2  select a.cardid,name,sum(drivermile) sum_drivermile
  3        from a,b
  4          where a.cardid=b.cardid
  5             group by a.cardid,a.name order by sum_drivermile) c
  6             where sum_drivermile<900
  7  /    CARDID NAME                                                         SUM_DRIVERMILE
---------- ------------------------------------------------------------ --------------
       101 小李                                                                    800SQL>

Answer 3

1、
  select cardid,name,(select sum(drivermile) from b where cardid=a.cardid) drivermile from a order by drivermile2、
  select cardid,name,(select sum(drivermile) from b where cardid=a.cardid) drivermile from a where drivermile<900

Answer 4

楼上的2可以吗SQL> select a.cardid,a.name,(select sum(b.drivermile) from b where a.cardid=b.cardid) drivermile
  2   from a where (select sum(b.drivermile) from b where a.cardid=b.cardid)<'900';    CARDID NAME       DRIVERMILE
---------- ---------- ----------
       101 小李              800

Answer 5

select
a.cardid,a.name,b.sumd 
from a,(select cardid,sum(drivermile) sumd from b group by cardid) t1
where a.cardid = t1.cardid  order by b.sumd asc;select
a.cardid,a.name,b.sumd 
from a,(select cardid,sum(drivermile) sumd from b group by cardid) t1
where a.cardid = t1.cardid and b.sumd <'900';

Answer 6

1.
select distinct a.cardid cardid,a.name name,sum(b.drivermile) sum_drivermile
from a,b
where a.cardid = b.cardid
group by a.cardid,a.name;2. 
select * from 
(
  select distinct a_.cardid cardid,a_.name name,sum(b_.drivermile) sum_drivermile
  from a_ ,b_ 
  where a_.cardid = b_.cardid
  group by a_.cardid,a_.name
)
where sum_drivermile < 900;

Answer 7

第二个查询的正确写法:
2. 
select * from 
(
  select distinct a.cardid cardid,a.name name,sum(b.drivermile) sum_drivermile
  from a,b 
  where a_.cardid = b.cardid
  group by a.cardid,a.name
)
where sum_drivermile < 900;

Answer 8

1.
select a.cardid,a.name,sum(b.drivermile)
from a, b
where a.cardid = b.cardid
group by a.cardid,a.name;
2.
select a.cardid,a.name,sum(b.drivermile)
from a, b
where a.cardid = b.cardid
group by a.cardid,a.name
having sum(b.drivermile) < 900;

Answer 9

select a.cardid,a.name,
   (select sum(drivenile) from b where b.cardid =a.cardid group by b.cardid) 
   as sum_drivermile
from a
order by sum_drivermile

Answer 10

在第一个的基础上，在from之后，order by之前加上where sum_drivermile < 900