oracle SELECT 列表与 GROUP BY 不一致
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25925749/
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
SELECT list inconsistent with GROUP BY
提问by Bart g
I don't seem to figure this one out. I am trying to run this query where I am suppose to get two columns; one column gets me the first and last name, the second column gets me the number of "authorid" which would represent books written by that author. If I add the count(authorid) in the first line it displays the error message shown below. Thank you very much in advance.
我似乎没有弄清楚这一点。我正在尝试在我想得到两列的地方运行这个查询；一列是我的名字和姓氏，第二列是我的“authorid”编号，代表该作者所写的书籍。如果我在第一行添加 count(authorid)，它会显示如下所示的错误消息。非常感谢您提前。
SELECT FNAME || ' ' || LNAME "Author", AUTHORID
FROM BOOKAUTHOR JOIN AUTHOR USING(AUTHORID)
ORDER BY FNAME;
=================================================================================
================================================== ================================
Error starting at line : 1 in command -
SELECT FNAME || ' ' || LNAME "Author", count(AUTHORID)
FROM BOOKAUTHOR JOIN AUTHOR USING(AUTHORID)
ORDER BY FNAME
Error at Command Line : 2 Column : 41
Error report -
SQL Error: ORA-01748: only simple column names allowed here
01748. 00000 - "only simple column names allowed here"
*Cause:
*Action:
=================================================================================
================================================== ================================
The output should look like the lines below
Author Books Written
========================================
John Doe 3
James Row 2
采纳答案by sgeddes
It doesn't look like you're including GROUP BY:
看起来你没有包括GROUP BY：
SELECT A.FNAME || ' ' || A.LNAME "Author", count(A.AUTHORID)
FROM BOOKAUTHOR BA
JOIN AUTHOR A ON BA.AUTHORID = A.AUTHORID
GROUP BY A.FNAME || ' ' || A.LNAME
回答by WW.
Your failing statement has this:
你失败的陈述是这样的：
USING(AUTHOR.AUTHORID)
But you are only allowed to have:
但您只能拥有：
USING(AUTHORID)
Try this:
尝试这个：
SELECT AUTHOR.FNAME || ' ' || AUTHOR.LNAME "Author", count(AUTHOR.AUTHORID)
FROM BOOKAUTHOR JOIN AUTHOR USING(AUTHORID)
GROUP BY AUTHOR.FNAME, AUTHOR.LNAME
ORDER BY AUTHOR.FNAME
