<!-- [if gte mso 9]><xml>
<w:WordDocument>
<w:View>Normal</w:View>
<w:Zoom>0</w:Zoom>
<w:PunctuationKerning/>
<w:DrawingGridVerticalSpacing>7.8 磅</w:DrawingGridVerticalSpacing>
<w:DisplayHorizontalDrawingGridEvery>0</w:DisplayHorizontalDrawingGridEvery>
<w:DisplayVerticalDrawingGridEvery>2</w:DisplayVerticalDrawingGridEvery>
<w:ValidateAgainstSchemas/>
<w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid>
<w:IgnoreMixedContent>false</w:IgnoreMixedContent>
<w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText>
<w:Compatibility>
<w:SpaceForUL/>
<w:BalanceSingleByteDoubleByteWidth/>
<w:DoNotLeaveBackslashAlone/>
<w:ULTrailSpace/>
<w:DoNotExpandShiftReturn/>
<w:AdjustLineHeightInTable/>
<w:BreakWrappedTables/>
<w:SnapToGridInCell/>
<w:WrapTextWithPunct/>
<w:UseAsianBreakRules/>
<w:DontGrowAutofit/>
<w:UseFELayout/>
</w:Compatibility>
<w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel>
</w:WordDocument>
</xml><![endif]--><!-- [if gte mso 9]><xml>
<w:LatentStyles DefLockedState="false" LatentStyleCount="156">
</w:LatentStyles>
</xml><![endif]--><!--
/* Font Definitions */
@font-face
{font-family:宋体;
panose-1:2 1 6 0 3 1 1 1 1 1;
mso-font-alt:SimSun;
mso-font-charset:134;
mso-generic-font-family:auto;
mso-font-pitch:variable;
mso-font-signature:3 135135232 16 0 262145 0;}
@font-face
{font-family:"\@宋体";
panose-1:2 1 6 0 3 1 1 1 1 1;
mso-font-charset:134;
mso-generic-font-family:auto;
mso-font-pitch:variable;
mso-font-signature:3 135135232 16 0 262145 0;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{mso-style-parent:"";
margin:0cm;
margin-bottom:.0001pt;
text-align:justify;
text-justify:inter-ideograph;
mso-pagination:none;
font-size:10.5pt;
mso-bidi-font-size:12.0pt;
font-family:"Times New Roman";
mso-fareast-font-family:宋体;
mso-font-kerning:1.0pt;}
/* Page Definitions */
@page
{mso-page-border-surround-header:no;
mso-page-border-surround-footer:no;}
@page Section1
{size:612.0pt 792.0pt;
margin:72.0pt 90.0pt 72.0pt 90.0pt;
mso-header-margin:36.0pt;
mso-footer-margin:36.0pt;
mso-paper-source:0;}
div.Section1
{page:Section1;}
--><!-- [if gte mso 10]>
<mce:style><!--
/* Style Definitions */
table.MsoNormalTable
{mso-style-name:普通表格;
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-parent:"";
mso-padding-alt:0cm 5.4pt 0cm 5.4pt;
mso-para-margin:0cm;
mso-para-margin-bottom:.0001pt;
mso-pagination:widow-orphan;
font-size:10.0pt;
font-family:"Times New Roman";
mso-fareast-font-family:"Times New Roman";
mso-ansi-language:#0400;
mso-fareast-language:#0400;
mso-bidi-language:#0400;}
-->
<!-- [endif]-->
WITH
语句如下写法 其执行计划可以看的出，先执行一次查询，然后在进行二次统计。原来union all
每个分句部分都要进行一次统计查询。 t_prouser_suc
每周有2
千万条数据
原来采用create table t_tmp
临时表
又制造更多的物理IO
使用 oracle
临时表法 写法上很麻烦；
with
就只能用于到一条语句中；不能用到同个过程多条语句里；
INSERT
INTO
t_buy_range_analyze
with
t_lotbuymone
as
(
SELECT
f_lotid,f_lotname,f_username,
sum
(f_paymoney) F_BUYMONEY
FROM
t_prouser_suc t
WHERE
f_yearweek =
'201020'
GROUP
BY
F_LOTID,f_lotname,f_username
)
select
a.f_lotid,a.f_lotname,f_start,f_end,
count
(f_username),
sum
(f_buymoney)
from
(
select
*
from
t_lotbuymone
union
all
select
-
10
as
f_lotid,
'
数
'
as
f_lotname,f_username,
sum
(F_BUYMONEY)
as
F_BUYMONEY
from
t_lotbuymone
where
f_lotid
in
(
select
Distinct
f_lotid
from
t_base_lotclass
where
f_Lotbigid =
4
)
group
by
f_username
union
all
select
-
20
as
f_lotid,
'
竞
'
as
f_lotname,f_username,
sum
(F_BUYMONEY)
as
F_BUYMONEY
from
t_lotbuymone
where
f_lotid
in
(
select
Distinct
f_lotid
from
t_base_lotclass
where
f_Lotbigid =
3
)
group
by
f_username
) a
inner
join
t_prod_buyrang_config
b
on
a.f_lotid=b.f_lotid
and
a.f_buymoney
>= b.f_start
and
a.f_buymoney< b.f_end
group
by
a.f_lotid,a.f_lotname,f_start,f_end
分享到：
相关推荐
用法： 用于复杂查询时可以用临时表来暂存相关记录，能够提高效率、提高程序的可读性，类似于游标中的 my_cursor declare my_cursor cursor scroll for select 字段 from tablename 临时表分为：用户临时表和系统...
1、判断数据表是否存在 方法一： use yourdb; go if object_id(N'tablename',N'U') is not null print '存在' else print '不存在' 例如： use fireweb; go if object_id(N'TEMP_TBL',N'U') is not null print '...
tk-mybatis-dynamic-tablename tk.mybatis动态表操作
动态创建一个模型的多个table name， 并通过 Django ORM 操作
该sql如下： 代码如下:Select /*+ parallel(src, 8) */ distinct src.systemname as systemname , src.databasename as databasename , src.tablename as tablename , src.username as usernamefrom ...
MyDAC是一个很好的访问MySQL的Delphi控件组，性能非常好。 2012年12月份发布的版本源码，版本历史如下： 7.6.11 12-Dec-12 Rad Studio XE3 Update 1 is now required C++Builder 64-bit for Windows is supported...
Find out Stored Procs Which Referenced Specified TableName
Systemid,Tablename,Tabledefine,Tabletype,SystemName AS,Accessaries,成套件档案,,公共 AS,AutoSetFieldInf,用户自定义档案字段设置,,公共 AS,AutoSetTableInf,用户自定义档案,,公共 AS,Bank,开户银行档案,,公共 ...
SET @sqlStr ='insert into ' + @log_tableName +'(' + @sqlStr + 'OccurTime) ' + 'select ' + @sqlStr + 'getdate() as OccurTime from deleted' SET @sqlStr = 'CREATE TRIGGER '+@tableName+'DeleteTrigger ON...
在Oracle中若删除一个不存在的表,如 “DROP TABLE tableName”,则会提示: ORA-00942:表或视图不存在 若在程序中执行该语句则会报异常,这就需要我们在删除表前先判断该表是否存在,若存在则删除. DECLARE num NUMBER;...
9、说明：in 的使用方法 select * from table1 where a [not] in (‘值1’,’值2’,’值4’,’值6’) 10、说明：两张关联表，删除主表中已经在副表中没有的信息 delete from table1 where not exists ( select * ...
Delphi 复合查询数据库的例子，一次查询三个表，主要是这句SQL实现复合查询：select distinct 学生.姓名,班级.班级,班级.班主任,教师.年龄 as 教师年龄 from 学生,教师,班级 where 学生.班级=班级.班级 and 班级.班...
9、说明：in 的使用方法 select * from table1 where a [not] in (‘值1’,’值2’,’值4’,’值6’) 10、说明：两张关联表，删除主表中已经在副表中没有的信息 delete from table1 where not exists ( select * ...
9、说明：in 的使用方法 select * from table1 where a [not] in (‘值1’,’值2’,’值4’,’值6’) 10、说明：两张关联表，删除主表中已经在副表中没有的信息 delete from table1 where not exists ( select * ...
9、说明：in 的使用方法 select * from table1 where a [not] in (‘值1’,’值2’,’值4’,’值6’) 10、说明：两张关联表，删除主表中已经在副表中没有的信息 delete from table1 where not exists ( select * ...
9、说明：in 的使用方法 select * from table1 where a [not] in (‘值1’,’值2’,’值4’,’值6’) 10、说明：两张关联表，删除主表中已经在副表中没有的信息 delete from table1 where not exists ( select *...
单个表的删除： DELETE FROM tableName WHERE columnName = value; 删除表内的所有行： 即：保留表的结构、属性、索引 DELETE FROM tablename; DELETE * FROM tablename; 删除同一张表内的所有内容（删除数据、表...
set @SQL = 'select top ' + Cast(@PageSize as varchar) + ' ' + @Fields + ' from ' + @TableName + ' where ' + @OrderField + '<(select min(' + @OrderField + ') from (select top ' + Cast(@PageSize*(@...
索引类似于书籍的目录，可以快速定位到相关的数据，一个表可以有多个索引。 创建索引： create index idx_temp_name on temp(name); 组合索引： create index idx_temp_name$pwd on temp(name, pwd); 删除索引...
查看库德使用状况.sql 十分方便，无需浪费大量时间去查看哪些表中有数据。 可以列出各表中有多少行记录，等等... 创建略 查询如下 --显示结果 select * from TableSpace where RowsCount>0 --order by ...