SQL语句的优化问题

conghui 2007-04-05 12:53:34

select a.ProjectGUID,a.ProjectName
,isnull((select '√' guid from ProjectState where ProjectGUID=a.ProjectGUID and ProjectPhase=a.ProjectStates and StateCode='Build-01'),'×') 项目建议书
,isnull((select '√' guid from ProjectState where ProjectGUID=a.ProjectGUID and ProjectPhase=a.ProjectStates and StateCode='Build-02'),'×') 工程可行性研究
,isnull((select '√' guid from ProjectState where ProjectGUID=a.ProjectGUID and ProjectPhase=a.ProjectStates and StateCode='Build-03'),'×') 项目立项
,isnull((select '√' guid from ProjectState where ProjectGUID=a.ProjectGUID and ProjectPhase=a.ProjectStates and StateCode='Build-04'),'×') 工程规划
,isnull((select '√' guid from ProjectState where ProjectGUID=a.ProjectGUID and ProjectPhase=a.ProjectStates and StateCode='Build-05'),'×') 勘测红线
,isnull((select '√' guid from ProjectState where ProjectGUID=a.ProjectGUID and ProjectPhase=a.ProjectStates and StateCode='Build-06'),'×') 地形图测量
,isnull((select '√' guid from ProjectState where ProjectGUID=a.ProjectGUID and ProjectPhase=a.ProjectStates and StateCode='Build-07'),'×') 工程地质勘察
,isnull((select '√' guid from ProjectState where ProjectGUID=a.ProjectGUID and ProjectPhase=a.ProjectStates and StateCode='Build-08'),'×') 环境评价
,isnull((select '√' guid from ProjectState where ProjectGUID=a.ProjectGUID and ProjectPhase=a.ProjectStates and StateCode='Build-09'),'×') 地质灾害评估
,isnull((select '√' guid from ProjectState where ProjectGUID=a.ProjectGUID and ProjectPhase=a.ProjectStates and StateCode='Build-10'),'×') 初步设计
,isnull((select '√' guid from ProjectState where ProjectGUID=a.ProjectGUID and ProjectPhase=a.ProjectStates and StateCode='Build-11'),'×') 规划用地红线
,isnull((select '√' guid from ProjectState where ProjectGUID=a.ProjectGUID and ProjectPhase=a.ProjectStates and StateCode='Build-12'),'×') 土地测绘
,isnull((select '√' guid from ProjectState where ProjectGUID=a.ProjectGUID and ProjectPhase=a.ProjectStates and StateCode='Build-13'),'×') 土地呈报表
,isnull((select '√' guid from ProjectState where ProjectGUID=a.ProjectGUID and ProjectPhase=a.ProjectStates and StateCode='Build-14'),'×') 建设管理招投标
,isnull((select '√' guid from ProjectState where ProjectGUID=a.ProjectGUID and ProjectPhase=a.ProjectStates and StateCode='Build-15'),'×') 施工图设计及审查
,isnull((select '√' guid from ProjectState where ProjectGUID=a.ProjectGUID and ProjectPhase=a.ProjectStates and StateCode='Build-16'),'×') 征地拆迁
,isnull((select '√' guid from ProjectState where ProjectGUID=a.ProjectGUID and ProjectPhase=a.ProjectStates and StateCode='Build-17'),'×') 财务决算
,isnull((select '√' guid from ProjectState where ProjectGUID=a.ProjectGUID and ProjectPhase=a.ProjectStates and StateCode='Build-18'),'×') 施工招投标
,isnull((select '√' guid from ProjectState where ProjectGUID=a.ProjectGUID and ProjectPhase=a.ProjectStates and StateCode='Build-19'),'×') 监理招投标
,isnull((select '√' guid from ProjectState where ProjectGUID=a.ProjectGUID and ProjectPhase=a.ProjectStates and StateCode='Build-20'),'×') 建设工程规划许可证
,isnull((select '√' guid from ProjectState where ProjectGUID=a.ProjectGUID and ProjectPhase=a.ProjectStates and StateCode='Build-21'),'×') 建设工程施工许可证
,isnull((select '√' guid from ProjectState where ProjectGUID=a.ProjectGUID and ProjectPhase=a.ProjectStates and StateCode='Build-22'),'×') 工程设计变更
,isnull((select '√' guid from ProjectState where ProjectGUID=a.ProjectGUID and ProjectPhase=a.ProjectStates and StateCode='Build-23'),'×') 工程概算
,isnull((select '√' guid from ProjectState where ProjectGUID=a.ProjectGUID and ProjectPhase=a.ProjectStates and StateCode='Build-24'),'×') 工程决算
,isnull((select '√' guid from ProjectState where ProjectGUID=a.ProjectGUID and ProjectPhase=a.ProjectStates and StateCode='Build-25'),'×') 移交
,isnull((select '√' guid from ProjectState where ProjectGUID=a.ProjectGUID and ProjectPhase=a.ProjectStates and StateCode='Build-26'),'×') 竣工验收
--,isnull((select '√' guid from ProjectState where ProjectGUID=a.ProjectGUID and ProjectPhase=a.ProjectStates and StateCode='Build-26'),'×') 竣工验收

from ProjectInfo a
where
a.ProjectStates='test'
And a.ProjectAction=1
Order By a.ProjectType Asc, a.OrderIndex Asc

我这个SQL语句能不能再优化一下,这个SQL语句在查询时速度太慢了.
...全文
354 8 打赏 收藏 转发到动态 举报
写回复
用AI写文章
8 条回复
切换为时间正序
请发表友善的回复…
发表回复
chenzhuomin 2007-04-08
  • 打赏
  • 举报
回复
LZ应该把相关表的结构、数据存储规则和你要实现的目标简单说明一下,不然修改后的脚步可能是不能达到你的目标。
zyqqzy 2007-04-07
  • 打赏
  • 举报
回复
select case s.StateCode when 'Build-01' then '√' else '×' end as 项目建议书
.......
from ProjectInfo a
left outer join ProjectState s on s.ProjectGUID=a.ProjectGUID and s.ProjectPhase=a.ProjectStates
where
a.ProjectStates='test'
And a.ProjectAction=1
Order By a.ProjectType Asc, a.OrderIndex Asc
chairEDU 2007-04-07
  • 打赏
  • 举报
回复
为有更好扩展性:
#1. create tabel t (StateCode varchar(10),StateName varchar(20),idx int default(0))
Build-01 项目建议书 1
-------------------------------------------------------------------------------
#2.select a.ProjectGUID,a.ProjectName,case when b.ProjectPhase is null
then '×' else '√' end +t.StateName ProjectPhase
from ProjectInfo a left join ProjectStates b
on a.ProjectStates =b.ProjectPhase and a.ProjectName='test'
And a.ProjectAction=1,t
order by t.idx
#3.页面处理
hillhx 2007-04-06
  • 打赏
  • 举报
回复
先查出一个空结果放临时表,然后用多句UPDATE设置对钩
paoluo 2007-04-05
  • 打赏
  • 举报
回复
修改為用關聯來寫,應該會優化一些。

如下:

select a.ProjectGUID,a.ProjectName
,Max(Case StateCode When 'Build-01' Then '√' Else '×' End) 项目建议书
,Max(Case StateCode When 'Build-02' Then '√' Else '×' End) 工程可行性研究
,Max(Case StateCode When 'Build-03' Then '√' Else '×' End) 项目立项
,Max(Case StateCode When 'Build-04' Then '√' Else '×' End) 工程规划
,Max(Case StateCode When 'Build-05' Then '√' Else '×' End) 勘测红线
,Max(Case StateCode When 'Build-06' Then '√' Else '×' End) 地形图测量
,Max(Case StateCode When 'Build-07' Then '√' Else '×' End) 工程地质勘察
,Max(Case StateCode When 'Build-08' Then '√' Else '×' End) 环境评价
,Max(Case StateCode When 'Build-09' Then '√' Else '×' End) 地质灾害评估
,Max(Case StateCode When 'Build-10' Then '√' Else '×' End) 初步设计
,Max(Case StateCode When 'Build-11' Then '√' Else '×' End) 规划用地红线
,Max(Case StateCode When 'Build-12' Then '√' Else '×' End) 土地测绘
,Max(Case StateCode When 'Build-13' Then '√' Else '×' End) 土地呈报表
,Max(Case StateCode When 'Build-14' Then '√' Else '×' End) 建设管理招投标
,Max(Case StateCode When 'Build-15' Then '√' Else '×' End) 施工图设计及审查
,Max(Case StateCode When 'Build-16' Then '√' Else '×' End) 征地拆迁
,Max(Case StateCode When 'Build-17' Then '√' Else '×' End) 财务决算
,Max(Case StateCode When 'Build-18' Then '√' Else '×' End) 施工招投标
,Max(Case StateCode When 'Build-19' Then '√' Else '×' End) 监理招投标
,Max(Case StateCode When 'Build-20' Then '√' Else '×' End) 建设工程规划许可证
,Max(Case StateCode When 'Build-21' Then '√' Else '×' End) 建设工程施工许可证
,Max(Case StateCode When 'Build-22' Then '√' Else '×' End) 工程设计变更
,Max(Case StateCode When 'Build-23' Then '√' Else '×' End) 工程概算
,Max(Case StateCode When 'Build-24' Then '√' Else '×' End) 工程决算
,Max(Case StateCode When 'Build-25' Then '√' Else '×' End) 移交
,Max(Case StateCode When 'Build-26' Then '√' Else '×' End) 竣工验收
--,Max(Case StateCode When 'Build-26'End) Else '×' End) 竣工验收

from ProjectInfo a
Left Join ProjectState b
On a.ProjectGUID=b.ProjectGUID and a.ProjectStates = b.ProjectPhase
where
a.ProjectStates = 'test'
And a.ProjectAction = 1
Group By a.ProjectGUID,a.ProjectName
Order By a.ProjectType Asc, a.OrderIndex Asc
ankor 2007-04-05
  • 打赏
  • 举报
回复
试用case when搜索语句,看看情况有没明显的改变
CathySun118 2007-04-05
  • 打赏
  • 举报
回复
用left join应该会快些
中国风 2007-04-05
  • 打赏
  • 举报
回复
把表的连接用左连
ProjectInfo left join

22,206

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧