如何从表格行中推导筛选条件

huangapple
go评论111阅读模式
英文:

how to derive filter condition from table rows

问题

如何根据以下类似的行来推导BTEQ中的过滤条件？

表B：

  1. COLA COLB COLC
  2. 1    CODE  AAA
  3. 1    DESC  BBB
  4. 1    TYPE  CCC

这是一些代码：

  1. SELECT * FROM TABLE A WHERE CODE IN ('AAA') AND DESC IN ('BBB') AND TYPE IN ('CCC')
英文:

How do i derive the filter condition in bteq based on rows like given below?

TABLE B:

  1. COLA COLB COLC
  2. 1    CODE  AAA
  3. 1    DESC  BBB
  4. 1    TYPE  CCC

Here is some code:

  1. SELECT * FROM TABLE A WHERE CODE IN ('AAA') AND DESC IN ('BBB') AND TYPE IN ('CCC')

答案1

得分: 2

SELECT
COLA
FROM yourTable
GROUP BY
COLA
HAVING
COUNT(CASE WHEN COLB = 'CODE' AND COLC = 'AAA' THEN 1 END) > 0 AND
COUNT(CASE WHEN COLB = 'DESC' AND COLC = 'BBB' THEN 1 END) > 0 AND
COUNT(CASE WHEN COLB = 'TYPE' AND COLC = 'CCC' THEN 1 END) > 0;

英文:

One approach here would be to aggregate by COLA, and then add three assertions for the key/values you expect:

  1. SELECT
  2.     COLA
  3. FROM yourTable
  4. GROUP BY
  5.     COLA
  6. HAVING
  7.     COUNT(CASE WHEN COLB = 'CODE' AND COLC = 'AAA' THEN 1 END) > 0 AND
  8.     COUNT(CASE WHEN COLB = 'DESC' AND COLC = 'BBB' THEN 1 END) > 0 AND
  9.     COUNT(CASE WHEN COLB = 'TYPE' AND COLC = 'CCC' THEN 1 END) > 0;

答案2

得分: 0

你可以使用通用表达式重写查询。

或者使用派生表。

  1. SELECT *
  2. FROM
  3. (
  4. SELECT COLA, CASE WHEN COLB = 'Code' THEN COLC ELSE NULL END AS Code
  5.            , CASE WHEN COLB = 'DESC' THEN COLC ELSE NULL END AS DESC
  6.            , CASE WHEN COLB = 'TYPE' THEN COLC ELSE NULL END AS TYPE
  7. FROM TABLEB
  8. ) AS t
  9. WHERE  CODE IN ('AAA') AND DESC IN ('BBB') AND TYPE IN ('CCC');
英文:

You can re-write query using common table expression.

  1. WITH CTE_TableB(ColA, Code, DESC,TYPE) AS
  2. (
  3. SELECT COLA, CASE WHEN COLB = 'Code' THEN COLC ELSE NULL AS Code
  4.            , CASE WHEN COLB = 'DESC' THEN COLC ELSE NULL AS DESC
  5.            , CASE WHEN COLB = 'TYPE' THEN COLC ELSE NULL AS TYPE
  6. FROM TABLEB
  7. )
  8. SELECT *
  9. FROM CTE_TableB
  10. WHERE  CODE IN ('AAA') AND DESC IN ('BBB') AND TYPE IN ('CCC');

Or using derived table

  1. SELECT *
  2. FROM
  3. (
  4. SELECT COLA, CASE WHEN COLB = 'Code' THEN COLC ELSE NULL AS Code
  5.            , CASE WHEN COLB = 'DESC' THEN COLC ELSE NULL AS DESC
  6.            , CASE WHEN COLB = 'TYPE' THEN COLC ELSE NULL AS TYPE
  7. FROM TABLEB
  8. ) AS t
  9. WHERE  CODE IN ('AAA') AND DESC IN ('BBB') AND TYPE IN ('CCC');

答案3

得分: 0

使用表B中的值来过滤表A，并假设表B中没有值的列表示不对该列进行过滤，您可以这样做：

  1. SELECT *
  2.   FROM A
  3.  WHERE ( NOT EXISTS ( SELECT * FROM B WHERE B.COLB = 'CODE' )
  4.       OR A.CODE IN ( SELECT B.COLC FROM B WHERE B.COLB = 'CODE' ) )
  5.    AND ( NOT EXISTS ( SELECT * FROM B WHERE B.COLB = 'DESC' )
  6.       OR A.DESC IN ( SELECT B.COLC FROM B WHERE B.COLB = 'DESC' ) )
  7.    AND ( NOT EXISTS ( SELECT * FROM B WHERE B.COLB = 'TYPE' )
  8.       OR A.TYPE IN ( SELECT B.COLC FROM B WHERE B.COLB = 'TYPE' ) )
英文:

To filter table A using the values in table B, and assuming that a column without values in table B means unfiltered on that column, you can do it like this:

  1. SELECT *
  2.   FROM A
  3.  WHERE ( NOT EXISTS ( SELECT * FROM B WHERE B.COLB = 'CODE' )
  4.       OR A.CODE IN ( SELECT B.COLC FROM B WHERE B.COLB = 'CODE' ) )
  5.    AND ( NOT EXISTS ( SELECT * FROM B WHERE B.COLB = 'DESC' )
  6.       OR A.DESC IN ( SELECT B.COLC FROM B WHERE B.COLB = 'DESC' ) )
  7.    AND ( NOT EXISTS ( SELECT * FROM B WHERE B.COLB = 'TYPE' )
  8.       OR A.TYPE IN ( SELECT B.COLC FROM B WHERE B.COLB = 'TYPE' ) )

答案4

得分: 0

您可以使用in或exists来实现此目的：

  1. select a.*
  2. from a 
  3. where a.code in (select b.colc from b where b.colb = 'CODE') and
  4.       a.desc in (select b.colc from b where b.colb = 'DESC') and
  5.       a.type in (select b.colc from b where b.colb = 'TYPE');

唯一的注意事项是，这假定表b中至少有一个每个值。

您可以处理缺失的值。一种方法是使用额外的子查询：

  1. select a.*
  2. from a 
  3. where (a.code in (select b.colc from b where b.colb = 'CODE') or
  4.        not exists (select 1 from b where b.colb = 'CODE')
  5.       ) and
  6.       (a.desc in (select b.colc from b where b.colb = 'DESC') or
  7.        not exists (select 1 from b where b.colb = 'DESC')
  8.       ) and
  9.       (a.type in (select b.colc from b where b.colb = 'TYPE') or
  10.        not exists (select 1 from b where b.colb = 'TYPE')
  11.       );
英文:

You can use in or exists for this purpose:

  1. select a.*
  2. from a 
  3. where a.code in (select b.colc from b where b.colb = 'CODE') and
  4.       a.desc in (select b.colc from b where b.colb = 'DESC') and
  5.       a.type in (select b.colc from b where b.colb = 'TYPE');

The one caveat is that this assumes that there is at least one of each value in table b.

You can handle missing values. One method uses additional subqueries:

  1. select a.*
  2. from a 
  3. where (a.code in (select b.colc from b where b.colb = 'CODE') or
  4.        not exists (select 1 from b where b.colb = 'CODE')
  5.       ) and
  6.       (a.desc in (select b.colc from b where b.colb = 'DESC') or
  7.        not exists (select 1 from b where b.colb = 'DESC')
  8.       ) and
  9.       (a.type in (select b.colc from b where b.colb = 'TYPE') or
  10.        not exists (select 1 from b where b.colb = 'TYPE')
  11.       );

huangapple
  • 本文由 发表于 2020年1月3日 16:05:48
  • 转载请务必保留本文链接：https://go.coder-hub.com/59575092.html
  • sql
  • teradata
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定