出现在小组中的子句中或在汇总函数中使用

发布于 2025-02-11 18:35:05 字数 456 浏览 3 评论 0原文

计算第一个实际购买的项目，并在TR2_Invoice中填充第一个_actual_item列。

SELECT cust_id, total_amount, items, MIN(time_in) 
FROM tr_invoice WHERE total_amount <> 0 
GROUP BY cust_id;

错误：列“ tr_invoice.total_amount”必须出现在该组中，或在汇总函数中使用第1行：选择CUST_ID，total_amount，项目，最小...

我使用avg（），min（），max（）或array_agg（）作为 total_amount 和项目的聚合强>，它的输出与我在MySQL上的询问的输出不同。有更好的解决方案可以解决这个问题吗？

原文

Calculate the first actual bought item and populate the first_actual_item column in tr2_invoice.

SELECT cust_id, total_amount, items, MIN(time_in) 
FROM tr_invoice WHERE total_amount <> 0 
GROUP BY cust_id;

ERROR: column "tr_invoice.total_amount" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT cust_id, total_amount, items, MIN...

I used AVG(), MIN(), MAX(), or ARRAY_AGG() as aggregations for total_amount and items, it would output differently from what I queried on MySQL. Any better solution to solve this?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

<逆流佳人身旁 2025-02-18 18:35:05

所选字段必须按子句出现在组中。

1。

选择CUST_ID，total_amount，项目，最小值（TIME_IN）（cust_id）为min_time_in
从tr_invoice中ratter_amount＆lt;＆gt; 0;

2。

SELECT
    b.cust_id     ,
    b.total_amount,
    b.items       ,
    a.min_time_in
from
    (
        SELECT
            cust_id,
            MIN(time_in) as min_time_in
        FROM
            tr_invoice 
        WHERE
            total_amount <> 0
        GROUP BY
            cust_id
    )a
    join
        tr_invoice b
        ON
            a.cust_id=b.cust_id;

请参考链接

the selected fields must appear in the GROUP BY clause.

SELECT cust_id, total_amount, items, MIN(time_in) over( PARTITION by cust_id) as min_time_in
FROM tr_invoice WHERE total_amount <> 0 ;

SELECT
    b.cust_id     ,
    b.total_amount,
    b.items       ,
    a.min_time_in
from
    (
        SELECT
            cust_id,
            MIN(time_in) as min_time_in
        FROM
            tr_invoice 
        WHERE
            total_amount <> 0
        GROUP BY
            cust_id
    )a
    join
        tr_invoice b
        ON
            a.cust_id=b.cust_id;

Please refer link

回复收藏 0 原文

~没有更多了~