多表查最大键 SQL 那些年我遇见的奇葩需求

前几天偶遇奇葩需求,今天记录下来,挑战自己的忍耐极限

客户要求在6个表里查某列字段的最大值有则递增无则新增

规律如下
table1
xxx-xxx-xxx-123456
table2
xxx-xxx-xxx-123456
table3
xxx-xxx-xxx-123456-123
table4
xxx-xxx-xxx-123456-123-123
table5
xxx-xxx-xxx-123456-123-123-123
table6
xxx-xxx-xxx-123456-123-123-123-123

规则就是截取后6位数字+1,但是我百思不得其解 为何要去遍历6张表，不由得把客户家人问候了一遍,他想要,咱就做给他,这里只列出SQL

select top 1 * from (  (select top 1 right(ID,6) 'x' from table1 where ID like '{type}-[0-9][0-9][0-9][0-9][0-9][0-9]' order by ID desc)  
UNION (select top 1 left(right(ID,10),6)  'x' from table3  where ID like '{type}-[0-9][0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9]' order by ID desc)  
UNION (select top 1 left(right(ID,14),6)  'x' from  table4  where ID like '{type}-[0-9][0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9]' order by ID desc) 
UNION (select top 1 left(right(ID,18),6) 'x'  from  table5 where ID like '{type}-[0-9][0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9]' order by ID desc)
UNION (select top 1 left(right(ID,22),6) 'x'  from  table6 where ID like '{type}-[0-9][0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9]' order by ID desc) 
UNION (select top 1 right(ID,6) 'x'  from table2 where ID like '{type}-[0-9][0-9][0-9][0-9][0-9][0-9]' order by ID desc )) a order by x desc

type为类型 具体实现就是通过union这个好几年用不了一次的关键字进行合并查询