考虑一个简单的表,如下所示:
CREATE TABLE example_groups(
id SERIAL PRIMARY KEY,
value1 INT NOT NULL,
value2 INT NOT NULL,
user_name TEXT NOT NULL
);
其中有一些行:
INSERT INTO example_groups(value1, value2, user_name) VALUES(3, 1, 'Tom');
INSERT INTO example_groups(value1, value2, user_name) VALUES(5, 2, 'Tom');
INSERT INTO example_groups(value1, value2, user_name) VALUES(1, 3, 'Tom');
INSERT INTO example_groups(value1, value2, user_name) VALUES(40, 10, 'John');
INSERT INTO example_groups(value1, value2, user_name) VALUES(1, 11, 'John');
我有以下查询以按user_name
列对表进行分组:
SELECT SUM(value1), SUM(value2), MAX(value2)
FROM example_groups
GROUP BY user_name;
结果返回什么,我将其插入到另一个表中(查询是INSERT SELECT
)。
我想将列中所有行的最大值返回到我的代码MAX
。到目前为止,我一直在使用该RETURNING
语句执行此操作,但是当我在此处尝试使用它时,出现错误。
RETURNING
在这种情况下使用该语句的正确方法是什么?
查询失败的示例:
SELECT SUM(value1), SUM(value2), MAX(value2) AS max2
FROM example_groups
RETURNING (MAX(max2))
GROUP BY user_name;
ps我正在使用postgres。
也试试这个
with cte as
(
SELECT SUM(value1) as sum1, SUM(value2) as sum1 , MAX(value2) as maxValue2
FROM example_groups
GROUP BY user_name
)
select * from cte where maxvalue2 = (select max(maxValue2) from cte)
或者
with cte as
(
SELECT SUM(value1) as sum1, SUM(value2) as sum1 , MAX(value2) as maxValue2
FROM example_groups
GROUP BY user_name
)
select max(maxValue2) as max_Of_all from cte
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句