dongxianji0968
2014-12-15 00:12
浏览 50
已采纳

mysql过滤结果与子查询结果

i have two tables:

titles

  • id
  • title

entry

  • id
  • entry
  • title_id
  • user_id
  • time (timestamp)

i need to check if any new entry is submitted after my last entry at that title. looking for a query which filters the updates (different from my user_id) on each title and list the result with a number included which shows the new entries.


Example

titles

id | title

1 | Cars
2 | Computers

entry

id | entry    | title_id | user_id | time
1  | bmw      | 1        | 1       | 1418595811
2  | mercedes | 2        | 2       | 1418595812
3  | ibm      | 2        | 2       | 1418595813
4  | hp       | 2        | 3       | 1418595814
5  | dell     | 2        | 1       | 1418595815
6  | acer     | 2        | 3       | 1418595816
7  | asus     | 2        | 4       | 1418595817
8  | toshiba  | 2        | 4       | 1418595818
9  | ferrari  | 1        | 3       | 1418595819
10 | toyota   | 1        | 4       | 1418595820
11 | honda    | 1        | 2       | 1418595821

Expected results:

if user_id=1 ->

Cars (3)
Computers (3)

if user_id=2 ->

Cars (0)
Computers (5)

if user_id=3 ->

Cars (2)
Computers (2)

as seen above, [user_id=1] submits a car (bmw). after that, other users submitted entries to that title. i need a query for new submissions for each title after user's last submission. (using the time field) only need is if there is new submission. if so, how many?

i need something like this:

select count(entry) 
from entry where time>last_entry_time[user_id] 
left join titles 
where title.id=entry.title_id
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • dongyang5716 2014-12-15 04:48
    已采纳

    This does it for all users:

    SELECT e1.user_id, t.title, COUNT(e2.id) AS count
    FROM titles AS t
    LEFT JOIN (SELECT user_id, title_id, MAX(time) AS last_entry
               FROM entry
               GROUP BY user_id, title_id) AS e1
    ON t.id = e1.title_id
    LEFT JOIN entry AS e2 ON e2.title_id = e1.title_id AND e2.user_id != e1.user_id AND e2.time > e1.last_entry
    GROUP BY e1.user_id, t.id
    ORDER BY e1.user_id, t.title
    

    DEMO

    To do it for a specific user:

    SET @user_id = 1;
    
    SELECT t.title, COUNT(e2.id) AS count
    FROM titles AS t
    LEFT JOIN (SELECT user_id, title_id, MAX(time) AS last_entry
               FROM entry
               WHERE user_id = @user_id
               GROUP BY title_id) AS e1
    ON t.id = e1.title_id
    LEFT JOIN entry AS e2 ON e2.title_id = e1.title_id AND e2.user_id != @user_id AND e2.time > e1.last_entry
    GROUP BY t.id
    ORDER BY t.title
    

    DEMO

    打赏 评论
  • dpcj32769 2014-12-15 00:19

    I am not sure if I am following your question correctly. Try this

    select e.id, e.tittle_id, e.user_id from entry e 
           where e.id not in(
                            select e.id from entry e, titles t where t.id = e.id
                            )
    
    打赏 评论

相关推荐 更多相似问题