Is there some elegant way to do that, without a big WHERE
with lots of AND
and OR
? For example there are 4 columns: A, B, C, D. For each row the columns have random integer values. I need to select only those rows which have more than one column with a non-zero value. For example (1,2,3,4) and (3,4,0,0) should get selected, however (0,0,7,0) should not be selected (there are no rows that have zeros only).
有没有一种优雅的方式去做这件事,没有一个很大的,有很多很多的,或者?例如,有4列:A、B、C、d。对于每一行,列都有随机整数值。我只需要选择那些具有非零值的列的行。例如(1,2,3,4)和(3,4,0,0)应该被选中,但是(0,7,0)不应该被选中(没有只有零的行)。
PS. I know how this looks but the funny thing is that this is not exam or something, it's a real query which I need to use in a real app :D
我知道这是什么样子,但有趣的是,这不是考试之类的,这是一个真正的查询,我需要在真正的应用中使用:D
3 个解决方案
#1
1
There. No WHERE
, no OR
and no AND
:
在那里。不,不,不,不,和:
SELECT
IF(`column1` != 0,1,0) +
IF(`column2` != 0,1,0) +
IF(`column3` != 0,1,0) +
IF(`column4` != 0,1,0) AS `results_sum`
FROM `table`
HAVING
`results_sum` > 1
#2
2
SELECT *
FROM mytable
WHERE (0, 0, 0) NOT IN ((a, b, c), (a, b, d), (a, c, d), (b, c, d))
This I believe is this shortest way, though not necessarily the most efficient.
我相信这是一条最短的路,虽然不一定是最有效的路。
#3
0
Try
试一试
select *
from table t
where ( abs(sign(A))
+ abs(sign(B))
+ abs(sign(C))
+ abs(sign(D))
) > 0
#1
1
There. No WHERE
, no OR
and no AND
:
在那里。不,不,不,不,和:
SELECT
IF(`column1` != 0,1,0) +
IF(`column2` != 0,1,0) +
IF(`column3` != 0,1,0) +
IF(`column4` != 0,1,0) AS `results_sum`
FROM `table`
HAVING
`results_sum` > 1
#2
2
SELECT *
FROM mytable
WHERE (0, 0, 0) NOT IN ((a, b, c), (a, b, d), (a, c, d), (b, c, d))
This I believe is this shortest way, though not necessarily the most efficient.
我相信这是一条最短的路,虽然不一定是最有效的路。
#3
0
Try
试一试
select *
from table t
where ( abs(sign(A))
+ abs(sign(B))
+ abs(sign(C))
+ abs(sign(D))
) > 0