oracle查询用户最后一次登录的信息

需求

查询用户最后一次登录的信息，每个用户只返回一条最后登录的记录

数据库

-- Create table
create table B_MAC_USB_KEY
(
  ID             VARCHAR2(64) not null,
  REMARK         VARCHAR2(2000),
  CREATE_BY      VARCHAR2(64),
  CREATE_DATE    DATE default sysdate,
  UPDATE_BY      VARCHAR2(64),
  UPDATE_DATE    DATE,
  STATUS         CHAR(1) default '0',
  SNO            VARCHAR2(20),
  MAC            VARCHAR2(100),
  BIND_NUM       NUMBER,
  LOGIN_DATE_MAC DATE,
  LOGIN_DATE_SNO DATE,
  NO             VARCHAR2(20)
)
tablespace YXDW
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64
    next 8
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table 
comment on table B_MAC_USB_KEY
  is 'MAC和加密狗绑定关系表';
-- Add comments to the columns 
comment on column B_MAC_USB_KEY.REMARK
  is '备注';
comment on column B_MAC_USB_KEY.CREATE_BY
  is '创建人';
comment on column B_MAC_USB_KEY.CREATE_DATE
  is '创建时间';
comment on column B_MAC_USB_KEY.UPDATE_BY
  is '修改人';
comment on column B_MAC_USB_KEY.UPDATE_DATE
  is '修改时间';
comment on column B_MAC_USB_KEY.STATUS
  is '0：正常1：删除';
comment on column B_MAC_USB_KEY.SNO
  is '加密狗编号（usb外表的号码）';
comment on column B_MAC_USB_KEY.MAC
  is '机器码';
comment on column B_MAC_USB_KEY.BIND_NUM
  is '加密狗已绑定的MAC数量';
comment on column B_MAC_USB_KEY.LOGIN_DATE_MAC
  is 'mac登录时间';
comment on column B_MAC_USB_KEY.LOGIN_DATE_SNO
  is 'sno登录时间';
comment on column B_MAC_USB_KEY.NO
  is 'SN，加密狗内部序列号';

SQL语句

参考：https://ask.csdn.net/questions/744504

SELECT * FROM (
SELECT user_id , login_time ,row_number() over (partition by user_id order by login_time desc) MM from xxx)
WHERE mm = 1

正式语句

<sql id="userUsbKeyColumns">

        a.ID AS "id",
        a.BRAND AS "brand",                   <!--     终端品牌        -->
        a.TYPE AS "type",                         <!-- 终端类型   -->
        a.OWNER_ID AS "ownerId",          <!-- 公司ID  -->
        (
            select b.owner_name
            from B_OWNER b
            WHERE a.OWNER_ID = b.OWNER_ID
        )  AS "ownerName",                     <!-- 公司名  -->
        a.USER_ID AS "userId",                 <!-- 使用人id  -->
        (select b.u_cname from sys_user b where b.USERID = a.USER_ID) AS "userName",     <!--     使用人姓名        -->
        a.REMARK AS "remark",
        a.CREATE_BY AS "createBy",
        a.CREATE_DATE AS "createDate",
        a.UPDATE_BY AS "updateBy",
        a.UPDATE_DATE AS "updateDate",
        a.STATUS AS "status",      <!-- 0正常1停用  -->
        a.SNO AS "sno",            <!-- 加密狗设备编号（usb外表的号码）   -->
        a.NO AS "no",               <!-- SN，加密狗内部序列号  -->
        a.LOGIN_DATE_SNO AS "loginDateSno"                <!-- 加密狗登录时间  -->


    </sql>

 SELECT
        *