这里是初学者。我已经编写了以下SQL语句,并且返回的记录没有问题。
SELECT COUNT(MAX(doc_line_num)) AS "TOTAL RECS"
FROM C_LAB
WHERE COMP_CODE = 'P1' AND OP_CODE = 'RMARTINEZ'
GROUP BY DOC_NUM
我的问题是…我可以用多列编写同一个查询吗?例如:
SELECT OP_CODE, COUNT(MAX(doc_line_num)) AS "TOTAL REC"
FROM C_LAB
WHERE COMP_CODE = 'P1' AND OP_CODE = 'CHRISTIANMONTALVO'
GROUP BY OP_CODE, DOC_NUM
我得到以下错误:
SQL错误[937][42000]:ORA-00937:不是单个组组函数
这不可能吗?或者我做错了什么?
首先,您使用的是Oracle提供的快捷方式。这个
SELECT COUNT(MAX(doc_line_num)) AS "TOTAL RECS"
FROM c_lab
WHERE comp_code = 'P1' AND op_code = 'RMARTINEZ'
GROUP BY doc_num;
相当于
SELECT COUNT(max_doc_line_num) AS "TOTAL RECS"
FROM
(
SELECT doc_num, MAX(doc_line_num) AS max_doc_line_num
FROM c_lab
WHERE comp_code = 'P1' AND op_code = 'RMARTINEZ'
GROUP BY doc_num
);
并最终计数具有doc_line_num的文档编号。
你也可以把它写成
SELECT COUNT(DISTINCT doc_num) AS "TOTAL RECS"
FROM c_lab
WHERE comp_code = 'P1' AND op_code = 'RMARTINEZ' AND doc_line_num IS NOT NULL;
您的第二个查询相当于
SELECT op_code, COUNT(max_doc_line_num) AS "TOTAL RECS"
FROM
(
SELECT op_code, doc_num, MAX(doc_line_num) AS max_doc_line_num
FROM c_lab
WHERE comp_code = 'P1' AND op_code = 'CHRISTIANMONTALVO'
GROUP BY op_code, doc_num
);
无效,因为在主查询中聚合(COUNT(max_doc_line_num)
(,但也选择了缺少GROUP BY
子句或聚合的op_code。
话虽如此,你可能正在寻找这个:
SELECT op_code, COUNT(max_doc_line_num) AS "TOTAL RECS"
FROM
(
SELECT op_code, doc_num, MAX(doc_line_num) AS max_doc_line_num
FROM c_lab
WHERE comp_code = 'P1' AND op_code = 'CHRISTIANMONTALVO'
GROUP BY op_code, doc_num
)
GROUP BY op_code
ORDER BY op_code;
但也许你想要完全不同的东西。我不知道。我的建议是:不要使用Oracle的快捷方式在不使用子查询的情况下再次聚合所有聚合行。使用子查询,这样目的就很清楚,这样就不会发生语法错误。