如何将查询结果用作另一个WHERE列？

I have a bunch of columns with yes/no values. These are identified by their length being 3:

我有一堆带有是/否值的列。它们的长度为3：

SELECT colname
  FROM syscat.columns 
  WHERE tabname='CL'
  AND length = 3

I'd like to count all the rows that have a yes for any of these columns.

我想计算所有这些列中包含yes的行。

So normally I'd do:

通常我会这样做：

SELECT COUNT(*)
  FROM CL
  WHERE c1=yes or c2=yes or c3=yes #...

But I'm wondering if there's a way to apply those column names in the WHERE clause of another query. Something like this:

但我想知道是否有办法在另一个查询的WHERE子句中应用这些列名。像这样的东西：

SELECT COUNT(sub.*)
  FROM (
    SELECT colname
      FROM syscat.columns 
      WHERE tabname='CL'
      AND length = 3
  ) sub
  WHERE [sub] = yes #How can I say "The VALUE of each column name from the subquery"

What I'm looking for is a count of rows where any 3-length column has a 'yes'.

我正在寻找的是一个行数，其中任何3长度的列都有'是'。

Note: I'm using db2 on bluemix, but any general information about this would be helpful.

注意：我在bluemix上使用db2，但任何关于此的一般信息都会有所帮助。

1 个解决方案

#1

For this you have to use dynamic sql, but you can make it simpler using the IN predicate:

为此，您必须使用动态SQL，但您可以使用IN谓词使其更简单：

SELECT COUNT(*)
FROM CL
WHERE yes in(c1, c2, c3, c4, ....)

Using dynamic sql you can generate the list of columns' names comma separated from the following query:

使用动态sql，您可以生成与以下查询分隔的列名称逗号列表：

SELECT colname
FROM syscat.columns 
WHERE tabname='CL'
  AND length = 3

#1