是否可以在 oracle 兼容的 sql 中比较元组?

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/5425041/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-18 23:09:12  来源:igfitidea点击:

Is it possible to compare tuples in oracle-compatible sql?

sqloracletuples

提问by user126715

I'm not 100% if tuples is the term for what I'm talking about but I'm looking at something like this:

如果元组是我所谈论的术语,我不是 100%,但我正在看这样的东西:

Table grades
user    grade
------------
Jim     B
Bill    C
Tim     A
Jim     B+

I know I can do:

我知道我可以做到:

SELECT COUNT(*)
FROM grades
WHERE (
   (user = 'Jim' AND grade = 'B')
   OR (user = 'Tim' AND grade = 'C')
);

But is there a way to do something more like this?

但是有没有办法做更多这样的事情?

SELECT COUNT(*)
    FROM grades
    WHERE (user, grade) IN (('Jim','B'), ('Tim','C'));

EDIT: As a side note, I'd only tested with:

编辑:作为旁注,我只测试过:

(user, grade) = ('Tim','C')

And that fails, so I assumed IN would fail as well, but I was wrong (thankfully!).

那失败了,所以我认为 IN 也会失败,但我错了(谢天谢地!)。

回答by Justin Cave

The query you posted should be valid syntax

您发布的查询应该是有效的语法

SQL> ed
Wrote file afiedt.buf

  1  with grades as (
  2    select 'Jim' usr, 'B' grade from dual
  3    union all
  4    select 'Bill', 'C' from dual
  5    union all
  6    select 'Tim', 'A' from dual
  7    union all
  8    select 'Jim', 'B+' from dual
  9  )
 10  select *
 11    from grades
 12   where (usr,grade) in (('Jim','B'),
 13                         ('Tim','C'),
 14*                        ('Tim','A'))
SQL> /

USR  GR
---- --
Jim  B
Tim  A

回答by Andomar

You could use a subquery to treat a list of tuples like a table:

您可以使用子查询将元组列表视为表格:

SELECT  COUNT(*)
FROM    grades
JOIN    (
        SELECT  'Jim' as user, 'B' as grade from dual
        UNION ALL
        SELECT  'Tim', 'C' from dual
        UNION ALL
        SELECT  'Pim', 'D' from dual
        ) as SearchTarget
ON      SearchTarget.user = grades.user
        and SearchTarget.grade = grades.grade