SQL查看表中的两个或多个列是否大于0

I have ran in to a little problem and would appreciate any help.

我遇到了一个小问题，并希望得到任何帮助。

My Table is such:

我的表是这样的：

CASH | CREDIT CARD | DEBIT CARD | ACCOUNT | OTHER
-------------------------------------------------
0.00   0.00          0.00         0.00      0.00

1.00   0.00          0.00         0.00      0.00

2.00   1.00          0.00         0.00      0.00

My aim is to SELECT * FROM any of the above rows that have more than one column > 0.

我的目标是SELECT * FROM具有多个列> 0的上述任何行。

So the third row would be selected in this scenario with the above table.

因此，在此方案中将使用上表选择第三行。

3 个解决方案

#1

SELECT 
  [CASH], [CREDIT CARD], [DEBIT CARD], [ACCOUNT], [OTHER]
FROM table
WHERE
  CASE WHEN [CASH] > 0 THEN 1 ELSE 0 END+
  CASE WHEN [CREDIT CARD] > 0 THEN 1 ELSE 0 END+
  CASE WHEN [DEBIT CARD] > 0 THEN 1 ELSE 0 END+
  CASE WHEN [ACCOUNT] > 0 THEN 1 ELSE 0 END+
  CASE WHEN [OTHER] > 0 THEN 1 ELSE 0 END >= 2

#2

I prefer t-clausen's answer but just as an exercise, I decide to try it as an UNPIVOT followed by a PIVOT, so that we could write it using more of the general SQL tools:

我更喜欢t-clausen的答案，但作为练习，我决定尝试将其作为UNPIVOT，然后是PIVOT，以便我们可以使用更多的常规SQL工具来编写它：

declare @t table (SomeID int,Cash money,Credit money,Debit money,Account money,Other money)
insert into @t(SomeID,Cash,Credit,Debit,Account,Other) values
(1,0.00,0.00,0.00,0.00,0.00),
(2,1.00,0.00,0.00,0.00,0.00),
(3,2.00,1.00,0.00,0.00,0.00)

;With Unpiv as (
    select *,SUM(CASE WHEN MoneyValue > 0.00 THEN 1 ELSE 0 END) OVER (PARTITION BY SomeID) as cnt
    from @t t
        unpivot (MoneyValue for MoneyType in (Cash,Credit,Debit,Account,Other)) x
), Repiv as (
    select *
    from Unpiv u
        pivot (SUM(MoneyValue) for MoneyType in (Cash,Credit,Debit,Account,Other)) x
    where
        cnt >= 2
)
select * from Repiv

This does assume that you've got another column (here, called SomeID) by which each row can be uniquely identified.

这确实假设您有另一列（此处称为SomeID），通过该列可以唯一标识每一行。

Result:

结果：

SomeID      cnt         Cash                  Credit                Debit                 Account               Other
----------- ----------- --------------------- --------------------- --------------------- --------------------- ---------------------
3           2           2.00                  1.00                  0.00                  0.00                  0.00

Just hoping the above might be more adaptable for some variants of the requirements.

只是希望上面的内容可能更适合某些需求的变体。

#3

SELECT 
  [CASH], [CREDIT CARD], [DEBIT CARD], [ACCOUNT], [OTHER]
FROM table
WHERE (
  SELECT COUNT(*)
  FROM (VALUES ([CASH]),([CREDIT CARD]),([DEBIT CARD]),([ACCOUNT]),([OTHER])) t(value)
  WHERE value > 0
) >= 2

#1

SELECT 
  [CASH], [CREDIT CARD], [DEBIT CARD], [ACCOUNT], [OTHER]
FROM table
WHERE
  CASE WHEN [CASH] > 0 THEN 1 ELSE 0 END+
  CASE WHEN [CREDIT CARD] > 0 THEN 1 ELSE 0 END+
  CASE WHEN [DEBIT CARD] > 0 THEN 1 ELSE 0 END+
  CASE WHEN [ACCOUNT] > 0 THEN 1 ELSE 0 END+
  CASE WHEN [OTHER] > 0 THEN 1 ELSE 0 END >= 2

#2