数据库相关 多重查询 导入 新建 数据表

1. 多重查询

select R.count , count(*)
FROM
    (SELECT think_problem.user_id
        ,count(think_problem.user_id) count
    FROM
        think_problem
    GROUP BY
        think_problem.user_id) R
GROUP BY 
    R.count

select count(*)
FROM
    (SELECT think_problem.user_id
        ,count(think_problem.user_id) count
    FROM
        think_problem
    GROUP BY
        think_problem.user_id) R
WHERE
    R.count=3

select Q3.SERVICE_USER 
                ,count(*) report_ti
    from qoe3 Q3 join think_problem TP on Q3.SERVICE_USER=TP.user_id 
    where Q3.START_TIME<TP.breakdown_time 
    group by Q3.SERVICE_USER

select count(*)
    from qoe3 Q3 join think_problem TP on Q3.SERVICE_USER=TP.user_id 
    where Q3.START_TIME<TP.breakdown_time 

select count(*)
    from qoe3 Q3 join think_problem TP on Q3.SERVICE_USER=TP.user_id 
    where Q3.START_TIME<TP.breakdown_time and Q3.SERVICE_USER IN (select R.user_id
                                                                                                                                    from (
                                                                                                                                                                    SELECT think_problem.user_id，                                                                                               count(think_problem.user_id) count
                                                                                                                                                FROM think_problem
                                                                                                                                                GROUP BY think_problem.user_id) R                                                                                                                                   where R.count=6)

//根据小时进行汇总
SELECT  HOUR(Q1.START_TIME),count(*)
FROM
qoe1 AS Q1
GROUP BY HOUR(Q1.START_TIME)

SELECT UI.num,count(*),LOG2(count(*))
from
(
    SELECT  Q1.user_id,COUNT(*) num
    FROM
    think_problem AS Q1
    GROUP BY (Q1.user_id)
) UI
GROUP BY UI.num

//统计在表Q4中出现的userid次数
select Q4.report_ti,count(*)
FROM
(   //找出Q2中和Q3中user_id相等，并且 start-time < break-time 的记录，并统计service-user出现次数，设为表Q4
    select Q3.SERVICE_USER,count(*) report_ti
    FROM
    (           //找出Q1中出现一次的user_id，结果为Q2
                SELECT UI.id,UI.num,UI.bktime
                from
                (
                        SELECT  Q1.user_id id,COUNT(*) num,Q1.breakdown_time bktime
                        FROM
                        think_problem AS Q1
                        GROUP BY (Q1.user_id)
                ) UI
                where UI.num=1
    ) Q2 join think_qoe3 Q3 on Q3.SERVICE_USER=Q2.id
    where Q3.START_TIME<Q2.bktime
    group by Q3.SERVICE_USER
) Q4 
GROUP BY Q4.report_ti

2.多重查询优化

#2.多重查询优化
select Q4.report_ti,count(*)
FROM
(  
select Q3.SERVICE_USER,count(*) report_ti
    FROM
    (           #找出Q1中出现一次的user_id，结果为Q2
                SELECT UI.id,UI.num,UI.bktime
                from
                (
                        SELECT  Q1.user_id id,COUNT(*) num,Q1.breakdown_time bktime
                        FROM
                        think_problem AS Q1
                        GROUP BY (Q1.user_id)  ORDER BY NULL
                ) UI
                where UI.num=1
    ) Q2 join think_qoe1 Q3 on Q3.SERVICE_USER=Q2.id
    where Q3.START_TIME<Q2.bktime
    group by Q3.SERVICE_USER ORDER BY null
) Q4 
GROUP BY Q4.report_ti 

select Q4.report_ti,count(*)
FROM
(  
select Q3.SERVICE_USER,count(*) report_ti
    FROM
    (           #找出Q1中出现一次的user_id，结果为Q2
                SELECT UI.id,UI.num,UI.bktime,count(num)
                from
                (
                        SELECT  Q1.user_id id,COUNT(*) num,Q1.breakdown_time bktime
                        FROM
                        think_problem AS Q1
                        GROUP BY (Q1.user_id)  ORDER BY NULL
                ) UI
                where UI.num=1
    ) Q2 join think_qoe1 Q3 on Q3.SERVICE_USER=Q2.id
   where Q3.START_TIME<Q2.bktime
    group by Q3.SERVICE_USER ORDER BY null
) Q4 
GROUP BY Q4.report_ti 

#新建联合主键
#add constraint:添加约束
alter TABLE think_user_topsetBox 
ADD CONSTRAINT pk_think_user_topsetBox 
PRIMARY KEY(user_call_num,top_set_box_onload_num);

3.将一张表的数据导入到另一张表

#如果2张表的字段一致，并且希望插入全部数据
INSERT
INTO 
目标表 SELECT
* FROM
来源表;
如：
insert
into 
insertTest select
* from
insertTest2;

#如果只希望导入指定字段，可以用这种方法：

INSERT
INTO 
目标表 (字段1, 字段2, ...) SELECT
字段1, 字段2, ... FROM
来源表;(这里的话字段必须保持一致)
如：
insert
into 
insertTest2(id) select
id from
insertTest2;

#如果您需要只导入目标表中不存在的记录，可以使用这种方法：
INSERT
INTO 
目标表  
 (字段1,
 字段2, ...)  
 SELECT
字段1, 字段2, ...  
 FROM
来源表  
 WHERE
not 
exists (select
* from
目标表  
 where
目标表.比较字段 = 来源表.比较字段); 

 #如：
 #1>.插入多条记录：

insert
into 
insertTest2
(id,name)
select
id,name
from
insertTest
where
not 
exists (select
* from
insertTest2
where
insertTest2.id=insertTest.id);

 2>.插入一条记录：
insert
into 
insertTest    
(id,
name)   
SELECT
100, 'liudehua'   
FROM
dual    
WHERE
not 
exists (select
* from
insertTest    
where
insertTest.id = 100);

4.将一张表的数据导入到新建的表

create table 新表表名 as select * from 旧表表名
//2015.06.05更新
create table QY_Nanjing_qoe1_norepair
as 
SELECT *
FROM
(
SELECT Q.id,Q.SERVICE_USER,Q.SEVERITY,Q.ALARM_NUM,Q.LOSSRATE,Q.MEDIARATE,Q.VSTQ,Q.MOS_VALUE,Q.START_TIME,Q.END_TIME
FROM
qoe1zeroclean Q
where Q.SERVICE_USER LIKE "025%"
)A

//乘法运算
select sum(multi) su,sum(A.top_set_box_onload_num) coun
FROM
(
SELECT * ,top_set_box_onload_num*top_set_box_num_count multi
FROM
think_user_top_set_box
where 
user_call_num=4
) A

5.将一张表中某列的重复值删除后，整体存入新建的表中

create table NLP30WgoodComment
as 
SELECT A.id,A.goods_id,A.goods_star,A.buy_time,A.comment_time,A.comment_url,A.goods_comment
FROM
(
SELECT *,COUNT(DISTINCT Q.goods_comment)
FROM
comment_test Q
GROUP BY Q.goods_comment
)A