SELECT tab.id id, tab.秩序号 catalogue_num, tab.CC CC, tab.CC_Remarks CC_Remarks, tab.CACIB CACIB, tab.CACIB_Remarks CACIB_Remarks, tab.BOB BOB, tab.BIG BIG, tab.BOW BOW, tab.WB WB, tab.BIS BIS, tab.本场积分 current_credits, tab.犬主中文名 dog_owner_cn_name, tab.犬主英文名 dog_owner_eg_name, tab.会员号 member_num, tab.FCI代码 fci_code, tab.性别 gender, tab.年龄组别 age_group, tab.血统证书号 studbook_num, tab.其他证书号 other_certifi_num, tab.赛事编号 show_num, tab.裁判 judge, tab.赛圈 show_ring, tab.修改日期 modifi_data, tab.del_tag del_tag, tab.犬种组 breed_group, tab.print_cc print_cc, tab.Remarks Remarks, tab.operater operater, game.赛事名称 show_name, game.开始日期 starting_data, game.结束日期 ending_data, left(CONVERT(varchar(100), game.结束日期, 23),7) gradeMonth FROM tab_grade tab LEFT JOIN tab_games game ON tab.赛事编号 = game.赛事编号 WHERE 血统证书号 = #{pedigreeNum} and left(CONVERT(varchar(100), 结束日期, 23),7) = #{charTime} SELECT left(CONVERT(varchar(100), game.结束日期, 23),7) gradeMonth FROM tab_grade tab LEFT JOIN tab_games game ON tab.赛事编号 = game.赛事编号 WHERE 血统证书号 = #{pedigreeNum} order by tab.id desc SELECT '全犬种' showSystem, 秩序号 catalogue_num, CC CC, 年龄组别 age_group, CACIB CACIB, 本场积分 current_credits, BIS, 裁判 judge, tab_games.赛事名称 show_name, tab_games.开始日期 starting_data, tab_games.结束日期 ending_data FROM tab_grade left join tab_games on tab_grade.赛事编号 = tab_games.赛事编号 WHERE tab_grade.赛事编号 IN (${showNumsQqz}) AND 血统证书号 = #{studbookNum} UNION ALL SELECT '单独展' showSystem, serialNumber catalogue_num, CC CC, ageClass age_group, NULL CACIB, NULL current_credits, NULL BIS, judges judge, tab_games.赛事名称 show_name, tab_games.开始日期 starting_data, tab_games.结束日期 ending_data FROM tab_grade_ddz left join tab_games on tab_grade_ddz.showsId = tab_games.赛事编号 WHERE tab_grade_ddz.showsId IN (${showNumsDdz}) AND breedId = #{studbookNum} select count(CASE WHEN tab.CC='CC' THEN 'CC' ELSE null END) CC, count(CASE WHEN tab.JCC='JCC' THEN 'JCC' ELSE null END) JCC, count(CASE WHEN tab.RCC='RCC' THEN 'RCC' ELSE null END) RCC, count(CASE WHEN tab.BOB='BOB' THEN 'BOB' ELSE null END) BOB, count(CASE WHEN tab.BOS='BOS' THEN 'BOS' ELSE null END) BOS, count(CASE WHEN tab.BOW='BOW' THEN 'BOW' ELSE null END) BOW, count(CASE WHEN tab.BIG='BIG' THEN 'BIG' ELSE null END) BIG, count(CASE WHEN tab.BIS='BIS' THEN 'BIS' ELSE null END) BIS, count(CASE WHEN tab.CACIB='CACIB' THEN 'CACIB' ELSE null END) CACIB from ( select 'CC' = max(CASE WHEN CC='CC' THEN 'CC' ELSE null END), 'JCC' = max(CASE WHEN CC='JCC' THEN 'JCC' ELSE null END), 'RCC' = max(CASE WHEN CC='RCC' THEN 'RCC' ELSE null END), 'BOB' = max(CASE WHEN BOB='BOB' THEN 'BOB' ELSE null END), 'BOS' = max(CASE WHEN BOB='BOS' THEN 'BOS' ELSE null END), 'BOW' = max(CASE WHEN BOW='BOW' THEN 'BOW' ELSE null END), 'BIG' = max(CASE WHEN BIG='BIG' THEN 'BIG' ELSE null END), 'BIS' = max(CASE WHEN BIS='BIS' THEN 'BIS' ELSE null END), 'CACIB' = max(CASE WHEN CACIB='CACIB' THEN 'CACIB' ELSE null END), 裁判 FROM tab_grade where 血统证书号 = #{studbookNum} group by 裁判) tab id, catalogue_num, CC, CC_Remarks, CACIB, CACIB_Remarks, BOB, BIG, BOW, WB, BIS, current_credits, dog_owner_cn_name, dog_owner_eg_name, member_num, fci_code, gender, age_group, studbook_num, other_certifi_num, show_num, judge, show_ring, modifi_data, del_tag, breed_group, print_cc Remarks, operater select , from tab_grade where id = #{id,jdbcType=INTEGER} delete from tab_grade where id = #{id,jdbcType=INTEGER} insert into tab_grade (id, catalogue_num, CC, CC_Remarks, CACIB, CACIB_Remarks, BOB, BIG, BOW, WB, BIS, current_credits, dog_owner_cn_name, dog_owner_eg_name, member_num, fci_code, gender, age_group, studbook_num, other_certifi_num, show_num, judge, show_ring, modifi_data, del_tag, breed_group, print_cc, Remarks, operater) values (#{id,jdbcType=INTEGER}, #{catalogueNum,jdbcType=VARCHAR}, #{cc,jdbcType=CHAR}, #{ccRemarks,jdbcType=VARCHAR}, #{cacib,jdbcType=CHAR}, #{cacibRemarks,jdbcType=VARCHAR}, #{bob,jdbcType=CHAR}, #{big,jdbcType=CHAR}, #{bow,jdbcType=CHAR}, #{wb,jdbcType=CHAR}, #{bis,jdbcType=CHAR}, #{currentCredits,jdbcType=INTEGER}, #{dogOwnerCnName,jdbcType=VARCHAR}, #{dogOwnerEgName,jdbcType=VARCHAR}, #{memberNum,jdbcType=VARCHAR}, #{fciCode,jdbcType=VARCHAR}, #{gender,jdbcType=VARCHAR}, #{ageGroup,jdbcType=INTEGER}, #{studbookNum,jdbcType=VARCHAR}, #{otherCertifiNum,jdbcType=VARCHAR}, #{showNum,jdbcType=VARCHAR}, #{judge,jdbcType=VARCHAR}, #{showRing,jdbcType=VARCHAR}, #{modifiData,jdbcType=TIMESTAMP}, #{delTag,jdbcType=CHAR}, #{breedGroup,jdbcType=VARCHAR}, #{printCc,jdbcType=CHAR}, #{remarks,jdbcType=LONGVARCHAR}, #{operater,jdbcType=LONGVARCHAR}) insert into tab_grade id, catalogue_num, CC, CC_Remarks, CACIB, CACIB_Remarks, BOB, BIG, BOW, WB, BIS, current_credits, dog_owner_cn_name, dog_owner_eg_name, member_num, fci_code, gender, age_group, studbook_num, other_certifi_num, show_num, judge, show_ring, modifi_data, del_tag, breed_group, print_cc, Remarks, operater, #{id,jdbcType=INTEGER}, #{catalogueNum,jdbcType=VARCHAR}, #{cc,jdbcType=CHAR}, #{ccRemarks,jdbcType=VARCHAR}, #{cacib,jdbcType=CHAR}, #{cacibRemarks,jdbcType=VARCHAR}, #{bob,jdbcType=CHAR}, #{big,jdbcType=CHAR}, #{bow,jdbcType=CHAR}, #{wb,jdbcType=CHAR}, #{bis,jdbcType=CHAR}, #{currentCredits,jdbcType=INTEGER}, #{dogOwnerCnName,jdbcType=VARCHAR}, #{dogOwnerEgName,jdbcType=VARCHAR}, #{memberNum,jdbcType=VARCHAR}, #{fciCode,jdbcType=VARCHAR}, #{gender,jdbcType=VARCHAR}, #{ageGroup,jdbcType=INTEGER}, #{studbookNum,jdbcType=VARCHAR}, #{otherCertifiNum,jdbcType=VARCHAR}, #{showNum,jdbcType=VARCHAR}, #{judge,jdbcType=VARCHAR}, #{showRing,jdbcType=VARCHAR}, #{modifiData,jdbcType=TIMESTAMP}, #{delTag,jdbcType=CHAR}, #{breedGroup,jdbcType=VARCHAR}, #{printCc,jdbcType=CHAR}, #{remarks,jdbcType=LONGVARCHAR}, #{operater,jdbcType=LONGVARCHAR}, update tab_grade catalogue_num = #{catalogueNum,jdbcType=VARCHAR}, CC = #{cc,jdbcType=CHAR}, CC_Remarks = #{ccRemarks,jdbcType=VARCHAR}, CACIB = #{cacib,jdbcType=CHAR}, CACIB_Remarks = #{cacibRemarks,jdbcType=VARCHAR}, BOB = #{bob,jdbcType=CHAR}, BIG = #{big,jdbcType=CHAR}, BOW = #{bow,jdbcType=CHAR}, WB = #{wb,jdbcType=CHAR}, BIS = #{bis,jdbcType=CHAR}, current_credits = #{currentCredits,jdbcType=INTEGER}, dog_owner_cn_name = #{dogOwnerCnName,jdbcType=VARCHAR}, dog_owner_eg_name = #{dogOwnerEgName,jdbcType=VARCHAR}, member_num = #{memberNum,jdbcType=VARCHAR}, fci_code = #{fciCode,jdbcType=VARCHAR}, gender = #{gender,jdbcType=VARCHAR}, age_group = #{ageGroup,jdbcType=INTEGER}, studbook_num = #{studbookNum,jdbcType=VARCHAR}, other_certifi_num = #{otherCertifiNum,jdbcType=VARCHAR}, show_num = #{showNum,jdbcType=VARCHAR}, judge = #{judge,jdbcType=VARCHAR}, show_ring = #{showRing,jdbcType=VARCHAR}, modifi_data = #{modifiData,jdbcType=TIMESTAMP}, del_tag = #{delTag,jdbcType=CHAR}, breed_group = #{breedGroup,jdbcType=VARCHAR}, print_cc = #{printCc,jdbcType=CHAR}, Remarks = #{remarks,jdbcType=LONGVARCHAR}, operater = #{operater,jdbcType=LONGVARCHAR}, where id = #{id,jdbcType=INTEGER} update tab_grade set catalogue_num = #{catalogueNum,jdbcType=VARCHAR}, CC = #{cc,jdbcType=CHAR}, CC_Remarks = #{ccRemarks,jdbcType=VARCHAR}, CACIB = #{cacib,jdbcType=CHAR}, CACIB_Remarks = #{cacibRemarks,jdbcType=VARCHAR}, BOB = #{bob,jdbcType=CHAR}, BIG = #{big,jdbcType=CHAR}, BOW = #{bow,jdbcType=CHAR}, WB = #{wb,jdbcType=CHAR}, BIS = #{bis,jdbcType=CHAR}, current_credits = #{currentCredits,jdbcType=INTEGER}, dog_owner_cn_name = #{dogOwnerCnName,jdbcType=VARCHAR}, dog_owner_eg_name = #{dogOwnerEgName,jdbcType=VARCHAR}, member_num = #{memberNum,jdbcType=VARCHAR}, fci_code = #{fciCode,jdbcType=VARCHAR}, gender = #{gender,jdbcType=VARCHAR}, age_group = #{ageGroup,jdbcType=INTEGER}, studbook_num = #{studbookNum,jdbcType=VARCHAR}, other_certifi_num = #{otherCertifiNum,jdbcType=VARCHAR}, show_num = #{showNum,jdbcType=VARCHAR}, judge = #{judge,jdbcType=VARCHAR}, show_ring = #{showRing,jdbcType=VARCHAR}, modifi_data = #{modifiData,jdbcType=TIMESTAMP}, del_tag = #{delTag,jdbcType=CHAR}, breed_group = #{breedGroup,jdbcType=VARCHAR}, print_cc = #{printCc,jdbcType=CHAR}, Remarks = #{remarks,jdbcType=LONGVARCHAR}, operater = #{operater,jdbcType=LONGVARCHAR} where id = #{id,jdbcType=INTEGER} update tab_grade set catalogue_num = #{catalogueNum,jdbcType=VARCHAR}, CC = #{cc,jdbcType=CHAR}, CC_Remarks = #{ccRemarks,jdbcType=VARCHAR}, CACIB = #{cacib,jdbcType=CHAR}, CACIB_Remarks = #{cacibRemarks,jdbcType=VARCHAR}, BOB = #{bob,jdbcType=CHAR}, BIG = #{big,jdbcType=CHAR}, BOW = #{bow,jdbcType=CHAR}, WB = #{wb,jdbcType=CHAR}, BIS = #{bis,jdbcType=CHAR}, current_credits = #{currentCredits,jdbcType=INTEGER}, dog_owner_cn_name = #{dogOwnerCnName,jdbcType=VARCHAR}, dog_owner_eg_name = #{dogOwnerEgName,jdbcType=VARCHAR}, member_num = #{memberNum,jdbcType=VARCHAR}, fci_code = #{fciCode,jdbcType=VARCHAR}, gender = #{gender,jdbcType=VARCHAR}, age_group = #{ageGroup,jdbcType=INTEGER}, studbook_num = #{studbookNum,jdbcType=VARCHAR}, other_certifi_num = #{otherCertifiNum,jdbcType=VARCHAR}, show_num = #{showNum,jdbcType=VARCHAR}, judge = #{judge,jdbcType=VARCHAR}, show_ring = #{showRing,jdbcType=VARCHAR}, modifi_data = #{modifiData,jdbcType=TIMESTAMP}, del_tag = #{delTag,jdbcType=CHAR}, breed_group = #{breedGroup,jdbcType=VARCHAR}, print_cc = #{printCc,jdbcType=CHAR} where id = #{id,jdbcType=INTEGER}