T-Sql:选择至少有两个字段匹配条件的行 [英] T-Sql: Select Rows where at least two fields matches condition
问题描述
我有一个表,我们称之为values
,带有一个主键和五个整数字段,如下所示:
I've got a table, let's call it values
with a primary key and five integer fields, like this:
id val1 val2 val3 val4 val5
1 4 3 4 5 3
2 2 3 2 2 2
3 5 4 1 3 3
4 1 4 3 4 4
现在我需要选择所有行,其中五个值字段中至少有两个字段的值为 4.因此结果集应包含第一行 (id=1) 和最后一行 (id=4).
Now I need to select all rows where at least any two of the five value fields got the value 4. So the result set should contain the first row (id=1) and the last row (id=4).
我从一个简单的 OR 条件开始,但组合太多了.然后我尝试了一个带有 HAVING 和 COUNT 的子选择,但没有成功.
I started with a simple OR condition but there are too many combinations. Then I tried a sub-select with HAVING and COUNT but no success.
任何想法如何解决这个问题?
Any Ideas how to solve this?
推荐答案
您可以使用 VALUES
以构建包含您的字段的内联表.然后查询此表以获取至少有两个字段等于 4 的行:
You can use VALUES
to construct an inline table containing your fields. Then query this table to get rows having at least two fields equal to 4:
SELECT *
FROM mytable
CROSS APPLY (
SELECT COUNT(*) AS cnt
FROM (VALUES (val1), (val2), (val3), (val4), (val5)) AS t(v)
WHERE t.v = 4) AS x
WHERE x.cnt >= 2
这篇关于T-Sql:选择至少有两个字段匹配条件的行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!