-
Posgresql数据库项目sql实操1
文章目录
前言
主要学会多表中的条件关联 而且这个表中的字段和另一个表中的字段合并 字符串的截取
有关正则表达式在Postgresql中的应用需求一的目标就是把新数据加入到ingredient表里面 同时加入新数据中的字段要取d_number_list里面的最新日期的日文和中文字段
需求一
1.1 查找A表存在并且B表不存在
select * a where not exists ( select * b --表如果是复合语句 那种外面可以套一个select 1 from where a.d_number = b.d_number -- 关联条件 )- 1
- 2
- 3
- 4
- 5
1.2 逻辑1的基础上加上时间是最新数据
--ronumber 这个是排序记录并且获得当前行排序的结果展示 就是第多少行的意思 select c.d_number,ROW_NUMBER() over(order by c.d_number) as ronumber from ( select distinct a.d_number from drg.repos_drug_d_number a where source_version in (select max(source_version) as source_version from drg.repos_drug_d_number where left(source_version,6) = '202207' ))as c where not exists ( select b.d_number from drg.maint_drug_ingredient b where left(b.ingredient_code,2) <> 'I9' and c.d_number = b.d_number )- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
== 取最新数据 ==
select distinct a.d_number from drg.repos_drug_d_number a where source_version in (select max(source_version) as source_version- 1
- 2
需求一反应的是主表 从表连接的是主表中按照d_number 对应在 d_number_list里面最新的日语名和英文名字
需求二 查找对应的最新日文和英文字段
2.1 查找d_number_list里面的最新字段
select * from 主表 f left join ()f2 on f.d_number= f1.d_number- 1
- 2
2.2 最新字段取名规则
如果有； 就取；最左侧的一个字段 同时去掉括号内的标准
如果没有分号 就取整个字段 同样也要去掉标准
同时多余的空格 要去掉 但是有的日语名中有空格 还要保留 一般就是在‘ （’这样的空格去掉就可以2.3 日语名的情况
分为好多种 有xxx()(JSP); xxx(); xxx(JSP); xx()xx()(jsp); xxx xxx() xxxx(JSP)首先考虑有分号的情况
–The first case in case: case Deal with this situation: xx()XXx; xx()xx()xx; xxxxx;
就是先截取分号左边的 然后reverse 如果有分号 并且还是第一位出现的 position函数是从1开始 而不是0 就判断出这种情况了 此时就是全取就好了
case when position(')' in reverse(substring(n1.d_name ,1,position(';' in n1.d_name )-1)))!=1 then substring(n1.d_name,1,position(';' in n1.d_name)-1)- 1
– case 2: xx(); xx(JPN);xx()(JSP); See whether the characters in the last () are in the standard
这种的话需要考虑最后括号里面的是否是标准 因为标准有20多个所以直接弄成字符串判断
在PostgreSQL中，substring函数用于从字符串中提取子字符串。 用法： SUBSTRING ( string, start_position, length )- 1
- 2
- 3
substring里面最右边是长度 长度！！！！！！
case语法：case when 表达式1 then 结果1 when 表达式2 then 结果2 else 结果n end- 1
- 2
- 3
- 4
- 5
这个PostgreSQL的POSITION()函数返回字符串中子字符串的位置。 用法：POSITION(substring in string) 让我们分析以上语法： substring参数是您要查找的字符串 这个串参数是要搜索子字符串的字符串。 POSITION()函数返回一个整数，该整数表示子字符串在字符串中的位置。如果在字符串中未找到子字符串，则POSITION()函数将返回零(0)。如果子字符串或字符串参数为null，则返回null。- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
截取最右边是括号的 截取
substring(反转的字符串，2，最右边括号内的字符内容)这是取最右边括号内的字符内容 没括号的话取0 主要是substring里面的语句不让连续见两个 只能携程这样的case when 了
(case when position('('in reverse(substring(n1.d_name,1,position(';' in n1.d_name)-1)))=0 then position('('in reverse(substring(n1.d_name,1,position(';' in n1.d_name)-1))) else position('('in reverse(substring(n1.d_name,1,position(';' in n1.d_name)-1)))-2 end)- 1
- 2
- 3
这是case2的情况写法
position(')' in reverse(substring(n1.d_name ,1,position(';' in n1.d_name )-1)))!=1 then substring(n1.d_name,1,position(';' in n1.d_name)-1) when position(reverse(substring( reverse(substring(n1.d_name,1,position(';' in n1.d_name)-1)),2,(case when position('('in reverse(substring(n1.d_name,1,position(';' in n1.d_name)-1)))=0 then position('('in reverse(substring(n1.d_name,1,position(';' in n1.d_name)-1))) else position('('in reverse(substring(n1.d_name,1,position(';' in n1.d_name)-1)))-2 end))) in 'BAN, DCF, INN, INN/BAN, INN/BAN/DCF, INN/JAN, INN/NF, INN/USAN, INN/USAN/BAN, JAN, JAN/BAN, JAN/INN, JAN/NF, JAN/USAN, JAN/USAN/INN, JAN/USP, JAN/USP/INN, JP18, JP18/INN, JP18/INN/NF, JP18/NF, JP18/NF/INN, JP18/USAN, JP18/USAN/INN, JP18/USP, JP18/USP/INN, NF, NF/INN, Non-JPS, TM, TN, USAN, USAN/INN, USAN/INN/BAN, USAN/JAN, USAN/JP18/INN, USNA, USP, USP/INN, USP/JP18, USPINN, USPXXII, USPXXX, USPXXXII') = 0 then substring(n1.d_name ,1,position(';' in n1.d_name )-1)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
第三种情况-- case 3: XXX; XXX()(JPN); xx(JPN); XX()XX(NF);
reverse(right(reverse(substring(n1.d_name,1,position(';' in n1.d_name)-1)) , char_length(substring(n1.d_name,1,position(';' in n1.d_name)-1) ) - position('('in reverse(substring(n1.d_name,1,position(';' in n1.d_name)-1)))) )- 1
- 2
- 3
就是取最右边的‘(’里面的内容
综上所述 直接合体
英文与日语同理
left join -- Get the field of the latest data from the master table corresponding to the slave table take Japanese (--Find data containing ';' and Find the data containing ; and intercept the data after removing the standard font size from the left of the semicolon in the order from right to left -- case 3: XXX; XXX()(JPN); xx(JPN); XX()XX(NF); --The first case in case: case Deal with this situation: xx()XXx; xx()xx()xx; xxxxx; select -- case 2: xx(); xx(JPN); See whether the characters in the last () are in the standard n1.d_number, (case when position(')' in reverse(substring(n1.d_name ,1,position(';' in n1.d_name )-1)))!=1 then substring(n1.d_name,1,position(';' in n1.d_name)-1) when position(reverse(substring( reverse(substring(n1.d_name,1,position(';' in n1.d_name)-1)),2,(case when position('('in reverse(substring(n1.d_name,1,position(';' in n1.d_name)-1)))=0 then position('('in reverse(substring(n1.d_name,1,position(';' in n1.d_name)-1))) else position('('in reverse(substring(n1.d_name,1,position(';' in n1.d_name)-1)))-2 end))) in 'BAN, DCF, INN, INN/BAN, INN/BAN/DCF, INN/JAN, INN/NF, INN/USAN, INN/USAN/BAN, JAN, JAN/BAN, JAN/INN, JAN/NF, JAN/USAN, JAN/USAN/INN, JAN/USP, JAN/USP/INN, JP18, JP18/INN, JP18/INN/NF, JP18/NF, JP18/NF/INN, JP18/USAN, JP18/USAN/INN, JP18/USP, JP18/USP/INN, NF, NF/INN, Non-JPS, TM, TN, USAN, USAN/INN, USAN/INN/BAN, USAN/JAN, USAN/JP18/INN, USNA, USP, USP/INN, USP/JP18, USPINN, USPXXII, USPXXX, USPXXXII') = 0 then substring(n1.d_name ,1,position(';' in n1.d_name )-1) else reverse(right(reverse(substring(n1.d_name,1,position(';' in n1.d_name)-1)) , char_length(substring(n1.d_name,1,position(';' in n1.d_name)-1) ) - position('('in reverse(substring(n1.d_name,1,position(';' in n1.d_name)-1)))) ) end) as d_name from drg.repos_d_number_list n1 where exists (--Get the latest data in the drg.repos_d_number_list table select 1 from (select max(source_version) as source_version from drg.repos_d_number_list where left(source_version,6) = '202207') n2 where n1.source_version = n2.source_version ) and n1.d_name like '%;%' union all -- Find data without ';' and intercept data with standard font size left select -- case : xxx xxxx(JSP) xxx()(JSP) xxx()xxx(NF) xxxx() n1.d_number, (case when position('('in reverse(n1.d_name))=0 then n1.d_name when position(reverse(substring(reverse(n1.d_name),2,position('('in reverse(n1.d_name))-2)) in 'BAN, DCF, INN, INN/BAN, INN/BAN/DCF, INN/JAN, INN/NF, INN/USAN, INN/USAN/BAN, JAN, JAN/BAN, JAN/INN, JAN/NF, JAN/USAN, JAN/USAN/INN, JAN/USP, JAN/USP/INN, JP18, JP18/INN, JP18/INN/NF, JP18/NF, JP18/NF/INN, JP18/USAN, JP18/USAN/INN, JP18/USP, JP18/USP/INN, NF, NF/INN, Non-JPS, TM, TN, USAN, USAN/INN, USAN/INN/BAN, USAN/JAN, USAN/JP18/INN, USNA, USP, USP/INN, USP/JP18, USPINN, USPXXII, USPXXX, USPXXXII') = 0 then n1.d_name else reverse(substring(reverse(n1.d_name),position('('in reverse(n1.d_name))+1,char_length(n1.d_name)-position('('in reverse(n1.d_name)))) end) as d_name from drg.repos_d_number_list n1 where exists ( select 1 from (select max(source_version) as source_version from drg.repos_d_number_list where left(source_version,6) = '202207') n2 where n1.source_version = n2.source_version ) and n1.d_name not like '%;%' )f1 --slave table1 on f.d_number=f1.d_number left join -- take English ditto ( select n1.d_number,(case when position(')' in reverse(substring(n1.d_name_eng ,1,position(';' in n1.d_name_eng )-1)))!=1 then substring(n1.d_name_eng,1,position(';' in n1.d_name_eng)-1) when position(reverse(substring( reverse(substring(n1.d_name_eng,1,position(';' in n1.d_name_eng)-1)),2,(case when position('('in reverse(substring(n1.d_name_eng,1,position(';' in n1.d_name_eng)-1)))=0 then position('('in reverse(substring(n1.d_name_eng,1,position(';' in n1.d_name_eng)-1))) else position('('in reverse(substring(n1.d_name_eng,1,position(';' in n1.d_name_eng)-1)))-2 end))) in 'BAN, DCF, INN, INN/BAN, INN/BAN/DCF, INN/JAN, INN/NF, INN/USAN, INN/USAN/BAN, JAN, JAN/BAN, JAN/INN, JAN/NF, JAN/USAN, JAN/USAN/INN, JAN/USP, JAN/USP/INN, JP18, JP18/INN, JP18/INN/NF, JP18/NF, JP18/NF/INN, JP18/USAN, JP18/USAN/INN, JP18/USP, JP18/USP/INN, NF, NF/INN, Non-JPS, TM, TN, USAN, USAN/INN, USAN/INN/BAN, USAN/JAN, USAN/JP18/INN, USNA, USP, USP/INN, USP/JP18, USPINN, USPXXII, USPXXX, USPXXXII') = 0 then substring(n1.d_name_eng ,1,position(';' in n1.d_name_eng )-1) else reverse(right(reverse(substring(n1.d_name_eng,1,position(';' in n1.d_name_eng)-1)) , char_length(substring(n1.d_name_eng,1,position(';' in n1.d_name_eng)-1) ) - position('('in reverse(substring(n1.d_name_eng,1,position(';' in n1.d_name_eng)-1)))) ) end) as d_name_eng from drg.repos_d_number_list n1 where exists ( select 1 from (select max(source_version) as source_version from drg.repos_d_number_list where left(source_version,6) = '202207') n2 where n1.source_version = n2.source_version ) and n1.d_name_eng like '%;%' -- include ';' union all -- Query data without semicolons select n1.d_number, (case when position('('in reverse(n1.d_name_eng))=0 then n1.d_name_eng when position(reverse(substring(reverse(n1.d_name_eng),2,position('('in reverse(n1.d_name_eng))-2)) in 'BAN, DCF, INN, INN/BAN, INN/BAN/DCF, INN/JAN, INN/NF, INN/USAN, INN/USAN/BAN, JAN, JAN/BAN, JAN/INN, JAN/NF, JAN/USAN, JAN/USAN/INN, JAN/USP, JAN/USP/INN, JP18, JP18/INN, JP18/INN/NF, JP18/NF, JP18/NF/INN, JP18/USAN, JP18/USAN/INN,202207 JP18/USP, JP18/USP/INN, NF, NF/INN, Non-JPS, TM, TN, USAN, USAN/INN, USAN/INN/BAN, USAN/JAN, USAN/JP18/INN, USNA, USP, USP/INN, USP/JP18, USPINN, USPXXII, USPXXX, USPXXXII') = 0 then n1.d_name_eng else reverse(substring(reverse(n1.d_name_eng),position('('in reverse(n1.d_name_eng))+1,char_length(n1.d_name_eng)-position('('in reverse(n1.d_name_eng)))) end) as d_name_eng from drg.repos_d_number_list n1 where exists ( select 1 from (select max(source_version) as source_version from drg.repos_d_number_list where left(source_version,6) = '202207') n2 where n1.source_version = n2.source_version ) and n1.d_name_eng not like '%;%' -- not include ';' )f2 --slave table2 on f.d_number=f2.d_number- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
- 114
- 115
- 116
- 117
- 118
- 119
- 120
- 121
- 122
- 123
- 124
- 125
- 126
- 127
- 128
- 129
- 130
- 131
- 132
- 133
- 134
- 135
- 136
- 137
- 138
- 139
- 140
- 141
- 142
- 143
- 144
- 145
- 146
- 147
- 148
- 149
- 150
- 151
- 152
- 153
- 154
- 155
- 156
- 157
- 158
- 159
- 160
- 161
- 162
- 163
- 164
- 165
- 166
- 167
- 168
- 169
- 170
- 171
- 172
- 173
- 174
- 175
- 176
- 177
- 178
- 179
- 180
- 181
- 182
- 183
- 184
- 185
- 186
- 187
- 188
- 189
- 190
- 191
- 192
- 193
- 194
- 195
- 196
- 197
- 198
- 199
- 200
- 201
- 202
- 203
- 204
- 205
- 206
- 207
- 208
- 209
- 210
- 211
- 212
- 213
- 214
- 215
- 216
- 217
- 218
- 219
- 220
- 221
- 222
- 223
- 224
- 225
- 226
- 227
- 228
- 229
- 230
- 231
- 232
- 233
- 234
- 235
- 236
- 237
- 238
- 239
- 240
- 241
- 242
- 243
- 244
- 245
- 246
- 247
- 248
- 249
- 250
- 251
- 252
- 253
- 254
- 255
- 256
- 257
2.4 去掉空格
--去除空白符 select regexp_replace(' a s d ', E'\\s+', ' ', 'g') --去除首空格 select regexp_replace(' a b d ',E'(^\\s*)','','g') --去除尾空格 select regexp_replace(' a b d ',E'(\\s*$)','','g') --去除首尾空格 select regexp_replace(' a b d ',E'(^\\s*)|(\\s*$)','','g') --去除首尾空格的函數 select trim(' a b d ')- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
先转成全角 然后在去掉空格之类的东西
trim(TRAILING ' ' from replace(drg.convert_half_to_full(f1.d_name), ' （', '（')) as ingredient_name,- 1
2.5插入的时候在I0002567下插入
这里面的I是自己编辑上去的 在左边自动补齐7位 以0填补 然后第一位是I
这里面介绍两种方式 公司中采用的是第一种 不让我用第二种 不过第一种确实省事了 可以不用多余操作第一种
这个PostgreSQL的LPAD()函数用于将字符串向左填充指定长度的其他指定字符。 用法： LPAD(string, length[, fill]) 让我们分析以上语法： 字符串参数是要在左侧填充的字符串。 它是一个正整数，用于设置填充后的结果字符串的长度。 fill 参数用于填充原始字符串。 它是一个可选参数。默认情况下，它的值为一个空格。 范例1： 以下语句使用LPAD()用于填充字符串“GeeksforGeeks”左侧的“*”的函数： SELECT LPAD('GeeksforGeeks', 15, '*');- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
输出：
首先将最大序列号的字符‘I’替换掉 之后剩下7位 加上前面排序的ronumber 放在序列号下 采用函数Lpad 补齐 最后再用I进行拼接
(这里面是插入不是I9的操作 因为都是有d_number关联的 所有都是I0 打头的)select concat('I',Lpad(cast((select cast(REPLACE(max(ingredient_code),'I','') as INTEGER) from drg.maint_drug_ingredient where left(ingredient_code,2) <> 'I9')+ronumber as text),7,'0')) as ingredient_code,- 1
- 2
- 3
这里面的ronumber 是前面进行了排序操作 获取了第多少位
select c.d_number,ROW_NUMBER() over(order by c.d_number) as ronumber from（主表）c- 1
第二种
这种需要自己设置当前的序列号
这是设置当前的序列号为多少ingredient_seq这个是在创建表的时候 用这个作为了序列号- 1
这是建表的时候
create sequence ingredient_seq; insert into drg.maint_drug_ingredient select distinct concat('I',to_char(nextval('ingredient_seq'), 'FM0000000')) ,f1.* ,current_user ,now() ,current_user ,now() from (select distinct d_name ,d_name_eng ,d_number from drg.repos_drug_d_number order by d_number ) f1;查询当前的最大的是多少 当前这时候去掉I
select right( max(ingredient_code) ,7 )from drg.maint_drug_ingredient b where left(b.ingredient_code,2) <> 'I9'- 1
- 2
select setval ('ingredient_seq',0002648,false) --这是设置当前的序列号- 1
就是再次插入的时候从0002648开始的意思 之前是0002647
select currval('ingredient_seq') -- 这是查询当前序列号- 1
select nextval('ingredient_seq')--得到下一个序列号- 1
select setval('ingredient_seq',right(max(ingredient_code) ,7),false) from drg.maint_drug_ingredient b where left(b.ingredient_code,2) <> 'I9'- 1
- 2
但是每次插入都需要找到并设置当前的序列号
然后从当前的序列号之后插入 每次插入一个增加一条然后前面在拿contcat进行连接就可以了
需求二就告一段落 主要是求取字段的问题 然后就是插入这个自动增加序列号的问题 当时是弄了半天
完整代码如下 加入了注释版 此外上面只是讲了正常的表1 group字段是数据也是这样 但是group 字段中对于d_name和d_name_eng会方便一些 但是他多了一个chemecial的字段 同时在插入的时候需要改一些参数 这里面就不再赘述 我会把sql文 列出如下 当然抽取的时候还可以优化一些 我们可以按照（）这样的进行查找和筛选 本文中用了四个这种字符字段 是按照分号进行筛选的 用（）的话 那就写两个这样的大长串就可以了 这里不再赘述 详情见sql语句这是全部sql查询
--************************************************************************************************ --処理内容：Maint成分・成分グループのデータ作成 --変更履歴： --新規作成：共兴达赵 2022/07/15 --************************************************************************************************ BEGIN; -- Maint成分 insert into drg.maint_drug_ingredient --ONE The first query contains 27 items in total --Remove 'I' and get the largest ingredient_code as the starting value of self growth, Fill eight digits from the left with 0 --Take character I and self growing number splicing select concat('I',Lpad(cast((select cast(REPLACE(max(ingredient_code),'I','') as INTEGER) from drg.maint_drug_ingredient where left(ingredient_code,2) <> 'I9')+ronumber as text),7,'0')) as ingredient_code, trim(TRAILING ' ' from replace(drg.convert_half_to_full(f1.d_name), ' （', '（')) as ingredient_name, f2.d_name_eng, f.d_number ,CURRENT_USER ,now() ,CURRENT_USER ,now() from (-- Table c exists, but the first two digits of ingredient_code are not data that does not exist in table b of I9 There are 27 select c.d_number,ROW_NUMBER() over(order by c.d_number) as ronumber from ( select distinct a.d_number from drg.repos_drug_d_number a where source_version in (select max(source_version) as source_version from drg.repos_drug_d_number ))as c where not exists ( select b.d_number from drg.maint_drug_ingredient b where left(b.ingredient_code,2) <> 'I9' and c.d_number = b.d_number ) ) f -- main table left join -- Get the field of the latest data from the master table corresponding to the slave table take Japanese (--Find data containing ';' and Find the data containing ; and intercept the data after removing the standard font size from the left of the semicolon in the order from right to left -- case 3: XXX; XXX()(JPN); xx(JPN); XX()XX(NF); --The first case in case: case Deal with this situation: xx()XXx; xx()xx()xx; xxxxx; select -- case 2: xx(); xx(JPN); See whether the characters in the last () are in the standard n1.d_number, (case when position(')' in reverse(substring(n1.d_name ,1,position(';' in n1.d_name )-1)))!=1 then substring(n1.d_name,1,position(';' in n1.d_name)-1) when position(reverse(substring( reverse(substring(n1.d_name,1,position(';' in n1.d_name)-1)),2,(case when position('('in reverse(substring(n1.d_name,1,position(';' in n1.d_name)-1)))=0 then position('('in reverse(substring(n1.d_name,1,position(';' in n1.d_name)-1))) else position('('in reverse(substring(n1.d_name,1,position(';' in n1.d_name)-1)))-2 end))) in 'BAN, DCF, INN, INN/BAN, INN/BAN/DCF, INN/JAN, INN/NF, INN/USAN, INN/USAN/BAN, JAN, JAN/BAN, JAN/INN, JAN/NF, JAN/USAN, JAN/USAN/INN, JAN/USP, JAN/USP/INN, JP18, JP18/INN, JP18/INN/NF, JP18/NF, JP18/NF/INN, JP18/USAN, JP18/USAN/INN, JP18/USP, JP18/USP/INN, NF, NF/INN, Non-JPS, TM, TN, USAN, USAN/INN, USAN/INN/BAN, USAN/JAN, USAN/JP18/INN, USNA, USP, USP/INN, USP/JP18, USPINN, USPXXII, USPXXX, USPXXXII') = 0 then substring(n1.d_name ,1,position(';' in n1.d_name )-1) else reverse(right(reverse(substring(n1.d_name,1,position(';' in n1.d_name)-1)) , char_length(substring(n1.d_name,1,position(';' in n1.d_name)-1) ) - position('('in reverse(substring(n1.d_name,1,position(';' in n1.d_name)-1)))) ) end) as d_name from drg.repos_d_number_list n1 where exists (--Get the latest data in the drg.repos_d_number_list table select 1 from (select max(source_version) as source_version from drg.repos_d_number_list ) n2 where n1.source_version = n2.source_version ) and n1.d_name like '%;%' union all -- Find data without ';' and intercept data with standard font size left select -- case : xxx xxxx(JSP) xxx()(JSP) xxx()xxx(NF) xxxx() n1.d_number, (case when position('('in reverse(n1.d_name))=0 then n1.d_name when position(reverse(substring(reverse(n1.d_name),2,position('('in reverse(n1.d_name))-2)) in 'BAN, DCF, INN, INN/BAN, INN/BAN/DCF, INN/JAN, INN/NF, INN/USAN, INN/USAN/BAN, JAN, JAN/BAN, JAN/INN, JAN/NF, JAN/USAN, JAN/USAN/INN, JAN/USP, JAN/USP/INN, JP18, JP18/INN, JP18/INN/NF, JP18/NF, JP18/NF/INN, JP18/USAN, JP18/USAN/INN, JP18/USP, JP18/USP/INN, NF, NF/INN, Non-JPS, TM, TN, USAN, USAN/INN, USAN/INN/BAN, USAN/JAN, USAN/JP18/INN, USNA, USP, USP/INN, USP/JP18, USPINN, USPXXII, USPXXX, USPXXXII') = 0 then n1.d_name else reverse(substring(reverse(n1.d_name),position('('in reverse(n1.d_name))+1,char_length(n1.d_name)-position('('in reverse(n1.d_name)))) end) as d_name from drg.repos_d_number_list n1 where exists ( select 1 from (select max(source_version) as source_version from drg.repos_d_number_list) n2 where n1.source_version = n2.source_version ) and n1.d_name not like '%;%' )f1 --slave table1 on f.d_number=f1.d_number left join -- take English ditto ( select n1.d_number,(case when position(')' in reverse(substring(n1.d_name_eng ,1,position(';' in n1.d_name_eng )-1)))!=1 then substring(n1.d_name_eng,1,position(';' in n1.d_name_eng)-1) when position(reverse(substring( reverse(substring(n1.d_name_eng,1,position(';' in n1.d_name_eng)-1)),2,(case when position('('in reverse(substring(n1.d_name_eng,1,position(';' in n1.d_name_eng)-1)))=0 then position('('in reverse(substring(n1.d_name_eng,1,position(';' in n1.d_name_eng)-1))) else position('('in reverse(substring(n1.d_name_eng,1,position(';' in n1.d_name_eng)-1)))-2 end))) in 'BAN, DCF, INN, INN/BAN, INN/BAN/DCF, INN/JAN, INN/NF, INN/USAN, INN/USAN/BAN, JAN, JAN/BAN, JAN/INN, JAN/NF, JAN/USAN, JAN/USAN/INN, JAN/USP, JAN/USP/INN, JP18, JP18/INN, JP18/INN/NF, JP18/NF, JP18/NF/INN, JP18/USAN, JP18/USAN/INN, JP18/USP, JP18/USP/INN, NF, NF/INN, Non-JPS, TM, TN, USAN, USAN/INN, USAN/INN/BAN, USAN/JAN, USAN/JP18/INN, USNA, USP, USP/INN, USP/JP18, USPINN, USPXXII, USPXXX, USPXXXII') = 0 then substring(n1.d_name_eng ,1,position(';' in n1.d_name_eng )-1) else reverse(right(reverse(substring(n1.d_name_eng,1,position(';' in n1.d_name_eng)-1)) , char_length(substring(n1.d_name_eng,1,position(';' in n1.d_name_eng)-1) ) - position('('in reverse(substring(n1.d_name_eng,1,position(';' in n1.d_name_eng)-1)))) ) end) as d_name_eng from drg.repos_d_number_list n1 where exists ( select 1 from (select max(source_version) as source_version from drg.repos_d_number_list ) n2 where n1.source_version = n2.source_version ) and n1.d_name_eng like '%;%' -- include ';' union all -- Query data without semicolons select n1.d_number, (case when position('('in reverse(n1.d_name_eng))=0 then n1.d_name_eng when position(reverse(substring(reverse(n1.d_name_eng),2,position('('in reverse(n1.d_name_eng))-2)) in 'BAN, DCF, INN, INN/BAN, INN/BAN/DCF, INN/JAN, INN/NF, INN/USAN, INN/USAN/BAN, JAN, JAN/BAN, JAN/INN, JAN/NF, JAN/USAN, JAN/USAN/INN, JAN/USP, JAN/USP/INN, JP18, JP18/INN, JP18/INN/NF, JP18/NF, JP18/NF/INN, JP18/USAN, JP18/USAN/INN, JP18/USP, JP18/USP/INN, NF, NF/INN, Non-JPS, TM, TN, USAN, USAN/INN, USAN/INN/BAN, USAN/JAN, USAN/JP18/INN, USNA, USP, USP/INN, USP/JP18, USPINN, USPXXII, USPXXX, USPXXXII') = 0 then n1.d_name_eng else reverse(substring(reverse(n1.d_name_eng),position('('in reverse(n1.d_name_eng))+1,char_length(n1.d_name_eng)-position('('in reverse(n1.d_name_eng)))) end) as d_name_eng from drg.repos_d_number_list n1 where exists ( select 1 from (select max(source_version) as source_version from drg.repos_d_number_list ) n2 where n1.source_version = n2.source_version ) and n1.d_name_eng not like '%;%' -- not include ';' )f2 --slave table2 on f.d_number=f2.d_number ; -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- Maint成分グループ one --The first query contains 44 items in total --Remove 'IG' and get the largest ingredient_code as the starting value of self growth, Fill seven digits from the left with 0 --Take character IG and self growing number splicing insert into drg.maint_drug_ingredient_group --TWO(1) 44 in IG group table select concat('IG',Lpad(cast((select cast(REPLACE(max(ingredient_group_code),'IG','') as INTEGER) from drg.maint_drug_ingredient_group )+ronumber as text),6,'0'))as ingredient_group_code, trim(TRAILING ' ' from replace(drg.convert_half_to_full(f1.dg_name), ' （', '（')) as ingredient_name, drg.convert_full_to_half(f2.dg_name_eng) as ingredient_name_eng, f.dg_number ,'0' ,CURRENT_USER ,now() ,CURRENT_USER ,now() from ( select c.dg_number ,ROW_NUMBER() over(order by c.dg_number) as ronumber from ( select distinct a.dg_number from drg.repos_drug_d_number a where source_version in (select max(source_version) as source_version from drg.repos_drug_d_number x where x.dg_number <>'' ) and a.dg_number <>'' )as c-- 44 the latest date20220519 where not exists ( select b.dg_number from drg.maint_drug_ingredient_group b where c.dg_number = b.dg_number ) ) f --the main table left join -- There is no standard in the group table ( select n1.dg_number, substring(n1.dg_name,1,position(';' in n1.dg_name)-1) as dg_name --Extract left of ';' from drg.repos_d_group_list n1 where exists ( select 1 from (select max(source_version) as source_version from drg.repos_d_group_list ) n2 --20220716 where n1.source_version = n2.source_version ) and n1.dg_name like '%;%' union all select n1.dg_number, n1.dg_name from drg.repos_d_group_list n1 where exists ( select 1 from (select max(source_version) as source_version from drg.repos_d_group_list ) n2 --20220716 where n1.source_version = n2.source_version ) and n1.dg_name not like '%;%' )f1 --the slave table 1 on f.dg_number=f1.dg_number left join ( select n1.dg_number, substring(n1.dg_name_eng,1,position(';' in n1.dg_name_eng)-1) as dg_name_eng from drg.repos_d_group_list n1 where exists ( select 1 from (select max(source_version) as source_version from drg.repos_d_group_list ) n2 --20220716 where n1.source_version = n2.source_version ) and n1.dg_name_eng like '%;%' union all select n1.dg_number, n1.dg_name_eng from drg.repos_d_group_list n1 where exists ( select 1 from (select max(source_version) as source_version from drg.repos_d_group_list ) n2 --20220716 where n1.source_version = n2.source_version ) and n1.dg_name_eng not like '%;%' )f2 --the slave table 2 on f.dg_number=f2.dg_number ; -- Maint成分グループ one --The second query contains 8 items in total --Remove 'IG' and get the largest ingredient_code as the starting value of self growth, Fill seven digits from the left with 0 --Take character IG and self growing number splicing insert into drg.maint_drug_ingredient_group --Two(2)The second query contains 8 items in total --Remove 'I' and get the largest ingredient_code as the starting value of self growth, Fill seven digits from the left with 0 --Take character I and self growing number splicing select concat('IG',Lpad(cast((select cast(REPLACE(max(ingredient_group_code),'IG','') as INTEGER) from drg.maint_drug_ingredient_group )+ronumber as text),6,'0'))as ingredient_group_code, trim(TRAILING ' ' from replace(drg.convert_half_to_full(f1.dg_name), ' （', '（')) as ingredient_name, drg.convert_full_to_half(f2.dg_name_eng) as ingredient_name_eng, f.chemical_dg_number ,'1' ,CURRENT_USER ,now() ,CURRENT_USER ,now() from ( select c.chemical_dg_number,ROW_NUMBER() over(order by c.chemical_dg_number) as ronumber from ( select distinct a.chemical_dg_number from drg.repos_drug_d_number a where source_version in (select max(source_version) as source_version from drg.repos_drug_d_number x where x.chemical_dg_number <>'' and left(source_version,6) = '202207') and a.chemical_dg_number <>'' )as c-- the latest data 20220519 where not exists ( select 1 from drg.maint_drug_ingredient_group b where c.chemical_dg_number = b.dg_number ) ) f --the main table left join ( select n1.dg_number,substring(n1.dg_name,1,position(';' in n1.dg_name)-1) as dg_name from drg.repos_d_group_list n1 where exists ( select 1 from (select max(source_version) as source_version from drg.repos_d_group_list ) n2 --the latest date 20220716 where n1.source_version = n2.source_version ) and n1.dg_name like '%;%' union all select n1.dg_number, n1.dg_name from drg.repos_d_group_list n1 where exists ( select 1 from (select max(source_version) as source_version from drg.repos_d_group_list) n2 --20220716 where n1.source_version = n2.source_version ) and n1.dg_name not like '%;%' )f1 --the slave table1 on f.chemical_dg_number=f1.dg_number left join ( select n1.dg_number, substring(n1.dg_name_eng,1,position(';' in n1.dg_name_eng)-1) as dg_name_eng from drg.repos_d_group_list n1 where exists ( select 1 from (select max(source_version) as source_version from drg.repos_d_group_list ) n2 --20220716 where n1.source_version = n2.source_version ) and n1.dg_name_eng like '%;%' union all select n1.dg_number, n1.dg_name_eng from drg.repos_d_group_list n1 where exists ( select 1 from (select max(source_version) as source_version from drg.repos_d_group_list ) n2 --20220716 where n1.source_version = n2.source_version ) and n1.dg_name_eng not like '%;%' )f2 ----the slave table2 on f.chemical_dg_number=f2.dg_number ; COMMIT; -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
- 114
- 115
- 116
- 117
- 118
- 119
- 120
- 121
- 122
- 123
- 124
- 125
- 126
- 127
- 128
- 129
- 130
- 131
- 132
- 133
- 134
- 135
- 136
- 137
- 138
- 139
- 140
- 141
- 142
- 143
- 144
- 145
- 146
- 147
- 148
- 149
- 150
- 151
- 152
- 153
- 154
- 155
- 156
- 157
- 158
- 159
- 160
- 161
- 162
- 163
- 164
- 165
- 166
- 167
- 168
- 169
- 170
- 171
- 172
- 173
- 174
- 175
- 176
- 177
- 178
- 179
- 180
- 181
- 182
- 183
- 184
- 185
- 186
- 187
- 188
- 189
- 190
- 191
- 192
- 193
- 194
- 195
- 196
- 197
- 198
- 199
- 200
- 201
- 202
- 203
- 204
- 205
- 206
- 207
- 208
- 209
- 210
- 211
- 212
- 213
- 214
- 215
- 216
- 217
- 218
- 219
- 220
- 221
- 222
- 223
- 224
- 225
- 226
- 227
- 228
- 229
- 230
- 231
- 232
- 233
- 234
- 235
- 236
- 237
- 238
- 239
- 240
- 241
- 242
- 243
- 244
- 245
- 246
- 247
- 248
- 249
- 250
- 251
- 252
- 253
- 254
- 255
- 256
- 257
- 258
- 259
- 260
- 261
- 262
- 263
- 264
- 265
- 266
- 267
- 268
- 269
- 270
- 271
- 272
- 273
- 274
- 275
- 276
- 277
- 278
- 279
- 280
- 281
- 282
- 283
- 284
- 285
- 286
- 287
- 288
- 289
- 290
- 291
- 292
- 293
- 294
- 295
- 296
- 297
- 298
- 299
- 300
- 301
- 302
- 303
- 304
- 305
- 306
- 307
- 308
- 309
- 310
- 311
- 312
- 313
- 314
- 315
- 316
- 317
- 318
- 319
- 320
- 321
- 322
- 323
- 324
- 325
- 326
- 327
- 328
- 329
- 330
- 331
- 332
- 333
- 334
- 335
- 336
- 337
- 338
- 339
- 340
- 341
- 342
- 343
- 344
- 345
- 346
- 347
- 348
- 349
- 350
- 351
- 352
- 353
- 354
- 355
- 356
- 357
- 358
- 359
- 360
- 361
- 362
- 363
- 364
- 365
- 366
- 367
- 368
- 369
- 370
- 371
- 372
- 373
- 374
- 375
- 376
- 377
- 378
- 379
- 380
- 381
- 382
- 383
- 384
- 385
- 386
- 387
- 388
- 389
- 390
- 391
- 392
- 393
- 394
- 395
- 396
- 397
- 398
- 399
- 400
- 401
- 402
- 403
- 404
- 405
- 406
- 407
- 408
- 409
- 410
- 411
- 412
- 413
- 414
- 415
- 416
- 417
- 418
- 419
- 420
- 421
- 422
- 423
- 424
- 425
- 426
- 427
- 428
- 429
- 430
- 431
- 432
- 433
- 434
- 435
- 436
- 437
- 438
- 439
- 440
- 441
- 442
- 443
- 444
- 445
- 446
- 447
- 448
- 449
- 450
- 451
- 452
- 453
- 454
- 455
- 456
- 457
- 458
- 459
- 460
- 461
- 462
- 463
- 464
- 465
- 466
- 467
- 468
- 469
- 470
- 471
- 472
- 473
- 474
- 475
- 476
- 477
需求三 简单合并两个表
left join 是依赖a表的数据 和full join 有区别
select a.*,b.d_name,b.d_code left join a on a.d_number = b.d_number- 1
- 2
- 3
如果是常规的添加列的那种 就是实现在表中加入alert增加列 然后Insert into 语句了
但是在企业中不会轻易的进行非select操作 select语句查询较多 特别是针对with …as 的这种临时表而言这里面普及一下with as 的用法
with A as (select * from class) select *from A- 1
- 2
先执行select * from class 得到一个结果，将这个结果记录为A ，在执行select *from A 语句。A 表只是一个别名。
也就是将重复用到的大批量 的SQL语句，放到with as 中，加一个别名，在后面用到的时候就可以直接用。
对于大批量的SQL数据，起到优化的作用。- 1：with子句的使用
使用现有数据库查询，查询在2017.05.01号0点到2017.05.08号0点之间的充值玩家的id,sid,期间充值总数num。
命令如下：
with u as (select id, sum(amount) as num from pay where pay_time >= 1493568000 and pay_time < 1494172800 group by id) select u.id, pinfo.sid, u.num from u join pinfo on u.id=pinfo.id;- 1
- 2
- 3
解析：
使用with查询出相关玩家的id和期间充值总数num，
再查询出对应id玩家的sid。- 2：多个with子句的使用
使用现有数据查询，在1的基础上，增加查询这些玩家到
2017.05.08号0点的历史充值总数total。
with u1 as (select id, sum(amount) as num from pay where pay_time >= 1493568000 and pay_time < 1494172800 group by id), u2 as(select id, sum(amount) as total from pay where pay_time < 1494172800 group by id) select u1.id, pinfo.sid, u1.num, u2.total from u1, u2, pinfo where u2.id = u1.id and pinfo.id = u1.id;- 1
- 2
- 3
- 4
- 5
解析：
使用with查询出固定时间内充值的玩家id和期间充值总数，
使用with查询出指定时间之前充值的玩家的id和历史充值总数，
再查询出u1中所有id对应的u2中的数据，以及pinfo中的sid。主要做的是两个验证 两个巨大表的验证 需要写一些字段 这里面也有验证的内容 由于忘记保存了 所以目前只有验证的俩表 原理如上 第三节做具体的验证 验证有两个不同的版本
连接的表如下：abolish分离with mstr_drug_xref_who_atc as ( --mstr_drug_xref_who_atc 的yj_code全包含于mstr_drug_main SELECT 'mstr_drug_xref_who_atc' as table_description, 'yj_code' as field_name, '「mstr_drug_xref_who_atc」のyj_codeは「mstr_drug_main」に含まれていません' as logic_description,COUNT(b.yj_code) as number_of_results, null AS category_description FROM mstr.mstr_drug_xref_who_atc as a left JOIN mstr.mstr_drug_main as b ON a.yj_code=b.yj_code WHERE b.yj_code IS NULL union SELECT 'mstr_drug_xref_who_atc' as table_description, 'who_atc_code' as field_name, '「mstr_drug_xref_who_atc」のwho_atc_codeは「mstr_drug_who_atc」に含まれていません' as logic_description, COUNT(b.who_atc_code) as number_of_results,NULL AS category_description FROM mstr.mstr_drug_xref_who_atc as a left JOIN mstr.mstr_drug_who_atc as b ON a.who_atc_code=b.who_atc_code WHERE b.who_atc_code IS NULL UNION select 'mstr_drug_xref_who_atc' as table_description,'abolish_flag' as field_name, 'ablish_flag それは0または1ですか' as logic_description,count(*) as number_of_results,abolish_flag AS category_description from mstr.mstr_drug_xref_who_atc group by abolish_flag ), mstr_drug_who_atc as( SELECT 'mstr_drug_who_atc' as table_description, 'who_atc_name' as field_name, 'who_atc_nameキャリッジリターンまたはラインフィードがあります' as logic_description,COUNT(who_atc_name) as number_of_results,NULL AS category_description FROM mstr.mstr_drug_who_atc where "who_atc_name" like '%'||chr(13)||'%' or "who_atc_name" like '%'||chr(10)||'%' UNION SELECT 'mstr_drug_who_atc' as table_description, 'who_atc_1st_name' as field_name, 'who_atc_1st_nameキャリッジリターンまたはラインフィードがあります' as logic_description,COUNT(who_atc_1st_name) as number_of_results,NULL AS category_description FROM mstr.mstr_drug_who_atc where "who_atc_1st_name" like '%'||chr(13)||'%' or "who_atc_1st_name" like '%'||chr(10)||'%' UNION SELECT 'mstr_drug_who_atc' as table_description, 'who_atc_2nd_name' as field_name, 'who_atc_2nd_nameキャリッジリターンまたはラインフィードがあります' as logic_description,COUNT(who_atc_2nd_name) as number_of_results,NULL AS category_description FROM mstr.mstr_drug_who_atc where "who_atc_2nd_name" like '%'||chr(13)||'%' or "who_atc_2nd_name" like '%'||chr(10)||'%' UNION SELECT 'mstr_drug_who_atc' as table_description, 'who_atc_3rd_name' as field_name, 'who_atc_3rd_nameキャリッジリターンまたはラインフィードがあります' as logic_description,COUNT(who_atc_3rd_name) as number_of_results,NULL AS category_description FROM mstr.mstr_drug_who_atc where "who_atc_3rd_name" like '%'||chr(13)||'%' or "who_atc_3rd_name" like '%'||chr(10)||'%' UNION SELECT 'mstr_drug_who_atc' as table_description, 'who_atc_4th_name' as field_name, 'who_atc_4th_nameキャリッジリターンまたはラインフィードがあります' as logic_description,COUNT(who_atc_4th_name) as number_of_results,NULL AS category_description FROM mstr.mstr_drug_who_atc where "who_atc_4th_name" like '%'||chr(13)||'%' or "who_atc_4th_name" like '%'||chr(10)||'%' UNION SELECT 'mstr_drug_who_atc' as table_description, 'who_atc_5th_name' as field_name, 'who_atc_5th_nameキャリッジリターンまたはラインフィードがあります' as logic_description,COUNT(who_atc_5th_name) as number_of_results,NULL AS category_description FROM mstr.mstr_drug_who_atc where "who_atc_5th_name" like '%'||chr(13)||'%' or "who_atc_5th_name" like '%'||chr(10)||'%' UNION SELECT 'mstr_drug_who_atc' as table_description, 'who_atc_name_eng' as field_name, 'who_atc_name_engキャリッジリターンまたはラインフィードがあります' as logic_description,COUNT(who_atc_name_eng) as number_of_results,NULL AS category_description FROM mstr.mstr_drug_who_atc where "who_atc_name_eng" like '%'||chr(13)||'%' or "who_atc_name_eng" like '%'||chr(10)||'%' UNION select 'mstr_drug_who_atc' as table_description, 'who_atc_name_eng' as field_name, 'who_atc_name_eng文字化けした文字が含まれています' as logic_description,COUNT(who_atc_name_eng) as number_of_results,NULL AS category_description from mstr.mstr_drug_who_atc where who_atc_name_eng like '%?%' UNION SELECT 'mstr_drug_who_atc' as table_description, 'who_atc_1st_name_eng' as field_name, 'who_atc_1st_name_engキャリッジリターンまたはラインフィードがあります' as logic_description,COUNT(who_atc_1st_name_eng) as number_of_results,NULL AS category_description FROM mstr.mstr_drug_who_atc where "who_atc_1st_name_eng" like '%'||chr(13)||'%' or "who_atc_1st_name_eng" like '%'||chr(10)||'%' UNION select 'mstr_drug_who_atc' as table_description, 'who_atc_1st_name_eng' as field_name, 'who_atc_1st_name_eng文字化けした文字が含まれています' as logic_description,COUNT(who_atc_1st_name_eng) as number_of_results,NULL AS category_description from mstr.mstr_drug_who_atc where who_atc_1st_name_eng like '%?%' UNION SELECT 'mstr_drug_who_atc' as table_description, 'who_atc_2nd_name_eng' as field_name, 'who_atc_2nd_name_engキャリッジリターンまたはラインフィードがあります' as logic_description,COUNT(who_atc_2nd_name_eng) as number_of_results,NULL AS category_description FROM mstr.mstr_drug_who_atc where "who_atc_2nd_name_eng" like '%'||chr(13)||'%' or "who_atc_2nd_name_eng" like '%'||chr(10)||'%' UNION select 'mstr_drug_who_atc' as table_description, 'who_atc_2nd_name_eng' as field_name, 'who_atc_2nd_name_eng文字化けした文字が含まれています' as logic_description,COUNT(who_atc_2nd_name_eng) as number_of_results,NULL AS category_description from mstr.mstr_drug_who_atc where who_atc_2nd_name_eng like '%?%' UNION SELECT 'mstr_drug_who_atc' as table_description, 'who_atc_3rd_name_eng' as field_name, 'who_atc_3rd_name_engキャリッジリターンまたはラインフィードがあります' as logic_description,COUNT(who_atc_3rd_name_eng) as number_of_results,NULL AS category_description FROM mstr.mstr_drug_who_atc where "who_atc_3rd_name_eng" like '%'||chr(13)||'%' or "who_atc_3rd_name_eng" like '%'||chr(10)||'%' UNION select 'mstr_drug_who_atc' as table_description, 'who_atc_3rd_name_eng' as field_name, 'who_atc_3rd_name_eng文字化けした文字が含まれています' as logic_description,COUNT(who_atc_3rd_name_eng) as number_of_results,NULL AS category_description from mstr.mstr_drug_who_atc where who_atc_3rd_name_eng like '%?%' UNION SELECT 'mstr_drug_who_atc' as table_description, 'who_atc_4th_name_eng' as field_name, 'who_atc_4th_name_engキャリッジリターンまたはラインフィードがあります' as logic_description,COUNT(who_atc_4th_name_eng) as number_of_results,NULL AS category_description FROM mstr.mstr_drug_who_atc where "who_atc_4th_name_eng" like '%'||chr(13)||'%' or "who_atc_4th_name_eng" like '%'||chr(10)||'%' UNION select 'mstr_drug_who_atc' as table_description, 'who_atc_4th_name_eng' as field_name, 'who_atc_4th_name_eng文字化けした文字が含まれています' as logic_description,COUNT(who_atc_4th_name_eng) as number_of_results,NULL AS category_description from mstr.mstr_drug_who_atc where who_atc_4th_name_eng like '%?%' UNION select 'mstr_drug_who_atc' as table_description, 'who_atc_5th_name_eng' as field_name, 'who_atc_5th_name_eng文字化けした文字が含まれています' as logic_description,COUNT(who_atc_5th_name_eng) as number_of_results,NULL AS category_description from mstr.mstr_drug_who_atc where who_atc_5th_name_eng like '%?%' UNION SELECT 'mstr_drug_who_atc' as table_description, 'who_atc_5th_name_eng' as field_name, 'who_atc_5th_name_engキャリッジリターンまたはラインフィードがあります' as logic_description,COUNT(who_atc_5th_name_eng) as number_of_results,NULL AS category_description FROM mstr.mstr_drug_who_atc where "who_atc_5th_name_eng" like '%'||chr(13)||'%' or "who_atc_5th_name_eng" like '%'||chr(10)||'%' UNION select 'mstr_drug_who_atc' as table_description,'abolish_flag' as field_name, 'ablish_flag それは0または1ですか' as logic_description,count(*) as number_of_results,abolish_flag AS category_description from mstr.mstr_drug_who_atc group by abolish_flag ), mstr_drug_xref_ephmra_atc AS( SELECT 'mstr_drug_xref_ephmra_atc' as table_description, 'yj_coode' as field_name, '「mstr_drug_xref_ephmra_atc」のyj_codeは「mstr_drug_main」に含まれていません' as logic_description,COUNT(b.yj_code) as number_of_results, NULL AS category_description FROM mstr.mstr_drug_xref_ephmra_atc as a left JOIN mstr.mstr_drug_main as b ON a.yj_code=b.yj_code WHERE b.yj_code IS NULL UNION SELECT 'mstr_drug_xref_ephmra_atc' as table_description, 'who_atc_code' as field_name, '「mstr_drug_xref_who_atc」のephmra_atc_codeは「mstr_drug_ephmra_atc」に含まれていません' as logic_description,COUNT(b.ephmra_atc_code) as number_of_results,NULL AS category_description FROM mstr.mstr_drug_xref_ephmra_atc as a left JOIN mstr.mstr_drug_ephmra_atc as b ON a.ephmra_atc_code=b.ephmra_atc_code WHERE b.ephmra_atc_code IS NULL UNION select 'mstr_drug_xref_ephmra_atc' as table_description,'abolish_flag' as field_name, 'ablish_flag それは0または1ですか' as logic_description,count(*) as number_of_results,abolish_flag AS category_description from mstr.mstr_drug_xref_ephmra_atc group by abolish_flag ), mstr_drug_ephmra_atc AS( SELECT 'mstr_drug_ephmra_atc' as table_description, 'ephmra_atc_name' as field_name, 'ephmra_atc_nameキャリッジリターンまたはラインフィードがあります' as logic_description,COUNT(ephmra_atc_name) as number_of_results,NULL AS category_description FROM mstr.mstr_drug_ephmra_atc where "ephmra_atc_name" like '%'||chr(13)||'%' or "ephmra_atc_name" like '%'||chr(10)||'%' UNION SELECT 'mstr_drug_ephmra_atc' as table_description, 'ephmra_atc_1st_name' as field_name, 'ephmra_atc_1st_nameキャリッジリターンまたはラインフィードがあります' as logic_description,COUNT(ephmra_atc_1st_name) as number_of_results,NULL AS category_description FROM mstr.mstr_drug_ephmra_atc where "ephmra_atc_1st_name" like '%'||chr(13)||'%' or "ephmra_atc_1st_name" like '%'||chr(10)||'%' UNION SELECT 'mstr_drug_ephmra_atc' as table_description, 'ephmra_atc_2nd_name' as field_name, 'ephmra_atc_2nd_nameキャリッジリターンまたはラインフィードがあります' as logic_description,COUNT(ephmra_atc_2nd_name) as number_of_results,NULL AS category_description FROM mstr.mstr_drug_ephmra_atc where "ephmra_atc_2nd_name" like '%'||chr(13)||'%' or "ephmra_atc_2nd_name" like '%'||chr(10)||'%' UNION SELECT 'mstr_drug_ephmra_atc' as table_description, 'ephmra_atc_3rd_name' as field_name, 'ephmra_atc_3rd_nameキャリッジリターンまたはラインフィードがあります' as logic_description,COUNT(ephmra_atc_3rd_name) as number_of_results,NULL AS category_description FROM mstr.mstr_drug_ephmra_atc where "ephmra_atc_3rd_name" like '%'||chr(13)||'%' or "ephmra_atc_3rd_name" like '%'||chr(10)||'%' UNION SELECT 'mstr_drug_ephmra_atc' as table_description, 'ephmra_atc_4th_name' as field_name, 'ephmra_atc_4th_nameキャリッジリターンまたはラインフィードがあります' as logic_description,COUNT(ephmra_atc_4th_name) as number_of_results,NULL AS category_description FROM mstr.mstr_drug_ephmra_atc where "ephmra_atc_4th_name" like '%'||chr(13)||'%' or "ephmra_atc_4th_name" like '%'||chr(10)||'%' UNION SELECT 'mstr_drug_ephmra_atc' as table_description, 'ephmra_atc_name_eng' as field_name, 'ephmra_atc_name_engキャリッジリターンまたはラインフィードがあります' as logic_description,COUNT(ephmra_atc_name_eng) as number_of_results,NULL AS category_description FROM mstr.mstr_drug_ephmra_atc where "ephmra_atc_name_eng" like '%'||chr(13)||'%' or "ephmra_atc_name_eng" like '%'||chr(10)||'%' UNION select 'mstr_drug_ephmra_atc' as table_description, 'ephmra_atc_name_eng' as field_name, 'ephmra_atc_name_eng文字化けした文字が含まれています' as logic_description,COUNT(ephmra_atc_name_eng) as number_of_results,NULL AS category_description from mstr.mstr_drug_ephmra_atc where ephmra_atc_name_eng like '%?%' UNION SELECT 'mstr_drug_ephmra_atc' as table_description, 'ephmra_atc_1st_name_eng' as field_name, 'ephmra_atc_1st_name_engキャリッジリターンまたはラインフィードがあります' as logic_description,COUNT(ephmra_atc_1st_name_eng) as number_of_results,NULL AS category_description FROM mstr.mstr_drug_ephmra_atc where "ephmra_atc_1st_name_eng" like '%'||chr(13)||'%' or "ephmra_atc_1st_name_eng" like '%'||chr(10)||'%' UNION select 'mstr_drug_ephmra_atc' as table_description, 'ephmra_atc_1st_name_eng' as field_name, 'ephmra_atc_1st_name_eng文字化けした文字が含まれています' as logic_description,COUNT(ephmra_atc_1st_name_eng) as number_of_results,NULL AS category_description from mstr.mstr_drug_ephmra_atc where ephmra_atc_1st_name_eng like '%?%' UNION SELECT 'mstr_drug_ephmra_atc' as table_description, 'ephmra_atc_2nd_name_eng' as field_name, 'ephmra_atc_2nd_name_engキャリッジリターンまたはラインフィードがあります' as logic_description,COUNT(ephmra_atc_2nd_name_eng) as number_of_results,NULL AS category_description FROM mstr.mstr_drug_ephmra_atc where "ephmra_atc_2nd_name_eng" like '%'||chr(13)||'%' or "ephmra_atc_2nd_name_eng" like '%'||chr(10)||'%' UNION select 'mstr_drug_ephmra_atc' as 名, 'ephmra_atc_2nd_name_eng' as field_name, 'ephmra_atc_2nd_name_eng文字化けした文字が含まれています' as logic_description,COUNT(ephmra_atc_2nd_name_eng) as number_of_results,NULL AS category_description from mstr.mstr_drug_ephmra_atc where ephmra_atc_2nd_name_eng like '%?%' UNION SELECT 'mstr_drug_ephmra_atc' as table_description, 'ephmra_atc_3rd_name_eng' as field_name, 'ephmra_atc_3rd_name_engキャリッジリターンまたはラインフィードがあります' as logic_description,COUNT(ephmra_atc_3rd_name_eng) as number_of_results,NULL AS category_description FROM mstr.mstr_drug_ephmra_atc where "ephmra_atc_3rd_name_eng" like '%'||chr(13)||'%' or "ephmra_atc_3rd_name_eng" like '%'||chr(10)||'%' UNION select 'mstr_drug_ephmra_atc' as table_description, 'ephmra_atc_3rd_name_eng' as field_name, 'ephmra_atc_3rd_name_eng文字化けした文字が含まれています' as logic_description,COUNT(ephmra_atc_3rd_name_eng) as number_of_results,NULL AS category_description from mstr.mstr_drug_ephmra_atc where ephmra_atc_3rd_name_eng like '%?%' UNION SELECT 'mstr_drug_ephmra_atc' as table_description, 'ephmra_atc_4th_name_eng' as field_name, 'ephmra_atc_4th_name_engキャリッジリターンまたはラインフィードがあります' as logic_description,COUNT(ephmra_atc_4th_name_eng) as number_of_results,NULL AS category_description FROM mstr.mstr_drug_ephmra_atc where "ephmra_atc_4th_name_eng" like '%'||chr(13)||'%' or "ephmra_atc_4th_name_eng" like '%'||chr(10)||'%' union select 'mstr_drug_ephmra_atc' as table_description, 'ephmra_atc_4th_name_eng' as field_name, 'ephmra_atc_4th_name_eng文字化けした文字が含まれています' as logic_description,COUNT(ephmra_atc_4th_name_eng) as number_of_results,NULL AS category_description from mstr.mstr_drug_ephmra_atc where ephmra_atc_4th_name_eng like '%?%' UNION select 'mstr_drug_ephmra_atc' as table_description,'abolish_flag' as field_name, 'ablish_flag それは0または1ですか' as logic_description,count(*) as number_of_results,abolish_flag AS category_description from mstr.mstr_drug_ephmra_atc group by abolish_flag ), mstr_drug_xref_efficacy_87 as( SELECT 'mstr_drug_xref_efficacy_87' as table_description, 'yj_code' as field_name, '「mstr_drug_xref_efficacy_87」のyj_codeは「mstr_drug_main」に含まれていません' as logic_description, COUNT(b.yj_code) as number_of_results,NULL AS category_description FROM mstr.mstr_drug_xref_efficacy_87 as a left JOIN mstr.mstr_drug_main as b ON a.yj_code=b.yj_code WHERE b.yj_code IS NULL UNION SELECT 'mstr_drug_xref_efficacy_87' as table_description, 'efficacy_87_code' as field_name, '「mstr_drug_xref_efficacy_87」のefficiency_87_codeは「mstr_drug_efficacy_87」に含まれていません ' as logic_description, COUNT(b.efficacy_87_code) as number_of_results,NULL AS category_description FROM mstr.mstr_drug_xref_efficacy_87 as a left JOIN mstr.mstr_drug_efficacy_87 as b ON a.efficacy_87_code=b.efficacy_87_code WHERE b.efficacy_87_code IS NULL UNION select 'mstr_drug_xref_efficacy_87' as table_description,'abolish_flag' as field_name, 'ablish_flag それは0または1ですか' as logic_description,count(*) as number_of_results,abolish_flag AS category_description from mstr.mstr_drug_xref_efficacy_87 group by abolish_flag ), mstr_drug_efficacy_87 AS( SELECT 'mstr_drug_efficacy_87' as table_description, 'efficacy_87_name' as field_name, 'efficacy_87_nameキャリッジリターンまたはラインフィードがあります' as logic_description,COUNT(efficacy_87_name) as number_of_results, NULL AS category_description FROM mstr.mstr_drug_efficacy_87 where "efficacy_87_name" like '%'||chr(13)||'%' or "efficacy_87_name" like '%'||chr(10)||'%' UNION SELECT 'mstr_drug_efficacy_87' as table_description, 'efficacy_87_1st_name' as field_name, 'efficacy_87_1st_nameキャリッジリターンまたはラインフィードがあります' as logic_description,COUNT(efficacy_87_1st_name) as number_of_results, NULL AS category_description FROM mstr.mstr_drug_efficacy_87 where "efficacy_87_1st_name" like '%'||chr(13)||'%' or "efficacy_87_1st_name" like '%'||chr(10)||'%' UNION SELECT 'mstr_drug_efficacy_87' as table_description, 'efficacy_87_2nd_name' as field_name, 'efficacy_87_2nd_nameキャリッジリターンまたはラインフィードがあります' as logic_description,COUNT(efficacy_87_2nd_name) as number_of_results, NULL AS category_description FROM mstr.mstr_drug_efficacy_87 where "efficacy_87_2nd_name" like '%'||chr(13)||'%' or "efficacy_87_2nd_name" like '%'||chr(10)||'%' UNION SELECT 'mstr_drug_efficacy_87' as table_description, 'efficacy_87_3rd_name' as field_name, 'efficacy_87_3rd_nameキャリッジリターンまたはラインフィードがあります' as logic_description,COUNT(efficacy_87_3rd_name) as number_of_results, NULL AS category_description FROM mstr.mstr_drug_efficacy_87 where "efficacy_87_3rd_name" like '%'||chr(13)||'%' or "efficacy_87_3rd_name" like '%'||chr(10)||'%' UNION SELECT 'mstr_drug_efficacy_87' as table_description, 'efficacy_87_4th_name' as field_name, 'efficacy_87_4th_nameキャリッジリターンまたはラインフィードがあります' as logic_description,COUNT(efficacy_87_4th_name) as number_of_results, NULL AS category_description FROM mstr.mstr_drug_efficacy_87 where "efficacy_87_4th_name" like '%'||chr(13)||'%' or "efficacy_87_4th_name" like '%'||chr(10)||'%' UNION SELECT 'mstr_drug_efficacy_87' as table_description, 'efficacy_87_name_eng' as field_name, 'efficacy_87_name_engキャリッジリターンまたはラインフィードがあります' as logic_description,COUNT(efficacy_87_name_eng) as number_of_results, NULL AS category_description FROM mstr.mstr_drug_efficacy_87 where "efficacy_87_name_eng" like '%'||chr(13)||'%' or "efficacy_87_name_eng" like '%'||chr(10)||'%' UNION select 'mstr_drug_efficacy_87' as table_description, 'efficacy_87_name_eng' as field_name, 'efficacy_87_name_eng文字化けした文字が含まれています' as logic_description,COUNT(efficacy_87_name_eng) as number_of_results,NULL AS category_description from mstr.mstr_drug_efficacy_87 where efficacy_87_name_eng like '%?%' UNION SELECT 'mstr_drug_efficacy_87' as table_description, 'efficacy_87_1st_name_eng' as field_name, 'efficacy_87_1st_name_engキャリッジリターンまたはラインフィードがあります' as logic_description,COUNT(efficacy_87_1st_name_eng) as number_of_results, NULL AS category_description FROM mstr.mstr_drug_efficacy_87 where "efficacy_87_1st_name_eng" like '%'||chr(13)||'%' or "efficacy_87_1st_name_eng" like '%'||chr(10)||'%' UNION select 'mstr_drug_efficacy_87' as table_description, 'efficacy_87_1st_name_eng' as field_name, 'efficacy_87_1st_name_eng文字化けした文字が含まれています' as logic_description,COUNT(efficacy_87_1st_name_eng) as number_of_results,NULL AS category_description from mstr.mstr_drug_efficacy_87 where efficacy_87_1st_name_eng like '%?%' UNION SELECT 'mstr_drug_efficacy_87' as table_description, 'efficacy_87_2nd_name_eng' as field_name, 'efficacy_87_2nd_name_engキャリッジリターンまたはラインフィードがあります' as logic_description,COUNT(efficacy_87_2nd_name_eng) as number_of_results, NULL AS category_description FROM mstr.mstr_drug_efficacy_87 where "efficacy_87_2nd_name_eng" like '%'||chr(13)||'%' or "efficacy_87_2nd_name_eng" like '%'||chr(10)||'%' UNION select 'mstr_drug_efficacy_87' as table_description, 'efficacy_87_2nd_name_eng' as field_name, 'efficacy_87_2nd_name_eng文字化けした文字が含まれています' as logic_description,COUNT(efficacy_87_2nd_name_eng) as number_of_results,NULL AS category_description from mstr.mstr_drug_efficacy_87 where efficacy_87_2nd_name_eng like '%?%' UNION SELECT 'mstr_drug_efficacy_87' as table_description, 'efficacy_87_3rd_name_eng' as field_name, 'efficacy_87_3rd_name_engキャリッジリターンまたはラインフィードがあります' as logic_description,COUNT(efficacy_87_3rd_name_eng) as number_of_results, NULL AS category_description FROM mstr.mstr_drug_efficacy_87 where "efficacy_87_3rd_name_eng" like '%'||chr(13)||'%' or "efficacy_87_3rd_name_eng" like '%'||chr(10)||'%' UNION select 'mstr_drug_efficacy_87' as table_description, 'efficacy_87_3rd_name_eng' as field_name, 'efficacy_87_3rd_name_eng文字化けした文字が含まれています' as logic_description,COUNT(efficacy_87_3rd_name_eng) as number_of_results,NULL AS category_description from mstr.mstr_drug_efficacy_87 where efficacy_87_3rd_name_eng like '%?%' UNION SELECT 'mstr_drug_efficacy_87' as table_description, 'efficacy_87_4th_name_eng' as field_name, 'efficacy_87_4th_name_engキャリッジリターンまたはラインフィードがあります' as logic_description,COUNT(efficacy_87_4th_name_eng) as number_of_results, NULL AS category_description FROM mstr.mstr_drug_efficacy_87 where "efficacy_87_4th_name_eng" like '%'||chr(13)||'%' or "efficacy_87_4th_name_eng" like '%'||chr(10)||'%' UNION select 'mstr_drug_efficacy_87' as table_description, 'efficacy_87_4th_name_eng' as field_name, 'efficacy_87_4th_name_eng文字化けした文字が含まれています' as logic_description,COUNT(efficacy_87_4th_name_eng) as number_of_results,NULL AS category_description from mstr.mstr_drug_efficacy_87 where efficacy_87_4th_name_eng like '%?%' UNION select 'mstr_drug_efficacy_87' as table_description,'abolish_flag' as field_name, 'ablish_flag それは0または1ですか' as logic_description,count(*) as number_of_results,abolish_flag AS category_description from mstr.mstr_drug_efficacy_87 group by abolish_flag ), mstr_drug_main as( SELECT 'mstr_drug_main' as table_description, 'yj_code,yakka_code' as field_name, 'yj_codeとyakka_codeの最初の9桁が矛盾しています' as logic_description,COUNT(left(yj_code,9) != left(yakka_code,9)) as number_of_results, NULL AS category_description FROM mstr.mstr_drug_main where left(yj_code,9) != left(yakka_code,9) UNION SELECT 'mstr_drug_main' as table_description, 'sales_drug_name' as field_name, 'sales_drug_nameキャリッジリターンまたはラインフィードがあります' as logic_description,COUNT(sales_drug_name) as number_of_results, NULL AS category_description FROM mstr.mstr_drug_main where "sales_drug_name" like '%'||chr(13)||'%' or "sales_drug_name" like '%'||chr(10)||'%' UNION SELECT 'mstr_drug_main' as table_description, 'sales_drug_name_kana' as field_name, 'sales_drug_name_kana漢字の存在を確認してください' as logic_description,COUNT(sales_drug_name_kana) as number_of_results, NULL AS category_description FROM mstr.mstr_drug_main where sales_drug_name_kana ~ '[一-龠々]+' UNION SELECT 'mstr_drug_main' as table_description, 'sales_drug_name_kana' as field_name, 'sales_drug_name_kanaひらがなの有無を確認する' as logic_description,COUNT(sales_drug_name_kana) as number_of_results, NULL AS category_description FROM mstr.mstr_drug_main where sales_drug_name_kana ~ '[ぁ-ん]+' UNION SELECT 'mstr_drug_main' as table_description, 'sales_drug_name_kana' as field_name, 'sales_drug_name_kanaキャリッジリターンまたはラインフィードがあります' as logic_description,COUNT(sales_drug_name_kana) as number_of_results, NULL AS category_description FROM mstr.mstr_drug_main where "sales_drug_name_kana" like '%'||chr(13)||'%' or "sales_drug_name_kana" like '%'||chr(10)||'%' UNION SELECT 'mstr_drug_main' as table_description, 'brand_name_1' as field_name, 'brand_name_1キャリッジリターンまたはラインフィードがあります' as logic_description,COUNT(brand_name_1) as number_of_results, NULL AS category_description FROM mstr.mstr_drug_main where "brand_name_1" like '%'||chr(13)||'%' or "brand_name_1" like '%'||chr(10)||'%' UNION SELECT 'mstr_drug_main' as table_description, 'brand_name_1_kana' as field_name, 'brand_name_1_kana漢字の存在を確認してください' as logic_description,COUNT(brand_name_1_kana) as number_of_results, NULL AS category_description FROM mstr.mstr_drug_main where brand_name_1_kana ~ '[一-龠々]+' UNION SELECT 'mstr_drug_main' as table_description, 'brand_name_1_kana' as field_name, 'brand_name_1_kanaひらがなの有無を確認する' as logic_description,COUNT(brand_name_1_kana) as number_of_results, NULL AS category_description FROM mstr.mstr_drug_main where brand_name_1_kana ~ '[ぁ-ん]+' UNION SELECT 'mstr_drug_main' as table_description, 'brand_name_1_kana' as field_name, 'brand_name_1_kanaキャリッジリターンまたはラインフィードがあります' as logic_description,COUNT(brand_name_1_kana) as number_of_results, NULL AS category_description FROM mstr.mstr_drug_main where "brand_name_1_kana" like '%'||chr(13)||'%' or "brand_name_1_kana" like '%'||chr(10)||'%' UNION SELECT 'mstr_drug_main' as table_description, 'generic_name' as field_name, 'generic_nameキャリッジリターンまたはラインフィードがあります' as logic_description,COUNT(generic_name) as number_of_results, NULL AS category_description FROM mstr.mstr_drug_main where "generic_name" like '%'||chr(13)||'%' or "generic_name" like '%'||chr(10)||'%' UNION SELECT 'mstr_drug_main' as table_description, 'generic_name_kana' as field_name, 'generic_name_kana漢字の存在を確認してください' as logic_description,COUNT(generic_name_kana) as number_of_results, NULL AS category_description FROM mstr.mstr_drug_main where generic_name_kana ~ '[一-龠々]+' UNION SELECT 'mstr_drug_main' as table_description, 'generic_name_kana' as field_name, 'generic_name_kanaひらがなの有無を確認する' as logic_description,COUNT(generic_name_kana) as number_of_results, NULL AS category_description FROM mstr.mstr_drug_main where generic_name_kana ~ '[ぁ-ん]+' UNION SELECT 'mstr_drug_main' as table_description, 'generic_name_kana' as field_name, 'generic_name_kanaキャリッジリターンまたはラインフィードがあります' as logic_description,COUNT(generic_name_kana) as number_of_results, NULL AS category_description FROM mstr.mstr_drug_main where "generic_name_kana" like '%'||chr(13)||'%' or "generic_name_kana" like '%'||chr(10)||'%' UNION SELECT 'mstr_drug_main' as table_description, 'sales_drug_name_eng' as field_name, 'sales_drug_name_engキャリッジリターンまたはラインフィードがあります' as logic_description,COUNT(sales_drug_name_eng) as number_of_results, NULL AS category_description FROM mstr.mstr_drug_main where "sales_drug_name_eng" like '%'||chr(13)||'%' or "sales_drug_name_eng" like '%'||chr(10)||'%' UNION select 'mstr_drug_main' as table_description, 'sales_drug_name_eng' as field_name, 'sales_drug_name_eng文字化けした文字が含まれています' as logic_description,COUNT(sales_drug_name_eng) as number_of_results,NULL AS category_description from mstr.mstr_drug_main where sales_drug_name_eng like '%?%' UNION SELECT 'mstr_drug_main' as table_description, 'generic_name_eng' as field_name, 'generic_name_engキャリッジリターンまたはラインフィードがあります' as logic_description,COUNT(generic_name_eng) as number_of_results, NULL AS category_description FROM mstr.mstr_drug_main where "generic_name_eng" like '%'||chr(13)||'%' or "generic_name_eng" like '%'||chr(10)||'%' UNION select 'mstr_drug_main' as table_description, 'generic_name_eng' as field_name, 'generic_name_eng文字化けした文字が含まれています' as logic_description,COUNT(generic_name_eng) as number_of_results,NULL AS category_description from mstr.mstr_drug_main where generic_name_eng like '%?%' UNION select 'mstr_drug_main' as table_description,'abolish_flag' as field_name, 'ablish_flag それは0または1ですか' as logic_description,count(*) as number_of_results,abolish_flag AS category_description from mstr.mstr_drug_main group by abolish_flag ), mstr_drug_xref_rece AS( SELECT 'mstr_drug_xref_rece' as table_description, 'yj_code' as field_name, '「mstr_drug_xref_rece」のyj_codeは「mstr_drug_main」に含まれていません' as logic_description,COUNT(b.yj_code) as number_of_results, NULL AS category_description FROM mstr.mstr_drug_xref_rece as a left JOIN mstr.mstr_drug_main as b ON a.yj_code=b.yj_code WHERE b.yj_code IS NULL UNION SELECT 'mstr_drug_xref_rece' as table_description, 'rece_drug_code_1' as field_name, '「mstr_drug_xref_rece」のrece_drug_code_1は「mstr_drug_rece」に含まれていません' as logic_description,COUNT(b.rece_drug_code_1) as number_of_results, NULL AS category_description FROM mstr.mstr_drug_xref_rece as a left JOIN mstr.mstr_drug_rece as b ON a.rece_drug_code_1=b.rece_drug_code_1 WHERE b.rece_drug_code_1 IS NULL UNION select 'mstr_drug_xref_rece' as table_description,'abolish_flag' as field_name, 'ablish_flag それは0または1ですか' as logic_description,count(*) as number_of_results,abolish_flag AS category_description from mstr.mstr_drug_xref_rece group by abolish_flag ), mstr_drug_rece AS( SELECT 'mstr_drug_rece' as table_description, 'drug_standard_name' as field_name, 'drug_standard_nameキャリッジリターンまたはラインフィードがあります' as logic_description,COUNT(drug_standard_name) as number_of_results, NULL AS category_description FROM mstr.mstr_drug_rece where "drug_standard_name" like '%'||chr(13)||'%' or "drug_standard_name" like '%'||chr(10)||'%' UNION SELECT 'mstr_drug_rece' as table_description, 'drug_standard_name_kana' as field_name, 'drug_standard_name_kana漢字の存在を確認してください' as logic_description,COUNT(drug_standard_name_kana) as number_of_results, NULL AS category_description FROM mstr.mstr_drug_rece WHERE drug_standard_name_kana ~ '[一-龠々]+' UNION SELECT 'mstr_drug_rece' as table_description, 'drug_standard_name_kana' as field_name, 'drug_standard_name_kanaひらがなの有無を確認する' as logic_description,COUNT(drug_standard_name_kana) as number_of_results, NULL AS category_description FROM mstr.mstr_drug_rece WHERE drug_standard_name_kana ~ '[ぁ-ん]+' UNION SELECT 'mstr_drug_rece' as table_description, 'drug_standard_name_kana' as field_name, 'drug_standard_name_kanaキャリッジリターンまたはラインフィードがあります' as logic_description,COUNT(drug_standard_name_kana) as number_of_results, NULL AS category_description FROM mstr.mstr_drug_rece where "drug_standard_name_kana" like '%'||chr(13)||'%' or "drug_standard_name_kana" like '%'||chr(10)||'%' union SELECT 'mstr_drug_rece' as table_description, 'basic_drug_name' as field_name, 'basic_drug_nameキャリッジリターンまたはラインフィードがあります' as logic_description,COUNT(basic_drug_name) as number_of_results, NULL AS category_description FROM mstr.mstr_drug_rece where "basic_drug_name" like '%'||chr(13)||'%' or "basic_drug_name" like '%'||chr(10)||'%' ), mstr_drug_ingredient_conversion AS( SELECT 'mstr_drug_ingredient_conversion VS mstr_drug_ingredient' as table_description, 'ingredient_code' as field_name, 'mstr_drug_ingredient_conversionとmstr_drug_ingredientとmstr_drug_mainの成分コードに整合性があるか' as logic_description, COUNT(*) as number_of_results, NULL AS category_description FROM mstr.mstr_drug_ingredient_conversion as a left JOIN mstr.mstr_drug_ingredient as b ON a.ingredient_code=b.ingredient_code WHERE a.ingredient_code !=b.ingredient_code union SELECT 'mstr_drug_ingredient_conversion VS mstr_drug_main' as table_description, 'ingredient_code' as field_name, 'mstr_drug_ingredient_conversionとmstr_drug_ingredientとmstr_drug_mainの成分コードに整合性があるか' as logic_description, COUNT(*) as number_of_results, NULL AS category_description FROM mstr.mstr_drug_ingredient_conversion as a left join mstr.mstr_drug_main c on a.ingredient_code = c.ingredient_code WHERE a.ingredient_code !=c.ingredient_code UNION SELECT 'mstr_drug_ingredient_conversion' as table_description, 'standard_unit_name' as field_name, 'standard_unit_nameキャリッジリターンまたはラインフィードがあります' as logic_description,COUNT(standard_unit_name) as number_of_results, NULL AS category_description FROM mstr.mstr_drug_ingredient_conversion where "standard_unit_name" like '%'||chr(13)||'%' or "standard_unit_name" like '%'||chr(10)||'%' UNION SELECT 'mstr_drug_ingredient_conversion' as table_description, 'ingredient_unit_name' as field_name, 'ingredient_unit_nameキャリッジリターンまたはラインフィードがあります' as logic_description,COUNT(ingredient_unit_name) as number_of_results, NULL AS category_description FROM mstr.mstr_drug_ingredient_conversion where "ingredient_unit_name" like '%'||chr(13)||'%' or "ingredient_unit_name" like '%'||chr(10)||'%' UNION select 'mstr_drug_ingredient_conversion' as table_description,'abolish_flag' as field_name, 'ablish_flag それは0または1ですか' as logic_description,count(*) as number_of_results,abolish_flag AS category_description from mstr.mstr_drug_ingredient_conversion group by abolish_flag ), mstr_drug_ingredient AS( SELECT 'mstr_drug_ingredient' as table_description, 'ingredient_name' as field_name, 'ingredient_nameキャリッジリターンまたはラインフィードがあります' as logic_description,COUNT(ingredient_name) as number_of_results, NULL AS category_description FROM mstr.mstr_drug_ingredient where "ingredient_name" like '%'||chr(13)||'%' or "ingredient_name" like '%'||chr(10)||'%' UNION SELECT 'mstr_drug_ingredient' as table_description, 'ingredient_name_eng' as field_name, 'ingredient_name_engキャリッジリターンまたはラインフィードがあります' as logic_description,COUNT(ingredient_name_eng) as number_of_results, NULL AS category_description FROM mstr.mstr_drug_ingredient where "ingredient_name_eng" like '%'||chr(13)||'%' or "ingredient_name_eng" like '%'||chr(10)||'%' UNION select 'mstr_drug_ingredient' as table_description, 'ingredient_name_eng' as field_name, 'ingredient_name_eng文字化けした文字が含まれています' as logic_description,COUNT(ingredient_name_eng) as number_of_results,NULL AS category_description from mstr.mstr_drug_ingredient where ingredient_name_eng like '%?%' UNION select 'mstr_drug_ingredient' as table_description,'abolish_flag' as field_name, 'ablish_flag それは0または1ですか' as logic_description,count(*) as number_of_results,abolish_flag AS category_description from mstr.mstr_drug_ingredient group by abolish_flag ), maint_drug_main AS ( SELECT 'maint_drug_main' as table_description,'yj_code' as field_name, 'テーブル「maint_drug_main」とテーブル「mstr_drug_main」のカラム「yj_code」の値は違っています' as logic_description,count(yj_code) as number_of_results,NULL AS category_description FROM ( SELECT yj_code FROM drg.maint_drug_main except SELECT yj_code FROM mstr.mstr_drug_main) AS a1 union SELECT 'maint_drug_main' as table_description,'ingredient_code' as field_name, 'テーブル「maint_drug_main」とテーブル「mstr_drug_main」のカラム「ingredient_code」の値は違っています' as logic_description,count(distinct ingredient_code) as number_of_results,NULL AS category_description FROM ( SELECT a.ingredient_code FROM drg.maint_drug_main a inner join mstr.mstr_drug_main b on a.yj_code = b.yj_code where a.ingredient_code <> b.ingredient_code ) AS a1 union SELECT 'maint_drug_main' as table_description,'chemical_structure_ingredient_group_code' as field_name, 'テーブル「maint_drug_main」とテーブル「mstr_drug_main」のカラム「chemical_structure_ingredient_group_code」の値は違っています' as logic_description,count(chemical_structure_ingredient_group_code) as number_of_results,NULL AS category_description FROM ( SELECT a.chemical_structure_ingredient_group_code FROM drg.maint_drug_main a inner join mstr.mstr_drug_main b on a.yj_code = b.yj_code where a.chemical_structure_ingredient_group_code <> b.chemical_structure_ingredient_group_code ) AS a1 union SELECT 'maint_drug_main' as table_description,'dosage_form_code' as field_name, 'テーブル「maint_drug_main」とテーブル「mstr_drug_main」のカラム「dosage_form_code」の値は違っています' as logic_description,count(dosage_form_code) as number_of_results,NULL AS category_description FROM ( SELECT a.dosage_form_code FROM drg.maint_drug_main a inner join mstr.mstr_drug_main b on a.yj_code = b.yj_code where a.dosage_form_code <> b.dosage_form_code ) AS a1 union SELECT 'maint_drug_main' as table_description,'sales_drug_name' as field_name, 'テーブル「maint_drug_main」とテーブル「mstr_drug_main」のカラム「sales_drug_name」の値は違っています' as logic_description,count(sales_drug_name) as number_of_results,NULL AS category_description FROM ( SELECT a.sales_drug_name FROM drg.maint_drug_main a inner join mstr.mstr_drug_main b on a.yj_code = b.yj_code where a.sales_drug_name <> b.sales_drug_name ) AS a1 union SELECT 'maint_drug_main' as table_description,'sales_drug_name_kana' as field_name, 'テーブル「maint_drug_main」とテーブル「mstr_drug_main」のカラム「sales_drug_name_kana」の値は違っています' as logic_description,count(sales_drug_name_kana) as number_of_results,NULL AS category_description FROM ( SELECT a.sales_drug_name_kana FROM drg.maint_drug_main a inner join mstr.mstr_drug_main b on a.yj_code = b.yj_code where a.sales_drug_name_kana <> b.sales_drug_name_kana ) AS a1 union SELECT 'maint_drug_main' as table_description,'sales_drug_name_eng' as field_name, 'テーブル「maint_drug_main」とテーブル「mstr_drug_main」のカラム「sales_drug_name_eng」の値は違っています' as logic_description,count(sales_drug_name_eng) as number_of_results,NULL AS category_description FROM ( SELECT a.sales_drug_name_eng FROM drg.maint_drug_main a inner join mstr.mstr_drug_main b on a.yj_code = b.yj_code where a.sales_drug_name_eng <> b.sales_drug_name_eng ) AS a1 union SELECT 'maint_drug_main' as table_description,'brand_name_1' as field_name, 'テーブル「maint_drug_main」とテーブル「mstr_drug_main」のカラム「brand_name_1」の値は違っています' as logic_description,count(brand_name_1) as number_of_results,NULL AS category_description FROM ( SELECT a.brand_name_1 FROM drg.maint_drug_main a inner join mstr.mstr_drug_main b on a.yj_code = b.yj_code where a.brand_name_1 <> b.brand_name_1 ) AS a1 union SELECT 'maint_drug_main' as table_description,'brand_name_1_kana' as field_name, 'テーブル「maint_drug_main」とテーブル「mstr_drug_main」のカラム「brand_name_1_kana」の値は違っています' as logic_description,count(brand_name_1_kana) as number_of_results,NULL AS category_description FROM ( SELECT a.brand_name_1_kana FROM drg.maint_drug_main a inner join mstr.mstr_drug_main b on a.yj_code = b.yj_code where a.brand_name_1_kana <> b.brand_name_1_kana ) AS a1 union SELECT 'maint_drug_main' as table_description,'brand_name_1_eng' as field_name, 'テーブル「maint_drug_main」とテーブル「mstr_drug_main」のカラム「brand_name_1_eng」の値は違っています' as logic_description,count(brand_name_1_eng) as number_of_results,NULL AS category_description FROM ( SELECT a.brand_name_1_eng FROM drg.maint_drug_main a inner join mstr.mstr_drug_main b on a.yj_code = b.yj_code where a.brand_name_1_eng <> b.brand_name_1_eng ) AS a1 union SELECT 'maint_drug_main' as table_description,'brand_name_2' as field_name, 'テーブル「maint_drug_main」とテーブル「mstr_drug_main」のカラム「brand_name_2」の値は違っています' as logic_description,count(brand_name_2) as number_of_results,NULL AS category_description FROM ( SELECT a.brand_name_2 FROM drg.maint_drug_main a inner join mstr.mstr_drug_main b on a.yj_code = b.yj_code where a.brand_name_2 <> b.brand_name_2 ) AS a1 union SELECT 'maint_drug_main' as table_description,'brand_name_2_kana' as field_name, 'テーブル「maint_drug_main」とテーブル「mstr_drug_main」のカラム「brand_name_2_kana」の値は違っています' as logic_description,count(brand_name_2_kana) as number_of_results,NULL AS category_description FROM ( SELECT a.brand_name_2_kana FROM drg.maint_drug_main a inner join mstr.mstr_drug_main b on a.yj_code = b.yj_code where a.brand_name_2_kana <> b.brand_name_2_kana ) AS a1 union SELECT 'maint_drug_main' as table_description,'brand_name_2_eng' as field_name, 'テーブル「maint_drug_main」とテーブル「mstr_drug_main」のカラム「brand_name_2_eng」の値は違っています' as logic_description,count(brand_name_2_eng) as number_of_results,NULL AS category_description FROM ( SELECT a.brand_name_2_eng FROM drg.maint_drug_main a inner join mstr.mstr_drug_main b on a.yj_code = b.yj_code where a.brand_name_2_eng <> b.brand_name_2_eng ) AS a1 union SELECT 'maint_drug_main' as table_description,'generic_name' as field_name, 'テーブル「maint_drug_main」とテーブル「mstr_drug_main」のカラム「generic_name」の値は違っています' as logic_description,count(generic_name) as number_of_results,NULL AS category_description FROM ( SELECT a.generic_name FROM drg.maint_drug_main a inner join mstr.mstr_drug_main b on a.yj_code = b.yj_code where a.generic_name <> b.generic_name ) AS a1 union SELECT 'maint_drug_main' as table_description,'generic_name_kana' as field_name, 'テーブル「maint_drug_main」とテーブル「mstr_drug_main」のカラム「generic_name_kana」の値は違っています' as logic_description,count(generic_name_kana) as number_of_results,NULL AS category_description FROM ( SELECT a.generic_name_kana FROM drg.maint_drug_main a inner join mstr.mstr_drug_main b on a.yj_code = b.yj_code where a.generic_name_kana <> b.generic_name_kana ) AS a1 union SELECT 'maint_drug_main' as table_description,'generic_name_eng' as field_name, 'テーブル「maint_drug_main」とテーブル「mstr_drug_main」のカラム「generic_name_eng」の値は違っています' as logic_description,count(generic_name_eng) as number_of_results,NULL AS category_description FROM ( SELECT a.generic_name_eng FROM drg.maint_drug_main a inner join mstr.mstr_drug_main b on a.yj_code = b.yj_code where a.generic_name_eng <> b.generic_name_eng ) AS a1 union SELECT 'maint_drug_main' as table_description,'standard_unit' as field_name, 'テーブル「maint_drug_main」とテーブル「mstr_drug_main」のカラム「standard_unit」の値は違っています' as logic_description,count(standard_unit) as number_of_results,NULL AS category_description FROM ( SELECT a.standard_unit FROM drg.maint_drug_main a inner join mstr.mstr_drug_main b on a.yj_code = b.yj_code where a.standard_unit <> b.standard_unit ) AS a1 union SELECT 'maint_drug_main' as table_description,'unit' as field_name, 'テーブル「maint_drug_main」とテーブル「mstr_drug_main」のカラム「unit」の値は違っています' as logic_description,count(unit) as number_of_results,NULL AS category_description FROM ( SELECT a.unit FROM drg.maint_drug_main a inner join mstr.mstr_drug_main b on a.yj_code = b.yj_code where a.unit <> b.unit ) AS a1 union SELECT 'maint_drug_main' as table_description,'original_generic_type' as field_name, 'テーブル「maint_drug_main」とテーブル「mstr_drug_main」のカラム「original_generic_type」の値は違っています' as logic_description,count(original_generic_type) as number_of_results,NULL AS category_description FROM ( SELECT a.original_generic_type FROM drg.maint_drug_main a inner join mstr.mstr_drug_main b on a.yj_code = b.yj_code where a.original_generic_type <> b.original_generic_type ) AS a1 union SELECT 'maint_drug_main' as table_description,'authorized_generic_flag' as field_name, 'テーブル「maint_drug_main」とテーブル「mstr_drug_main」のカラム「authorized_generic_flag」の値は違っています' as logic_description,count(authorized_generic_flag) as number_of_results,NULL AS category_description FROM ( SELECT a.authorized_generic_flag FROM drg.maint_drug_main a inner join mstr.mstr_drug_main b on a.yj_code = b.yj_code where a.authorized_generic_flag <> b.authorized_generic_flag ) AS a1 union SELECT 'maint_drug_main' as table_description,'biosimilar_flag' as field_name, 'テーブル「maint_drug_main」とテーブル「mstr_drug_main」のカラム「biosimilar_flag」の値は違っています' as logic_description,count(biosimilar_flag) as number_of_results,NULL AS category_description FROM ( SELECT a.biosimilar_flag FROM drg.maint_drug_main a inner join mstr.mstr_drug_main b on a.yj_code = b.yj_code where a.biosimilar_flag <> b.biosimilar_flag ) AS a1 union SELECT 'maint_drug_main' as table_description,'poisoning_flag' as field_name, 'テーブル「maint_drug_main」とテーブル「mstr_drug_main」のカラム「poisoning_flag」の値は違っています' as logic_description,count(poisoning_flag) as number_of_results,NULL AS category_description FROM ( SELECT a.poisoning_flag FROM drg.maint_drug_main a inner join mstr.mstr_drug_main b on a.yj_code = b.yj_code where a.poisoning_flag <> b.poisoning_flag ) AS a1 union SELECT 'maint_drug_main' as table_description,'powerful_flag' as field_name, 'テーブル「maint_drug_main」とテーブル「mstr_drug_main」のカラム「powerful_flag」の値は違っています' as logic_description,count(powerful_flag) as number_of_results,NULL AS category_description FROM ( SELECT a.powerful_flag FROM drg.maint_drug_main a inner join mstr.mstr_drug_main b on a.yj_code = b.yj_code where a.powerful_flag <> b.powerful_flag ) AS a1 union SELECT 'maint_drug_main' as table_description,'narcotic_flag' as field_name, 'テーブル「maint_drug_main」とテーブル「mstr_drug_main」のカラム「narcotic_flag」の値は違っています' as logic_description,count(narcotic_flag) as number_of_results,NULL AS category_description FROM ( SELECT a.narcotic_flag FROM drg.maint_drug_main a inner join mstr.mstr_drug_main b on a.yj_code = b.yj_code where a.narcotic_flag <> b.narcotic_flag ) AS a1 union SELECT 'maint_drug_main' as table_description,'psychotropic_type' as field_name, 'テーブル「maint_drug_main」とテーブル「mstr_drug_main」のカラム「psychotropic_type」の値は違っています' as logic_description,count(psychotropic_type) as number_of_results,NULL AS category_description FROM ( SELECT a.psychotropic_type FROM drg.maint_drug_main a inner join mstr.mstr_drug_main b on a.yj_code = b.yj_code where a.psychotropic_type <> b.psychotropic_type ) AS a1 union SELECT 'maint_drug_main' as table_description,'stimulant_ingredient_flag' as field_name, 'テーブル「maint_drug_main」とテーブル「mstr_drug_main」のカラム「stimulant_ingredient_flag」の値は違っています' as logic_description,count(stimulant_ingredient_flag) as number_of_results,NULL AS category_description FROM ( SELECT a.stimulant_ingredient_flag FROM drg.maint_drug_main a inner join mstr.mstr_drug_main b on a.yj_code = b.yj_code where a.stimulant_ingredient_flag <> b.stimulant_ingredient_flag ) AS a1 union SELECT 'maint_drug_main' as table_description,'additctive_flag' as field_name, 'テーブル「maint_drug_main」とテーブル「mstr_drug_main」のカラム「additctive_flag」の値は違っています' as logic_description,count(additctive_flag) as number_of_results,NULL AS category_description FROM ( SELECT a.additctive_flag FROM drg.maint_drug_main a inner join mstr.mstr_drug_main b on a.yj_code = b.yj_code where a.additctive_flag <> b.additctive_flag ) AS a1 union SELECT 'maint_drug_main' as table_description,'biological_product_type' as field_name, 'テーブル「maint_drug_main」とテーブル「mstr_drug_main」のカラム「biological_product_type」の値は違っています' as logic_description,count(biological_product_type) as number_of_results,NULL AS category_description FROM ( SELECT a.biological_product_type FROM drg.maint_drug_main a inner join mstr.mstr_drug_main b on a.yj_code = b.yj_code where a.biological_product_type <> b.biological_product_type ) AS a1 union SELECT 'maint_drug_main' as table_description,'highrisk_flag' as field_name, 'テーブル「maint_drug_main」とテーブル「mstr_drug_main」のカラム「highrisk_flag」の値は違っています' as logic_description,count(highrisk_flag) as number_of_results,NULL AS category_description FROM ( SELECT a.highrisk_flag FROM drg.maint_drug_main a inner join mstr.mstr_drug_main b on a.yj_code = b.yj_code where a.highrisk_flag <> b.highrisk_flag ) AS a1 union SELECT 'maint_drug_main' as table_description,'sales_start_date' as field_name, 'テーブル「maint_drug_main」とテーブル「mstr_drug_main」のカラム「sales_start_date」の値は違っています' as logic_description,count(sales_start_date) as number_of_results,NULL AS category_description FROM ( SELECT a.sales_start_date FROM drg.maint_drug_main a inner join mstr.mstr_drug_main b on a.yj_code = b.yj_code where a.sales_start_date <> b.sales_start_date ) AS a1 union SELECT 'maint_drug_main' as table_description,'identification_code' as field_name, 'テーブル「maint_drug_main」とテーブル「mstr_drug_main」のカラム「identification_code」の値は違っています' as logic_description,count(identification_code) as number_of_results,NULL AS category_description FROM ( SELECT a.identification_code FROM drg.maint_drug_main a inner join mstr.mstr_drug_main b on a.yj_code = b.yj_code where a.identification_code <> b.identification_code ) AS a1 union SELECT 'maint_drug_main' as table_description,'remark_1' as field_name, 'テーブル「maint_drug_main」とテーブル「mstr_drug_main」のカラム「remark_1」の値は違っています' as logic_description,count(remark_1) as number_of_results,NULL AS category_description FROM ( SELECT a.remark_1 FROM drg.maint_drug_main a inner join mstr.mstr_drug_main b on a.yj_code = b.yj_code where a.remark_1 <> b.remark_1 ) AS a1 union SELECT 'maint_drug_main' as table_description,'remark_2' as field_name, 'テーブル「maint_drug_main」とテーブル「mstr_drug_main」のカラム「remark_2」の値は違っています' as logic_description,count(remark_2) as number_of_results,NULL AS category_description FROM ( SELECT a.remark_2 FROM drg.maint_drug_main a inner join mstr.mstr_drug_main b on a.yj_code = b.yj_code where a.remark_2 <> b.remark_2 ) AS a1 union SELECT 'maint_drug_main' as table_description,'ss_mix2_flag' as field_name, 'テーブル「maint_drug_main」とテーブル「mstr_drug_main」のカラム「ss_mix2_flag」の値は違っています' as logic_description,count(ss_mix2_flag ) as number_of_results,NULL AS category_description FROM ( SELECT a.ss_mix2_flag FROM drg.maint_drug_main a inner join mstr.mstr_drug_main b on a.yj_code = b.yj_code where a.ss_mix2_flag <> b.ss_mix2_flag ) AS a1 ) select * from ( select * from mstr_drug_xref_who_atc UNION select * from mstr_drug_who_atc UNION select * from mstr_drug_xref_ephmra_atc UNION select * from mstr_drug_ephmra_atc UNION select * from mstr_drug_xref_efficacy_87 UNION select * from mstr_drug_efficacy_87 UNION select * from mstr_drug_main UNION select * from mstr_drug_xref_rece UNION select * from mstr_drug_rece UNION select * from mstr_drug_ingredient_conversion UNION select * from mstr_drug_ingredient UNION select * from maint_drug_main ) a order by a.table_description,a.field_name,a.category_description -- abolish_flag ?独判断 select a1.table_name_info, a1.abolish_flag, a1.mstr, a1.dwh, a1.mstr - a1.dwh as "mstr-dwh", a1.mstr_count, a1.dwh_count, a1.mstr_count-a1.dwh_count as "mstr_count-dwh_count" from ( select 'mstr_drug_ephmra_atc' as table_name_info, n1.abolish_flag, n1.abolish_flag_num as mstr, n2.abolish_flag_num as dwh, sum(n1.abolish_flag_num) over() as mstr_count, sum(n2.abolish_flag_num) over() as dwh_count from ( select abolish_flag,count(abolish_flag) as abolish_flag_num from mstr.mstr_drug_ephmra_atc group by abolish_flag) n1 left join ( select abolish_flag,count(abolish_flag) as abolish_flag_num from dwh.mstr_drug_ephmra_atc group by abolish_flag) n2 on n1.abolish_flag = n2.abolish_flag union all select 'mstr_drug_ingredient' as table_name_info, n1.abolish_flag, n1.abolish_flag_num as mstr, n2.abolish_flag_num as dwh, sum(n1.abolish_flag_num) over() as mstr_count, sum(n2.abolish_flag_num) over() as dwh_count from ( select abolish_flag,count(abolish_flag) as abolish_flag_num from mstr.mstr_drug_ingredient group by abolish_flag) n1 left join ( select abolish_flag,count(abolish_flag) as abolish_flag_num from dwh.mstr_drug_ingredient group by abolish_flag) n2 on n1.abolish_flag = n2.abolish_flag union all select 'mstr_drug_company' as table_name_info, n1.abolish_flag, n1.abolish_flag_num as mstr, n2.abolish_flag_num as dwh, sum(n1.abolish_flag_num) over() as mstr_count, sum(n2.abolish_flag_num) over() as dwh_count from ( select abolish_flag,count(abolish_flag) as abolish_flag_num from mstr.mstr_drug_company group by abolish_flag) n1 left join ( select abolish_flag,count(abolish_flag) as abolish_flag_num from dwh.mstr_drug_company group by abolish_flag) n2 on n1.abolish_flag = n2.abolish_flag union all select 'mstr_drug_efficacy_87' as table_name_info, n1.abolish_flag, n1.abolish_flag_num as mstr, n2.abolish_flag_num as dwh, sum(n1.abolish_flag_num) over() as mstr_count, sum(n2.abolish_flag_num) over() as dwh_count from ( select abolish_flag,count(abolish_flag) as abolish_flag_num from mstr.mstr_drug_efficacy_87 group by abolish_flag) n1 left join ( select abolish_flag,count(abolish_flag) as abolish_flag_num from dwh.mstr_drug_efficacy_87 group by abolish_flag) n2 on n1.abolish_flag = n2.abolish_flag union all select 'mstr_drug_rece' as table_name_info, n1.abolish_flag, n1.abolish_flag_num as mstr, n2.abolish_flag_num as dwh, sum(n1.abolish_flag_num) over() as mstr_count, sum(n2.abolish_flag_num) over() as dwh_count from ( select abolish_flag,count(abolish_flag) as abolish_flag_num from mstr.mstr_drug_rece group by abolish_flag) n1 left join ( select abolish_flag,count(abolish_flag) as abolish_flag_num from dwh.mstr_drug_rece group by abolish_flag) n2 on n1.abolish_flag = n2.abolish_flag union all select 'mstr_drug_xref_rece' as table_name_info, n1.abolish_flag, n1.abolish_flag_num as mstr, n2.abolish_flag_num as dwh, sum(n1.abolish_flag_num) over() as mstr_count, sum(n2.abolish_flag_num) over() as dwh_count from ( select abolish_flag,count(abolish_flag) as abolish_flag_num from mstr.mstr_drug_xref_rece group by abolish_flag) n1 left join ( select abolish_flag,count(abolish_flag) as abolish_flag_num from dwh.mstr_drug_xref_rece group by abolish_flag) n2 on n1.abolish_flag = n2.abolish_flag union all select 'mstr_drug_main' as table_name_info, n1.abolish_flag, n1.abolish_flag_num as mstr, n2.abolish_flag_num as dwh, sum(n1.abolish_flag_num) over() as mstr_count, sum(n2.abolish_flag_num) over() as dwh_count from ( select abolish_flag,count(abolish_flag) as abolish_flag_num from mstr.mstr_drug_main group by abolish_flag) n1 left join ( select abolish_flag,count(abolish_flag) as abolish_flag_num from dwh.mstr_drug_main group by abolish_flag) n2 on n1.abolish_flag = n2.abolish_flag union all select 'mstr_drug_ingredient_conversion' as table_name_info, n1.abolish_flag, n1.abolish_flag_num as mstr, n2.abolish_flag_num as dwh, sum(n1.abolish_flag_num) over() as mstr_count, sum(n2.abolish_flag_num) over() as dwh_count from ( select abolish_flag,count(abolish_flag) as abolish_flag_num from mstr.mstr_drug_ingredient_conversion group by abolish_flag) n1 left join ( select abolish_flag,count(abolish_flag) as abolish_flag_num from dwh.mstr_drug_ingredient_conversion group by abolish_flag) n2 on n1.abolish_flag = n2.abolish_flag union all select 'mstr_drug_dest' as table_name_info, n1.abolish_flag, n1.abolish_flag_num as mstr, n2.abolish_flag_num as dwh, sum(n1.abolish_flag_num) over() as mstr_count, sum(n2.abolish_flag_num) over() as dwh_count from ( select abolish_flag,count(abolish_flag) as abolish_flag_num from mstr.mstr_drug_dest group by abolish_flag) n1 left join ( select abolish_flag,count(abolish_flag) as abolish_flag_num from dwh.mstr_drug_dest group by abolish_flag) n2 on n1.abolish_flag = n2.abolish_flag union all select 'mstr_drug_who_atc' as table_name_info, n1.abolish_flag, n1.abolish_flag_num as mstr, n2.abolish_flag_num as dwh, sum(n1.abolish_flag_num) over() as mstr_count, sum(n2.abolish_flag_num) over() as dwh_count from ( select abolish_flag,count(abolish_flag) as abolish_flag_num from mstr.mstr_drug_who_atc group by abolish_flag) n1 left join ( select abolish_flag,count(abolish_flag) as abolish_flag_num from dwh.mstr_drug_who_atc group by abolish_flag) n2 on n1.abolish_flag = n2.abolish_flag union all select 'mstr_drug_xref_company' as table_name_info, n1.abolish_flag, n1.abolish_flag_num as mstr, n2.abolish_flag_num as dwh, sum(n1.abolish_flag_num) over() as mstr_count, sum(n2.abolish_flag_num) over() as dwh_count from ( select abolish_flag,count(abolish_flag) as abolish_flag_num from mstr.mstr_drug_xref_company group by abolish_flag) n1 left join ( select abolish_flag,count(abolish_flag) as abolish_flag_num from dwh.mstr_drug_xref_company group by abolish_flag) n2 on n1.abolish_flag = n2.abolish_flag union all select 'mstr_drug_xref_disease' as table_name_info, n1.abolish_flag, n1.abolish_flag_num as mstr, n2.abolish_flag_num as dwh, sum(n1.abolish_flag_num) over() as mstr_count, sum(n2.abolish_flag_num) over() as dwh_count from ( select abolish_flag,count(abolish_flag) as abolish_flag_num from mstr.mstr_drug_xref_disease group by abolish_flag) n1 left join ( select abolish_flag,count(abolish_flag) as abolish_flag_num from dwh.mstr_drug_xref_disease group by abolish_flag) n2 on n1.abolish_flag = n2.abolish_flag union all select 'mstr_drug_xref_efficacy_87' as table_name_info, n1.abolish_flag, n1.abolish_flag_num as mstr, n2.abolish_flag_num as dwh, sum(n1.abolish_flag_num) over() as mstr_count, sum(n2.abolish_flag_num) over() as dwh_count from ( select abolish_flag,count(abolish_flag) as abolish_flag_num from mstr.mstr_drug_xref_efficacy_87 group by abolish_flag) n1 left join ( select abolish_flag,count(abolish_flag) as abolish_flag_num from dwh.mstr_drug_xref_efficacy_87 group by abolish_flag) n2 on n1.abolish_flag = n2.abolish_flag union all select 'mstr_drug_xref_ephmra_atc' as table_name_info, n1.abolish_flag, n1.abolish_flag_num as mstr, n2.abolish_flag_num as dwh, sum(n1.abolish_flag_num) over() as mstr_count, sum(n2.abolish_flag_num) over() as dwh_count from ( select abolish_flag,count(abolish_flag) as abolish_flag_num from mstr.mstr_drug_xref_ephmra_atc group by abolish_flag) n1 left join ( select abolish_flag,count(abolish_flag) as abolish_flag_num from dwh.mstr_drug_xref_ephmra_atc group by abolish_flag) n2 on n1.abolish_flag = n2.abolish_flag union all select 'mstr_drug_xref_who_atc' as table_name_info, n1.abolish_flag, n1.abolish_flag_num as mstr, n2.abolish_flag_num as dwh, sum(n1.abolish_flag_num) over() as mstr_count, sum(n2.abolish_flag_num) over() as dwh_count from ( select abolish_flag,count(abolish_flag) as abolish_flag_num from mstr.mstr_drug_xref_who_atc group by abolish_flag) n1 left join ( select abolish_flag,count(abolish_flag) as abolish_flag_num from dwh.mstr_drug_xref_who_atc group by abolish_flag) n2 on n1.abolish_flag = n2.abolish_flag union all select 'mstr_drug_original_xref_generic' as table_name_info, n1.abolish_flag, n1.abolish_flag_num as mstr, n2.abolish_flag_num as dwh, sum(n1.abolish_flag_num) over() as mstr_count, sum(n2.abolish_flag_num) over() as dwh_count from ( select abolish_flag,count(abolish_flag) as abolish_flag_num from mstr.mstr_drug_original_xref_generic group by abolish_flag) n1 left join ( select abolish_flag,count(abolish_flag) as abolish_flag_num from dwh.mstr_drug_original_xref_generic group by abolish_flag) n2 on n1.abolish_flag = n2.abolish_flag ) a1 --到这里就结束 了 下面是一些小查询验证 -- lianxi select count(*) from mstr.mstr_drug_who_atc select source_version,count(source_version) as source_version_count from drg.repos_drug_xref_who_atc group by source_version order by source_version desc select * from drg.repos_drug_who_atc select source_version,count(source_version) as source_version_count from drg.repos_drug_who_atc_1st group by source_version order by source_version desc with w1 as ( select distinct who_atc_code, source_version, to_char(source_version::date - interval '1 month','YYYYMMDD') as source_version_1 from drg.repos_drug_xref_who_atc where source_version>'20220228' ) select w1.*, rank() OVER (PARTITION BY w1.who_atc_code ORDER BY w1.source_version_1 desc) as rnk from w1 where left(w1.source_version_1,6)<='202205' select x.*, r.source_version FROM drg.maint_drug_xref_rece x inner join drg.repos_drug_rece r on r.rece_drug_code_1 = x.rece_drug_code_1 and left(r.yakka_code,9)=left(x.yj_code,9) left join drg.repos_drug_rece r2 on r2.rece_drug_code_1 = x.rece_drug_code_1 and left(r2.yakka_code,9)=left(x.yj_code,9) and left(r2.source_version,6)<='202205' left join drg.repos_drug_xref_rece x2 on x.yj_code=x2.yj_code and x.rece_drug_code_1=x2.rece_drug_code_1 and left(r.source_version,6)=left(x2.source_version,6) where r2.rece_drug_code_1 is null and x2.yj_code is null select * from mstr.mstr_drug_main where yj_code = '1143001X1147' limit 10 select yj_code,rece_drug_code_1 from ( select distinct x.yj_code, r.rece_drug_code_1, r.rece_drug_code_2, r.price from drg.repos_drug_rece r inner JOIN drg.repos_drug_xref_rece x ON r.rece_drug_code_1 = x.rece_drug_code_1 AND substring(r.yakka_code, 1, 9) = substring(x.yj_code, 1, 9) AND left(r.source_version,6) = left(x.source_version,6) WHERE left(r.source_version,6) = '202205' -- 20220615 -- and r.rece_drug_code_1 not in ('630011001','630011002') and r.rece_drug_code_1 not in (select rece_drug_code_1 from drg.maint_drug_xref_except where left(remark,1)='9' ) ) a where yj_code in('1143001X1147','6123402A3217','7123701X1076') select rece_drug_code_1 from drg.maint_drug_xref_except order by rece_drug_code_1 where left(remark,1)='9' yj_code rece_drug_code_1 1143001X1147 611140017 1143001X1147 620072715 6123402A3217 620003462 6123402A3217 621070301 7123701X1076 621171102 7123701X1076 667120016 select * from drg.maint_drug_xref_except where rece_drug_code_1 in ('611140017','620072715','620003462','621070301','621171102','667120016') select aaa,count(aaa) from ( select efficacy_87_code as aaa , efficacy_87_name , efficacy_87_1st_code , efficacy_87_2nd_code , efficacy_87_3rd_code , efficacy_87_4th_code , efficacy_87_1st_name , efficacy_87_2nd_name , efficacy_87_3rd_name , efficacy_87_4th_name from mstr.mstr_drug_efficacy_87 where left(source_version,6) = '202205' ) a group by aaa having count(aaa)>1 select yj_code,count(yj_code) from ( select distinct x.yj_code, r.rece_drug_code_1, r.rece_drug_code_2, r.price, r.update_type from drg.repos_drug_rece r inner JOIN drg.repos_drug_xref_rece x ON r.rece_drug_code_1 = x.rece_drug_code_1 AND substring(r.yakka_code, 1, 9) = substring(x.yj_code, 1, 9) AND left(r.source_version,6) = left(x.source_version,6) WHERE left(r.source_version,6) = '202205' -- 20220615 -- and r.rece_drug_code_1 not in ('630011001','630011002') and r.rece_drug_code_1 not in (select rece_drug_code_1 from drg.maint_drug_xref_except where left(remark,1)='9' ) ) a group by yj_code having count(yj_code)>1 select * from drg.repos_drug_rece r limit 10 select * from drg.repos_drug_xref_rece x where x.rece_drug_code_1 in ('611140017','620072715') and x.yj_code = '1143001X1147' and left(x.source_version,6) = '202205' select * from drg.repos_drug_rece r where r.rece_drug_code_1 in ('611140017','620072715') and substring(r.yakka_code, 1, 9) = substring('1143001X1147', 1, 9) and left(r.source_version,6) = '202205' select r.yakka_code,count(r.rece_drug_code_1) from drg.repos_drug_rece r where left(r.source_version,6) = '202205' group by r.yakka_code having count(r.rece_drug_code_1) >1 select * from ( select x.yj_code,x.rece_drug_code_1,r.update_type,r.source_version,count(x.rece_drug_code_1) over(partition by x.yj_code) rece_drug_code_count from drg.repos_drug_xref_rece x inner join drg.repos_drug_rece r ON r.rece_drug_code_1 = x.rece_drug_code_1 AND substring(r.yakka_code, 1, 9) = substring(x.yj_code, 1, 9) AND left(r.source_version,6) = left(x.source_version,6) where left(x.source_version,6) = '202205' and x.rece_drug_code_1 not in (select rece_drug_code_1 from drg.maint_drug_xref_except where left(remark,1)='9' ) ) a where a.rece_drug_code_count >1 and a.update_type yj_code rece_drug_code_1 rece_drug_code_2 price rnk 1143001X1147 620072715 0 3.56 1 select rec1.* from ( select x.yj_code, r.rece_drug_code_1, r.rece_drug_code_2, r.price, -- 20220707 rank() OVER (PARTITION BY x.yj_code ORDER BY r.source_version desc,r.abolish_date desc) as rnk from drg.repos_drug_rece r inner JOIN drg.repos_drug_xref_rece x ON r.rece_drug_code_1 = x.rece_drug_code_1 AND substring(r.yakka_code, 1, 9) = substring(x.yj_code, 1, 9) AND left(r.source_version,6) = left(x.source_version,6) WHERE left(r.source_version,6) = '202205' -- 20220615 -- and r.rece_drug_code_1 not in ('630011001','630011002') and x.rece_drug_code_1 in ('611140017','620072715') and x.yj_code = '1143001X1147' and r.rece_drug_code_1 not in (select rece_drug_code_1 from drg.maint_drug_xref_except where left(remark,1)='9' ) ) rec1 where rec1.rnk=1 select abolish_flag,count(abolish_flag) from mstr.mstr_drug_xref_rece group by abolish_flag where yj_code in ('7123701X1076','6123402A3217','1143001X1147') select count(*) from customer_desc.mstr_drug_efficacy_87 690 select count(*) from customer_desc.mstr_drug_ephmra_atc 900 select count(*) from customer_desc.mstr_drug_who_atc 5597 select count(*) from customer_desc.mstr_drug_ingredient 2925 select count(*) from customer_desc.mstr_drug_main 30485 select count(*) from customer_desc.mstr_drug_xref_efficacy_87 32341 select count(*) from customer_desc.mstr_drug_xref_ephmra_atc 30416 select count(*) from customer_desc.mstr_drug_xref_who_atc 30622 select count(*) from customer_desc.mstr_drug_xref_rece 30759 select count(*) from customer_desc.mstr_drug_xref_disease 4272021 select count(*) from customer_desc.mstr_drug_ingredient_conversion 30539 select count(*) from customer_desc.mstr_drug_rece 177173 select count(*) from customer_desc.mstr_drug_rece_dest 3831 select * from drg.repos_drug_xref_rece where yj_code = '2325002F2190' and rece_drug_code_1 = '622243201' order by source_version desc limit 10 select count(*) from ( SELECT distinct x.yj_code , r.rece_drug_code_1 , to_char(date_trunc('month',to_date('202205','YYYYMM')) + '1 month' + '-1 days','YYYYMMDD') as source_version , CURRENT_USER , now() , CURRENT_USER , now() FROM drg.repos_drug_rece r INNER JOIN drg.maint_drug_xref_rece x ON r.rece_drug_code_1 = x.rece_drug_code_1 AND substring(r.yakka_code, 1, 9) = substring(x.yj_code, 1, 9) WHERE left(r.source_version,6) = '202205' and -- 20220703 x.yj_code||x.rece_drug_code_1 not in (select yj_code||rece_drug_code_1 from drg.maint_drug_xref_except where left(remark,1)='1') ) a 21990- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
- 114
- 115
- 116
- 117
- 118
- 119
- 120
- 121
- 122
- 123
- 124
- 125
- 126
- 127
- 128
- 129
- 130
- 131
- 132
- 133
- 134
- 135
- 136
- 137
- 138
- 139
- 140
- 141
- 142
- 143
- 144
- 145
- 146
- 147
- 148
- 149
- 150
- 151
- 152
- 153
- 154
- 155
- 156
- 157
- 158
- 159
- 160
- 161
- 162
- 163
- 164
- 165
- 166
- 167
- 168
- 169
- 170
- 171
- 172
- 173
- 174
- 175
- 176
- 177
- 178
- 179
- 180
- 181
- 182
- 183
- 184
- 185
- 186
- 187
- 188
- 189
- 190
- 191
- 192
- 193
- 194
- 195
- 196
- 197
- 198
- 199
- 200
- 201
- 202
- 203
- 204
- 205
- 206
- 207
- 208
- 209
- 210
- 211
- 212
- 213
- 214
- 215
- 216
- 217
- 218
- 219
- 220
- 221
- 222
- 223
- 224
- 225
- 226
- 227
- 228
- 229
- 230
- 231
- 232
- 233
- 234
- 235
- 236
- 237
- 238
- 239
- 240
- 241
- 242
- 243
- 244
- 245
- 246
- 247
- 248
- 249
- 250
- 251
- 252
- 253
- 254
- 255
- 256
- 257
- 258
- 259
- 260
- 261
- 262
- 263
- 264
- 265
- 266
- 267
- 268
- 269
- 270
- 271
- 272
- 273
- 274
- 275
- 276
- 277
- 278
- 279
- 280
- 281
- 282
- 283
- 284
- 285
- 286
- 287
- 288
- 289
- 290
- 291
- 292
- 293
- 294
- 295
- 296
- 297
- 298
- 299
- 300
- 301
- 302
- 303
- 304
- 305
- 306
- 307
- 308
- 309
- 310
- 311
- 312
- 313
- 314
- 315
- 316
- 317
- 318
- 319
- 320
- 321
- 322
- 323
- 324
- 325
- 326
- 327
- 328
- 329
- 330
- 331
- 332
- 333
- 334
- 335
- 336
- 337
- 338
- 339
- 340
- 341
- 342
- 343
- 344
- 345
- 346
- 347
- 348
- 349
- 350
- 351
- 352
- 353
- 354
- 355
- 356
- 357
- 358
- 359
- 360
- 361
- 362
- 363
- 364
- 365
- 366
- 367
- 368
- 369
- 370
- 371
- 372
- 373
- 374
- 375
- 376
- 377
- 378
- 379
- 380
- 381
- 382
- 383
- 384
- 385
- 386
- 387
- 388
- 389
- 390
- 391
- 392
- 393
- 394
- 395
- 396
- 397
- 398
- 399
- 400
- 401
- 402
- 403
- 404
- 405
- 406
- 407
- 408
- 409
- 410
- 411
- 412
- 413
- 414
- 415
- 416
- 417
- 418
- 419
- 420
- 421
- 422
- 423
- 424
- 425
- 426
- 427
- 428
- 429
- 430
- 431
- 432
- 433
- 434
- 435
- 436
- 437
- 438
- 439
- 440
- 441
- 442
- 443
- 444
- 445
- 446
- 447
- 448
- 449
- 450
- 451
- 452
- 453
- 454
- 455
- 456
- 457
- 458
- 459
- 460
- 461
- 462
- 463
- 464
- 465
- 466
- 467
- 468
- 469
- 470
- 471
- 472
- 473
- 474
- 475
- 476
- 477
- 478
- 479
- 480
- 481
- 482
- 483
- 484
- 485
- 486
- 487
- 488
- 489
- 490
- 491
- 492
- 493
- 494
- 495
- 496
- 497
- 498
- 499
- 500
- 501
- 502
- 503
- 504
- 505
- 506
- 507
- 508
- 509
- 510
- 511
- 512
- 513
- 514
- 515
- 516
- 517
- 518
- 519
- 520
- 521
- 522
- 523
- 524
- 525
- 526
- 527
- 528
- 529
- 530
- 531
- 532
- 533
- 534
- 535
- 536
- 537
- 538
- 539
- 540
- 541
- 542
- 543
- 544
- 545
- 546
- 547
- 548
- 549
- 550
- 551
- 552
- 553
- 554
- 555
- 556
- 557
- 558
- 559
- 560
- 561
- 562
- 563
- 564
- 565
- 566
- 567
- 568
- 569
- 570
- 571
- 572
- 573
- 574
- 575
- 576
- 577
- 578
- 579
- 580
- 581
- 582
- 583
- 584
- 585
- 586
- 587
- 588
- 589
- 590
- 591
- 592
- 593
- 594
- 595
- 596
- 597
- 598
- 599
- 600
- 601
- 602
- 603
- 604
- 605
- 606
- 607
- 608
- 609
- 610
- 611
- 612
- 613
- 614
- 615
- 616
- 617
- 618
- 619
- 620
- 621
- 622
- 623
- 624
- 625
- 626
- 627
- 628
- 629
- 630
- 631
- 632
- 633
- 634
- 635
- 636
- 637
- 638
- 639
- 640
- 641
- 642
- 643
- 644
- 645
- 646
- 647
- 648
- 649
- 650
- 651
- 652
- 653
- 654
- 655
- 656
- 657
- 658
- 659
- 660
- 661
- 662
- 663
- 664
- 665
- 666
- 667
- 668
- 669
- 670
- 671
- 672
- 673
- 674
- 675
- 676
- 677
- 678
- 679
- 680
- 681
- 682
- 683
- 684
- 685
- 686
- 687
- 688
- 689
- 690
- 691
- 692
- 693
- 694
- 695
- 696
- 697
- 698
- 699
- 700
- 701
- 702
- 703
- 704
- 705
- 706
- 707
- 708
- 709
- 710
- 711
- 712
- 713
- 714
- 715
- 716
- 717
- 718
- 719
- 720
- 721
- 722
- 723
- 724
- 725
- 726
- 727
- 728
- 729
- 730
- 731
- 732
- 733
- 734
- 735
- 736
- 737
- 738
- 739
- 740
- 741
- 742
- 743
- 744
- 745
- 746
- 747
- 748
- 749
- 750
- 751
- 752
- 753
- 754
- 755
- 756
- 757
- 758
- 759
- 760
- 761
- 762
- 763
- 764
- 765
- 766
- 767
- 768
- 769
- 770
- 771
- 772
- 773
- 774
- 775
- 776
- 777
- 778
- 779
- 780
- 781
- 782
- 783
- 784
- 785
- 786
- 787
- 788
- 789
- 790
- 791
- 792
- 793
- 794
- 795
- 796
- 797
- 798
- 799
- 800
- 801
- 802
- 803
- 804
- 805
- 806
- 807
- 808
- 809
- 810
- 811
- 812
- 813
- 814
- 815
- 816
- 817
- 818
- 819
- 820
- 821
- 822
- 823
- 824
- 825
- 826
- 827
- 828
- 829
- 830
- 831
- 832
- 833
- 834
- 835
- 836
- 837
- 838
- 839
- 840
- 841
- 842
- 843
- 844
- 845
- 846
- 847
- 848
- 849
- 850
- 851
- 852
- 853
- 854
- 855
- 856
- 857
- 858
- 859
- 860
- 861
- 862
- 863
- 864
- 865
- 866
- 867
- 868
- 869
- 870
- 871
- 872
- 873
- 874
- 875
- 876
- 877
- 878
- 879
- 880
- 881
- 882
- 883
- 884
- 885
- 886
- 887
- 888
- 889
- 890
- 891
- 892
- 893
- 894
- 895
- 896
- 897
- 898
- 899
- 900
- 901
- 902
- 903
- 904
- 905
- 906
- 907
- 908
- 909
- 910
- 911
- 912
- 913
- 914
- 915
- 916
- 917
- 918
- 919
- 920
- 921
- 922
- 923
- 924
- 925
- 926
- 927
- 928
- 929
- 930
- 931
- 932
- 933
- 934
- 935
- 936
- 937
- 938
- 939
- 940
- 941
- 942
- 943
- 944
- 945
- 946
- 947
- 948
- 949
- 950
- 951
- 952
- 953
- 954
- 955
- 956
- 957
- 958
- 959
- 960
- 961
- 962
- 963
- 964
- 965
- 966
- 967
- 968
- 969
- 970
- 971
- 972
- 973
- 974
- 975
- 976
- 977
- 978
- 979
- 980
- 981
- 982
- 983
- 984
- 985
- 986
- 987
- 988
- 989
- 990
- 991
- 992
- 993
- 994
- 995
- 996
- 997
- 998
- 999
- 1000
- 1001
- 1002
- 1003
- 1004
- 1005
- 1006
- 1007
- 1008
- 1009
- 1010
- 1011
- 1012
- 1013
- 1014
- 1015
- 1016
- 1017
- 1018
- 1019
- 1020
- 1021
- 1022
- 1023
- 1024
- 1025
- 1026
- 1027
- 1028
- 1029
- 1030
- 1031
- 1032
- 1033
- 1034
- 1035
- 1036
- 1037
- 1038
- 1039
- 1040
- 1041
- 1042
- 1043
- 1044
- 1045
- 1046
- 1047
- 1048
- 1049
- 1050
- 1051
- 1052
- 1053
- 1054
- 1055
- 1056
- 1057
- 1058
- 1059
- 1060
- 1061
- 1062
- 1063
- 1064
- 1065
- 1066
- 1067
- 1068
- 1069
- 1070
- 1071
- 1072
- 1073
- 1074
- 1075
- 1076
- 1077
- 1078
- 1079
- 1080
- 1081
- 1082
- 1083
- 1084
- 1085
- 1086
- 1087
- 1088
- 1089
- 1090
- 1091
- 1092
- 1093
- 1094
- 1095
- 1096
- 1097
- 1098
- 1099
- 1100
- 1101
- 1102
- 1103
- 1104
- 1105
- 1106
- 1107
- 1108
- 1109
- 1110
- 1111
- 1112
- 1113
- 1114
- 1115
- 1116
- 1117
- 1118
- 1119
- 1120
- 1121
- 1122
- 1123
- 1124
- 1125
- 1126
- 1127
- 1128
- 1129
- 1130
- 1131
- 1132
- 1133
- 1134
- 1135
- 1136
- 1137
- 1138
- 1139
- 1140
- 1141
- 1142
- 1143
- 1144
- 1145
- 1146
- 1147
- 1148
- 1149
- 1150
- 1151
- 1152
- 1153
- 1154
- 1155
- 1156
- 1157
- 1158
- 1159
- 1160
- 1161
- 1162
- 1163
- 1164
- 1165
- 1166
- 1167
- 1168
- 1169
- 1170
- 1171
- 1172
- 1173
- 1174
- 1175
- 1176
- 1177
- 1178
- 1179
- 1180
- 1181
- 1182
- 1183
- 1184
- 1185
- 1186
- 1187
- 1188
- 1189
- 1190
- 1191
- 1192
- 1193
- 1194
- 1195
- 1196
- 1197
- 1198
- 1199
- 1200
- 1201
- 1202
- 1203
- 1204
- 1205
- 1206
- 1207
- 1208
- 1209
- 1210
- 1211
- 1212
- 1213
- 1214
- 1215
- 1216
- 1217
- 1218
- 1219
- 1220
- 1221
- 1222
- 1223
- 1224
- 1225
- 1226
- 1227
- 1228
- 1229
- 1230
- 1231
- 1232
- 1233
- 1234
- 1235
- 1236
- 1237
- 1238
- 1239
- 1240
- 1241
- 1242
- 1243
- 1244
- 1245
- 1246
- 1247
- 1248
- 1249
- 1250
- 1251
- 1252
- 1253
- 1254
- 1255
- 1256
- 1257
- 1258
- 1259
- 1260
- 1261
- 1262
- 1263
- 1264
- 1265
- 1266
- 1267
- 1268
- 1269
- 1270
- 1271
- 1272
- 1273
- 1274
- 1275
- 1276
- 1277
- 1278
- 1279
- 1280
- 1281
- 1282
- 1283
- 1284
- 1285
- 1286
- 1287
- 1288
- 1289
- 1290
- 1291
- 1292
- 1293
- 1294
- 1295
- 1296
- 1297
- 1298
- 1299
- 1300
- 1301
- 1302
- 1303
- 1304
- 1305
- 1306
- 1307
- 1308
- 1309
- 1310
- 1311
- 1312
- 1313
- 1314
- 1315
- 1316
- 1317
- 1318
- 1319
- 1320
- 1321
- 1322
- 1323
- 1324
- 1325
- 1326
- 1327
- 1328
- 1329
- 1330
- 1331
- 1332
- 1333
- 1334
- 1335
- 1336
- 1337
- 1338
- 1339
- 1340
- 1341
- 1342
- 1343
- 1344
- 1345
- 1346
- 1347
- 1348
- 1349
- 1350
- 1351
- 1352
- 1353
- 1354
- 1355
- 1356
- 1357
- 1358
- 1359
- 1360
- 1361
- 1362
- 1363
- 1364
- 1365
- 1366
- 1367
- 1368
- 1369
- 1370
- 1371
- 1372
- 1373
- 1374
- 1375
- 1376
- 1377
- 1378
- 1379
- 1380
- 1381
- 1382
- 1383
- 1384
- 1385
- 1386
- 1387
- 1388
- 1389
- 1390
- 1391
- 1392
- 1393
- 1394
- 1395
- 1396
- 1397
- 1398
- 1399
- 1400
- 1401
- 1402
- 1403
- 1404
- 1405
- 1406
- 1407
- 1408
- 1409
- 1410
- 1411
- 1412
- 1413
- 1414
- 1415
- 1416
- 1417
- 1418
- 1419
- 1420
- 1421
- 1422
- 1423
- 1424
- 1425
- 1426
- 1427
- 1428
- 1429
- 1430
- 1431
- 1432
- 1433
- 1434
- 1435
- 1436
- 1437
- 1438
- 1439
- 1440
- 1441
- 1442
- 1443
- 1444
- 1445
- 1446
- 1447
- 1448
- 1449
- 1450
- 1451
- 1452
- 1453
- 1454
- 1455
- 1456
- 1457
- 1458
- 1459
- 1460
- 1461
- 1462
- 1463
- 1464
- 1465
- 1466
- 1467
- 1468
- 1469
- 1470
需求四：在.bat文件中验证数据的正确性
123.bat和check2.bat是没问题的； check3.bat是有问题的
调用了函数 出现错误的时候先显示错误 之后暂停 按照任意键继续 然后退出程序 下面的check2.bat无法执行
chcp 65001 @rem 这是识别中文 也就是utf-8的意思 @echo off ::开启 call 123.bat call check3.bat if %errorlevel% == 0 ( echo 123.bat文件中的返回值%errorlevel% this is not a error )else ( echo this is a error pause exit ) call check2.bat- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
这是check2.bat文件
@REM @echo off @REM set /a jian=4/0 @REM echo %jian% @REM rem echo %p% @REM exit /b %errorlevel% @echo off set /a jian=4/1 echo %jian%- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
这是123.bat文件
@echo off @REM echo %errorlevel% set x=csdn echo %x%+%p% @REM exit /b 1- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
这是check3.bat
@echo off set /a jian=4/0 rem echo %p% echo %jian%- 1
- 2
- 3
- 4
-
相关阅读:
自定义实现简易版ArrayList
CV：阿里在CV数据增强领域带来SOTA新范式(已被NeurIPS2022接收)—基于离散化对抗训练的鲁棒视觉新基准！
C#WPF通知更改公共类使用实例
stm32编写Modbus步骤
大疆笔试题
Cesium 报错：TypeError: Cesium.MeasurementTool is not a constructor
4 个 Linux 技巧让工作效率翻倍
H5画布绘制笑脸
L10.linux命令每日一练 -- 第二章 文件和目录操作命令 -- find和xargs命令
git ------ IDEA中建立本地/远程仓库及上传
- 原文地址：https://blog.csdn.net/qq_41810415/article/details/125847281
