表a 和表b a(name varchar(20), url varchar(40))b(name varchar(20), age int) 显示a中的全部数据,如果 select a.name,a.url,标志为 from a,b 如果a中的记录在b中也有哪标志为1如果没有就为0 大家快帮忙
这样可以吗(注意,exists 好像在mysql 4.0中不能用,5.0肯定可以) ------------------------------------------------------------------- select name,1 in_it from a, b where a.name=b.name union SELECT name,0 in_it FROM a WHERE not EXISTS (SELECT * FROM b WHERE a.name=a.name)
写错了,第三行是 WHERE b.name=a.name
谢谢 我的就是mysql 4.0
4.0好像很多类似exist,in的子查询都不支持,要实现你的功能,难. 4.1开始应该就可以了(升一下级) Starting with MySQL 4.1, all subquery forms and operations that the SQL standard requires are supported, as well as a few features that are MySQL-specific.
查了一下MYSQL手册,这样应该可以select name,1 in_it from a, b where a.name=b.name union select name,0 in_it from a left join b on a.name=b.name where b.name is null
又错了: 两个select name 应该为select a.name
我的mysql是4.1.7 谢谢大家 我刚才提示 Column 'name' in field list is ambiguous 怎么出现这个错误亚
name前要加表名或别名 4.1: select a.name,1 in_it from a, b where a.name=b.name union SELECT a.name,0 in_it FROM a WHERE not EXISTS (SELECT * FROM b WHERE a.name=a.name) 4.0: select a.name,1 in_it from a, b where a.name=b.name union select a.name,0 in_it from a left join b on a.name=b.name where b.name is null
a(name varchar(20),
url varchar(40))b(name varchar(20),
age int)
显示a中的全部数据,如果
select a.name,a.url,标志为 from a,b
如果a中的记录在b中也有哪标志为1如果没有就为0
大家快帮忙
-------------------------------------------------------------------
select name,1 in_it from a, b where a.name=b.name
union
SELECT name,0 in_it FROM a WHERE not EXISTS (SELECT * FROM b WHERE a.name=a.name)
WHERE b.name=a.name
4.1开始应该就可以了(升一下级)
Starting with MySQL 4.1, all subquery forms and operations that the SQL standard requires are supported, as well as a few features that are MySQL-specific.
union
select name,0 in_it from a left join b on a.name=b.name where b.name is null
两个select name 应该为select a.name
谢谢大家 我刚才提示
Column 'name' in field list is ambiguous
怎么出现这个错误亚
4.1:
select a.name,1 in_it from a, b where a.name=b.name
union
SELECT a.name,0 in_it FROM a WHERE not EXISTS (SELECT * FROM b WHERE a.name=a.name)
4.0:
select a.name,1 in_it from a, b where a.name=b.name
union
select a.name,0 in_it from a left join b on a.name=b.name where b.name is null