需求描述:查询姓名、手机号、地区、报名时间,同时查询多个数据表。
实现代码:
SELECT name,mobile,(case WHEN lang='TC' THEN '港澳台' WHEN area LIKE '%珠海%' THEN '珠海' ELSE '深圳' END) as address,baomingdate
FROM `52届登记信息` WHERE lang='TC' OR area LIKE '%珠海%' OR area LIKE '%深圳%' UNION
SELECT uname,phone,(case WHEN lang='TC' THEN '港澳台' WHEN area LIKE '%珠海%' THEN '珠海' ELSE '深圳' END) as address,FROM_UNIXTIME(inputtime,'%Y-%m-%d')
FROM `51届登记信息` WHERE lang='TC' OR area LIKE '%珠海%' OR area LIKE '%深圳%' UNION
SELECT uname,phone,(case WHEN lang='TC' THEN '港澳台' WHEN area LIKE '%珠海%' THEN '珠海' ELSE '深圳' END) as address,FROM_UNIXTIME(inputtime,'%Y-%m-%d')
FROM `50届登记信息` WHERE lang='TC' OR area LIKE '%珠海%' OR area LIKE '%深圳%' UNION
SELECT uname,phone,(case WHEN lang='TC' THEN '港澳台' WHEN area LIKE '%珠海%' THEN '珠海' ELSE '深圳' END) as address,FROM_UNIXTIME(inputtime,'%Y-%m-%d')
FROM `49届预登记到场数据` WHERE lang='TC' OR area LIKE '%珠海%' OR area LIKE '%深圳%'
注意点:
各个表之间的字符集需要统一,否侧会报错
1271 - Illegal mix of collations for operation 'UNION', Time: 0.000000s