是否可以编写一个无需子选择即可执行此操作的 SQL 查询？

发布于 2024-10-12 09:43:19 字数 811 浏览 6 评论 0原文

想象一个一对多的关系，例如：

Mail: subject, date etc 
Recipient: address

是否可以不使用子选择执行此查询：[电子邮件受保护] 没有来自同一域 @domain.com 的其他收件人？

我能找到的唯一方法是使用子选择：

select mail m, recipient r where m.pkm=r.pkm 
and (r.address='[email protected]')
and not exists (select * from mail ms, recipient rs where m.pkm=ms.pkm and ms.pkm=rs.pkm and rs.address<>'[email protected]' and rs.address like '%@domain.com')

原文

Imagine a relation one to many, for example:

Mail: subject, date etc 
Recipient: address

Is it possible to do this query WITHOUT using a subselect: all mail received by [email protected] that did not have another recipient from the same domain @domain.com??

the only way I can find is using a subselect:

select mail m, recipient r where m.pkm=r.pkm 
and (r.address='[email protected]')
and not exists (select * from mail ms, recipient rs where m.pkm=ms.pkm and ms.pkm=rs.pkm and rs.address<>'[email protected]' and rs.address like '%@domain.com')

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

思念绕指尖 2024-10-19 09:43:19

select m.*, r1.* 
from mail m
inner join recipient r1 on m.pkm=r1.pkm
left join recipient r2 on m.pkm=r2.pkm and r2.address<>'[email protected]' and r2.address like '%@domain.com'
where r2.pkm is null
and r1.address='[email protected]'

select m.*, r1.* 
from mail m
inner join recipient r1 on m.pkm=r1.pkm
left join recipient r2 on m.pkm=r2.pkm and r2.address<>'[email protected]' and r2.address like '%@domain.com'
where r2.pkm is null
and r1.address='[email protected]'

回复收藏 0 原文

红焚 2024-10-19 09:43:19

我不确定这是否比你原来的好！ -- 它仍然有一个子选择 -- 但允许域不是文字“domain.com”。

SELECT m.*
FROM mail m
INNER JOIN 
(
    SELECT DISTINCT pkm
    FROM recipient
    GROUP BY pkm, SUBSTRING(email, CHARINDEX('@', email) + 1, 1000)
    HAVING COUNT(SUBSTRING(email, CHARINDEX('@', email) + 1, 1000))  = 1
) r
ON m.pkm = r.pkm

hth，R

I'm not sure this is any better than your original! -- it still has a sub-select -- but allows for domains to be other than the literal 'domain.com'.

SELECT m.*
FROM mail m
INNER JOIN 
(
    SELECT DISTINCT pkm
    FROM recipient
    GROUP BY pkm, SUBSTRING(email, CHARINDEX('@', email) + 1, 1000)
    HAVING COUNT(SUBSTRING(email, CHARINDEX('@', email) + 1, 1000))  = 1
) r
ON m.pkm = r.pkm

hth, R

回复收藏 0 原文

~没有更多了~