各位大佬们好,我现在有这样一个问题:
表newMail和表mailAddress.
--------------------
newMail:
ID mail Flag
1 [email protected] NULL
2 [email protected] NULL
3 [email protected] X
4 [email protected] X
-----------------
mailAddress:
ID mail Tag
1 [email protected] NULL
2 [email protected] NULL
3 [email protected] NULL
4 [email protected] X
-----------------
如何才能得到两个表中的所有mail,但不包含重复的mail,单个表中Flag或Tag字段为X的也不包含.
上面两个表中我希望得到的mail为:[email protected],[email protected],[email protected]
我用
SELECT DISTINCT `mail` FROM `newMail` where `Flag` is NULL
UNION
SELECT DISTINCT `mail` FROM `mailAddress` where `Tag` is NULL
会得到[email protected],[email protected],[email protected],[email protected],多了个[email protected]
请问大佬们怎么修改?谢谢了,我一直在线
表newMail和表mailAddress.
--------------------
newMail:
ID mail Flag
1 [email protected] NULL
2 [email protected] NULL
3 [email protected] X
4 [email protected] X
-----------------
mailAddress:
ID mail Tag
1 [email protected] NULL
2 [email protected] NULL
3 [email protected] NULL
4 [email protected] X
-----------------
如何才能得到两个表中的所有mail,但不包含重复的mail,单个表中Flag或Tag字段为X的也不包含.
上面两个表中我希望得到的mail为:[email protected],[email protected],[email protected]
我用
SELECT DISTINCT `mail` FROM `newMail` where `Flag` is NULL
UNION
SELECT DISTINCT `mail` FROM `mailAddress` where `Tag` is NULL
会得到[email protected],[email protected],[email protected],[email protected],多了个[email protected]
请问大佬们怎么修改?谢谢了,我一直在线
解决方案 »
- 如何升级数据表?如何比对数据库表结构?并进行合并?
- MySQL如何实现每隔一段时间执行一句sql语句
- mysql workbench 管理工具,有三个下载,我应该下载哪一个呢?
- 在mysql存储过程中为什么不能这样写:update chain_order_number con set iNextId = con.next_id = co
- 如何替换关键字字串
- Linux下MySQL数据导出问题.
- 请教mysql-jdbc3-beta编程问题??
- 请问mysql中 如何可以把一列字段排序后给一列空字段排序赋值
- 一个update问题
- 在MYSQL中,视图中增加了一个distinct导致执行计划不先走where 条件
- 截取中英文混合字符串(前3个字符)
- 问一个sql排序问题
union
select mail from mailAddress;
这样是不行的.
我希望得到"两个表中的所有mail,但不包含重复的mail,单个表中Flag或Tag字段为X的也不包含."
你这样的写法没有考虑到Flag或Tag字段.
union
select mail from mailAddress where id in(select id from mailAddress where tag='X')
;
------------
照兄弟所说的这样写还是有问题哦,结果里面还是会多个[email protected]...真郁闷 :(
就是建一张临时表。把所有数据和所要的字段插入到一张表中。然后再搞一个联合关键字alert.....union() 就把相同的mail去掉了。
方法2:left join where 方法3: not exists
你需要在查询的时候,把null改称一个标志,比如说1,或者什么的,
然后再where就可以了
[email protected]也是重复。
mysql> select * from newmail;
+----+---------------+------+
| id | mail | flag |
+----+---------------+------+
| 1 | [email protected] | NULL |
| 2 | [email protected] | NULL |
| 3 | [email protected] | x |
| 4 | [email protected] | x |
+----+---------------+------+
4 rows in set (0.00 sec)mysql> select * from mailaddress;
+----+----------------+------+
| id | mail | tag |
+----+----------------+------+
| 1 | [email protected] | NULL |
| 2 | [email protected] | NULL |
| 3 | [email protected] | NULL |
| 4 | [email protected] | x |
+----+----------------+------+
4 rows in set (0.00 sec)mysql> select mail,flag from (select mail,flag from newmail union all select mai
l,tag from mailaddress) t group by mail
-> having count(mail) = 1 and flag is null;
+----------------+------+
| mail | flag |
+----------------+------+
| [email protected] | NULL |
| [email protected] | NULL |
+----------------+------+
2 rows in set (0.00 sec)
这样得出来的结果才是楼主要要的结果:
select email,tag, count(email) from
(select email,tag from a
union all
select email,flag from b)
t group by email
having count(email)>=1 and tag is null结果:
[email protected],[email protected],[email protected]
是重复的。
LZ的要求是不要得到重复的EMAIL。所以只有两条EMAIL
按照人怎么去做,程序就怎么写的思路,总能写出来的:SELECT *
FROM (SELECT mail
FROM newmail
WHERE flag IS NULL
UNION
SELECT mail
FROM mailaddress
WHERE tag IS NULL
)a
WHERE NOT
EXISTS (SELECT *
FROM (SELECT mail
FROM newmail
WHERE flag = "x"
UNION
SELECT mail
FROM mailaddress
WHERE tag = "x"
)b
WHERE a.mail = b.mail
)