oracle 将一个选择结果除以另一个选择结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13237186/
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
Divide one select result by another select result
提问by Brian Brock
I've looked at the other similar questions, and they seem to be a little bit different than what I'm trying to do.
我看过其他类似的问题，它们似乎与我想要做的有点不同。
I have a single query, where I'm selecting two count values, and I want to divide one by the other.
我有一个查询，我在其中选择两个计数值，我想将一个除以另一个。
Here is my working code:
这是我的工作代码：
SELECT (SELECT count(DISTINCT s.lastfirst)
FROM students s
JOIN cc ON s.id = cc.studentid
JOIN courses c on cc.course_number = c.course_number
WHERE cc.schoolid='109'
AND c.course_name LIKE 'AP %'
AND substr(cc.termid,0,1) <> '-'
AND cc.dateenrolled BETWEEN to_date('08/01/2010','MM/DD/YYYY') AND
to_date('08/01/2011','MM/DD/YYYY')) as AP,
(SELECT count(DISTINCT s.lastfirst)
FROM students s
JOIN cc ON s.id = cc.studentid
JOIN courses c on cc.course_number = c.course_number
WHERE cc.schoolid = '109'
AND substr(cc.termid,0,1) <> '-'
AND cc.dateenrolled BETWEEN to_date('08/01/2010','MM/DD/YYYY')
AND to_date('08/01/2011','MM/DD/YYYY')) as ttl
FROM DUAL
Here is where I break it:
这是我打破它的地方：
SELECT (SELECT count(DISTINCT s.lastfirst)
FROM students s
JOIN cc ON s.id = cc.studentid
JOIN courses c on cc.course_number = c.course_number
WHERE cc.schoolid='109'
AND c.course_name LIKE 'AP %'
AND substr(cc.termid,0,1) <> '-'
AND cc.dateenrolled BETWEEN to_date('08/01/2010','MM/DD/YYYY')
AND to_date('08/01/2011','MM/DD/YYYY')) as AP,
(SELECT count(DISTINCT s.lastfirst)
FROM students s
JOIN cc ON s.id = cc.studentid
JOIN courses c on cc.course_number = c.course_number
WHERE cc.schoolid = '109'
AND substr(cc.termid,0,1) <> '-'
AND cc.dateenrolled BETWEEN to_date('08/01/2010','MM/DD/YYYY')
AND to_date('08/01/2011','MM/DD/YYYY')) as ttl,
(AP / ttl) as pcnt
FROM DUAL
Could somebody please tell me the properway to do this?
有人可以告诉我这样做的正确方法吗？
Thank You
谢谢你
回答by lc.
The simplest answer is just divide the two values:
最简单的答案是将两个值相除：
SELECT (SELECT count(DISTINCT s.lastfirst)
FROM students s
JOIN cc ON s.id = cc.studentid
JOIN courses c on cc.course_number = c.course_number
WHERE cc.schoolid='109'
AND c.course_name LIKE 'AP %'
AND substr(cc.termid,0,1) <> '-'
AND cc.dateenrolled BETWEEN to_date('08/01/2010','MM/DD/YYYY')
AND to_date('08/01/2011','MM/DD/YYYY')) /
(SELECT count(DISTINCT s.lastfirst)
FROM students s
JOIN cc ON s.id = cc.studentid
JOIN courses c on cc.course_number = c.course_number
WHERE cc.schoolid = '109'
AND substr(cc.termid,0,1) <> '-'
AND cc.dateenrolled BETWEEN to_date('08/01/2010','MM/DD/YYYY')
AND to_date('08/01/2011','MM/DD/YYYY')) as quotient
FROM DUAL
If you want to keep the values and get the quotient, you could do:
如果您想保留值并获得商，您可以这样做：
SELECT AP, ttl, AP/ttl AS quotient
FROM (
SELECT (SELECT count(DISTINCT s.lastfirst)
FROM students s
JOIN cc ON s.id = cc.studentid
JOIN courses c on cc.course_number = c.course_number
WHERE cc.schoolid='109'
AND c.course_name LIKE 'AP %'
AND substr(cc.termid,0,1) <> '-'
AND cc.dateenrolled BETWEEN to_date('08/01/2010','MM/DD/YYYY') AND
to_date('08/01/2011','MM/DD/YYYY')) as AP,
(SELECT count(DISTINCT s.lastfirst)
FROM students s
JOIN cc ON s.id = cc.studentid
JOIN courses c on cc.course_number = c.course_number
WHERE cc.schoolid = '109'
AND substr(cc.termid,0,1) <> '-'
AND cc.dateenrolled BETWEEN to_date('08/01/2010','MM/DD/YYYY')
AND to_date('08/01/2011','MM/DD/YYYY')) as ttl
FROM DUAL)
but at this point you may as well just do the division in the calling code rather than the SQL, since you're just selecting one result row with two scalar values anyway.
但此时您最好只在调用代码中进行除法，而不是在 SQL 中进行除法，因为无论如何您只是选择了一个具有两个标量值的结果行。
