4左连接表，我必须在MySQL中获取最小日期和最小日期的创建者
4左连接表，我必须在MySQL中获取最小日期和最小日期的创建者
提问于 2018-05-30 14:10:13
有没有人能告诉我如何获取mindate的另一列的值？
我已经显示了mindate，但是每当我还需要显示'Creator‘列时，所有的结果都在抓取，它没有抓取mindate。
这是我的问题。
如何仅显示最小tr.Created？
create table Tickets (EffectiveId int, Created (datetime), IsMerged);
create table Transactions (ObjectId (varchar64), Created (datetime), Creator
(int),Type(varchar 20);
create table ObjectCustomFieldValues (ObjectId(int), Content(datetime));
create table Users (id (int) , Realname (varchar120);
insert into Tickets values
(549837, 2018-04-02 12:03:23, null);
(612302,2018-04-02 09:46:29, null );
(616982 , 2018-04-02 09:33:24, null);
insert into Transactions values
(549837,'2018-04-05 08:35:22','50','Correspond'),(549837,'2018-04-06 08:35:22','45',,'Correspond'),
(612302,'2018-04-06 01:02:22','12','Correspond'), (612302,'2018-04-08
01:02:22','56', 'Comment'),(612302,'2018-04-10 01:02:22','700','Correspond');
(616982 , '2018-05-03 09:33:24', '10','Correspond');
insert into ObjectCustomFieldValues values
(549837,'2018-04-02 08:35:22');
(612302,'2018-04-02 01:02:22');
(616982,NULL);
insert into Users values
(50, 'Grover');
(45, 'Seb');
(12, 'Jane');
(56, 'Greg');
(700,'Emo');
(10, 'Kade');
#I should compute also the minutes respond from Ticket Created to
Transaction Respond
select t.EffectiveId, t.Created As 'Ticket Created', o.Content,u.Realname
tr.Created As 'Min Correspond',
,Case When o.Content IS NULL Then
TIMESTAMPDIFF(MINUTE,t.Created,MIN(tr.Created)) < 0 THEN 0
ELSE TIMESTAMPDIFF(MINUTE,t.Created,MIN(o.Content)) END AS 'Computed
Minutes'
from Tickets AS t
LEFT JOIN Transactions tr ON tr.ObjectId = t.EffectiveId AND
tr.type='Correspond'
LEFT JOIN ObjectCustomFieldValues o ON o.Objectid = a.EffectiveId
where t.Ismerged IS NULL
+-------------+--------------------+---------------------+------------------
| EffectiveId | Ticket Created | Content | Min Correspond
+-------------+--------------------+---------------------+-----------------
| 549837 | 2018-04-02 12:03:23| 2018-04-02 08:35:22 |2018-04-05 08:35:22
| 612302 | 2018-04-02 09:46:29| 2018-04-02 01:02:22 |2018-04-06 01:02:22
| 616982 | 2018-04-02 09:33:24| NULL |2018-05-03 09:33:24
+-----------+------------------+
| Realname | Computed Minutes |
+-----------+------------------+
| Grover | 12485 |
| Jane | 557687 |
| Kade | 668878 |假设计算的分钟数是正确的计算。
因此，MIN(Created)和creator在同一个表上，但如果我添加Creator，则MIN(created)不起作用。另外，我需要使用case语句来计算分钟数
当内容为空值时，则公式应为，
TIMESTAMPDIFF(MINUTE,t.Created,tr.Created)但是如果内容有值，那么
TIMESTAMPDIFF(MINUTE,t.Created,o.Content)).
But if the result of Computed Minutes is negative then it is 0谢谢。
K.
回答 1
Stack Overflow用户
发布于 2018-05-30 15:59:53
也许这就是子查询查找最小日期的地方
DROP TABLE IF EXISTS T,TR,O,U;
create table T (EffectiveId int, Created datetime, IsMerged int);
create table Tr (ObjectId varchar(64), Created datetime, Creator int,Type varchar (20));
create table O (ObjectId int, Content datetime);
create table U (id int , Realname varchar(120));
insert into T values
(549837, '2018-04-02 12:03:23', null),
(612302,'2018-04-02 09:46:29', null ),
(616982 , '2018-04-02 09:33:24', null);
insert into Tr values
(549837,'2018-04-05 08:35:22','50','Correspond'),(549837,'2018-04-06 08:35:22','45','Correspond'),
(612302,'2018-04-06 01:02:22','12','Correspond'), (612302,'2018-04-08 01:02:22','56', 'Comment'),
(612302,'2018-04-10 01:02:22','700','Correspond'),
(616982 , '2018-05-03 09:33:24', '10','Correspond');
insert into O values
(549837,'2018-04-02 08:35:22'),
(612302,'2018-04-02 01:02:22'),
(616982,NULL);
insert into U values
(50, 'Grover'),
(45, 'Seb'),
(12, 'Jane'),
(56, 'Greg'),
(700,'Emo'),
(10, 'Kade');
select t.EffectiveId, tr.CREATED, tr.creator, o.content,u.realname,
TIMESTAMPDIFF(MINUTE,t.Created,tr.Created) minutes
from t
join tr on tr.objectid = t.EffectiveId
left join o on o.ObjectId = tr.ObjectId
left join u on u.id = tr.Creator
where tr.created = (select min(created) from tr where t.EffectiveId = tr.objectid) ;结果
+-------------+---------------------+---------+---------------------+----------+---------+
| EffectiveId | CREATED | creator | content | realname | minutes |
+-------------+---------------------+---------+---------------------+----------+---------+
| 549837 | 2018-04-05 08:35:22 | 50 | 2018-04-02 08:35:22 | Grover | 4111 |
| 612302 | 2018-04-06 01:02:22 | 12 | 2018-04-02 01:02:22 | Jane | 5235 |
| 616982 | 2018-05-03 09:33:24 | 10 | NULL | Kade | 44640 |
+-------------+---------------------+---------+---------------------+----------+---------+
3 rows in set (0.00 sec)页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/50597441
复制相关文章
相似问题