Oracle常用查询大全

  SELECT T_MEMBER.NAME, T_CARD.CARD_NUM, SUM(T_CARD_EXPENSE.MONEY)
    FROM T_CARD_EXPENSE, T_CARD, T_MEMBER
   WHERE T_CARD_EXPENSE.CARD_ID = T_CARD.ID
     AND T_CARD.MEMBER_ID = T_MEMBER.ID
     AND TO_CHAR(T_CARD_EXPENSE.CREATE_DT, 'yymmdd') BETWEEN '120701' AND
         '120707'
   GROUP BY T_CARD.CARD_NUM, T_MEMBER.NAME
   ORDER BY SUM(T_CARD_EXPENSE.MONEY) DESC

select tc.card_num, tt.money
  from (select t.card_id, sum(t.money) as money
          from T_CARD_EXPENSE t
         where t.create_dt between
               to_date('2012-07-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and
               to_date('2012-07-07 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
         group by t.card_id) tt
  left join t_card tc
    on tt.card_id = tc.id
 order by tt.money desc

select card_num,sum
  from (select card.card_num,
               sum(expense.deduction_money) as sum
          from t_card_expense expense, t_card card
         where expense.card_id = card.id
           and to_char(expense.create_dt, 'yymmdd') between
               '020701' and '130707'
         group by card.card_num
         order by sum desc)
 where rownum < 11

1.创建表

-- Create table
create table O_USER
(
  USERID   NUMBER(20),
  USERNAME VARCHAR2(40),
  PASSWORD VARCHAR2(40),
  USERTYPE NUMBER(1),
  EMAIL    VARCHAR2(40)
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

2.查询前十条记录

select userid     as 编号,
       username   as 用户名,
       password   as 密码,
       createdate as 创建日期,
       email      as 电子邮箱
  from (select t.userid, t.username, t.email, t.createdate, t.password
          from o_user t
         order by t.createdate desc)
 where rownum <= 10; 
 --desc/asc 降序/升序

3.查询结果



查询某列最大值的全部记录

select *
  from t_account_detail t
 where t.id in (select max(id) from t_account_detail);