主页 / dba / 问题 / 68512
Accepted
Tawfik Khalifeh
Asked: 2014-06-19 10:14:32 +0800 CST

选择满足 n 个外键条件的行

  • 772

我正在尝试根据相对外键关系中的 2 个以上条件过滤主表行，我想出了这个工作正常，但我无法说服自己这是一个大惊小怪

select r.pageid
from
((
select co.pageid
from (cmscontentpublished as co join cmsnode as cm on co.PageID = cm.ID)
where cm.parentid=365 and co.Title='Title' and co.Content like '%test%'
)
union all
(
select co.pageid 
from (cmscontentpublished as co join cmsnode as cm on co.PageID = cm.ID)
where cm.parentid=365 and year(co.DateContent) =2012
)) as r
group by r.pageid
having COUNT(*)=2

关于如何正确做的任何想法

performance query-performance

1 个回答

  1. Best Answer

    试试这是否适合你

    SELECT page_id FROM
(
SELECT co.pageid , count(case when year(co.DateContent) =2012 THEN 1 END) AS cnt1,
count(CASE WHEN co.Title='Title' and co.Content like '%test%' THEN 1 END) AS cnt2
FROM cmscontentpublished as co 
JOIN cmsnode as cm on co.PageID = cm.ID
WHERE cm.parentid=365 AND ((co.Title='Title' and co.Content like '%test%') OR
(year(co.DateContent) =2012))
)a 
WHERE (cnt1 + cnt2 )= 2 
-- if you want page_id only if each condition is satisfied exactly once,
-- then where should look "cnt1 = 1 and cnt2 =1"
    • 0

相关问题