如何从不同值中的列中获取特定值的数据

Example related to my requirement. I have a table t_policies_details columns pol_ref , inrtyp

与我的要求相关的示例。我有一个表t_policies_details列pol_ref,inrtyp

inrtyp has values = ORD,PPC,AVCO,DSS,RVP, SP,TV 

I want to fetch polref for which inrtyp is only SP and TV both , no any other.

我想获取polref,其中inrtyp只有SP和TV两者,没有任何其他。

SELECT DISTINCT p.polref, p.inrtyp FROM t_policies_details p WHERE p.inrtyp NOT IN ('ORD')
INTERSECT
SELECT DISTINCT p.polref, p.inrtyp FROM t_policies_details p WHERE p.inrtyp NOT IN ('PPC')
INTERSECT
SELECT DISTINCT p.polref, p.inrtyp FROM t_policies_details p WHERE p.inrtyp NOT IN ('AVCO')
INTERSECT
SELECT DISTINCT p.polref, p.inrtyp FROM t_policies_details p WHERE p.inrtyp NOT IN ('DSS')
INTERSECT
SELECT DISTINCT p.polref, p.inrtyp FROM t_policies_details p WHERE p.inrtyp NOT IN ('RVP') 

I have tried with above query, exclusing things which are not required, but this gives me polref, which has either SP or TV , or both. I have to search again which has both. I found few by searching. But when I included the condition:

我尝试过以上查询,排除不需要的东西,但这给了我polref,它有SP或TV,或两者兼而有之。我必须再次搜索哪两个都有。我通过搜索找到了一些。但是当我把条件包括在内时:

INTERSECT
SELECT DISTINCT p.polref, p.inrtyp FROM t_policies_details p WHERE p.inrtyp IN ('SP')
INTERSECT
SELECT DISTINCT p.polref, p.inrtyp FROM t_policies_details p WHERE p.inrtyp IN ('TV')

I don't get any polref.

我没有得到任何polref。

Please help me to get this right.

请帮助我做到这一点。

4 个解决方案

#1

If I understand correctly, you want all instances where a polref has inrtyp for both 'TV', 'SP' but no other values at all. You can count the number of wanted and unwanted instances in a group by, and then apply logic in an outer select:

如果我理解正确,你想要一个polref具有“TV”,“SP”的所有实例,但根本没有其他值。您可以计算组中所需和不需要的实例的数量,然后在外部选择中应用逻辑:

WITH cte AS
(
  SELECT p.polref,
       SUM(CASE WHEN p.inrtyp IN ('TV', 'SP') THEN 1 ELSE 0 END) AS Good, 
       SUM(CASE WHEN p.inrtyp NOT IN ('TV', 'SP') THEN 1 ELSE 0 END) AS Bad
  FROM t_policies_details p
  GROUP BY p.polref
)
SELECT cte.polref
FROM cte
WHERE Good = 2 AND Bad = 0;

SqlFiddle here

#2

remove p.inrtyp from your selected list of columns and that will do it. If you're using a column in the where clause it doesn't have to appear in the selected list of columns. You'll get a list of pol_refs which match your condition

从您选择的列列表中删除p.inrtyp,这样就可以了。如果您在where子句中使用列,则不必显示在选定的列列表中。您将获得符合您条件的pol_refs列表

SELECT DISTINCT p.polref FROM t_policies_details p WHERE p.inrtyp NOT IN ('ORD')
INTERSECT
SELECT DISTINCT p.polref FROM t_policies_details p WHERE p.inrtyp NOT IN ('PPC')
INTERSECT
SELECT DISTINCT p.polref FROM t_policies_details p WHERE p.inrtyp NOT IN ('AVCO')
INTERSECT
SELECT DISTINCT p.polref FROM t_policies_details p WHERE p.inrtyp NOT IN ('DSS')
INTERSECT
SELECT DISTINCT p.polref FROM t_policies_details p WHERE p.inrtyp NOT IN ('RVP')
INTERSECT
SELECT DISTINCT p.polref FROM t_policies_details p WHERE p.inrtyp IN ('SP')
INTERSECT
SELECT DISTINCT p.polref FROM t_policies_details p WHERE p.inrtyp IN ('TV')

#3

Please try below query. This may be helping you

请尝试以下查询。这可能对你有帮助

Query:

SELECT DISTINCT p.polref, p.inrtyp
FROM t_policies_details p
WHERE p.inrtyp = ALL ('SP','TV');

#4

Please try below query.

请尝试以下查询。

Query:

SELECT DISTINCT p.polref, p.inrtyp FROM t_policies_details p WHERE p.inrtyp in ('SP','TV');

SELECT DISTINCT p.polref,p.inrtyp FROM t_policies_details p WHERE p.inrtyp in('SP','TV');