a.id AS "id", a.show_name AS "showName", a.show_code AS "showCode", a.result_bob AS "resultBob", a.result_bis AS "resultBis", a.result_big AS "resultBig", a.pedigree_certified AS "pedigreeCertified", a.dog_owner_name AS "dogOwnerName", a.dog_gender AS "dogGender", a.birthdate AS "birthdate", a.start_time AS "startTime", a.breeder AS "breeder", a.dog_breed_cn AS "dogBreedCn", a.dog_breed_en AS "dogBreedEn", a.fci_code AS "fciCode", a.year AS "year", a.age_group AS "ageGroup", a.dog_name AS "dogName", a.show_result AS "showResult" SELECT FROM show_results_hk a WHERE a.id = #{id} SELECT FROM show_results_hk a a.del_flag = #{DEL_FLAG_NORMAL} AND a.show_name like concat('%',#{showName},'%') AND a.show_code = #{showCode} AND a.pedigree_certified = #{pedigreeCertified} AND a.dog_owner_name like concat('%',#{dogOwnerName},'%') AND a.dog_gender = #{dogGender} AND a.dog_breed_cn = #{dogBreedCn} AND a.fci_code = #{fciCode} AND a.year = #{year} ORDER BY ${page.orderBy} ORDER BY a.update_date desc SELECT FROM show_results_hk a a.del_flag = #{DEL_FLAG_NORMAL} ORDER BY ${page.orderBy} INSERT INTO show_results_hk( id, show_name, show_code, result_bob, result_bis, result_big, pedigree_certified, dog_owner_name, dog_gender, birthdate, start_time, breeder, dog_breed_cn, dog_breed_en, fci_code, year, age_group, dog_name, create_by, create_date, update_by, update_date, remarks, del_flag ) VALUES ( #{id}, #{showName}, #{showCode}, #{resultBob}, #{resultBis}, #{resultBig}, #{pedigreeCertified}, #{dogOwnerName}, #{dogGender}, #{birthdate}, #{startTime}, #{breeder}, #{dogBreedCn}, #{dogBreedEn}, #{fciCode}, #{year}, #{ageGroup}, #{dogName}, #{createBy.id}, #{createDate}, #{updateBy.id}, #{updateDate}, #{remarks}, #{delFlag} ) UPDATE show_results_hk SET show_name = #{showName}, show_code = #{showCode}, result_bob = #{resultBob}, result_bis = #{resultBis}, result_big = #{resultBig}, pedigree_certified = #{pedigreeCertified}, dog_owner_name = #{dogOwnerName}, dog_gender = #{dogGender}, birthdate = #{birthdate}, start_time = #{startTime}, breeder = #{breeder}, dog_breed_cn = #{dogBreedCn}, dog_breed_en = #{dogBreedEn}, fci_code = #{fciCode}, year = #{year}, age_group = #{ageGroup}, dog_name = #{dogName}, update_by = #{updateBy.id}, update_date = #{updateDate}, remarks = #{remarks} WHERE id = #{id} UPDATE show_results_hk SET del_flag='1' WHERE id = #{id} SELECT count(1) FROM show_results_hk a WHERE a.pedigree_certified = #{pedigreeCertified} AND a.show_code = #{showCode} AND a.del_flag = '0' SELECT t1.* ,t2.updateDate FROM ( SELECT DISTINCT YEAR FROM show_results_hk a WHERE a.del_flag = '0' AND a.year like concat('%',#{yearLike},'%') )t1 LEFT JOIN ( SELECT YEAR, MAX(create_date) AS updateDate FROM show_score_ranking WHERE del_flag='0' AND score_type='HK' GROUP BY YEAR)t2 ON t1.YEAR=t2.YEAR UPDATE ( SELECT CASE WHEN result_bob='BOB' THEN 5 ELSE 0 END AS result_bob_result, CASE WHEN result_big='BIG1' THEN 30 WHEN result_big='BIG2' THEN 20 WHEN result_big='BIG3' THEN 15 WHEN result_big='BIG4' THEN 10 ELSE 0 END AS result_big_result, CASE WHEN result_bis='BIS1' THEN 80 WHEN result_bis='BIS2' THEN 60 WHEN result_bis='BIS3' THEN 50 WHEN result_bis='BIS4' THEN 40 ELSE 0 END AS result_bis_result, id FROM show_results_hk)t LEFT JOIN show_results_hk a ON a.id=t.id SET a.show_result= t.result_bob_result+t.result_big_result+t.result_bis_result WHERE a.del_flag ='0' AND a.year = #{year}; SELECT pedigree_certified, SUM(show_result) AS 'showResult' FROM show_results_hk WHERE del_flag ='0' AND YEAR = #{year} GROUP BY pedigree_certified HAVING showResult>0 ORDER BY showResult desc SELECT a.* FROM show_results_hk a WHERE del_flag ='0' AND pedigree_certified = #{pedigreeCertified} order by start_time desc limit 1 SELECT a.show_code FROM show_results_hk a WHERE del_flag ='0' AND year = #{year} order by start_time desc limit 1