优化笔记: 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