北京华夏安泰技术论坛

 找回密码
 立即注册

扫一扫,访问微社区

QQ登录

只需要一步,快速开始

只需一步,快速开始

搜索
热搜: 活动 交友 discuz
查看: 476|回复: 0

关于科目配置查询语句

[复制链接]

165

主题

219

帖子

1200

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
1200
发表于 2017-8-11 15:09:03 | 显示全部楼层 |阅读模式
把树状数据转换成横向数据,思路就出来了。
用于科目自动生成映射




  1. select (select fs.subject_code from FIN_SUBJECT fs where  fs.subject_id = fsa1.subject_id) code,
  2. fsa1.levels,fsa1.source_name,fsa1.data_source,fsa1.source_id,
  3. fsa2.levels,fsa2.source_name,fsa2.data_source,fsa2.source_id,
  4. fsa3.levels,fsa3.source_name,fsa3.data_source,fsa3.source_id,
  5. fsa4.levels,fsa4.source_name,fsa4.data_source,fsa4.source_id,
  6. fsa5.levels,fsa5.source_name,fsa5.data_source,fsa5.source_id

  7. from FIN_SUBJECT_AUTOGENE fsa1
  8. left join FIN_SUBJECT_AUTOGENE fsa2
  9.      on fsa1.subject_id = fsa2.subject_id and fsa2.account_book_id = '8e2de0c3-c857-4607-b9af-d3e987ecc8e4' and fsa2.parent_id = fsa1.subject_autogene_id
  10. left join FIN_SUBJECT_AUTOGENE fsa3
  11.      on fsa2.subject_id = fsa3.subject_id and fsa3.account_book_id = '8e2de0c3-c857-4607-b9af-d3e987ecc8e4' and fsa3.parent_id = fsa2.subject_autogene_id
  12. left join FIN_SUBJECT_AUTOGENE fsa4
  13.      on fsa3.subject_id = fsa4.subject_id and fsa4.account_book_id = '8e2de0c3-c857-4607-b9af-d3e987ecc8e4' and fsa4.parent_id = fsa3.subject_autogene_id
  14. left join FIN_SUBJECT_AUTOGENE fsa5
  15.      on fsa4.subject_id = fsa5.subject_id and fsa5.account_book_id = '8e2de0c3-c857-4607-b9af-d3e987ecc8e4' and fsa5.parent_id = fsa4.subject_autogene_id
  16. where 1=1
  17. and fsa1.account_book_id = '8e2de0c3-c857-4607-b9af-d3e987ecc8e4'
  18. --and fsa1.subject_id = '0001A110000000061HI3'
  19. and (                                                             --一级条件                                                               
  20. (fsa1.data_source = '1' and fsa1.source_id = '8e7cd5f9-2b56-4b30-9aca-f0723f3ba15f')  --支基本
  21. or
  22. (fsa1.data_source = '2' and fsa1.source_id = '')
  23. or
  24. (fsa1.data_source = '3' and fsa1.source_id = '')
  25. or
  26. (fsa1.data_source = '4' and fsa1.source_id = '')
  27. or
  28. (fsa1.data_source = '5' and fsa1.source_id = '')
  29. or
  30. (fsa1.data_source = '6' and fsa1.source_id = '3')                                     --结算,转账
  31. )
  32. and (                                                             --二级条件         
  33. ((fsa2.data_source = '1' and fsa2.source_id = '8e7cd5f9-2b56-4b30-9aca-f0723f3ba15f') or fsa2.source_id is null)
  34. or
  35. ((fsa2.data_source = '2' and fsa2.source_id = '')  or fsa2.source_id is null)
  36. or
  37. ((fsa2.data_source = '3' and fsa2.source_id = '')  or fsa2.source_id is null)
  38. or
  39. ((fsa2.data_source = '4' and fsa2.source_id = '')  or fsa2.source_id is null)
  40. or
  41. ((fsa2.data_source = '5' and fsa2.source_id = '')  or fsa2.source_id is null)
  42. or
  43. ((fsa2.data_source = '6' and fsa2.source_id = '3')  or fsa2.source_id is null)
  44. )
  45. and (                                                             --三级条件         
  46. ((fsa3.data_source = '1' and fsa3.source_id = '8e7cd5f9-2b56-4b30-9aca-f0723f3ba15f') or fsa3.source_id is null)
  47. or
  48. ((fsa3.data_source = '2' and fsa3.source_id = '') or fsa3.source_id is null)
  49. or
  50. ((fsa3.data_source = '3' and fsa3.source_id = '') or fsa3.source_id is null)
  51. or
  52. ((fsa3.data_source = '4' and fsa3.source_id = '') or fsa3.source_id is null)
  53. or
  54. ((fsa3.data_source = '5' and fsa3.source_id = '') or fsa3.source_id is null)
  55. or
  56. ((fsa3.data_source = '6' and fsa3.source_id = '3') or fsa3.source_id is null)
  57. )
  58. and (                                                             --四级条件         
  59. ((fsa4.data_source = '1' and fsa4.source_id = '8e7cd5f9-2b56-4b30-9aca-f0723f3ba15f') or fsa4.source_id is null)
  60. or
  61. ((fsa4.data_source = '2' and fsa4.source_id = '') or fsa4.source_id is null)
  62. or
  63. ((fsa4.data_source = '3' and fsa4.source_id = '') or fsa4.source_id is null)
  64. or
  65. ((fsa4.data_source = '4' and fsa4.source_id = '') or fsa4.source_id is null)
  66. or
  67. ((fsa4.data_source = '5' and fsa4.source_id = '') or fsa4.source_id is null)
  68. or
  69. ((fsa4.data_source = '6' and fsa4.source_id = '3') or fsa4.source_id is null)
  70. )
  71. and (                                                             --五级条件         
  72. ((fsa5.data_source = '1' and fsa5.source_id = '8e7cd5f9-2b56-4b30-9aca-f0723f3ba15f') or fsa5.source_id is null)
  73. or
  74. ((fsa5.data_source = '2' and fsa5.source_id = '') or fsa5.source_id is null)
  75. or
  76. ((fsa5.data_source = '3' and fsa5.source_id = '') or fsa5.source_id is null)
  77. or
  78. ((fsa5.data_source = '4' and fsa5.source_id = '') or fsa5.source_id is null)
  79. or
  80. ((fsa5.data_source = '5' and fsa5.source_id = '') or fsa5.source_id is null)
  81. or
  82. ((fsa5.data_source = '6' and fsa5.source_id = '3') or fsa5.source_id is null)
  83. )

  84. /*----相关数据
  85. 1、支出事项
  86. 2、支出类型
  87. 3、资金来源
  88. 4、资金性质
  89. 5、银行账号
  90. 6、结算方式
  91. ----有可能不配全,有可能不传数据,,
  92. select * from FIN_SUBJECT fs
  93. where fs.account_book_id = '8e2de0c3-c857-4607-b9af-d3e987ecc8e4'
  94. and fs.subject_code = '1212'
  95. */
复制代码


回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册  

本版积分规则


小黑屋|手机版|Archiver|北京华夏安泰技术论坛  

GMT+8, 2019-6-20 23:18 , Processed in 0.140625 second(s), 24 queries .

Powered by Discuz! X3.2

© 2001-2013 Comsenz Inc.

快速回复 返回顶部 返回列表