我可以对我的 SQL 命令使用一些帮助 [英] I could use some help with my SQL command
问题描述
我有一个名为mesg"的数据库表，其结构如下:
receiver_id |sender_id |留言 |时间戳 |阅读
示例:
2 *(«me)* |6 *(«好女孩)* |'我喜欢你，胜过哥蒂' |多年前 |1 *(«看过)*2 *(«我)* |6 *(«好女孩)* |'我喜欢你，胜过鱼' |现在 |1 *(«看过)*6 *(«好女孩)* |2 *(«我)* |嘿，想去钓鱼吗?"|年前+1秒 |0 *(«她没看过)*这是我想要实现的非常棘手的事情.
我想获得:最新消息(=按时间 DESC 排序)+联系人姓名"+ 每次对话"的时间.
- 联系人姓名 = uname WHERE uid = '联系人 ID'(用户名在另一个表中)
- 联系人 ID = if(sessionID*(«me)*=sender_id){receiver_id}else{sender_id}
- 对话是我 = 接收者或我=发送者
例如:
From: **Bas Kreuntjes** *(« 来自 bas 的消息是最新的)*嘿$py，你好吗...来自:**Sophie Naarden** *(« 最近的第二次)*你好，你想买我的垃圾邮件吗?... *(«我稍后会处理这个 >p)*致:**Melanie van Deijk** *(« 我给 Melanie 的消息是第 3 条)*和?你吻过他吗?...这是一个粗略的输出.
问题:有人可以帮我设置一个好的 SQL 命令.这将是 while loup
<div class="message-block"><h1><?php echo($fetch['uname']);?></h1><p><?php echo($fetch['message']);?></p><p><?php echo($fetch['time']);?></p><?php } ?>
我希望我的解释足够好，如果不是，请告诉我.请不要介意我的英语和荷兰人的名字(我自己是荷兰人)随时纠正我的英语
UPDATE1: 迄今为止我得到的最好的:但我不希望出现不止一个对话......u=用户表m=消息表
SELECT u.uname, m.message, m.receiver_uid, m.sender_uid, m.time从米，你WHERE (m.receiver_uid = '$myID' AND u.uid = m.sender_uid)或 (m.sender_uid = '$myID' AND u.uid = m.receiver_uid)按时间排序 DESC；繁琐...但可行.一次构建一个答案.
构建查询 - 一步一步
给定示例中的特定参考用户(我")，您需要查找我"和另一个人之间的所有消息.对于每个这样的其他人，您希望找到具有最新时间戳的一条消息.
查询 1
让我们建立一个对话 ID，将所有消息链接在一起.由于我们一次只对一方(我")感兴趣，因此我们可以使用另一方的 ID 来识别对话.
SELECT m.recipient_uid AS 对话，m.recipient_uid 作为recipient_uid，m.sender_uid AS sender_uid,m.timestamp AS时间戳FROM 消息 AS 米WHERE m.sender_uid = '$myID'联盟SELECT m.sender_uid AS 对话，m.recipient_uid 作为recipient_uid，m.sender_uid AS sender_uid,m.timestamp AS时间戳FROM 消息 AS 米WHERE m.recipient_uid = '$myID'这允许您将与具有相同对话 ID 的个人的所有聊天串连在一起.
查询 2
现在您需要按对话分组以找到对话和最近的时间；为此，您不需要其他(recipient_uid 或 sender_uid)列:
SELECT 对话，MAX(timestamp) AS max_timestampFROM (SELECT m.recipient_uid AS 对话，m.timestamp AS时间戳FROM 消息 AS 米WHERE m.sender_uid = '$myID'联盟SELECT m.sender_uid AS 对话，m.timestamp AS时间戳FROM 消息 AS 米WHERE m.recipient_uid = '$myID') AS cGROUP BY 对话查询 3
因此，对于每个对话，我们现在知道最近的时间戳.我们只需将该信息与之前的查询结合起来即可获得大部分详细信息:
SELECT c.recipient_uid, c.sender_uid, c.timestampFROM (SELECT 对话, MAX(timestamp) AS max_timestampFROM (SELECT m.recipient_uid AS 对话，m.timestamp AS时间戳FROM 消息 AS 米WHERE m.sender_uid = '$myID'联盟SELECT m.sender_uid AS 对话，m.timestamp AS时间戳FROM 消息 AS 米WHERE m.recipient_uid = '$myID') AS cGROUP BY 对话) 作为 x加入(选择 m.recipient_uid AS 对话，m.recipient_uid 作为recipient_uid，m.sender_uid AS sender_uid,m.timestamp AS时间戳FROM 消息 AS 米WHERE m.sender_uid = '$myID'联盟SELECT m.sender_uid AS 对话，m.recipient_uid 作为recipient_uid，m.sender_uid AS sender_uid,m.timestamp AS时间戳FROM 消息 AS 米WHERE m.recipient_uid = '$myID') 作为 cON c.conversation = x.conversation AND c.timestamp = x.max_timestamp查询 4
既然你说你也想要名字，你可以扩展它两次加入用户表:
SELECT c.recipient_uid, c.sender_uid, c.timestamp,u1.uname AS 收件人，u2.uname AS 发件人FROM (SELECT 对话, MAX(timestamp) AS max_timestampFROM (SELECT m.recipient_uid AS 对话，m.timestamp AS时间戳FROM 消息 AS 米WHERE m.sender_uid = '$myID'联盟SELECT m.sender_uid AS 对话，m.timestamp AS时间戳FROM 消息 AS 米WHERE m.recipient_uid = '$myID') AS cGROUP BY 对话) 作为 x加入(选择 m.recipient_uid AS 对话，m.recipient_uid 作为recipient_uid，m.sender_uid AS sender_uid,m.timestamp AS时间戳FROM 消息 AS 米WHERE m.sender_uid = '$myID'联盟SELECT m.sender_uid AS 对话，m.recipient_uid 作为recipient_uid，m.sender_uid AS sender_uid,m.timestamp AS时间戳FROM 消息 AS 米WHERE m.recipient_uid = '$myID') 作为 cON c.conversation = x.conversation AND c.timestamp = x.max_timestamp加入用户作为 u1ON u1.uid = c.recipient_uid加入用户作为 u2ON u2.uid = c.sender_uid那里 - 这很有趣.我不想一口气写完，而是一次构建一个，它不会太吓人(虽然它不是微不足道的).
测试架构
用户表
创建表用户(uid 整数非空主键，uname VARCHAR(30) 非空);消息表
DATETIME YEAR TO SECOND 是一种编写 TIMESTAMP 的有趣方式(在 IBM Informix Dynamic Server 中 - 我在那里进行了测试).
创建表消息(接收者_uid INTEGER NOT NULL REFERENCES user(uid),sender_uid INTEGER NOT NULL REFERENCES user(uid),消息 VARCHAR(255) 非空，时间戳日期时间年到第二年不为空，PRIMARY KEY (recipient_uid, sender_uid, 时间戳),读取字符(1) 非空);用户数据
INSERT INTO USER VALUES(2, '我的全名');INSERT INTO USER VALUES(6, '她的全名');INSERT INTO USER VALUES(3, 'Dag Brunner');消息数据
INSERT INTO mesg VALUES(2, 6, 'I like you, more than ghti', '2008-01-01 00:05:03', 1);INSERT INTO mesg VALUES(2, 6, '我喜欢你，胜过鱼', '2011-01-15 13:45:09', 1);INSERT INTO mesg VALUES(6, 2, '嘿，想去钓鱼吗?', '2008-01-01 09:30:47', 0);INSERT INTO mesg VALUES(2, 3, '想喝啤酒吗?', '2011-01-14 13:45:09', 1);INSERT INTO mesg VALUES(3, 2, 'Sounds good to me!!', '2011-01-14 13:55:39', 1);INSERT INTO mesg VALUES(3, 6, '现在回家了吗???', '2010-12-31 12:27:41', 1);INSERT INTO mesg VALUES(6, 3, 'Yes - on the bus!!!', '2010-12-31 13:55:39', 1);结果 1
Conv Recv 发送时间3 2 3 2011-01-14 13:45:093 3 2 2011-01-14 13:55:396 2 6 2008-01-01 00:05:036 2 6 2011-01-15 13:45:096 6 2 2008-01-01 09:30:47结果 2
Conv 最近的3 2011-01-14 13:55:396 2011-01-15 13:45:09结果 3
接收发送时间3 2 2011-01-14 13:55:392 6 2011-01-15 13:45:09结果 4
Recv Send When Receiver Sender3 2 2011-01-14 13:55:39 Dag Brunner 我的全名2 6 2011-01-15 13:45:09 我的全名 她的全名哇，我第一次得到这么复杂的 SQL 并不常见，但在这种情况下，除了在第一次迭代中遇到receiver_uid 和recipient_uid 之外，它确实首先正常工作时间.
<小时>一般解决方案
省略'who'(或'me'，或'$myID')参数，我们可以为两个人之间的任何对话中的最新消息提出一个通用的解决方案.对话由较高和较低(或相反)的参与者 ID 标识.否则，它与上一个非常相似.
SELECT c.recipient_uid, c.sender_uid, c.timestamp,u1.uname AS 收件人，u2.uname AS 发件人FROM (SELECT conv01, conv02, MAX(timestamp) AS max_timestampFROM (SELECT m.recipient_uid AS conv01,m.sender_uid AS conv02，m.timestamp AS时间戳FROM 消息 AS 米WHERE m.sender_uid 样本数据的结果
Recv Send when Recipient Sender6 3 2010-12-31 13:55:39 她的全名 Dag Brunner2 6 2011-01-15 13:45:09 我的全名 她的全名3 2 2011-01-14 13:55:39 Dag Brunner 我的全名I've got a database table called 'mesg' with the following structure:
receiver_id | sender_id | message | timestamp | read
Example:
2 *(«me)* | 6 *(«nice girl)* | 'I like you, more than ghoti' | yearsago | 1 *(«seen it)*
2 *(«me)* | 6 *(«nice girl)* | 'I like you, more than fish' | now | 1 *(«seen it)*
6 *(«nice girl)* | 2 *(«me)* | 'Hey, wanna go fish?' | yearsago+1sec | 0 *(«she hasn't seen it)*
It's quite a tricky thing that I want to achieve.
I want to get: the most recent message(=ORDER BY time DESC) + 'contact name' + time for each 'conversation'.
- Contact name = uname WHERE uid = 'contact ID' (the username is in another table)
- Contact ID = if(sessionID*(«me)*=sender_id){receiver_id}else{sender_id}
- Conversation is me = receiver OR me = sender
For example:
From: **Bas Kreuntjes** *(« The message from bas is the most recent)*
Hey $py, How are you doing...
From: **Sophie Naarden** *(« Second recent)*
Well hello, would you like to buy my spam? ... *(«I'll work on that later >p)*
To: **Melanie van Deijk** *(« My message to Melanie is 3th)*
And? Did you kiss him? ...
That is a rough output.
QUESTION: Could someone please help me setup a good SQL command. This will be the while loup
<?php
$sql = "????";
$result = mysql_query($sql);
while($fetch = mysql_fetch_assoc($result)){ ?>
<div class="message-block">
<h1><?php echo($fetch['uname']); ?></h1>
<p><?php echo($fetch['message']); ?></p>
<p><?php echo($fetch['time']); ?></p>
</div>
<?php } ?>
I hope my explanation is good enough, if not, please tell me. Please don't mind my English, and the Dutch names (I am Dutch myself) Feel free to correct my English
UPDATE1: Best I've got until now: But I do not want more than one conversation to show up... u=user table m=message table
SELECT u.uname, m.message, m.receiver_uid, m.sender_uid, m.time
FROM m, u
WHERE (m.receiver_uid = '$myID' AND u.uid = m.sender_uid)
OR (m.sender_uid = '$myID' AND u.uid = m.receiver_uid)
ORDER BY time DESC;
Fiddly...but doable. Build the answer up one piece at a time.
Constructing the query - step by step
Given a particular reference user ('me') in the examples, you need to find all messages between 'me' and another person. For each such other person, you want to find the one message with the most recent time stamp.
Query 1
Let's get a conversation ID established, linking all messages together. Since we're interested in one party ('me') at a time, we can use the other person's ID to identify the conversation.
SELECT m.recipient_uid AS conversation,
m.recipient_uid AS recipient_uid,
m.sender_uid AS sender_uid,
m.timestamp AS timestamp
FROM mesg AS m
WHERE m.sender_uid = '$myID'
UNION
SELECT m.sender_uid AS conversation,
m.recipient_uid AS recipient_uid,
m.sender_uid AS sender_uid,
m.timestamp AS timestamp
FROM mesg AS m
WHERE m.recipient_uid = '$myID'
That allows you to thread all the chats with an individual together with the same conversation ID.
Query 2
Now you need to group that by conversation to find the conversation and the most recent time; for this, you don't need the other (recipient_uid or sender_uid) columns:
SELECT conversation, MAX(timestamp) AS max_timestamp
FROM (SELECT m.recipient_uid AS conversation,
m.timestamp AS timestamp
FROM mesg AS m
WHERE m.sender_uid = '$myID'
UNION
SELECT m.sender_uid AS conversation,
m.timestamp AS timestamp
FROM mesg AS m
WHERE m.recipient_uid = '$myID') AS c
GROUP BY conversation
Query 3
So, for each conversation, we now know the most recent timestamp. We just have to join that information back with the previous query to get the majority of the details:
SELECT c.recipient_uid, c.sender_uid, c.timestamp
FROM (SELECT conversation, MAX(timestamp) AS max_timestamp
FROM (SELECT m.recipient_uid AS conversation,
m.timestamp AS timestamp
FROM mesg AS m
WHERE m.sender_uid = '$myID'
UNION
SELECT m.sender_uid AS conversation,
m.timestamp AS timestamp
FROM mesg AS m
WHERE m.recipient_uid = '$myID') AS c
GROUP BY conversation
) AS x
JOIN (SELECT m.recipient_uid AS conversation,
m.recipient_uid AS recipient_uid,
m.sender_uid AS sender_uid,
m.timestamp AS timestamp
FROM mesg AS m
WHERE m.sender_uid = '$myID'
UNION
SELECT m.sender_uid AS conversation,
m.recipient_uid AS recipient_uid,
m.sender_uid AS sender_uid,
m.timestamp AS timestamp
FROM mesg AS m
WHERE m.recipient_uid = '$myID'
) AS c
ON c.conversation = x.conversation AND c.timestamp = x.max_timestamp
Query 4
Since you said you wanted names as well, you can extend that to join with the users table twice:
SELECT c.recipient_uid, c.sender_uid, c.timestamp,
u1.uname AS recipient, u2.uname AS sender
FROM (SELECT conversation, MAX(timestamp) AS max_timestamp
FROM (SELECT m.recipient_uid AS conversation,
m.timestamp AS timestamp
FROM mesg AS m
WHERE m.sender_uid = '$myID'
UNION
SELECT m.sender_uid AS conversation,
m.timestamp AS timestamp
FROM mesg AS m
WHERE m.recipient_uid = '$myID') AS c
GROUP BY conversation
) AS x
JOIN (SELECT m.recipient_uid AS conversation,
m.recipient_uid AS recipient_uid,
m.sender_uid AS sender_uid,
m.timestamp AS timestamp
FROM mesg AS m
WHERE m.sender_uid = '$myID'
UNION
SELECT m.sender_uid AS conversation,
m.recipient_uid AS recipient_uid,
m.sender_uid AS sender_uid,
m.timestamp AS timestamp
FROM mesg AS m
WHERE m.recipient_uid = '$myID'
) AS c
ON c.conversation = x.conversation AND c.timestamp = x.max_timestamp
JOIN user AS u1
ON u1.uid = c.recipient_uid
JOIN user AS u2
ON u2.uid = c.sender_uid
There - that's fun. I wouldn't want to write that in one go, but built up a piece at a time, it isn't too intimidating (though it isn't trivial).
Test Schema
User table
CREATE TABLE user
(
uid INTEGER NOT NULL PRIMARY KEY,
uname VARCHAR(30) NOT NULL
);
Mesg Table
DATETIME YEAR TO SECOND is a funny way of writing TIMESTAMP (in IBM Informix Dynamic Server - which is where I tested this).
CREATE TABLE mesg
(
recipient_uid INTEGER NOT NULL REFERENCES user(uid),
sender_uid INTEGER NOT NULL REFERENCES user(uid),
message VARCHAR(255) NOT NULL,
timestamp DATETIME YEAR TO SECOND NOT NULL,
PRIMARY KEY (recipient_uid, sender_uid, timestamp),
READ CHAR(1) NOT NULL
);
User Data
INSERT INTO USER VALUES(2, 'My Full Name');
INSERT INTO USER VALUES(6, 'Her Full Name');
INSERT INTO USER VALUES(3, 'Dag Brunner');
Mesg Data
INSERT INTO mesg VALUES(2, 6, 'I like you, more than ghoti', '2008-01-01 00:05:03', 1);
INSERT INTO mesg VALUES(2, 6, 'I like you, more than fish', '2011-01-15 13:45:09', 1);
INSERT INTO mesg VALUES(6, 2, 'Hey, wanna go fish?', '2008-01-01 09:30:47', 0);
INSERT INTO mesg VALUES(2, 3, 'Wanna catch a beer?', '2011-01-14 13:45:09', 1);
INSERT INTO mesg VALUES(3, 2, 'Sounds good to me!!', '2011-01-14 13:55:39', 1);
INSERT INTO mesg VALUES(3, 6, 'Heading home now???', '2010-12-31 12:27:41', 1);
INSERT INTO mesg VALUES(6, 3, 'Yes - on the bus!!!', '2010-12-31 13:55:39', 1);
Results 1
Conv Recv Send When
3 2 3 2011-01-14 13:45:09
3 3 2 2011-01-14 13:55:39
6 2 6 2008-01-01 00:05:03
6 2 6 2011-01-15 13:45:09
6 6 2 2008-01-01 09:30:47
Results 2
Conv Most Recent
3 2011-01-14 13:55:39
6 2011-01-15 13:45:09
Results 3
Recv Send When
3 2 2011-01-14 13:55:39
2 6 2011-01-15 13:45:09
Results 4
Recv Send When Receiver Sender
3 2 2011-01-14 13:55:39 Dag Brunner My Full Name
2 6 2011-01-15 13:45:09 My Full Name Her Full Name
Wow, it isn't often I get a piece of SQL that is this complex right on the first go, but on this occasion, apart from a trip up over receiver_uid vs recipient_uid in the first iteration, it did work correctly first time.
General Solution
Omitting the 'who' (or 'me', or '$myID') parameter, we can come up with a general solution for the latest message in any of the conversations between two people. The conversation is identified by the higher and lower (or vice versa) participant ID. Otherwise, it is very similar to the previous one.
SELECT c.recipient_uid, c.sender_uid, c.timestamp,
u1.uname AS recipient, u2.uname AS sender
FROM (SELECT conv01, conv02, MAX(timestamp) AS max_timestamp
FROM (SELECT m.recipient_uid AS conv01,
m.sender_uid AS conv02,
m.timestamp AS timestamp
FROM mesg AS m
WHERE m.sender_uid < m.recipient_uid
UNION
SELECT m.sender_uid AS conv01,
m.recipient_uid AS conv02,
m.timestamp AS timestamp
FROM mesg AS m
WHERE m.sender_uid > m.recipient_uid
) AS C
GROUP BY conv01, conv02
) AS x
JOIN (SELECT m.recipient_uid AS conv01,
m.sender_uid AS conv02,
m.recipient_uid AS recipient_uid,
m.sender_uid AS sender_uid,
m.timestamp AS timestamp
FROM mesg AS m
WHERE m.sender_uid < m.recipient_uid
UNION
SELECT m.sender_uid AS conv01,
m.recipient_uid AS conv02,
m.recipient_uid AS recipient_uid,
m.sender_uid AS sender_uid,
m.timestamp AS timestamp
FROM mesg AS m
WHERE m.sender_uid > m.recipient_uid
) AS C
ON c.conv01 = x.conv01 AND c.conv02 = x.conv02
AND c.timestamp = x.max_timestamp
JOIN USER AS u1
ON u1.uid = C.recipient_uid
JOIN USER AS u2
ON u2.uid = C.sender_uid;
Result with sample data
Recv Send When Recipient Sender
6 3 2010-12-31 13:55:39 Her Full Name Dag Brunner
2 6 2011-01-15 13:45:09 My Full Name Her Full Name
3 2 2011-01-14 13:55:39 Dag Brunner My Full Name
这篇关于我可以对我的 SQL 命令使用一些帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
