优化存储过程，帮忙谢谢

2008-03-05 03:46:44 155 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

基于Simulink的直流电机双闭环调速系统抗扰动仿真与参数优化本文详细介绍了基于Simulink的直流电机双闭环调速系统仿真与参数优化方法。通过构建转速外环与电流内环的仿真模型，阐述了利用工程设计法整定PI参数的核心步骤，并重点探讨了如何优化系统参数以增强其抗负载突变等扰动的能力，实现快速、平稳的调速性能。阅读详情



select Convert(nvarchar(2),d.CheckTime,101) as [month],

'bendi'=cast(cast(sum(case when p.CityID = 4 then 1 else 0 end)*100.0/count(p.PeopleID) as int) as varchar)+'%',

'shanghai'=cast(cast(sum(case when p.CityID = 5 then 1 else 0 end)*100.0/count(p.PeopleID) as int) as varchar)+'%',

'waidi'=cast(cast(sum(case when p.CityID = 6 then 1 else 0 end)*100.0/count(p.PeopleID) as int) as varchar)+'%',

'haiwai'=cast(cast(sum(case when p.CityID = 9 then 1 else 0 end)*100.0/count(p.PeopleID) as int) as varchar)+'%',

'waiguo'=cast(cast(sum(case when p.CountryID = 5 then 1 else 0 end)*100.0/count(p.PeopleID) as int) as varchar)+'%',

'gangao'=cast(cast(sum(case when p.CityID = 7 or p.CityID = 8 then 1 else 0 end)*100.0/count(p.PeopleID) as int) as varchar)+'%'



from Peoples p inner join Country as c on p.CountryID = c.CountryID inner join Deals d on p.PeopleID = d.BuyID 

inner join  Houses h on h.HouseID=d.HouseID 

where h.XiangMu like  '%上海%' and d.CheckTime between '2007-1-1' and '2008-1-1' and h.FirstHandID = 1

group by Convert(nvarchar(2),d.CheckTime,101)



union all

select '合计' as [month],

'bendi'=cast(cast(sum(case when p.CityID = 4 then 1 else 0 end)*100.0/count(p.PeopleID) as int) as varchar)+'%',

'shanghai'=cast(cast(sum(case when p.CityID = 5 then 1 else 0 end)*100.0/count(p.PeopleID) as int) as varchar)+'%',

'waidi'=cast(cast(sum(case when p.CityID = 6 then 1 else 0 end)*100.0/count(p.PeopleID) as int) as varchar)+'%',

'haiwai'=cast(cast(sum(case when p.CityID = 9 then 1 else 0 end)*100.0/count(p.PeopleID) as int) as varchar)+'%',

'waiguo'=cast(cast(sum(case when p.CountryID = 5 then 1 else 0 end)*100.0/count(p.PeopleID) as int) as varchar)+'%',

'gangao'=cast(cast(sum(case when p.CityID = 7 or p.CityID = 8 then 1 else 0 end)*100.0/count(p.PeopleID) as int) as varchar)+'%'



from Peoples p inner join Country as c on p.CountryID = c.CountryID inner join Deals d on p.PeopleID = d.BuyID inner join  Houses h on h.HouseID=d.HouseID 

where h.XiangMu like  '%上海%' and d.CheckTime between '2007-1-1' and '2008-1-1' and h.FirstHandID = 1

order by [month]

帮忙优化下，谢谢。

...全文

MySQL表名大小写敏感问题终极解决：lower_case_table_names=1不生效的3个隐藏坑本文深入剖析了MySQL表名大小写敏感问题中，设置lower_case_table_names=1参数不生效的三个核心原因。关键在于理解该参数只能在数据库初始化前设置，并需排查配置文件优先级与正确停止服务的顺序。文章提供了从Windows迁移到Linux的完整避坑指南与验证方法，帮助DBA和开发者彻底解决这一跨平台兼容性难题。阅读详情

wishY

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

Andy-W 2008-03-05

打赏
举报

这样情况，使用临时表会快的，
如把union all前统计的数据insert into #Tmp，
再对#tmp进行统计,try:

SET NOCOUNT ON
select Convert(nvarchar(2),d.CheckTime,101) as [month],
'bendi'=cast(cast(sum(case when p.CityID = 4 then 1 else 0 end)*100.0/count(p.PeopleID) as int) as varchar)+'%',
'shanghai'=cast(cast(sum(case when p.CityID = 5 then 1 else 0 end)*100.0/count(p.PeopleID) as int) as varchar)+'%',
'waidi'=cast(cast(sum(case when p.CityID = 6 then 1 else 0 end)*100.0/count(p.PeopleID) as int) as varchar)+'%',
'haiwai'=cast(cast(sum(case when p.CityID = 9 then 1 else 0 end)*100.0/count(p.PeopleID) as int) as varchar)+'%',
'waiguo'=cast(cast(sum(case when p.CountryID = 5 then 1 else 0 end)*100.0/count(p.PeopleID) as int) as varchar)+'%',
'gangao'=cast(cast(sum(case when p.CityID = 7 or p.CityID = 8 then 1 else 0 end)*100.0/count(p.PeopleID) as int) as varchar)+'%'

INSERT INTO #Tmp
from Peoples p inner join Country as c on p.CountryID = c.CountryID inner join Deals d on p.PeopleID = d.BuyID
inner join Houses h on h.HouseID=d.HouseID
where h.FirstHandID = 1 and d.CheckTime between '2007-1-1' and '2008-1-1' and h.XiangMu like '%上海%'
group by Convert(nvarchar(2),d.CheckTime,101)

SELECT * FROM #Tmp
UNION ALL
SELECT '合计',SUM(bendi),... SUM(gangao) FROM #Tmp

DROP TABLE #Tmp

ojuju10 2008-03-05

打赏
举报

用系统自带的rollup

ojuju10 2008-03-05

打赏
举报

最好写成动态的Sql

CoffeeShop 2008-03-05

打赏
举报

需要做什么事情说不定有更好的方法

-狙击手- 2008-03-05

打赏
举报

索引与查询的关系大很了

wishY 2008-03-05

打赏
举报

恩，检查好多遍索引了，我不知道和数据有关系不。现在大约每张表有100多W。

wishY 2008-03-05

打赏
举报

哦，二次查询好象好一点点了。

-狙击手- 2008-03-05

打赏
举报

索引合理吗？

wishY 2008-03-05

打赏
举报

to : happyflystone
和之前速度差不多，大约需要1分钟。

数据库数据有点多。

dawugui 2008-03-05

打赏
举报

参考如下行列转换:

/*

普通行列转换

(爱新觉罗.毓华 2007-11-18于海南三亚)



假设有张学生成绩表(tb)如下:

Name Subject Result

张三 语文　　74

张三 数学　　83

张三 物理　　93

李四 语文　　74

李四 数学　　84

李四 物理　　94

*/



-------------------------------------------------------------------------

/*

想变成 

姓名         语文        数学        物理          

---------- ----------- ----------- ----------- 

李四         74          84          94

张三         74          83          93

*/



create table tb

(

   Name    varchar(10) ,

   Subject varchar(10) ,

   Result  int

)



insert into tb(Name , Subject , Result) values('张三' , '语文' , 74)

insert into tb(Name , Subject , Result) values('张三' , '数学' , 83)

insert into tb(Name , Subject , Result) values('张三' , '物理' , 93)

insert into tb(Name , Subject , Result) values('李四' , '语文' , 74)

insert into tb(Name , Subject , Result) values('李四' , '数学' , 84)

insert into tb(Name , Subject , Result) values('李四' , '物理' , 94)

go



--静态SQL,指subject只有语文、数学、物理这三门课程。

select name 姓名,

  max(case subject when '语文' then result else 0 end) 语文,

  max(case subject when '数学' then result else 0 end) 数学,

  max(case subject when '物理' then result else 0 end) 物理

from tb

group by name

/*

姓名         语文        数学        物理          

---------- ----------- ----------- ----------- 

李四         74          84          94

张三         74          83          93

*/



--动态SQL,指subject不止语文、数学、物理这三门课程。

declare @sql varchar(8000)

set @sql = 'select Name as ' + '姓名'

select @sql = @sql + ' , max(case Subject when ''' + Subject + ''' then Result else 0 end) [' + Subject + ']'

from (select distinct Subject from tb) as a

set @sql = @sql + ' from tb group by name'

exec(@sql) 

/*

姓名         数学        物理        语文          

---------- ----------- ----------- ----------- 

李四         84          94          74

张三         83          93          74

*/



-------------------------------------------------------------------

/*加个平均分，总分

姓名         语文        数学        物理        平均分                总分          

---------- ----------- ----------- ----------- -------------------- ----------- 

李四         74          84          94          84.00                252

张三         74          83          93          83.33                250

*/



--静态SQL,指subject只有语文、数学、物理这三门课程。

select name 姓名,

  max(case subject when '语文' then result else 0 end) 语文,

  max(case subject when '数学' then result else 0 end) 数学,

  max(case subject when '物理' then result else 0 end) 物理,

  cast(avg(result*1.0) as decimal(18,2)) 平均分,

  sum(result) 总分

from tb

group by name

/*

姓名         语文        数学        物理        平均分                总分          

---------- ----------- ----------- ----------- -------------------- ----------- 

李四         74          84          94          84.00                252

张三         74          83          93          83.33                250

*/



--动态SQL,指subject不止语文、数学、物理这三门课程。

declare @sql1 varchar(8000)

set @sql1 = 'select Name as ' + '姓名'

select @sql1 = @sql1 + ' , max(case Subject when ''' + Subject + ''' then Result else 0 end) [' + Subject + ']'

from (select distinct Subject from tb) as a

set @sql1 = @sql1 + ' , cast(avg(result*1.0) as decimal(18,2)) 平均分,sum(result) 总分 from tb group by name'

exec(@sql1) 

/*

姓名         数学        物理        语文        平均分                总分          

---------- ----------- ----------- ----------- -------------------- ----------- 

李四         84          94          74          84.00                252

张三         83          93          74          83.33                250

*/



drop table tb	



---------------------------------------------------------

---------------------------------------------------------

/*

如果上述两表互相换一下：即



姓名 语文 数学 物理

张三 74　　83　　93

李四 74　　84　　94



想变成 

Name       Subject Result      

---------- ------- ----------- 

李四         语文      74

李四         数学      84

李四         物理      94

张三         语文      74

张三         数学      83

张三         物理      93

*/



create table tb1

(

   姓名 varchar(10) ,

   语文 int ,

   数学 int ,

   物理 int

)



insert into tb1(姓名 , 语文 , 数学 , 物理) values('张三',74,83,93)

insert into tb1(姓名 , 语文 , 数学 , 物理) values('李四',74,84,94)



select * from

(

  select 姓名 as Name , Subject = '语文' , Result = 语文 from tb1 

  union all

  select 姓名 as Name , Subject = '数学' , Result = 数学 from tb1

  union all

  select 姓名 as Name , Subject = '物理' , Result = 物理 from tb1

) t

order by name , case Subject when '语文' then 1 when '数学' then 2 when '物理' then 3 when '总分' then 4 end



--------------------------------------------------------------------

/*加个平均分,总分

Name       Subject     Result               

---------- -------    -------------------- 

李四         语文      74.00

李四         数学      84.00

李四         物理      94.00

李四         平均分    84.00

李四         总分      252.00

张三         语文      74.00

张三         数学      83.00

张三         物理      93.00

张三         平均分    83.33

张三         总分      250.00

*/



select * from

(

  select 姓名 as Name , Subject = '语文' , Result = 语文 from tb1 

  union all

  select 姓名 as Name , Subject = '数学' , Result = 数学 from tb1

  union all

  select 姓名 as Name , Subject = '物理' , Result = 物理 from tb1

  union all

  select 姓名 as Name , Subject = '平均分' , Result = cast((语文 + 数学 + 物理)*1.0/3 as decimal(18,2)) from tb1

  union all

  select 姓名 as Name , Subject = '总分' , Result = 语文 + 数学 + 物理 from tb1

) t

order by name , case Subject when '语文' then 1 when '数学' then 2 when '物理' then 3 when '平均分' then 4 when '总分' then 5 end



drop table tb1

-狙击手- 2008-03-05

打赏
举报

select isnull(Convert(nvarchar(2),d.CheckTime,101),'合计') as [month],

'bendi'=cast(cast(sum(case when p.CityID = 4 then 1 else 0 end)*100.0/count(p.PeopleID) as int) as varchar)+'%',

'shanghai'=cast(cast(sum(case when p.CityID = 5 then 1 else 0 end)*100.0/count(p.PeopleID) as int) as varchar)+'%',

'waidi'=cast(cast(sum(case when p.CityID = 6 then 1 else 0 end)*100.0/count(p.PeopleID) as int) as varchar)+'%',

'haiwai'=cast(cast(sum(case when p.CityID = 9 then 1 else 0 end)*100.0/count(p.PeopleID) as int) as varchar)+'%',

'waiguo'=cast(cast(sum(case when p.CountryID = 5 then 1 else 0 end)*100.0/count(p.PeopleID) as int) as varchar)+'%',

'gangao'=cast(cast(sum(case when p.CityID = 7 or p.CityID = 8 then 1 else 0 end)*100.0/count(p.PeopleID) as int) as varchar)+'%'



from Peoples p inner join Country as c on p.CountryID = c.CountryID inner join Deals d on p.PeopleID = d.BuyID 

inner join  Houses h on h.HouseID=d.HouseID 

where h.XiangMu like  '%上海%' and d.CheckTime between '2007-1-1' and '2008-1-1' and h.FirstHandID = 1

group by Convert(nvarchar(2),d.CheckTime,101)

with rollup

-狙击手- 2008-03-05

打赏
举报

select Convert(nvarchar(2),d.CheckTime,101) as [month],

'bendi'=cast(cast(sum(case when p.CityID = 4 then 1 else 0 end)*100.0/count(p.PeopleID) as int) as varchar)+'%',

'shanghai'=cast(cast(sum(case when p.CityID = 5 then 1 else 0 end)*100.0/count(p.PeopleID) as int) as varchar)+'%',

'waidi'=cast(cast(sum(case when p.CityID = 6 then 1 else 0 end)*100.0/count(p.PeopleID) as int) as varchar)+'%',

'haiwai'=cast(cast(sum(case when p.CityID = 9 then 1 else 0 end)*100.0/count(p.PeopleID) as int) as varchar)+'%',

'waiguo'=cast(cast(sum(case when p.CountryID = 5 then 1 else 0 end)*100.0/count(p.PeopleID) as int) as varchar)+'%',

'gangao'=cast(cast(sum(case when p.CityID = 7 or p.CityID = 8 then 1 else 0 end)*100.0/count(p.PeopleID) as int) as varchar)+'%'



from Peoples p inner join Country as c on p.CountryID = c.CountryID inner join Deals d on p.PeopleID = d.BuyID 

inner join  Houses h on h.HouseID=d.HouseID 

where h.XiangMu like  '%上海%' and d.CheckTime between '2007-1-1' and '2008-1-1' and h.FirstHandID = 1

group by Convert(nvarchar(2),d.CheckTime,101)

with rollup

lhsxsh 2008-03-05

打赏
举报

dawugui 2008-03-05

打赏
举报

静态的行列转换,估计不好优化.

-狙击手- 2008-03-05

打赏
举报

似乎可以用with rollup

nzperfect 2008-03-05

打赏
举报

很复杂但有50分

dawugui 2008-03-05

打赏
举报

很长很复杂.

优化存储过程 ，帮忙 谢谢

相关推荐

优化存储过程，帮忙谢谢