当我进行内部联接时，我有2个表事务和trans_comments，它返回第一个条目，我想做的是显示事务表的地址和状态以及最后输入的注释和来自trans_comments表的日期
这就是我得到的
SELECT t.address, t.status, c.time, c.comment
FROM transactions t
INNER JOIN trans_comments c ON c.transactionId = t.transactionId
WHERE STATUS LIKE '%Listing%'
GROUP BY t.address DESC
我想念什么？
这是表格的布局：
`transactions` (
`transactionId` int(11) NOT NULL AUTO_INCREMENT,
`address` varchar(45) NOT NULL,
`listing_agent` varchar(65) DEFAULT NULL,
`status` varchar(45) NOT NULL,
`notes` varchar(100) DEFAULT NULL,
`file_type` varchar(25) NOT NULL,
`system` varchar(25) DEFAULT NULL,
PRIMARY KEY (`transactionId`)
)ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=41 ;
`trans_comments` (
`commId` int(11) NOT NULL AUTO_INCREMENT,
`transactionId` int(11) NOT NULL,
`comment` blob NOT NULL,
`commentBy` varchar(45) NOT NULL,
`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`commId`),
KEY `transactionId` (`transactionId`)
)ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=101 ;
谢谢
SELECT t.address, t.status, c.time, c.comment
FROM transactions t
INNER JOIN
(SELECT c1.*
FROM trans_comments c1
LEFT JOIN trans_comments c2
ON c1.transactionId = c2.transactionId
AND c1.`time` < c2.`time`
WHERE c2.commId IS NULL
) c
ON c.transactionId = t.transactionId
WHERE STATUS LIKE '%Listing%'
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句