select R.count , count(*)
FROM
(SELECT think_problem.user_id
,count(think_problem.user_id) countFROM
think_problem
GROUPBY
think_problem.user_id) R
GROUPBY
R.count
selectcount(*)
FROM
(SELECT think_problem.user_id
,count(think_problem.user_id) countFROM
think_problem
GROUPBY
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
selectcount(*)
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) countFROM think_problem
GROUPBY think_problem.user_id) R where R.count=6)
//根据小时进行汇总
SELECTHOUR(Q1.START_TIME),count(*)
FROM
qoe1 AS Q1
GROUPBYHOUR(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
GROUPBY (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
groupby Q3.SERVICE_USER
) Q4
GROUPBY 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
GROUPBY (Q1.user_id) ORDERBYNULL
) UI
where UI.num=1
) Q2 join think_qoe1 Q3 on Q3.SERVICE_USER=Q2.id
where Q3.START_TIME<Q2.bktime
groupby Q3.SERVICE_USER ORDERBYnull
) Q4
GROUPBY 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
GROUPBY (Q1.user_id) ORDERBYNULL
) UI
where UI.num=1
) Q2 join think_qoe1 Q3 on Q3.SERVICE_USER=Q2.id
where Q3.START_TIME<Q2.bktime
groupby Q3.SERVICE_USER ORDERBYnull
) Q4
GROUPBY Q4.report_ti
#如果2张表的字段一致,并且希望插入全部数据
INSERTINTO
目标表 SELECT
* FROM
来源表;
如:
insertinto
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
来源表
WHEREnot
exists (select
* from
目标表
where
目标表.比较字段 = 来源表.比较字段);
#如:#1>.插入多条记录:
insert
into
insertTest2
(id,name)
select
id,name
from
insertTest
wherenot
exists (select
* from
insertTest2
where
insertTest2.id=insertTest.id);
2>.插入一条记录:
insert
into
insertTest
(id,
name)
SELECT100, 'liudehua' FROM
dual
WHEREnot
exists (select
* from
insertTest
where
insertTest.id = 100);
4.将一张表的数据导入到新建的表
createtable 新表表名 asselect * from 旧表表名
//2015.06.05更新
createtable QY_Nanjing_qoe1_norepair
asSELECT *
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
//乘法运算
selectsum(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.将一张表中某列的重复值删除后,整体存入新建的表中
createtable NLP30WgoodComment
asSELECT 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
GROUPBY Q.goods_comment
)A