oracle的按月统计sql（一个数据库面试题）

题目：

 

两个数据库表，schema如下：

 

CREATE TABLE REG_USERS
(
  ID           NUMBER,
  NAME         VARCHAR2(30 BYTE),
  CREATE_DATE  DATE    --注册日期
)

 

CREATE TABLE SAMENAME_USERS
(
  ID    NUMBER,
  NAME  VARCHAR2(30 BYTE)
)

 

要求：

1 统计出每个月的注册人数；

2 列出用户名相同的所有记录；

3 将用户名相同的记录插入SAMENAME_USERS表；

 

 

将以下记录插入表TABLE REG_USERS：

Insert into REG_USERS
   (ID, NAME, CREATE_DATE)
 Values
   (2, 'lisa', TO_DATE('10/27/2009 23:53:58', 'MM/DD/YYYY HH24:MI:SS'));
Insert into REG_USERS
   (ID, NAME, CREATE_DATE)
 Values
   (12, 'sd', TO_DATE('05/27/2009 00:04:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into REG_USERS
   (ID, NAME, CREATE_DATE)
 Values
   (13, 'lisi', TO_DATE('08/27/2009 02:02:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into REG_USERS
   (ID, NAME, CREATE_DATE)
 Values
   (23, 'skote', TO_DATE('08/27/2009 23:52:58', 'MM/DD/YYYY HH24:MI:SS'));
Insert into REG_USERS
   (ID, NAME, CREATE_DATE)
 Values
   (1, 'zhangsan', TO_DATE('09/27/2009 23:52:58', 'MM/DD/YYYY HH24:MI:SS'));
Insert into REG_USERS
   (ID, NAME, CREATE_DATE)
 Values
   (3, 'wangwu', TO_DATE('10/27/2009 23:52:58', 'MM/DD/YYYY HH24:MI:SS'));
Insert into REG_USERS
   (ID, NAME, CREATE_DATE)
 Values
   (4, 'zhanglong', TO_DATE('08/27/2009 23:52:58', 'MM/DD/YYYY HH24:MI:SS'));
Insert into REG_USERS
   (ID, NAME, CREATE_DATE)
 Values
   (5, 'zhaohu', TO_DATE('10/27/2009 23:52:58', 'MM/DD/YYYY HH24:MI:SS'));
Insert into REG_USERS
   (ID, NAME, CREATE_DATE)
 Values
   (6, 'wangchao', TO_DATE('07/27/2009 23:52:58', 'MM/DD/YYYY HH24:MI:SS'));
Insert into REG_USERS
   (ID, NAME, CREATE_DATE)
 Values
   (7, 'mahan', TO_DATE('10/27/2009 23:52:58', 'MM/DD/YYYY HH24:MI:SS'));
Insert into REG_USERS
   (ID, NAME, CREATE_DATE)
 Values
   (8, 'zhanzhao', TO_DATE('07/27/2009 23:52:58', 'MM/DD/YYYY HH24:MI:SS'));
Insert into REG_USERS
   (ID, NAME, CREATE_DATE)
 Values
   (9, 'jojoy', TO_DATE('10/27/2009 23:52:58', 'MM/DD/YYYY HH24:MI:SS'));
Insert into REG_USERS
   (ID, NAME, CREATE_DATE)
 Values
   (10, 'lisi', TO_DATE('10/27/2009 23:52:58', 'MM/DD/YYYY HH24:MI:SS'));
Insert into REG_USERS
   (ID, NAME, CREATE_DATE)
 Values
   (11, 'sd', TO_DATE('10/27/2009 23:52:58', 'MM/DD/YYYY HH24:MI:SS'));
Insert into REG_USERS
   (ID, NAME, CREATE_DATE)
 Values
   (22, 'lisi', TO_DATE('07/27/2009 23:53:25', 'MM/DD/YYYY HH24:MI:SS'));
COMMIT;

 

 

 解题答案：

 

1 统计出每个月的注册人数
SELECT create_month||'月' as month,(SELECT COUNT (*)  FROM reg_users  WHERE TO_CHAR (create_date, 'MM') = t1.create_month) total  FROM (SELECT DISTINCT TO_CHAR (create_date, 'MM') create_month FROM reg_users ORDER BY create_month) t1;

 

MON      TOTAL
------    ----------
05月           1                                                            
07月           3                                    
08月           3                                                        
09月           1                                                        
10月           7 

5 rows selected.

 

******20091105补充******

    以上解法其实不严谨，只是按月进行了统计，未对年进行统计，比如再执行以下SQL插入一条记录：

Insert into REG_USERS
   (ID, NAME, CREATE_DATE)
 Values
   (22, 'lisi', TO_DATE('10/27/2008 23:53:25', 'MM/DD/YYYY HH24:MI:SS'));

 

    即插入一条2008年10月注册的记录，若用以上解法则10月的注册人数统计TOTAL为8，这样就有点不符合题意，正确的结果应该将数据按年月进行统计，SQL如下：

select to_char(create_date,'yyyy-MM'),count(create_date) from reg_users group by to_char(create_date,'yyyy-MM');

执行结果为：

  MON            TOTAL
-----------        ----------
2009-05           1                                                            
2009-07           3                                    
2009-08           3                                                        
2009-09           1                                                        
2009-10           7
2008-10           1 

6 rows selected.

2 列出用户名相同的记录 
SELECT * FROM reg_users
    WHERE NAME IN (SELECT NAME FROM reg_users
                                                      GROUP BY NAME
                                                      HAVING COUNT (NAME) > 1);

 

 

 

        ID NAME                           CREATE_DA
---------- ------------------------------ ---------
        13 lisi                           27-八月-09                   
        10 lisi                           27-十月-09                   
        22 lisi                           27-七月-09                   
        12 sd                           27-五月-09                    
        11 sd                           27-十月-09  

5 rows selected.

                 
3 将用户名相同的记录插入samename_users表
INSERT INTO samename_users(ID, NAME)
     SELECT ID, NAME FROM reg_users
                                 WHERE NAME IN (SELECT  NAME  FROM reg_users
                                                                                     GROUP BY NAME
                                                                                     HAVING COUNT (NAME) > 1);

 

SELECT * FROM samename_users;

 

        ID NAME                          
---------- --------
        13 lisi   
        10 lisi  
        22 lisi 
        12 sd  
        11 sd   

5 rows selected.