题目:
两个数据库表,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.