三条类似的SQL合并.怎么弄啊?

[sqz10200]

首先.
数据库中有表A,B,C

表A中有一个字段name
表B中有一个字段name
表C中有一个字段name

我想查询foo这个在三个表中的name的字段里是否存在.

我是这么写的
select count(*) from A where A.name='foo'
select count(*) from B where B.name='foo'
select count(*) from C where C.name='foo'

这样的3条SQL我想可以合并成一个来查询.但是不知道怎么写好.

用不用count(*)都可以.只要能知道foo是否存在于三张表里就好.

如:在A.name中存在foo的话就返回1
都不存在就返回0


这样的形式...

求解.

[jornye]

mysql你可以这样查：

select  (case when (
		(SELECT COUNT(*) FROM A WHERE name='foo')=1  OR
		(SELECT COUNT(*) FROM B WHERE name='foo')=1  OR
		(SELECT COUNT(*) FROM C WHERE name='foo')=1) 
THEN '1 ' ELSE '0' END) as result

[Quote=引用 6 楼 的回复:]
引用 4 楼 的回复:

mssql:
SQL code
select top 1 case when (
(SELECT COUNT(*) FROM A WHERE name='foo')=1 OR
(SELECT COUNT(*) FROM B WHERE name='foo')=1 OR
(SELECT COUNT(*) FROM C WHERE name='……


s……
[/Quote]

[sqz10200]

不行啊.

[孤独加百列]

SELECT (select count(*) from A where A.name='foo') + (select count(*) from B where B.name='foo') + (select count(*) from C where C.name='foo')

[wanghao2979]

declare @tb_a table(name varchar(50))
declare @tb_b table(name varchar(50))
declare @tb_c table(name varchar(50))
--insert into @tb_a(name) values('foo')


select 1 from @tb_a where name = 'foo'
union all
select 1 from @tb_b where name = 'foo'
union all
select 1 from @tb_c where name = 'foo'

[wanghao2979]

declare @tb_a table(name varchar(50))
declare @tb_b table(name varchar(50))
declare @tb_c table(name varchar(50))
insert into @tb_a(name) values('foo')
insert into @tb_b(name) values('foo')


select 1 from @tb_a where name = 'foo'
union 
select 1 from @tb_b where name = 'foo'
union 
select 1 from @tb_c where name = 'foo'

union all 改成 union

[昵称被占用了]

IF EXISTS (
select 1 from A where A.name='foo'
) OR EXISTS (
select 1 from B where B.name='foo'
) OR EXISTS (
select 1 from C where C.name='foo'
)
PRINT 1
ELSE
PEINR 0

[天-笑]

if exists(
		 select 1 as A where A.name='foo' UNION ALL
		 select 1 as B where B.name='foo' UNION ALL
		 select 1 as C where C.name='foo' 
)
begin
    print 'foo'
end

[sqz10200]

对了..
我的是mysql数据库.

[jornye]

mssql:

select top 1 case when (
		(SELECT COUNT(*) FROM A WHERE name='foo')=1  OR
		(SELECT COUNT(*) FROM B WHERE name='foo')=1  OR
		(SELECT COUNT(*) FROM C WHERE name='foo')=1)
THEN '1 ' ELSE '0' END FROM sysusers

[xxjltan]

一条搞定试下这样

select A=(select count(*) from A where A.name='foo'),B=(select count(*) from B where B.name='foo'),C=(select count(*) from C where C.name='foo')

[sqz10200]

[Quote=引用 4 楼 的回复:]

mssql:
SQL code
select top 1 case when (
(SELECT COUNT(*) FROM A WHERE name='foo')=1 OR
(SELECT COUNT(*) FROM B WHERE name='foo')=1 OR
(SELECT COUNT(*) FROM C WHERE name='……
[/Quote]

sysusers

啥意思啊.最后一句没看懂.

[sqz10200]

select sum(( select count(*) as A where A.name='foo')
+(select count(*) as B where B.name='foo')
+(select count(*) as C where C.name='foo')
) as s

我是这么写的

[英児]

if exists ( select 1 from A,B,C where A.name='foo' or b.name='foo' or c.name='foo')
begin
select '存在'
end
else
begin
select '不存在'
end

[以学习为目的]

select  (case when (
        (SELECT COUNT(*) FROM A WHERE name='foo')=1  OR
        (SELECT COUNT(*) FROM B WHERE name='foo')=1  OR
        (SELECT COUNT(*) FROM C WHERE name='foo')=1) 
THEN '1 ' ELSE '0' END)