'in (select…)'语句的多个变量？-Java 学习之路

-2

我想做这样的事情：

select value1, value2, value3, value4, count(1)
from mytable
where value1, value2, value3, value4 in
    (select value1, value2, value3, value4 from mytable
     where record_no = 1 and value5 = 'foobar')
group by value1, value2, value3, value4
having count(1)>4;

也就是说，我想为所有1-4组中找到值1-4，这些组在其至少一个记录上具有特定属性，并且我只想要具有四个以上记录的组 .

Update for clarification

select * from mytable;

会给你一些类似的东西

value1    value2    value3   value4   record_no    value5    lots more columns
------    ------    ------   ------   ---------    ------    -----------------
aaa       bbb       ccc      ddd      1            foobar
aaa       bbb       ccc      ddd      2            abcdef
aaa       bbb       ccc      ddd      3            zzzzzz
aaa       bbb       ccc      ddd      4            barfoo
aaa       bbb       ccc      ddd      5            dsnmatr
a1        b1        c1       d1       1            foobar
a1        b1        c1       d1       2            foobar
a2        b2        c2       d2       1            barfoo
a2        b2        c2       d2       2            barfoo

我想找到value1，value2，value3，value4的值对于value1，value2，value3，value4的所有组，其记录1的值为'foobar'，其值为> 4 .

例如，它应该返回

value1    value2    value3   value4 
------    ------    ------   ------  
aaa       bbb       ccc      ddd

3 回答

你非常接近开始 . 这部分是问题：

where value1, value2, value3, value4 in

你必须将 value1, value2, value3, value4 视为一个集合，所以只需将括号放在它们周围：

where (value1, value2, value3, value4) in

这是整个查询;从你的帖子中唯一改变的是括号：

select value1, value2, value3, value4, count(1)
from mytable
where (value1, value2, value3, value4) in
    (select value1, value2, value3, value4
    from mytable
    where record_no = 1 and value5 = 'foobar')
group by value1, value2, value3, value4
having count(1) > 4;

这是here的SQL小提琴 .

回复于 2024-04-19T22:10:07+08:00

2
当使用多个变量时，我经常将这些项与一些不会发生的字符连接在一起，所以
```
where value1 + '|' + value2 + '|' + value3 in (
    select value1 + '|' + value2 + '|' + value3 from ...
```
如果我有三个领域只有在所有值相同的情况下才能实现平等，从而实现结果 .
回复于 2024-04-19T22:10:07+08:00

你可以这样做

select  value1, value2, value3, value4, count(*)
from    mytable
group by value1, value2, value3, value4
having  count(*) > 4 
and     sum(decode(record_no , 1 , 1 , 0)) = 1
and     sum(decode(value5 , 'foobar' , 1 , 0) = 1;

这是解码的一个小技巧 . 它应该工作正常 .

出于教育目的，如果你不想't need and actual data from the sub query (this is your case) it'总是使用 EXISTS . 在你的例子中它会是

select value1, value2, value3, value4, count(*)
from mytable
where exists 
    (select 1 
     from   mytable b
     where  b.record_no = 1 
     and    b.value5 = 'foobar'
     and    b.value1 = a.value1
     and    b.value2 = a.value2 
     and    b.value3 = a.value3
     and    b.value4 = a.value4
     )
group by value1, value2, value3, value4
having count(1) > 4;

回复于 2024-04-19T22:10:07+08:00

'in (select…)'语句的多个变量？

3 回答

相关问题