SQL WITH CUBE 的用法
select [name],numb,sum(fenshu) from dbo.PeopleInfo group by [name],numb with cube
结果为下列语句的总和
select [name],numb,sum(fenshu) from dbo.PeopleInfo group by [name],numb
select [name],sum(fenshu) from dbo.PeopleInfo group by [name]
select numb,sum(fenshu) from dbo.PeopleInfo group by numb
select sum(fenshu) from dbo.PeopleInfo
WITH CUBE 为聚合字段的所有排列组合再加一列不聚合为总和的记录，SELECT 中不含有的字段值为NULL
select [name],numb,sum(fenshu),grouping(numb) isaddtional from dbo.PeopleInfo group by [name],numb with rollup
having grouping(numb)=1
grouping(numb) 列的值为一NUMB列 WITH CUBE 后 额外透视出的列
用法规律 SELECT EmployeeId,spdb_orgno,spdb_vipclass,GROUPING(EmployeeId),GROUPING(spdb_orgno),GROUPING(spdb_vipclass),
SUM (spdb_countmonthbal)
FROM [PCRM_REPORT].[dbo].[tb_VipContactBusiness] group by EmployeeId,spdb_orgno,spdb_vipclass with cube
having GROUPING(EmployeeId)=1 and GROUPING(spdb_orgno)=1 and GROUPING(spdb_vipclass)=1
在统计时如果 如果要各自多列 看统计结果时 可以CUBE 一次得到，
拿需要的结果， HAVING GROUPING 对应规律即可
select
base.org_number,
base.account_number,
GROUPING(base.account_number) as g1,
COUNT(1) as cuscount,
Sum(detail.Spdb_lastmonthbal) as productmonthbal,
Sum(detail.spdb_monthavgbal9) as productmonthavg
from
pcrm_report.dbo.tb_PCRM_ReportBase base with(nolock)
inner join
pcrm_mscrm.dbo.spdb_proddetail detail with(nolock) on
base.account_number = detail.Spdb_accountnumber
where
detail.Spdb_productnumber like '01%'
and detail.spdb_monthavgbal9 >0
group by
GROUPING sets
(
base.org_number,
(base.account_number,base.org_number)
)
grouping set 更灵活， 比起GROUP BY UOION ALL 效率更高
; WITH ShowSubDepartment(bid,subid, name)
AS
(
-- Anchor Member (AM)
SELECT BusinessUnitId,ParentBusinessUnitId,DivisionName
FROM PCRM_MSCRM.dbo.BusinessUnitBase
WHERE DivisionName='980000'
UNION ALL
-- Recursive Member (RM)
SELECT BusinessUnitId,ParentBusinessUnitId,DivisionName
FROM PCRM_MSCRM.dbo.BusinessUnitBase AS curground
inner join ShowSubDepartment PrevGroud on
curground.ParentBusinessUnitId=PrevGroud.bid
)
select * from ShowSubDepartment
查询某一机构下的 所有下级机构 参数980000 可选
SELECT d.DivisionName,st.bid,st.subid
FROM BusinessUnitBase AS D
CROSS APPLY fn_getsubtree(D.DivisionName) AS ST
where DivisionName='990000'
CROSS APPLY 每个机构调用辖下机构表值函数， 得到所有的机构辖下记录
; WITH SetUnitlvl(bid, bname,subbid, lvl)
AS
(
-- Anchor Member (AM)
SELECT BusinessUnitId,DivisionName,ParentBusinessUnitId, 1
FROM PCRM_MSCRM.dbo.BusinessUnitBase
WHERE DivisionName='990000'
UNION ALL
-- Recursive Member (RM)
SELECT BusinessUnitId, DivisionName,ParentBusinessUnitId ,PrevGroud.lvl+1
FROM PCRM_MSCRM.dbo.BusinessUnitBase AS curground
inner join SetUnitlvl PrevGroud on
curground.ParentBusinessUnitId=PrevGroud.bid
)
select * from SetUnitlvl order by lvl
设置机构层级
--机构打平
select
a.DivisionName as UnitCode,
b.DivisionName as ParentUnitCode
into #UnitUser
from
PCRM_MSCRM.dbo.BusinessUnit a
left join
PCRM_MSCRM.dbo.BusinessUnit b on
b.BusinessUnitId = a.ParentBusinessUnitId and
b.IsDisabled = 0
where
a.IsDisabled = 0
select * from #UnitUser ;
with tmp( ParentUnitCode, UnitCode,lvl,lel1,lel2,lel3,lel4,lel5,lel6,lel7,lel8,lel9)
as(
select ParentUnitCode, UnitCode ,1,
UnitCode as lel1,UnitCode as lel2,UnitCode as lel3,
UnitCode as lel4,UnitCode as lel5,UnitCode as lel6,
UnitCode as lel7,UnitCode as lel8,UnitCode as lel9
from #UnitUser where ParentUnitCode is null
union all
select cur.ParentUnitCode, cur.UnitCode,prev.lvl+1,
-- 规律对于lel2来说 除了第1层级机构的lel2 为它自己的CODE,其他都是以上一层级的 lel2 来赋值自己的lel2
对于lel3来说 除了第1,2层级机构的lel3 为它自己的CODE,其他都是以上一层级的 lel3来赋值自己的lel3
以此类推
prev.lel1 as lel1,
case lvl
when 1 then cur.UnitCode
else prev.lel2
end as lel2,
case lvl
when 1 then cur.UnitCode
when 2 then cur.UnitCode
else prev.lel3
end as lel3,
case lvl
when 1 then cur.UnitCode
when 2 then cur.UnitCode
when 3 then cur.UnitCode
else prev.lel4
end as lel4,
case lvl
when 1 then cur.UnitCode
when 2 then cur.UnitCode
when 3 then cur.UnitCode
when 4 then cur.UnitCode
else prev.lel5
end as lel5,
case lvl
when 1 then cur.UnitCode
when 2 then cur.UnitCode
when 3 then cur.UnitCode
when 4 then cur.UnitCode
when 5 then cur.UnitCode
else prev.lel6
end as lel6,
case lvl
when 1 then cur.UnitCode
when 2 then cur.UnitCode
when 3 then cur.UnitCode
when 4 then cur.UnitCode
when 5 then cur.UnitCode
when 6 then cur.UnitCode
else prev.lel7
end as lel7,
case lvl
when 1 then cur.UnitCode
when 2 then cur.UnitCode
when 3 then cur.UnitCode
when 4 then cur.UnitCode
when 5 then cur.UnitCode
when 6 then cur.UnitCode
when 7 then cur.UnitCode
else prev.lel8
end as lel8,
case lvl
when 1 then cur.UnitCode
when 2 then cur.UnitCode
when 3 then cur.UnitCode
when 4 then cur.UnitCode
when 5 then cur.UnitCode
when 6 then cur.UnitCode
when 7 then cur.UnitCode
when 8 then cur.UnitCode
else prev.lel9
end as lel9
from #UnitUser cur inner join tmp prev on
prev.UnitCode=cur.ParentUnitCode
)
select * from tmp
Stuff 用法 从字符串A 的 第1个位置往后的0个长度 插入字符串B
select STUFF('abcdefg',1,0,'1234') --结果为'1234abcdefg'
select STUFF('abcdefg',1,1,'1234') --结果为'1234bcdefg'
select STUFF('abcdefg',2,1,'1234') --结果为'a1234cdefg'
select STUFF('abcdefg',2,2,'1234') --结果为'a1234defg'
For xml path 将多行的结果合并成1行XML格式串，参数可以为空 或者XML 节点名
select stu_name,stu_course from stu_courses for xml path('');
--简单例子 PATH FOR XML
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([款号] nvarchar(21),[规格] nvarchar(22))
Insert #T
select N'A',N'毛巾' union all
select N'A',N'坯布' union all
select N'A',N'手巾' union all
select N'b',N'手机' union all
select N'b',N'电脑'
Go
--测试数据结束
SELECT [款号] ,
STUFF(( SELECT '/' + #T.[规格]
FROM #T
WHERE [款号] = a.[款号]
FOR
XML PATH('')
), 1, 1, '') AS [规格]
FROM #T a
GROUP BY a.[款号]
示例
with dic (productkeyname,productkey)
as
(
select
case when CHARINDEX('网银', aa.Name)>0 then '网银'
when CHARINDEX('及时语', aa.Name)>0 then '及时语'
when CHARINDEX('三方存管', aa.Name)>0 then '三方存管'
when CHARINDEX('银基通', aa.Name)>0 then '银基通'
when CHARINDEX('外汇宝', aa.Name)>0 then '外汇宝'
when CHARINDEX('黄金', aa.Name)>0 then '黄金'
when CHARINDEX('保险', aa.Name)>0 then '保险'
when CHARINDEX('周周赢', aa.Name)>0 then '周周赢'
else '' end as Name,
aa.ProductNumber
from
PCRM_MSCRM.dbo.ProductBase aa with(nolock)
inner join
pcrm_mscrm.dbo.spdb_category bb with(nolock) on
bb.SPDB_categoryno = aa.ProductNumber
where
aa.ProductNumber like '04%' and
(aa.Name like '%网银%' or aa.Name like '%及时语%' or aa.Name like '%三方存管%'
or aa.Name like '%银基通%' or aa.Name like '%外汇宝%'or aa.Name like '%黄金%'
or aa.Name like '%保险%' or aa.Name like '%周周赢%'
) and
bb.SPDB_level = 6
)
select productkeyname,stuff((select ','+productkey from dic where productkeyname =a.productkeyname for xml path('')),1,1,'') from dic a group by productkeyname
--取共有值和不共有值，都是唯一值
select Spdb_accountnumber from pcrm_mscrm.dbo.spdb_proddetail detail
intersect
select SPDB_accountnumber from PCRM_MSCRM.dbo.ContactBase
select Spdb_accountnumber from pcrm_mscrm.dbo.spdb_proddetail detail
except
select SPDB_accountnumber from PCRM_MSCRM.dbo.ContactBase
if object_id('tempdb..#tempcus') is not null
Begin
drop table #tempcus
End
EXEC sp_recompile procname
清除存储过程执行计划缓存
sp_create_plan_guide
@name = N‘recompile_Guide’,
@stmt =
N‘SELECT * FROM Sales.SalesOrderHeader h, Sales.Customer c,Sales.SalesTerritory t
WHERE h.CustomerID = c.CustomerID
AND c.TerritoryID = t.TerritoryID
AND CountryRegionCode = N”CA”;’,
@type = N‘SQL’,
@module_or_batch =NULL,
@params =NULL,
@hints = N‘OPTION (RECOMPILE)’
go
exec sp_control_plan_guide N‘drop’,N‘recompile_Guide’
上面的sp_create_plan_guide使用RECOMPILE参数，意思是说，每次碰到该语句，必须重新编译。sp_create_plan_guide运行后，该语句的执行计划缓存就被删除了，下次该语句再次执行就会重新编译。那么我为什么马上又删除这个plan guide呢？因为该语句的缓冲被清除后，我不希望该语句每次执行都重新编译，所以我删除了它，毕竟我执行sp_create_plan_guide的目的是删除该语句的执行计划缓存而已。所以如果你使用同样的手段，务必记得立即把sp_create_plan_guide建立的guide删除。
DBCC FREEPROCCACHE 清除缓存
GO
DBCC DROPCLEANBUFFERS 清除缓存
GO
exec sp_helpindex 查看索引
临时表在tempdb
use tempdb
exec sp_helpindex '#temptable'
统计信息
1、 在一个空表中有数据的改动。
2、 当统计信息创建时，表的行数只有500或以下，且后来统计对象中的引导列（统计信息的第一个字段数据）的更改次数大于500.
3、 当表的统计信息收集时，超过了500行，且统计对象的引导列（统计信息的第一个字段数据）后来更改次数超过500+表总行数的20%时
查看索引的统计信息更新时间
SELECT name AS index_name,
STATS_DATE(OBJECT_ID, index_id) AS StatsUpdated
FROM sys.indexes
WHERE OBJECT_ID = OBJECT_ID('dbo.TEST1')
GO
查看某表的所有统计信息列和名称
sp_helpstats 'Production.Product' ,'ALL'
查看统计信息基本信息
DBCC SHOW_STATISTICS(TEST1, PK_TEST1)
（以下为08版本,SQL 2014更新了计算公式，使预估行数偏大）
where xxxx=@aaa and cccc=@aaa
查询为参数时 ，无法准确预计，而取平均密度*表行数
xxxx 密度0.1 , cccc密度 0.2
0.1*0.2*1000=20
where xxxx=1 and cccc=2
查询为具体值时 ，密度取 当前值行数/总行数 预估计算公式不变
xxxx=1 有200行 cccc列有 100行 共1000行
200/1000 * 100/1000 *1000 =20 行
拼接字符串
IF isnull(@level,'')<>''
SET @sql=@sql + ' AND bs.spdb_level='''+ @level+''''
IF isnull(@Prjt_Name,'')<>''
SET @sql=@sql + ' AND main.Prjt_Name='''+ @Prjt_Name+''''
IF isnull(@Prjt_Stat_Cd,'')<>''
SET @sql=@sql + ' AND main.Prjt_Stat_Cd='''+ @Prjt_Stat_Cd+''''
declare @b datetime,@e datetime
set @b =getdate()
select * from dbo.contact
set @e =getdate()
select datediff(ms,@b,@e)
SET Statistics IO OFF
GO
SET Statistics TIME OFF
GO
CREATE NonCLUSTERED INDEX [NCIX_tb_contact] ON [dbo].[contact]
(
[NickName] ASC
)
DROP INDEX index_name ON talbe_name
ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY
--找客户表中持有产品的客户
select account_number,cust_name,accountclass from pcrm_report.dbo.tb_PCRM_ReportBase base with(nolock)
where
exists
(select 1 from pcrm_mscrm.dbo.spdb_proddetail detail with(nolock) where detail.Spdb_accountnumber=base.account_number)
select
ROW_NUMBER() over(partition by base.account_number order by base.account_number)
,*
from
pcrm_report.dbo.tb_PCRM_ReportBase base with(nolock)
inner join
pcrm_mscrm.dbo.spdb_proddetail detail with(nolock) on
base.account_number = detail.Spdb_accountnumber
where
detail.Spdb_productnumber like '01%' and
detail.spdb_monthavgbal9 >0
统计一张表某字段多数种类的时候，应该一次统计出，不是各自统计然后UNION ALL 连接
此为错误的SQL
select
base.org_number,
base.account_number,
base.accountclass,
'存款类' as productname,
Sum(detail.Spdb_lastmonthbal) as productmonthbal,
Sum(detail.spdb_monthavgbal9) as productmonthavg,
0 as IsHistory
from
pcrm_report.dbo.tb_PCRM_ReportBase base with(nolock)
inner join
pcrm_mscrm.dbo.spdb_proddetail detail with(nolock) on
base.account_number = detail.Spdb_accountnumber
where
detail.Spdb_productnumber like '01%'
and detail.spdb_monthavgbal9 >0
group by
base.org_number,
base.account_number,
base.accountclass
union all
select
base.org_number,
base.account_number,
base.accountclass,
'贷款类' as productname,
Sum(detail.Spdb_lastmonthbal) as productmonthbal,
Sum(detail.spdb_monthavgbal9) as productmonthavg,
0 as IsHistory
from
pcrm_report.dbo.tb_PCRM_ReportBase base with(nolock)
inner join
pcrm_mscrm.dbo.spdb_proddetail detail with(nolock) on
base.account_number = detail.Spdb_accountnumber
where
detail.Spdb_productnumber like '02%'
and detail.spdb_monthavgbal9 >0
group by
base.org_number,
base.account_number,
base.accountclass
DETAIL 表的 CASE WHEN 部分 为正确的写法
select base.accountclass,
base.org_number,
Sum(detail.productcount),
Sum(detail.productbal),
Sum(detail.productavg)
from
(select
case when Spdb_productnumber like '01%' then '存款类'
when Spdb_productnumber like '02%' then '贷款类'
when Spdb_productnumber like '0301%' then '结构性存款'
when Spdb_productnumber like '0302%' then '专项理财产品'
when Spdb_productnumber like '0304%' then '基金'
when Spdb_productnumber like '0306%' then '三方存款'
when Spdb_productnumber like '0303%' then '国债'
when Spdb_productnumber like '0305%' then '证券集合理财'
when Spdb_productnumber like '0310%' then '黄金'
when Spdb_productnumber like '0307%' then '保险'
end as spdb_productname,
Spdb_accountnumber,
COUNT(1) as productcount,
Sum(spdb_lastmonthbal) as productbal,
Sum(spdb_monthavgbal1) as productavg
from pcrm_mscrm.dbo.spdb_proddetail detail with(nolock)
group by
case when Spdb_productnumber like '01%' then '存款类'
when Spdb_productnumber like '02%' then '贷款类'
when Spdb_productnumber like '0301%' then '结构性存款'
when Spdb_productnumber like '0302%' then '专项理财产品'
when Spdb_productnumber like '0304%' then '基金'
when Spdb_productnumber like '0306%' then '三方存款'
when Spdb_productnumber like '0303%' then '国债'
when Spdb_productnumber like '0305%' then '证券集合理财'
when Spdb_productnumber like '0310%' then '黄金'
when Spdb_productnumber like '0307%' then '保险'
end ,
Spdb_accountnumber) detail
inner join
pcrm_report.dbo.tb_PCRM_ReportBase base with(nolock) on
base.account_number = detail.Spdb_accountnumber
group by
base.accountclass,
base.org_number
Union all 不是并行，每次都是独立执行，引起多次扫描
索引对GROUP BY 的影响
select fin_manager_no,accountclass,COUNT(1) from [Test].[dbo].[tb_ReportBase] group by fin_manager_no,accountclass
如建 fin_manager_no 单列索引，无法覆盖，扫描表
如建 fin_manager_no ,accountclass 多列索引 索引覆盖 非聚集索引扫描
CREATE NonCLUSTERED INDEX NIDX_fin_manager_no_accountclass ON [Test].[dbo].[tb_ReportBase]
(
fin_manager_no ASC,
accountclass ASC
)
select fin_manager_no,accountclass,SUM(lastyearbal) from [Test].[dbo].[tb_ReportBase] group by fin_manager_no,accountclass
如SUM(lastyearbal) ， lastyearbal 不在非聚集索引叶子节点,会扫描表
不会键查找，因为全量键查找不如表扫描
如要覆盖字段，索引建
CREATE NonCLUSTERED INDEX NIDX_fin_manager_no_accountclass ON [Test].[dbo].[tb_ReportBase]
(
fin_manager_no ASC,
accountclass ASC
)
INCLUDE (lastyearbal)
SQLServer中间接实现函数索引或者Hash索引
本文出处：http://www.cnblogs.com/wy123/p/6617700.html
dbcc ind ( TESTDB3, [dbo.Suppliers], -1)
DBCC PAGE (TESTDB3,1,109, 3);--也可以是DBCC PAGE (TESTDB3,1,2190, 1);
--SQL MERGE 用法
create table SourceTable(
id int ,
[DESC] varchar(50)
)
create table TargetTable(
id int ,
[DESC] varchar(50)
)
delete TargetTable
insert into TargetTable values (1,'存在更新')
insert into TargetTable values (2,'存在更新')
insert into TargetTable values (5,'不存在删除')
insert into TargetTable values (6,'不存在删除')
select * from SourceTable
select * from TargetTable
merge into TargetTable a --只对目标表DDL
using SourceTable b on a.id=b.id
when matched then --两表匹配
update set a.[DESC]=b.[DESC]
when not matched then --原表有，目标表没有
insert values(b.id,b.[DESC])
when not matched by source then --目标表有， 原表没有
delete
output $Action as [ACTION],inserted.id,inserted.[desc],deleted.id,deleted.[desc]
;
-- SQL查锁
01.use master
02.go
03.
04.--检索死锁进程
05.select spid, blocked, loginame, last_batch, status, cmd, hostname, program_name
06.from sysprocesses
07.where spid in
08.( select blocked from sysprocesses where blocked <> 0 ) or (blocked <>0)
KILL SPID 150 解锁
