这个SQL语句该怎么写？

[slowgrace]

我有多个表（表1、表2、表3、表4、表5），都具有name字段和value字段和id字段，其他字段不同。现在想知道这所有表里value为2、name为“nm”的记录一共有几条，这个SQL语句该怎么写？

[饮水需思源]

select [name],[value],id from 表1 where [name]='nm' and [value]=2
union
select [name],[value],id from 表2 where [name]='nm' and [value]=2
union
select [name],[value],id from 表3 where [name]='nm' and [value]=2
union
select [name],[value],id from 表4 where [name]='nm' and [value]=2
union
select [name],[value],id from 表5 where [name]='nm' and [value]=2

[lingshao0909]

[Quote=引用 1 楼 Leftie 的回复:]
select [name],[value],id from 表1 where [name]='nm' and [value]=2
union
select [name],[value],id from 表2 where [name]='nm' and [value]=2
union
select [name],[value],id from 表3 where [name]='nm' and [value]=2
union
select [name],[value],id from 表4 where [name]='nm' and [value]=2
union
select [name],[value],id from 表5 where [name]='nm' and [value]=2
[/Quote]
正解

[slowgrace]

[Quote=引用 1 楼 Leftie 的回复:]
select [name],[value],id from 表1 where [name]='nm' and [value]=2
union
select [name],[value],id from 表2 where [name]='nm' and [value]=2
union
select [name],[value],id from 表3 where [name]='nm' and [value]=2
union
select [name],[value],id from 表4 where [name]='nm' and [value]=2
union
select [name],[value],id from 表5 where [name]='nm' and [value]=2
[/Quote]

谢谢。不过我是想要知道 总过有几条这样的记录，就是检索结果只是一个数字，表明所有表里这样的记录共有几条。

[slowgrace]

[Quote=引用 2 楼 lingshao0909 的回复:]
引用 1 楼 Leftie 的回复:
select [name],[value],id from 表1 where [name]='nm' and [value]=2
union
select [name],[value],id from 表2 where [name]='nm' and [value]=2
union
select [name],[value],id from 表3 where [name]='nm' and [value]=2
union
select [name],[value],id from 表4 where [name]='nm' and [value]=2
union
select [name],[value],id from 表5 where [name]='nm' and [value]=2
…
[/Quote]

呵呵，貌似你也没理解对哦。

[jhone99]

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

[jhone99]

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
)

[slowgrace]

[Quote=引用 6 楼 jhone99 的回复:]
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 w…
[/Quote]

GOOD。谢谢！