SQL获取特定行中具有特定值的列数 [英] SQL get number of columns in a particular row having a particular value

问题描述

我知道我可以通过使用COUNT（*）函数来获取在特定列中具有特定值的行数，但是我想查找特定行中具有特定值的COLUMNS数。任何建议如何做到这一点？

我会张贴一个例子，直到现在我已经尝试，但我完全失去了这一个...

编辑1 - 下面是一些示例数据和预期结果：

Table - trackbill | u1 | u1paid | u2 | u2paid | u3 | u3paid | u4 | u4paid | U5 | u5paid

所以我想做的是当我使用trackbill选择一个特定的行，我想通过它的所有列，看看哪些具有值'false '。因此，如果u1paid和u3paid是唯一的值为false的列，则返回的答案应为2.

解决方案

您的表结构如下：

  TABLE trackbill 
（u1 NUMBER（10,2）
， u1paid VARCHAR（10） - 包含值是或否
，u2 NUMBER（10,2）
，u2paid VARCHAR（10） - 包含值是 b $ b，u3 NUMBER（10,2）
，u3paid VARCHAR（10） - 包含值'Yes'或'No'
，u4 NUMBER（10,2）
，u4paid VARCHAR（30） - 包含值'Yes'或'No'
，u5 NUMBER（10,2）
，u5paid VARCHAR $ b）
  

因此，检查 uxpaid 列，我们可以写为：

  SELECT u1，u1paid，u2，u2paid，u3 ，u3pai，u4，u4paid，u5，u5paid 
，CASE u1paid WHEN'是'THEN 1 ELSE 0 END 
 + CASE u2paid WHEN'是'THEN 1 ELSE 0 END 
 + CASE u3paid WHEN '是'THEN 1 ELSE 0 END 
 + CASE u4paid WHEN'是'THEN 1 ELSE 0 END 
 + CASE u5paid WHEN'是'THEN 1 ELSE 0 END 
 AS no_of_paid_columns 
 FROM trackbill 
  

我不能帮你用SQLite，因为我不知道。 >

I know that I can get the number of rows having a particular value in a particular column by making use of the COUNT(*) function, but I want to find the number of COLUMNS in a particular row that have a particular value. Any suggestions on how to do this?

I would have posted an example of what I've tried up till now, but I'm completely lost on this one...

Edit 1 - Here's some sample data and the expected result:

Table - trackbill | u1 | u1paid | u2 | u2paid | u3 | u3paid | u4 | u4paid | u5 | u5paid

So what I want to do is when I select a particular row using trackbill, I want to go through all of its columns and see which ones have the value 'false'. So if u1paid and u3paid were the only columns which had the value false, the answer returned should be 2.

解决方案

I would consider your table structure is as follows:

TABLE trackbill
( u1     NUMBER(10,2)
, u1paid VARCHAR(10)  -- contains values 'Yes' or 'No'
, u2     NUMBER(10,2)
, u2paid VARCHAR(10)  -- contains values 'Yes' or 'No'
, u3     NUMBER(10,2)
, u3paid VARCHAR(10)  -- contains values 'Yes' or 'No'
, u4     NUMBER(10,2)
, u4paid VARCHAR(30)  -- contains values 'Yes' or 'No'
, u5     NUMBER(10,2)
, u5paid VARCHAR(30)  -- contains values 'Yes' or 'No'
)

So, to check how many 'Yes's are for the uxpaid columns, we can write an SQL as:

SELECT u1, u1paid, u2, u2paid, u3, u3pai, u4, u4paid, u5, u5paid
       , CASE u1paid WHEN 'Yes' THEN 1 ELSE 0 END
       + CASE u2paid WHEN 'Yes' THEN 1 ELSE 0 END
       + CASE u3paid WHEN 'Yes' THEN 1 ELSE 0 END
       + CASE u4paid WHEN 'Yes' THEN 1 ELSE 0 END
       + CASE u5paid WHEN 'Yes' THEN 1 ELSE 0 END
       AS no_of_paid_columns
  FROM trackbill

I can't help you with SQLite as I don't know it.

这篇关于SQL获取特定行中具有特定值的列数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！