是否可以在 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
Is it possible to compare tuples in oracle-compatible sql?
提问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