匹配SQL Server中的多个行

I got a complex SQL Server query I'm trying to knock out, hitting a wall and not sure the best / fastest way. I got a good start but not getting it all right.

我遇到了一个复杂的SQL Server查询，我正在尝试解决它，遇到了瓶颈，但不确定最好的/最快的方法。我有一个良好的开端，但没有做好。

Basically, I need to create a Select statement thats going to me a list of content_ids that meet the following requirements:

基本上，我需要创建一个Select语句，它将给我一个满足以下要求的content_ids列表:

• Unique content_id with field_id of 12680 that field_values match
• 唯一的content_id与field_id为12680的field_values匹配
• content_id with field_id of 12643 that don't have 'Yes' but another row with the same content_id but field_id 12680 matches this same content_id with the value of 'Yes'
• content_id和field_id(12643)它们没有'Yes'但是另一行具有相同的content_id但field_id 12680将同样的content_id与值'Yes'匹配
• Only items with create_date in the last 24hrs
• 只有在过去24小时内具有create_date的项目

The database looks like:

数据库的样子:

content_id | field_id | field_value                             | create_date
_________________________________________________________________________________________
12         | 12680    | 210b1183c3718142594425ab9538376ebb7f1e0 | 2012-02-21 22:44:51.167
12         | 12643    | Yes                                     | 2012-02-21 22:44:51.167
13         | 12680    | 210b1183c3718142594425ab9538376ebb7f1e0 | 2012-02-21 22:44:51.167
13         | 12643    |                                         | 2012-02-21 22:44:51.167

Heres what I got so far:

以下是我到目前为止得到的:

SELECT DISTINCT Event1.content_id
FROM tblIVTextData AS Event1
    JOIN tblIVTextData AS Event2
        ON (Event1.content_id != Event2.content_id AND 
            Event1.field_value = Event2.field_value)
    JOIN tblIVTextData AS Event3
        ON(Event3.field_id = 12643 AND
           Event3.field_value = 'Yes' AND
           Event3.content_id = Event1.content_id)
    WHERE Event1.field_id = 12680 AND 
        Event1.create_date > dateadd(hh, -300, getdate())

I'm getting lost on selecting the first occurrence of the 'Yes' with field_values matching. Any suggestions on how to accomplish this or perf improvements?

我在选择第一次出现带有field_values匹配的“Yes”时迷失了方向。关于如何完成这个或perf的改进有什么建议吗?

2 个解决方案

#1

I don't understand your conditions, nor the sample query (you say "24" hours as a condition but use "300" hours in the SQL).

我不了解您的条件，也不了解示例查询(您说“24小时”作为条件，但在SQL中使用“300”小时)。

However, I think you can do what you want with aggregation and a having clause. Here is an example that tests for one occurrence of 12680 and at least one occurrence of 12643 with field_value <> 'Yes':

但是，我认为您可以使用聚合和have子句来实现您想要的功能。下面的示例测试12680次和12643次中至少一次出现的情况，其中field_value <> 'Yes':

select e.ContentId
from tblIVTextData e
where e.create_date > dateadd(hh, -24, getdate())
group by e.ContentId
having sum(case when field_id = 12680 then 1 else 0 end) = 1 and
       sum(case when field_id = 12643 and field_value <> 'Yes' then 1 else 0 end) > 0

#2

with content_cte(content_id,field_id)as
(select distinct content_id,field_id from tblIVTextData where content_id=12643 
and field_value <>'Yes')

SELECT DISTINCT Event1.content_id
FROM 
tblIVTextData AS Event1
JOIN tblIVTextData AS Event2
ON (Event1.content_id != Event2.content_id AND 
Event1.field_value = Event2.field_value)
JOIN content_cte AS Event3
ON(Event3.content_id = Event1.content_id AND Event1.field_value='Yes')
WHERE Event1.field_id = 12680 
Event1.create_date > dateadd(hh, -24, getdate())