oracle 查询未返回正确的结果

qni6mghb 于 2022-11-22 发布在 Oracle

关注(0)|答案(1)|浏览(139)

我有一个POST表、一个ACTION表和ACTION_TYPE表，我解释ACTION表包含用户执行的所有操作，ACTION_TYPE表包含操作详细信息，例如ID = 4的ACTION对于POST_ID 6具有ACTION_TYPE_ID = 1，这意味着对编号为50的帖子执行了操作，我们可以对一个post_id执行多个操作
POST表

id         title       content     user_id
----------  ----------  ----------  ----------  
 1          title1      Text...     1
 2          title2      Text...     1
 3          title3      Text...     1
 4          title4      Text...     5
 5          title5      Text...     2
 6          title6      Text...     1

ACTION_TYPE表格

id         name      
----------  ----------  
 1          updated
 2          deleted
 3          restored
 4          hided

ACTION表格

id         post_id         action_type_id    date
----------  ----------      --------------    -----
 1          1               1                 2017-01-01
 2          1               1                 2017-02-15
 3          1               3                 2018-06-10
 4          6               1                 2019-08-01
 5          5               2                 2019-12-09
 6          2               3                 2020-04-27
 7          2               1                 2020-07-29
 8          3               2                 2021-03-13

所以我想知道每个帖子的最后一个动作，有时我想得到每个帖子的特定动作类型和用户的最后一个动作。
这是我的问题

select actions, count(*) as cnt
   from(
        select ac.post_id as action_post_id, max(ac.date) as max_date, 
        case
        when ac.action_type_id is not null then act.name
        end as actions,
        case
        when p.user_id is not null then u.name
        end as user_name
        from action ac
        left join post p on ac.post_id = p.id  
        left join user u on p.user_id = u.id
        left join action_type act on ac.action_type_id = act.id
        where p.user_id = 1
        and act.name in ('restored','deleted','updated')
        group by ac.post_id, case when ac.action_type_id is not null then act.name end , case when p.user_id is not null then u.full_name end 
            )
            group by actions
;

所以这里有一个问题我想groupe的post_id，但它要求我添加案件太多，所以我得到以下错误：/*ORA-00979. 00000 -“不是group by表达式 */当我从group by中删除案例但使用此查询时，得到错误结果
这是我得到的结果

actions    user_name   cnt
----------  ----------  -----------   
updated      ERIC       2
deleted      ERIC       2
restored     ERIC       2

所以这里的结果应该是

actions    user_name   cnt
----------  ----------  -----------   
updated      ERIC       2
deleted      ERIC       1
restored     ERIC       1

总和必须等于4，但我得到6，这就像如果它带来了一个以上的行动，每个职位
重要！当我使用简单查询来检查手动操作时，总和等于4
顺祝商祺

oracle

来源：https://stackoverflow.com/questions/74390655/query-dont-return-the-right-result

1条答案

按热度按时间

1cosmwyk1#

您可以用途：

SELECT MAX(t.name) AS action_name,
       MAX(u.name) AS user_name,
       COUNT(*) AS number_posts
FROM   users u
       INNER JOIN post p
       ON (u.id = p.user_id)
       INNER JOIN (
         SELECT post_id,
                MAX(action_type_id) KEEP (DENSE_RANK LAST ORDER BY "DATE", id)
                  AS action_type_id
         FROM   action
         GROUP BY post_id
       ) a
       ON (p.id = a.post_id)
       INNER JOIN action_type t
       ON (t.id = a.action_type_id)
GROUP BY
      u.id,
      t.id

其中，对于示例数据：

CREATE TABLE users (id, name) AS
SELECT 1, 'Eric' FROM DUAL;

CREATE TABLE POST ( id, title, content, user_id) AS
SELECT 1, 'title1', 'Text...', 1 FROM DUAL UNION ALL
SELECT 2, 'title2', 'Text...', 1 FROM DUAL UNION ALL
SELECT 3, 'title3', 'Text...', 1 FROM DUAL UNION ALL
SELECT 4, 'title4', 'Text...', 5 FROM DUAL UNION ALL
SELECT 5, 'title5', 'Text...', 2 FROM DUAL UNION ALL
SELECT 6, 'title6', 'Text...', 1 FROM DUAL;

CREATE TABLE ACTION_TYPE ( id, name ) AS
SELECT 1, 'updated'  FROM DUAL UNION ALL
SELECT 2, 'deleted'  FROM DUAL UNION ALL
SELECT 3, 'restored' FROM DUAL UNION ALL
SELECT 4, 'hided'    FROM DUAL;

CREATE TABLE ACTION ( id, post_id, action_type_id, "DATE") AS
SELECT 1, 1, 1, DATE '2017-01-01' FROM DUAL UNION ALL
SELECT 2, 1, 1, DATE '2017-02-15' FROM DUAL UNION ALL
SELECT 3, 1, 3, DATE '2018-06-10' FROM DUAL UNION ALL
SELECT 4, 6, 1, DATE '2019-08-01' FROM DUAL UNION ALL
SELECT 5, 5, 2, DATE '2019-12-09' FROM DUAL UNION ALL
SELECT 6, 2, 3, DATE '2020-04-27' FROM DUAL UNION ALL
SELECT 7, 2, 1, DATE '2020-07-29' FROM DUAL UNION ALL
SELECT 8, 3, 2, DATE '2021-03-13' FROM DUAL;

输出：
| 动作名称|使用者名称|编号_过帐|
| - -|- -|- -|
| 已恢复|埃里克|一个|
| 已更新|埃里克|2个|
| 删除的|埃里克|一个|
fiddle

赞(0）回复(0）举报 2022-11-22

我来回答

oracle 查询未返回正确的结果

1条答案

相关问题

热门标签

最新问答