在同一个字段和同一个表中查询2个不同的项目-Query2differentitemswithinthesamefieldandthesametable

0  

Since the column references aren't qualified, we can't tell which table each column reference refers to.

由于列引用不合格，我们无法分辨每个列引用所引用的表。

SUGGESTION: as an aid to future readers of the SQL, please consider qualifying all column references with the table name, or even better, a short (unique) table alias. Then the future reader won't be scouring the table definitions to figure out which table contains which column.) Also consider what is going to happen with this SQL statement when a column of the same name is added to another table used in the query.)

建议：作为对SQL未来读者的一种帮助，请考虑使用表名来限定所有列引用，或者甚至更好地使用短（唯一）表别名。然后，未来的读者将不会搜索表定义以确定哪个表包含哪个列。）还要考虑当将同一列添加到查询中使用的另一个表时，此SQL语句将会发生什么。 ）

We'll just have to guess at which table contains which columns.

我们只需要猜测哪个表包含哪些列。

I'd suggest a pattern using a GROUP BY clause, rather than DISTINCT.

我建议使用GROUP BY子句而不是DISTINCT的模式。

And I suggest computing an aggregate on the result of an expression that tests whether an included row satisfies the particular condition.

我建议在表达式的结果上计算聚合，该表达式测试包含的行是否满足特定条件。

And then performing a test on the aggregate, to see if any rows existed (within the group) where the condition evaluated to TRUE.

然后对聚合执行测试，以查看条件评估为TRUE的任何行（在组内）是否存在。

As an example: (not tested)

举个例子:(未经测试）

SELECT s.master_id
     , s.eventdate
     , s.code
     , s.term
     , p.surname
     , p.forename
  FROM srch s
 INNER
  JOIN person p 
    ON p.entity_id = s.master_id
 WHERE ( s.code LIKE 'C10..%' OR s.code LIKE 'R110%' )
 GROUP
    BY s.master_id
     , s.eventdate
     , s.code
     , s.term
     , p.surname
     , p.forename
HAVING SUM(CASE WHEN s.code LIKE 'C10..%' THEN 1 ELSE 0 END) > 0
   AND SUM(CASE WHEN s.code LIKE 'R110%'  THEN 1 ELSE 0 END) > 0
 ORDER BY s.master_id

The CASE expressions will evaluate to either 1 or 0, for each row. The SUM() aggregate will total the 1s and 0s up. The return from the aggregate will be greater than zero if there was any row (within a "group") that satisfied the condition.

对于每一行，CASE表达式将评估为1或0。 SUM（）聚合将总计1和0。如果满足条件的任何行（在“组”内），则从聚合返回的值将大于零。

If there are no rows (within the group) with code LIKE 'R110%', the SUM() will evaluate to zero, and the comparison will evaluate to FALSE, and the row will not be returned.

如果没有代码LIKE'R110％'的行（在组内），SUM（）将计算为零，并且比较将评估为FALSE，并且不会返回该行。

NOTE: The comparison of the aggregates is in a HAVING clause because the results from the aggregates is not available when the conditions in the WHERE clause are evaluated, when the rows are accessed.

注意：聚合的比较在HAVING子句中，因为当访问行时，在评估WHERE子句中的条件时，聚合的结果不可用。

FOLLOWUP

跟进

Doh! That query above isn't going to return rows. That's my bad. There's a reason we test against some test cases. It helps us identify doofus problems like the one in the query I suggested above.

卫生署！上面的查询不会返回行。那是我的坏事。我们测试一些测试用例是有原因的。它有助于我们识别doofus问题，例如我上面建议的查询中的问题。

It's impossible for that query to return any rows. The code column (as I hadn't really noticed) is in the GROUP BY clause. So at least one of the aggregate functions in the HAVING clause is guaranteed to evaluate to zero.

该查询不可能返回任何行。代码列（我没有注意到）是在GROUP BY子句中。因此，HAVING子句中至少有一个聚合函数可以保证计算为零。

(My problem was that I hadn't noticed that code was a column in the GROUP BY. Doh!)

（我的问题是我没有注意到代码是GROUP BY中的一列。多哈！）

If all of the columns in the SELECT list need to match, except for the "code" column... (I hate to use an expensive correlated subquery when we don't have to...) we could add "EXISTS (correlated subquery).

如果SELECT列表中的所有列都需要匹配，除了“代码”列...（我讨厌在不需要时使用昂贵的相关子查询...）我们可以添加“EXISTS（相关）子查询）。

If master_id is a foreign key reference to entity_id in person, and entity_id is the primary key of person... we could put off the join operation until after we had the results from srch.

如果master_id是对person_id的外键引用，而entity_id是person的主键...我们可以推迟连接操作，直到我们得到srch的结果。

Does term and event also need to match, or just the code? How we write the query depends on that...

术语和事件是否也需要匹配，或仅仅是代码？我们如何编写查询取决于...

Based on the responses in the comments, term and event_date don't need to match. We're looking for rows in srch for the same person (master_id) that have at least one row with the C10 code and at least one row with the R110 code.

根据评论中的回复，term和event_date不需要匹配。我们正在为同一个人（master_id）寻找srch中的行，这些行至少有一行包含C10代码，至少一行包含R110代码。

Identifying those values of master_id follows the same pattern in the query above, using the GROUP BY and conditional tests on aggregates in the HAVING clause.

识别master_id的那些值遵循上面查询中的相同模式，使用GROUP BY和HAVING子句中聚合的条件测试。

This is a query that should return the master_id values that have both a C10 code and an R110 code. This is something we can test... it doesn't return the whole resultset, in only gets us the master_id values we want to return:

这是一个查询，它应该返回同时具有C10代码和R110代码的master_id值。这是我们可以测试的东西......它不返回整个结果集，只获取我们想要返回的master_id值：

SELECT r.master_id
  FROM srch r
 WHERE ( r.code LIKE 'C10..%' OR r.code LIKE 'R110%' )
 GROUP
    BY r.master_id
HAVING SUM(CASE WHEN r.code LIKE 'C10..%' THEN 1 ELSE 0 END) > 0
   AND SUM(CASE WHEN r.code LIKE 'R110%'  THEN 1 ELSE 0 END) > 0    

Once we get that, we can use that query as an inline view... wrap in parens, assign an alias and reference it like it was a table. For example:

一旦我们得到它，我们可以将该查询用作内联视图...包装在parens中，分配别名并引用它就像它是一个表。例如：

SELECT q.*
  FROM ( SELECT r.master_id
           FROM srch r
          WHERE ( r.code LIKE 'C10..%' OR r.code LIKE 'R110%' )
          GROUP
             BY r.master_id
         HAVING SUM(CASE WHEN r.code LIKE 'C10..%' THEN 1 ELSE 0 END) > 0
            AND SUM(CASE WHEN r.code LIKE 'R110%'  THEN 1 ELSE 0 END) > 0
       ) q    

We should test whether PostgreSQL will run that. If we can't get that to run, there's no point in building on it. Once we confirm that runs, we can add a join to the srch table, get the rows that have a matching master_id with a C10 or R110 code.

我们应该测试PostgreSQL是否会运行它。如果我们无法实现这一目标，那么建立它就没有意义了。一旦我们确认运行，我们就可以在srch表中添加一个连接，获得具有匹配的master_id和C10或R110代码的行。

SELECT q.master_id
     , s.code
     , s.term
     , s.event_date
  FROM ( SELECT r.master_id
           FROM srch r
          WHERE ( r.code LIKE 'C10..%' OR r.code LIKE 'R110%' )
          GROUP
             BY r.master_id
         HAVING SUM(CASE WHEN r.code LIKE 'C10..%' THEN 1 ELSE 0 END) > 0
            AND SUM(CASE WHEN r.code LIKE 'R110%'  THEN 1 ELSE 0 END) > 0
       ) q
  JOIN srch s
    ON s.master_id = q.master_id
   AND s.code LIKE 'R110%'

We can add a join to the person table, to retrieve the given name and surname by primary key lookup.

我们可以向person表添加连接，以通过主键查找来检索给定的名称和姓氏。

  JOIN person p
    ON p.entity_id = s.master_id

add the appropriate column references to the SELECT list. The original queries had DISTINCT keyword. We can add that, or we could add GROUP BY clause. Whichever.

将适当的列引用添加到SELECT列表。原始查询具有DISTINCT关键字。我们可以添加，或者我们可以添加GROUP BY子句。任何。

We can reference master_id from either the inline view or the srch table, or even the entity_id from the person table. The join conditions guarantee us they will all be non-null and equal to each other.

我们可以从内联视图或srch表引用master_id，甚至可以引用person表中的entity_id。连接条件保证它们都将是非空的并且彼此相等。

And wind up with something like this (desk checked only, not tested):

并结束这样的事情（只检查桌面，未经测试）：

SELECT s.master_id
     , s.code
     , s.term
     , s.event_date
     , p.surname
     , p.forename
  FROM ( SELECT r.master_id
           FROM srch r
          WHERE ( r.code LIKE 'C10..%' OR r.code LIKE 'R110%' )
          GROUP
             BY r.master_id
         HAVING SUM(CASE WHEN r.code LIKE 'C10..%' THEN 1 ELSE 0 END) > 0
            AND SUM(CASE WHEN r.code LIKE 'R110%'  THEN 1 ELSE 0 END) > 0
       ) q
  JOIN srch s
    ON s.master_id = q.master_id
   AND s.code LIKE 'R110%'
  JOIN person p
    ON p.entity_id = s.master_id
 GROUP
    BY s.master_id
     , s.code
     , s.term
     , s.event_date
     , p.surname
     , p.forename
 ORDER
    BY s.master_id
     , s.code

And, if I haven't made some other doofus mistake again, I expect this will return the result specified by OP. (I've attempted to provide a few comments along the way, about how we went about building the query.)

而且，如果我没有再犯一些其他的doofus错误，我希望这会返回OP指定的结果。 （我试图提供一些评论，关于我们如何构建查询。）

I'd be interested in finding out how big of smoke ball this one makes.

我有兴趣了解这个烟球有多大。

ANOTHER FOLLOWUP

另一个关注

As an alternative, since we don't need to return the C10 rows, the query could actually be a little simpler. The inline view could just return us master_id values related to the C10 codes, and we can dispense with the HAVING clause with tests on the aggregates. This should return a result equivalent to the one above, perhaps even a little faster:

作为替代方案，由于我们不需要返回C10行，因此查询实际上可能更简单一些。内联视图可以返回与C10代码相关的master_id值，并且我们可以省略对聚合的测试的HAVING子句。这应该返回一个等于上面的结果，甚至可能更快一点：

SELECT s.master_id
     , s.code
     , s.term
     , s.event_date
     , p.surname
     , p.forename
  FROM ( SELECT r.master_id
           FROM srch r
          WHERE r.code LIKE 'C10..%'
          GROUP
             BY r.master_id
       ) q
  JOIN srch s
    ON s.master_id = q.master_id
   AND s.code LIKE 'R110%'
  JOIN person p
    ON p.entity_id = s.master_id
 GROUP
    BY s.master_id
     , s.code
     , s.term
     , s.event_date
     , p.surname
     , p.forename
 ORDER
    BY s.master_id
     , s.code

0  

This is a shot in the dark. This may or may not work because we still don't know which DBMS you are using. This should work for sql server and maybe for postgresql. You may have to tweak the syntax slightly depending on which DBMS you are actually using. No matter which system you are using please don't be scared to use aliases and formatting.

这是在黑暗中拍摄的。这可能有效，也可能无效，因为我们仍然不知道您使用的是哪个DBMS。这应该适用于sql server，也许适用于postgresql。您可能需要稍微调整语法，具体取决于您实际使用的DBMS。无论您使用哪种系统，请不要害怕使用别名和格式。

SELECT s.master_id
    , s.eventdate
    , count(distinct s.code) as CodeCount
    , s.term
    , p.surname
    , p.forename 
FROM srch s
INNER JOIN person p ON s.master_id = p.entity_id 
WHERE s.code LIKE 'C10..%' 
    OR s.code like 'R110%'
group by s.master_id
    , s.eventdate
    , s.term
    , p.surname
    , p.forename 
having count(distinct s.code) = 2
ORDER BY s.master_id 

0  

You could try this wildcard and AND approach, to return both:

您可以尝试使用此通配符和AND方法，以返回两者：

I'm assuming that R110 might be followed by C10.. or vice versa.

我假设R110可能跟着C10 ..反之亦然。

Dim DIAG As String
    DIAG = "SELECT DISTINCT master_id, eventdate, code, term, surname, forename " _
    & "FROM srch INNER JOIN person p ON master_id=p.entity_id " _
    & "WHERE code LIKE '*R110*' AND code LIKE '*C10..*' " _
    & "ORDER BY master_id "