从多个具有相同字段的表里检索记录

 http://topic.youkuaiyun.com/u/20090215/09/9b77ea38-5cbf-4dd2-a553-731e644e7a8a.html?392976002

 

我有多个表，都具有name字段和value字段和id字段，其他字段不同。我想从这所有表里检索出value为2、name为“nm”的记录，并且在这个查询里包含一个字段叫“表名”，说明当前记录是具体从哪个表里查出来的。要完成这个任务，两个要点：

（1）用union all

（2）直接把表名（硬拷贝）作为一个字段包含在查询里

SQL语句如下：

select [name],value,'表1' as tname from 表1 where value=2 and [name]='nm' union all select [name],value,'表2' as tname from 表2 where value=2 and [name]='nm' union all select [name],value,'表3' as tname from 表3 where value=2 and [name]='nm'

感谢Leftie babyt 两位朋友指点。

另外，如果想要知道 总过有几条这样的记录，就是检索结果只是一个数字，表明所有表里这样的记录共有几条。可以如下这样写： 

SELECT COUNT(*) FROM ( select [name],[value],id from 表1 where [name]='nm' and [value]=2 union ALL select [name],[value],id from 表2 where [name]='nm' and [value]=2 union ALL select [name],[value],id from 表3 where [name]='nm' and [value]=2 union ALL select [name],[value],id from 表4 where [name]='nm' and [value]=2 union ALL select [name],[value],id from 表5 where [name]='nm' and [value]=2 )

或者

SELECT SUM(CNUM) FROM ( select COUNT(*) AS CNUM from 表1 where [name]='nm' and [value]=2 union select COUNT(*) AS CNUM from 表2 where [name]='nm' and [value]=2 union select COUNT(*) AS CNUM from 表3 where [name]='nm' and [value]=2 union select COUNT(*) AS CNUM from 表4 where [name]='nm' and [value]=2 union select COUNT(*) AS CNUM from 表5 where [name]='nm' and [value]=2 )

感谢jhone99 指点