优化笔记: jxrsfxrxx_D_20140916.gz
性能瓶颈在1734表的重复扫描。
----------------------------------想进一步研究,继续往下看------------------------------
1. 所有相似子查询合并成一个(不仅仅是相同,如WBPJRQ,DBPJ,WBPJ字段),合并方法见下面代码。
2. 如果仅是为了验证存在的话,可以把join改为exist或者in。比如第一段脚本的1018表和1090表相连。
在oracle和sqlserver中,join有3中算法,分别是循环迭代,组合,哈希。而这3种算法,都要进行全部匹配。MySQL没有组合连接,只有两种算法。
而exist 和 in会根据统计信息,自动选择用半连接算法,(not exist 和not in是反向半连接)。也就是说,假如a表与b表是1对多的,用join的话,会把所有记录都比对一遍。而用exist或in,只要找到第一个就返回,不再向下扫描。
3. 第一段和第三段可以用or合并到一起。合并后代码长度减少了一半,但是性能变的更差。
因为or有时候会破坏索引(不是一定会破坏,但是我们这个案例中确实破坏了)。解决办法就是用union把or拆成几个查询。
4. 选择扫多次索引,还是一次全表扫描,具体情况具体分析。比如这个案例。
1018表有71万数据,而第一段代码,返回的存在于1429的数据有1064条。第三段代码,返回存在于1090的数据有6243条。
也就是说,如果用or合并,全表扫描,要扫71万数据。而用索引union扫2次索引,只需扫约1064+6242,约1万的数据。(所有的都是约数,还有扫中间节点等其他开销,但是对于这个案例,开销可以忽略)
而对于1734表的扫描,我们需要的所有数据的父集合才4264的数据。所以对于1734的扫描,一次全表扫描远比扫n次索引效率高。
在这个案例中,最终结果返回7000左右,如果忽略掉优化器自动优化,需要扫描的数据页数约7000*子查询个数*索引层级(因为每次扫描只拿一条数据,所以加上索引的层级开销会成倍数放大。)
5.写sql思维要转一下,尽量批量操作。
因为数据库的最小io单位是页,一个页有8k大。也就是说,就算只操作一个2进制位,最少也要扫8k的数据。
打个比方,现在有100条数据,放在10个数据页里面,索引有5层(可能不太合理)。要拿其中10条数据,批量操作,走索引最少扫5个数据页(4个索引中间页,一个数据页),最多全表扫描10个数据页。而单条操作,如果走索引5*10=50个数据页(这还是在索引是聚集索引的情况下,如果是非聚集索引,再*2),不走索引10*10=100个数据页。数据量越大,这个杠杆越高。所以有时候相同的业务逻辑,不同的代码性能会差距很大。
|
|
------------------优化过脚本--------------
SELECT FXRH,
FXFL,
FXMC,
FXQC,
FXLXR,
FXRLXDH,
FXRCZ,
JBRXM,
JBRZJLX,
JBRZJHM,
JBRLXDH,
JBRCZ,
JBRQZWJM,
YLQZWJM,
FXZC,
JBYX,
WBPJ,
NBPJ,
ZHPJ,
SXED,
FXGJ,
ZCBZ,
DBPJ,
WBPJRQ
FROM (Select f34_1018 FXRH,
Case
When ob_object_name_1018 Like'%银行%'Then
'2'
Else
'1'
EndAs FXFL,
substrb(nvl(f50_1018, ob_object_name_1018), 1, 32) FXMC,
ob_object_name_1018 FXQC,
substr((Select ob_object_name_1014
From wind.tb_object_1014 a
Wheresubstr(f29_1018, 6) = a.ob_object_id),
1,
16) FXLXR,
substrb(f36_1018, 1, 32) FXRLXDH,
substrb(f37_1018, 1, 32) FXRCZ,
'' JBRXM,
'' JBRZJLX,
'' JBRZJHM,
'' JBRLXDH,
'' JBRCZ,
'' JBRQZWJM,
'' YLQZWJM,
(SelectRound(f140_1853, 2)
From wind.tb_object_1853 a
Where f1_1853 = f34_1018
And f4_1853 = '合并报表'
And f2_1853 =
(SelectMax(b.f2_1853)
From wind.tb_object_1853 b
Where b.f1_1853 = f34_1018
And b.f4_1853 = '合并报表'
Andsubstr(b.f2_1853, 5, 4) = '1231')) FXZC,
'' JBYX,
decode(pj.jno,
'AAA',
'1',
'AAA-',
'2',
'AA+',
'3',
'AA',
'4',
'AA-',
'5',
'A+',
'P',
'A',
'6',
'A-',
'7',
'BBB+',
'Q',
'BBB',
'8',
'BBB-',
'9',
'BB+',
'A',
'BB',
'B',
'BB-',
'C',
'B+',
'D',
'B',
'E',
'B-',
'F',
'CCC',
'G',
'CC',
'H',
'C',
'I',
'D',
'J',
'A-1+',
'K',
'A-1',
'L',
'A-1-',
'M',
'A-2',
'N',
'A-3',
'O',
null) WBPJ,
'' NBPJ,
'' ZHPJ,
'' SXED,
decode(f3_1018,
'中国',
'CN',
'英国',
'GB',
'美国',
'US',
'澳大利亚',
'AU',
'百慕大群岛',
'BM',
'加拿大',
'CA',
'德国',
'DE',
'中国台湾',
'TW',
'新加坡',
'SG',
'俄罗斯',
'RU',
'韩国',
'KR',
'日本',
'JP',
'以色列',
'IL',
'爱尔兰',
'IE',
'中国香港',
'HK',
null) FXGJ,
'CNY' ZCBZ,
decode(pj.jno,
'AAA',
'1',
'AAA-',
'2',
'AA+',
'3',
'AA',
'4',
'AA-',
'5',
'A',
'6',
'BBB',
'7',
'BB',
'8',
'B',
'9',
null) DBPJ,
pj.rq WBPJRQ
From wind.tb_object_1018
leftjoin (Select a.f2_1734, Max(f4_1734) jno, max(f5_1734) rq
From wind.tb_object_1734 a
Where f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f2_1734 = a.f2_1734)
groupby a.f2_1734) pj
on f34_1018 = pj.f2_1734
whereexists
(select1
from wind.tb_object_1090
where f34_1018 = ob_revisions_1090
and f4_1090 In
('A', 'B', 'Z', 'Q', 'K', 'DQ', 'P', 'F', 'ZC'))
union
Select f34_1018,
case
when f41_1018 = 18THEN
'3'
ELSE
'1'
END,
case
when f41_1018 = 18THEN
substr(ob_object_name_1018,
1,
instr(ob_object_name_1018, '基金') + 1)
ELSE
ob_object_name_1018
END,
ob_object_name_1018,
substr((Select ob_object_name_1014
From wind.tb_object_1014 a
Wheresubstr(f29_1018, 6) = a.ob_object_id),
1,
16),
substrb(f36_1018, 1, 32),
substrb(f37_1018, 1, 32),
'',
'',
'',
'',
'',
'',
'',
to_number(''),
'' JBYX,
decode(pj.jno,
'AAA',
'1',
'AAA-',
'2',
'AA+',
'3',
'AA',
'4',
'AA-',
'5',
'A+',
'P',
'A',
'6',
'A-',
'7',
'BBB+',
'Q',
'BBB',
'8',
'BBB-',
'9',
'BB+',
'A',
'BB',
'B',
'BB-',
'C',
'B+',
'D',
'B',
'E',
'B-',
'F',
'CCC',
'G',
'CC',
'H',
'C',
'I',
'D',
'J',
'A-1+',
'K',
'A-1',
'L',
'A-1-',
'M',
'A-2',
'N',
'A-3',
'O',
null) WBPJ,
'' NBPJ,
'' ZHPJ,
'' SXED,
decode(f3_1018,
'中国',
'CN',
'英国',
'GB',
'美国',
'US',
'澳大利亚',
'AU',
'百慕大群岛',
'BM',
'加拿大',
'CA',
'德国',
'DE',
'中国台湾',
'TW',
'新加坡',
'SG',
'俄罗斯',
'RU',
'韩国',
'KR',
'日本',
'JP',
'以色列',
'IL',
'爱尔兰',
'IE',
'中国香港',
'HK',
null) FXGJ,
'CNY' ZCBZ,
decode(pj.jno,
'AAA',
'1',
'AAA-',
'2',
'AA+',
'3',
'AA',
'4',
'AA-',
'5',
'A',
'6',
'BBB',
'7',
'BB',
'8',
'B',
'9',
null) DBPJ,
pj.rq WBPJRQ
From wind.tb_object_1018
leftjoin (Select a.f2_1734, Max(f4_1734) jno, max(f5_1734) rq
From wind.tb_object_1734 a
Where f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f2_1734 = a.f2_1734)
groupby a.f2_1734) pj
on f34_1018 = pj.f2_1734
Where f34_1018 In (Select f12_1099 From wind.tb_object_1099)
union
Select f34_1018 FXRH,
Case
When ob_object_name_1018 Like'%银行%'Then
'2'
Else
'1'
EndAs FXFL,
substrb(nvl(f50_1018, ob_object_name_1018), 1, 32) FXMC,
ob_object_name_1018 FXQC,
substr((Select ob_object_name_1014
From wind.tb_object_1014 a
Wheresubstr(f29_1018, 6) = a.ob_object_id),
1,
16) FXLXR,
substrb(f36_1018, 1, 32) FXRLXDH,
substrb(f37_1018, 1, 32) FXRCZ,
'' JBRXM,
'' JBRZJLX,
'' JBRZJHM,
'' JBRLXDH,
'' JBRCZ,
'' JBRQZWJM,
'' YLQZWJM,
(SelectRound(f140_1853, 2)
From wind.tb_object_1853 a
Where f1_1853 = f34_1018
And f4_1853 = '合并报表'
And f2_1853 =
(SelectMax(b.f2_1853)
From wind.tb_object_1853 b
Where b.f1_1853 = f34_1018
And b.f4_1853 = '合并报表'
Andsubstr(b.f2_1853, 5, 4) = '1231')) FXZC,
'' JBYX,
decode(pj.jno,
'AAA',
'1',
'AAA-',
'2',
'AA+',
'3',
'AA',
'4',
'AA-',
'5',
'A+',
'P',
'A',
'6',
'A-',
'7',
'BBB+',
'Q',
'BBB',
'8',
'BBB-',
'9',
'BB+',
'A',
'BB',
'B',
'BB-',
'C',
'B+',
'D',
'B',
'E',
'B-',
'F',
'CCC',
'G',
'CC',
'H',
'C',
'I',
'D',
'J',
'A-1+',
'K',
'A-1',
'L',
'A-1-',
'M',
'A-2',
'N',
'A-3',
'O',
null) WBPJ,
'' NBPJ,
'' ZHPJ,
'' SXED,
decode(f3_1018,
'中国',
'CN',
'英国',
'GB',
'美国',
'US',
'澳大利亚',
'AU',
'百慕大群岛',
'BM',
'加拿大',
'CA',
'德国',
'DE',
'中国台湾',
'TW',
'新加坡',
'SG',
'俄罗斯',
'RU',
'韩国',
'KR',
'日本',
'JP',
'以色列',
'IL',
'爱尔兰',
'IE',
'中国香港',
'HK',
null) FXGJ,
'CNY' ZCBZ,
decode(pj.jno,
'AAA',
'1',
'AAA-',
'2',
'AA+',
'3',
'AA',
'4',
'AA-',
'5',
'A',
'6',
'BBB',
'7',
'BB',
'8',
'B',
'9',
null) DBPJ,
pj.rq WBPJRQ
From wind.tb_object_1018
leftjoin (Select a.f2_1734, Max(f4_1734) jno, max(f5_1734) rq
From wind.tb_object_1734 a
Where f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f2_1734 = a.f2_1734)
groupby a.f2_1734) pj
on f34_1018 = pj.f2_1734
whereExists
(Select1From wind.tb_object_1429 Where f47_1429 = f34_1018))
OrderBy2, 3
----------------原脚本------------------------
SELECT FXRH,
FXFL,
FXMC,
FXQC,
FXLXR,
FXRLXDH,
FXRCZ,
JBRXM,
JBRZJLX,
JBRZJHM,
JBRLXDH,
JBRCZ,
JBRQZWJM,
YLQZWJM,
FXZC,
JBYX,
WBPJ,
NBPJ,
ZHPJ,
SXED,
FXGJ,
ZCBZ,
DBPJ,
WBPJRQ
FROM (SelectDistinct f34_1018 FXRH, --distinct去掉,union已经去重。distinct暗含排序操作,会占用系统临时表。
Case
When ob_object_name_1018 Like'%银行%'Then
'2'
Else
'1'
EndAs FXFL,
substrb(nvl(f50_1018, ob_object_name_1018), 1, 32) FXMC,
ob_object_name_1018 FXQC,
substr((Select ob_object_name_1014
From wind.tb_object_1014 a
Wheresubstr(f29_1018, 6) = a.ob_object_id),
1,
16) FXLXR,
substrb(f36_1018, 1, 32) FXRLXDH,
substrb(f37_1018, 1, 32) FXRCZ,
'' JBRXM,
'' JBRZJLX,
'' JBRZJHM,
'' JBRLXDH,
'' JBRCZ,
'' JBRQZWJM,
'' YLQZWJM,
(SelectRound(f140_1853, 2)
From wind.tb_object_1853 a
Where f1_1853 = f34_1018
And f4_1853 = '合并报表'
And f2_1853 =
(SelectMax(b.f2_1853)
From wind.tb_object_1853 b
Where b.f1_1853 = a.f1_1853
And b.f4_1853 = '合并报表'
Andsubstr(b.f2_1853, 5, 4) = '1231')) FXZC,
'' JBYX,
Case
When (SelectMax(f4_1734) --以下所有对1734的扫描合并成一次
From wind.tb_object_1734 a,
wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) =
'AAA'Then
'1'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a,
wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) =
'AAA-'Then
'2'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a,
wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) =
'AA+'Then
'3'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a,
wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'AA'Then
'4'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a,
wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) =
'AA-'Then
'5'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a,
wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'A+'Then
'P'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a,
wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'A'Then
'6'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a,
wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'A-'Then
'7'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a,
wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) =
'BBB+'Then
'Q'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a,
wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) =
'BBB'Then
'8'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a,
wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) =
'BBB-'Then
'9'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a,
wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) =
'BB+'Then
'A'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a,
wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'BB'Then
'B'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a,
wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) =
'BB-'Then
'C'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a,
wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'B+'Then
'D'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a,
wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'B'Then
'E'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a,
wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'B-'Then
'F'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a,
wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) =
'CCC'Then
'G'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a,
wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'CC'Then
'H'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a,
wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'C'Then
'I'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a,
wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'D'Then
'J'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a,
wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) =
'A-1+'Then
'K'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a,
wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) =
'A-1'Then
'L'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a,
wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) =
'A-1-'Then
'M'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a,
wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) =
'A-2'Then
'N'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a,
wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) =
'A-3'Then
'O'
End WBPJ,
'' NBPJ,
'' ZHPJ,
'' SXED,
case
when f3_1018 = '中国'then
'CN'
when f3_1018 = '英国'then
'GB'
when f3_1018 = '美国'then
'US'
when f3_1018 = '澳大利亚'then
'AU'
when f3_1018 = '百慕大群岛'then
'BM'
when f3_1018 = '加拿大'then
'CA'
when f3_1018 = '德国'then
'DE'
when f3_1018 = '中国台湾'then
'TW'
when f3_1018 = '新加坡'then
'SG'
when f3_1018 = '俄罗斯'then
'RU'
when f3_1018 = '韩国'then
'KR'
when f3_1018 = '日本'then
'JP'
when f3_1018 = '以色列'then
'IL'
when f3_1018 = '爱尔兰'then
'IE'
when f3_1018 = '中国香港'then
'HK'
end FXGJ,
'CNY' ZCBZ,
Case
When (SelectMax(f4_1734)
From wind.tb_object_1734 a,
wind.tb_object_0004,
wind.tb_object_1429
Where f47_1429 = f34_1018
And ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) =
'AAA'Then
'1'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a,
wind.tb_object_0004,
wind.tb_object_1429
Where f47_1429 = f34_1018
And ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) =
'AAA-'Then
'2'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a,
wind.tb_object_0004,
wind.tb_object_1429
Where f47_1429 = f34_1018
And ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) =
'AA+'Then
'3'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a,
wind.tb_object_0004,
wind.tb_object_1429
Where f47_1429 = f34_1018
And ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'AA'Then
'4'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a,
wind.tb_object_0004,
wind.tb_object_1429
Where f47_1429 = f34_1018
And ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) =
'AA-'Then
'5'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a,
wind.tb_object_0004,
wind.tb_object_1429
Where f47_1429 = f34_1018
And ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'A'Then
'6'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a,
wind.tb_object_0004,
wind.tb_object_1429
Where f47_1429 = f34_1018
And ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) =
'BBB'Then
'7'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a,
wind.tb_object_0004,
wind.tb_object_1429
Where f47_1429 = f34_1018
And ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'BB'Then
'8'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a,
wind.tb_object_0004,
wind.tb_object_1429
Where f47_1429 = f34_1018
And ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'B'Then
'9'
End DBPJ,
(SelectMax(f5_1734)
From wind.tb_object_1734 a, wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) WBPJRQ
From wind.tb_object_1090, wind.tb_object_1018
Where f4_1090 In ('A', 'B', 'Z', 'Q', 'K', 'DQ', 'P', 'F', 'ZC')
And f34_1018 = ob_revisions_1090
Union
Select f34_1018,
case
when f41_1018 = 18THEN
'3'
ELSE
'1'
END,
case
when f41_1018 = 18THEN
substr(ob_object_name_1018,
1,
instr(ob_object_name_1018, '基金') + 1)
ELSE
ob_object_name_1018
END,
ob_object_name_1018,
substr((Select ob_object_name_1014
From wind.tb_object_1014 a
Wheresubstr(f29_1018, 6) = a.ob_object_id),
1,
16),
substrb(f36_1018, 1, 32),
substrb(f37_1018, 1, 32),
'',
'',
'',
'',
'',
'',
'',
to_number(''),
'' JBYX,
Case
When (SelectMax(f4_1734) --同上
From wind.tb_object_1734 a, wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'AAA'Then
'1'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a, wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'AAA-'Then
'2'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a, wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'AA+'Then
'3'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a, wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'AA'Then
'4'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a, wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'AA-'Then
'5'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a, wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'A+'Then
'P'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a, wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'A'Then
'6'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a, wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'A-'Then
'7'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a, wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'BBB+'Then
'Q'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a, wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'BBB'Then
'8'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a, wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'BBB-'Then
'9'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a, wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'BB+'Then
'A'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a, wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'BB'Then
'B'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a, wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'BB-'Then
'C'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a, wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'B+'Then
'D'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a, wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'B'Then
'E'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a, wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'B-'Then
'F'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a, wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'CCC'Then
'G'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a, wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'CC'Then
'H'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a, wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'C'Then
'I'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a, wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'D'Then
'J'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a, wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'A-1+'Then
'K'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a, wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'A-1'Then
'L'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a, wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'A-1-'Then
'M'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a, wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'A-2'Then
'N'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a, wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'A-3'Then
'O'
End WBPJ,
'' NBPJ,
'' ZHPJ,
'' SXED,
case
when f3_1018 = '中国'then
'CN'
when f3_1018 = '英国'then
'GB'
when f3_1018 = '美国'then
'US'
when f3_1018 = '澳大利亚'then
'AU'
when f3_1018 = '百慕大群岛'then
'BM'
when f3_1018 = '加拿大'then
'CA'
when f3_1018 = '德国'then
'DE'
when f3_1018 = '中国台湾'then
'TW'
when f3_1018 = '新加坡'then
'SG'
when f3_1018 = '俄罗斯'then
'RU'
when f3_1018 = '韩国'then
'KR'
when f3_1018 = '日本'then
'JP'
when f3_1018 = '以色列'then
'IL'
when f3_1018 = '爱尔兰'then
'IE'
when f3_1018 = '中国香港'then
'HK'
end FXGJ,
'CNY' ZCBZ,
Case
When (SelectMax(f4_1734)
From wind.tb_object_1734 a,
wind.tb_object_0004,
wind.tb_object_1429
Where f47_1429 = f34_1018
And ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'AAA'Then
'1'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a,
wind.tb_object_0004,
wind.tb_object_1429
Where f47_1429 = f34_1018
And ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'AAA-'Then
'2'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a,
wind.tb_object_0004,
wind.tb_object_1429
Where f47_1429 = f34_1018
And ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'AA+'Then
'3'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a,
wind.tb_object_0004,
wind.tb_object_1429
Where f47_1429 = f34_1018
And ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'AA'Then
'4'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a,
wind.tb_object_0004,
wind.tb_object_1429
Where f47_1429 = f34_1018
And ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'AA-'Then
'5'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a,
wind.tb_object_0004,
wind.tb_object_1429
Where f47_1429 = f34_1018
And ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'A'Then
'6'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a,
wind.tb_object_0004,
wind.tb_object_1429
Where f47_1429 = f34_1018
And ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'BBB'Then
'7'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a,
wind.tb_object_0004,
wind.tb_object_1429
Where f47_1429 = f34_1018
And ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'BB'Then
'8'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a,
wind.tb_object_0004,
wind.tb_object_1429
Where f47_1429 = f34_1018
And ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'B'Then
'9'
End DBPJ,
(SelectMax(f5_1734)
From wind.tb_object_1734 a, wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 = (SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) WBPJRQ
From wind.tb_object_1018
Where f34_1018 In (Select f12_1099 From wind.tb_object_1099)
UNION
Select f34_1018,
Case
When ob_object_name_1018 Like'%银行%'Then
'2'
Else
'1'
End,
substrb(nvl(f50_1018, ob_object_name_1018), 1, 32),
ob_object_name_1018,
substr((Select ob_object_name_1014
From wind.tb_object_1014 a
Wheresubstr(f29_1018, 6) = a.ob_object_id),
1,
16),
substrb(f36_1018, 1, 32),
substrb(f37_1018, 1, 32),
'',
'',
'',
'',
'',
'',
'',
(SelectRound(f140_1853, 2)
From wind.tb_object_1853 a
Where f1_1853 = f34_1018
And f4_1853 = '合并报表'
And f2_1853 =
(SelectMax(b.f2_1853)
From wind.tb_object_1853 b
Where b.f1_1853 = a.f1_1853
And b.f4_1853 = '合并报表'
Andsubstr(b.f2_1853, 5, 4) = '1231')) FXZC,
'' JBYX,
Case
When (SelectMax(f4_1734) ----同上
From wind.tb_object_1734 a, wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'AAA'Then
'1'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a, wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'AAA-'Then
'2'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a, wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'AA+'Then
'3'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a, wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'AA'Then
'4'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a, wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'AA-'Then
'5'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a, wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'A+'Then
'P'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a, wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'A'Then
'6'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a, wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'A-'Then
'7'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a, wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'BBB+'Then
'Q'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a, wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'BBB'Then
'8'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a, wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'BBB-'Then
'9'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a, wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'BB+'Then
'A'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a, wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'BB'Then
'B'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a, wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'BB-'Then
'C'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a, wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'B+'Then
'D'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a, wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'B'Then
'E'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a, wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'B-'Then
'F'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a, wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'CCC'Then
'G'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a, wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'CC'Then
'H'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a, wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'C'Then
'I'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a, wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'D'Then
'J'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a, wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'A-1+'Then
'K'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a, wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'A-1'Then
'L'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a, wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'A-1-'Then
'M'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a, wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'A-2'Then
'N'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a, wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'A-3'Then
'O'
End WBPJ,
'' NBPJ,
'' ZHPJ,
'' SXED,
case
when f3_1018 = '中国'then
'CN'
when f3_1018 = '英国'then
'GB'
when f3_1018 = '美国'then
'US'
when f3_1018 = '澳大利亚'then
'AU'
when f3_1018 = '百慕大群岛'then
'BM'
when f3_1018 = '加拿大'then
'CA'
when f3_1018 = '德国'then
'DE'
when f3_1018 = '中国台湾'then
'TW'
when f3_1018 = '新加坡'then
'SG'
when f3_1018 = '俄罗斯'then
'RU'
when f3_1018 = '韩国'then
'KR'
when f3_1018 = '日本'then
'JP'
when f3_1018 = '以色列'then
'IL'
when f3_1018 = '爱尔兰'then
'IE'
when f3_1018 = '中国香港'then
'HK'
end FXGJ,
'CNY' ZCBZ,
Case
When (SelectMax(f4_1734)
From wind.tb_object_1734 a,
wind.tb_object_0004,
wind.tb_object_1429
Where f47_1429 = f34_1018
And ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'AAA'Then
'1'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a,
wind.tb_object_0004,
wind.tb_object_1429
Where f47_1429 = f34_1018
And ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'AAA-'Then
'2'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a,
wind.tb_object_0004,
wind.tb_object_1429
Where f47_1429 = f34_1018
And ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'AA+'Then
'3'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a,
wind.tb_object_0004,
wind.tb_object_1429
Where f47_1429 = f34_1018
And ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'AA'Then
'4'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a,
wind.tb_object_0004,
wind.tb_object_1429
Where f47_1429 = f34_1018
And ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'AA-'Then
'5'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a,
wind.tb_object_0004,
wind.tb_object_1429
Where f47_1429 = f34_1018
And ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'A'Then
'6'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a,
wind.tb_object_0004,
wind.tb_object_1429
Where f47_1429 = f34_1018
And ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'BBB'Then
'7'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a,
wind.tb_object_0004,
wind.tb_object_1429
Where f47_1429 = f34_1018
And ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'BB'Then
'8'
When (SelectMax(f4_1734)
From wind.tb_object_1734 a,
wind.tb_object_0004,
wind.tb_object_1429
Where f47_1429 = f34_1018
And ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 =
(SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) = 'B'Then
'9'
End DBPJ,
(SelectMax(f5_1734)
From wind.tb_object_1734 a, wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 = (SelectMax(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)) WBPJRQ
From wind.tb_object_1018 T
WhereExists
(Select1From wind.tb_object_1429 Where f47_1429 = f34_1018))
OrderBy2, 3