在一个已经实放的项目中,有一个纵表变横表的需求,实现代码如下:
1
2
select
a.FProjectNO, a.FProjectName,
2
as
FRegister, a.FRegisterDate
as
FInsertedDate,
3
case
b02.FIsVised
when
1
then
2
else
0
end
as
FTrading,
4
case
b03.FIsVised
when
1
then
2
else
0
end
as
FPlanChecking,
5
case
b14.FIsVised
when
1
then
2
else
0
end
as
FTender,
6
case
b06.FIsVised
when
1
then
2
else
0
end
as
FQuality,
7
case
b05.FIsVised
when
1
then
2
else
0
end
as
FSafety,
8
case
b07.FIsVised
when
1
then
2
else
0
end
as
FCost,
9
case
b08.FIsVised
when
1
then
2
else
0
end
as
FAudit,
10
case
b09.FIsVised
when
1
then
2
else
0
end
as
FArrears,
11
case
b10.FApproveStatus
%
20
when
10
then
2
else
0
end
as
FLicence
12
from
PRO_BaseInfo a
left
outer
join
13
PRO_NodeStatus b02
on
b02.FProjectNO
=
a.FProjectNO
and
b02.FNodeID
=
11402
left
outer
join
14
PRO_NodeStatus b03
on
b03.FProjectNO
=
a.FProjectNO
and
b03.FNodeID
=
11403
left
outer
join
15
PRO_NodeStatus b14
on
b14.FProjectNO
=
a.FProjectNO
and
b14.FNodeID
=
11414
left
outer
join
16
PRO_NodeStatus b06
on
b06.FProjectNO
=
a.FProjectNO
and
b06.FNodeID
=
11406
left
outer
join
17
PRO_NodeStatus b05
on
b05.FProjectNO
=
a.FProjectNO
and
b05.FNodeID
=
11405
left
outer
join
18
PRO_NodeStatus b07
on
b07.FProjectNO
=
a.FProjectNO
and
b07.FNodeID
=
11407
left
outer
join
19
PRO_NodeStatus b08
on
b08.FProjectNO
=
a.FProjectNO
and
b08.FNodeID
=
11408
left
outer
join
20
PRO_NodeStatus b09
on
b09.FProjectNO
=
a.FProjectNO
and
b09.FNodeID
=
11409
left
outer
join
21
PRO_NodeStatus b10
on
b10.FProjectNO
=
a.FProjectNO
and
b10.FNodeID
=
11410
22
where
a.FStatus
=
70
and
a.FIsDeleted
=
0
![](/Images/OutliningIndicators/None.gif)
2
![](/Images/OutliningIndicators/None.gif)
3
![](/Images/OutliningIndicators/None.gif)
4
![](/Images/OutliningIndicators/None.gif)
5
![](/Images/OutliningIndicators/None.gif)
6
![](/Images/OutliningIndicators/None.gif)
7
![](/Images/OutliningIndicators/None.gif)
8
![](/Images/OutliningIndicators/None.gif)
9
![](/Images/OutliningIndicators/None.gif)
10
![](/Images/OutliningIndicators/None.gif)
11
![](/Images/OutliningIndicators/None.gif)
12
![](/Images/OutliningIndicators/None.gif)
13
![](/Images/OutliningIndicators/None.gif)
14
![](/Images/OutliningIndicators/None.gif)
15
![](/Images/OutliningIndicators/None.gif)
16
![](/Images/OutliningIndicators/None.gif)
17
![](/Images/OutliningIndicators/None.gif)
18
![](/Images/OutliningIndicators/None.gif)
19
![](/Images/OutliningIndicators/None.gif)
20
![](/Images/OutliningIndicators/None.gif)
21
![](/Images/OutliningIndicators/None.gif)
22
![](/Images/OutliningIndicators/None.gif)
此表记录每天以几何级数增长,结果是客户一再反映太慢。经过测试,确实慢,首次执行3分钟,再次执行3秒。
决定优化,代码如下:
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
执行结果为2秒,优化成功。
此思路为经老五再三埋汰的条件下指点而成。
开始时没有用Group,结果是多条记录。
这种方法以前用过,离代码远了,思路不开阔了。