SQL 按问题分组

发布于 2024-10-06 00:37:20 字数 531 浏览 3 评论 0原文

我必须向数据库编写一个查询，该数据库具有这样的表：

TABLE HISTORY:
ID | ITEM_ID | USER_ID | DATE

历史记录

ITEM TABLE:
ID | NAME | OWNER_ID

表包含用户对项目的所有操作的记录。我必须编写一个查询，从 HISTORY 表中获取所有不在所有者手中的项目。因此，它应该为每个项目获取最后一条记录，并从中仅选择那些掌握在其所有者之外的人手中的记录。

我尝试使用嵌套查询来编写它，但是如果我使用这样的子查询：

SELECT ITEM_ID, MAX(DATE) 
FROM HISTORY
GROUP BY ITEM_ID

我无法获取该记录的 ID。因此，我无法循环嵌套查询结果，因为我不知道它们的 ID。

你能帮我吗？

PS 您能否建议我将来如何从带有 group by 子句的查询中获取不在 group by 语句中的列？

原文

I have to write a query to database, that has such table:

TABLE HISTORY:
ID | ITEM_ID | USER_ID | DATE

and

ITEM TABLE:
ID | NAME | OWNER_ID

History table contains a records of all operations with items by users.
I have to write a query, that takes from HISTORY table all items, which are not in the hands of owner. So, it should take for each item last record and select from them only those, which holds in other than it's owner's hands.

I tried to write it using nested queries, but if I'm using such subquery:

SELECT ITEM_ID, MAX(DATE) 
FROM HISTORY
GROUP BY ITEM_ID

I can't take ID of this record. So, I can't loop over the nested queries result's, because I don't know an ID of their ID's.

Could you help me?

P.S. And could you advise me to the future, how to take from queries with group by clause the columns, which not in group by statement?

分享到QQ

分享到微博

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

发布评论

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

椒妓 2024-10-13 00:37:20

我对这个问题的理解是，您想要所有最近历史记录条目是由非所有者用户创建的项目。如果我错了，请纠正我。

select h.item_id
from item i
    inner join history h
        on h.item_id = i.id and i.owner_id != h.user_id
    inner join (select item_id, max(id), max(date) as date
                from history
                group by item_id) mh
        on mh.item_id = h.item_id and h.date = mh.date

上面的查询假设日期时间在日期列中具有一定的唯一性保证。如果没有，你也许可以使用history.id，如果它是一个完美的自动递增身份列，并且你保证永远不会让任何人乱搞（好吧，也许不是那么严格，但你明白我的意思）。

在这种情况下：

select h.item_id
from item i
    inner join history h
        on h.item_id = i.id and i.owner_id != h.user_id
    inner join (select item_id, max(id) as id
                from history
                group by item_id) mh
        on mh.id = h.id

My understanding of the question is that you want all items whose most recent history entry was made by a non-owner user. Please correct me if I'm wrong.

select h.item_id
from item i
    inner join history h
        on h.item_id = i.id and i.owner_id != h.user_id
    inner join (select item_id, max(id), max(date) as date
                from history
                group by item_id) mh
        on mh.item_id = h.item_id and h.date = mh.date

The above query assumes a datetime with some guarantee of uniqueness in the date column. If not, you might be able to use history.id if it is a perfectly auto-incrementing identity column and you promise never to let anyone mess with (ok, maybe not that strict, but you get my point).

In this case:

select h.item_id
from item i
    inner join history h
        on h.item_id = i.id and i.owner_id != h.user_id
    inner join (select item_id, max(id) as id
                from history
                group by item_id) mh
        on mh.id = h.id

回复收藏 0 原文

~没有更多了~