好的，所以这可能听起来很复杂，让我解释一下......我一直在敲打这个问题一段时间，现在我陷入困境......无法理解！
T1是一个简单的警报数据表。 它有这些标题：
| ID | TITLE | DATA | --------------------------------- | 1 | Title1 | Text1 | | 2 | Title2 | Text2 |T2，只是一个简单的用户表
| ID | Name | ------------------- | 1 | Fred | | 2 | Bill | | 3 | Brad |T3是T1和T2之间的链接表。 基本上，当用户（T2.ID）第一次查看他未查看的警报（T1.ID）时，他的ID被添加到该表中，因此我知道他已经查看过它。
| ID | T1ID | T2ID | -------------------------- | 1 | 1 | 1 | | 2 | 1 | 2 | | 3 | 1 | 3 | | 4 | 2 | 2 | | 5 | 2 | 3 | | 6 | 3 | 1 | | 7 | 3 | 3 |我可以告诉用户1没有查看提醒2也没有用户2查看提醒3.所以下次他们登录时，我应该弹出这些提醒给他们...所以如果我是用户1，当我登录我的管理网站时，我希望MySQL告诉我，我没有查看警报2，然后在我这样做时更新该表。
正如我所说，每当用户查看其ID与警报ID不匹配的警报时，T3就应该建立起来。 我觉得，我真是太近了......
很简单，现在该怎么做？ 我不会发布什么不起作用......
请帮忙...
OK, so this might sound complicated, let me explain... i've been banging my head on this for a while, and i'm stuck in a loop now... can't figure it out!
T1 is a simple alert data table. It's got theses headers :
| ID | TITLE | DATA | --------------------------------- | 1 | Title1 | Text1 | | 2 | Title2 | Text2 |T2, is just a simple user table
| ID | Name | ------------------- | 1 | Fred | | 2 | Bill | | 3 | Brad |T3 is a link table between T1 and T2. Basically, the first time a user (T2.ID) views an alert (T1.ID) he hasn't viewed, his ID is added to this table so I know he's viewed it.
| ID | T1ID | T2ID | -------------------------- | 1 | 1 | 1 | | 2 | 1 | 2 | | 3 | 1 | 3 | | 4 | 2 | 2 | | 5 | 2 | 3 | | 6 | 3 | 1 | | 7 | 3 | 3 |I can tell user 1 has not viewed alert 2 nor has user 2 viewed alert 3. So next time they login, I should popup these alerts to them... So if i'm user 1, when I login my admin site, I want MySQL to tell me I havn't viewed alert 2, then UPDATE that table when I do.
As I sayed, T3 should build up everytime a user views an alert where his ID does not match an alert ID. I feel, i'm so damn close...
Simple enough, now how do I do it? I ain't going to post what doesn't work...
Please help...
最满意答案
首先，您要从警报表开始，因为它适用于所有人。 从那里，对已经查看的表进行LEFT-JOIN专门针对有问题的登录用户ID，并且警报ID是相同的。
where子句仅查找那些在查看警报时找不到相应登录用户ID的条目。 对于那些，获取警报的标题和数据。 之后，向用户显示，您可以在查看警报后插入。
我会确保已查看的表在两个键（T1ID，T2ID）上都有一个索引，以帮助优化连接
SELECT T1.Title, T1.Data from YourAlertTable T1 LEFT JOIN AlreadyViewedTable T3 ON T3.T2ID = TheUserIDParameterWhoLoggedIn AND T1.ID = T3.T1ID where T3.T2ID IS NULLThe first thing, you want to start with the alert table as it is applicable to all uers. From there, doing a LEFT-JOIN to the already viewed table specifically for the login user ID in question AND the alert IDs are the same.
The where clause is looking ONLY for those entries that it CAN NOT find the corresponding login user ID as having viewed the alert. For those, get the alert's title and data. After that, and presentation to the user, you can insert after the alerts have been viewed.
I would ensure the already viewed table has an index on both keys (T1ID, T2ID) to help optimize the join
SELECT T1.Title, T1.Data from YourAlertTable T1 LEFT JOIN AlreadyViewedTable T3 ON T3.T2ID = TheUserIDParameterWhoLoggedIn AND T1.ID = T3.T1ID where T3.T2ID IS NULL更多推荐
发布评论