Question

我有一个表:

HOST_ID | ContractYear
1       | 2008
2       | 2008
3       | 2008
1       | 2009
4       | 2009

我需要的是问,这将告诉我所有的口服体液补充疗法。截至2008年12月31日的2009年收入指数。

以上例子中的答案是HOST_ID=4。

感谢。

Answer 1

select
  HOST_ID
from
  table_name
where
  ContractYear = 2009
  AND HOST_ID NOT IN (select HOST_ID from table_name where ContractYear = 2008)

Answer 2

使用:

SELECT t.host_id
  FROM TABLE t
 WHERE t.contractyear = 2009
   AND NOT EXISTS(SELECT NULL
                    FROM TABLE t2
                   WHERE t2.host_id = t.host_id
                     AND t.contractyear = 2008)

Answer 3

SELECT  DISTINCT host_id
FROM    mytable mo
WHERE   ContractYear = 2009
        AND NOT EXISTS
        (
        SELECT  NULL
        FROM    mytable mi
        WHERE   mi.host_id = mo.host_id
                AND ContractYear = 2008
        )

Answer 4

更通用,因为它过滤了并非你所期望的年份的所有数据。

select HOST_ID
from table t
where ContractYear = 2009
and HOST_ID not in (select distinct HOST_ID
                    from table t2
                    where t.ContractYear != t2.ContractYear)

友情链接