如何在mysql中获得与max(date)对应的字段？

stszievb 于 2021-06-17 发布在 Mysql

关注(0)|答案(4)|浏览(340)

我的两张表如下：

Table 1 : Transaction
TRANS_ID (primary key), TRANS_DATE, TRANS_STATUS, USER_ID (Foreign_Key)

状态更改时，将重复相同的用户id

Table 2 : Users
USER_ID (Primary_Key), USER_NAME, USER_DOB, OTHER_INFO

我想获得用户信息以及最后的交易状态。
我熟悉以下问题。

SELECT MAX(Transaction.TRANS_DATE),Transaction.TRANS_STATUS, Users.USER_NAME, Users.USER_DOB
FROM Users
INNER_JOIN Transaction ON Transaction.USER_ID = Users.USER_ID 
WHERE Transaction.USER_ID = @UserID

我通过了考试 UserID 与 Parameter.AddWithValue . 不幸的是，此方法不返回 TRANS_STATUS 对于 MAX(TRANS_DATE) . 它确实回来了 MAX(TRANS_DATE) 但是 TRANS_STATUS 是第一次出现，不是对应的 STATUS 至 MAX(TRANS_DATE) 记录。
请告诉我怎样才能拿到 TRANS_STATUS 对于 MAX(TRANS_DATE) . 我更喜欢使用 INNER JOIN 但建议是值得赞赏的。
我仍然不能工作。
这是我的表格脚本。

CREATE TABLE `Transactions` (
  `TRANS_ID` int(11) NOT NULL,
  `TRANS_DATE` datetime NOT NULL,
  `TRANS_STATUS` varchar(45) NOT NULL,
  `USER_ID` int(11) NOT NULL,
  PRIMARY KEY (`TRANS_ID`),
  UNIQUE KEY `TRANS_ID_UNIQUE` (`TRANS_ID`),
  KEY `USER_ID_idx` (`USER_ID`),
  CONSTRAINT `USER_ID` FOREIGN KEY (`USER_ID`) REFERENCES `Users` (`USER_ID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `Users` (
  `USER_ID` int(11) NOT NULL AUTO_INCREMENT,
  `USER_NAME` varchar(45) NOT NULL,
  `USER_DOB` datetime NOT NULL,
  `OTHER_INFO` varchar(45) NOT NULL,
  PRIMARY KEY (`USER_ID`),
  UNIQUE KEY `USER_ID_UNIQUE` (`USER_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

mysql Join

来源：https://stackoverflow.com/questions/53484189/how-to-get-corresponding-field-to-maxdate-in-mysql

4条答案

按热度按时间

qltillow1#

如果我没弄错的话这对你有用

SELECT A.USER_ID AS USER_ID, A.TRANS_DATE AS TRANS_DATE, TRANS_STATUS, USER_NAME, USER_DOB
FROM
(SELECT USER_ID, MAX(TRANS_DATE) AS TRANS_DATE FROM TRANSACTION
GROUP BY USER_ID) A
INNER JOIN
(SELECT USER_ID, TRANS_DATE, TRANS_STATUS FROM TRANSACTION) B
ON A.USER_ID = B.USER_ID
AND A.TRANS_DATE=B.TRANS_DATE
INNER JOIN USERS U
ON A.USER_ID=U.USER_ID;

赞(0）回复(0）举报 2021-06-18

rjee0c152#

SELECT Users.USER_NAME, Users.USER_DOB
FROM Users usr INNER JOIN(
    SELECT Transaction.TRANS_STATUS, MAX(Transaction.TRANS_DATE)
    FROM Transaction GROUP BY Transaction.TRANS_STATUS) trs ON trs.USER_ID=usr.USER_ID

赞(0）回复(0）举报 2021-06-18

o7jaxewo3#

您可以在子查询中使用联接以获取max trans\u date

select t.max_trans, t.USER_NAME, t.USER_DOB
    from Transaction 
    INNER JOIN 
    (
        SELECT MAX(Transaction.TRANS_DATE) max_trans, 
             Transaction.USER_ID, Users.USER_NAME, Users.USER_DOB
        FROM Users
        INNER_JOIN Transaction ON Transaction.USER_ID = Users.USER_ID 
        WHERE Transaction.USER_ID = @UserID
    ) t on Transaction.USER_ID = t.USER_ID and t.max_trans = Transaction.TRANS_DATE

赞(0）回复(0）举报 2021-06-18

muk1a3rh4#

您可以使用last\u value函数

SELECT Top 1 
      u.User_ID
     ,u.user_name
     ,u.user_dob
     ,u.other_info
    ,LAST_VALUE(t.Trans_Date) OVER (PARTITION BY t.user_id ORDER BY 
              t.Trans_Date RANGE BETWEEN UNBOUNDED PRECEDING AND
            UNBOUNDED FOLLOWING) AS Max_Tran_Date
    ,LAST_VALUE(t.trans_status) OVER (PARTITION BY t.user_id ORDER BY 
              t.Trans_Date RANGE BETWEEN UNBOUNDED PRECEDING AND
            UNBOUNDED FOLLOWING) as Last_Status
FROM #Users as u
INNER JOIN #Transaction as t ON t.user_id = u.user_id
WHERE u.User_ID = @UserID

赞(0）回复(0）举报 2021-06-17

我来回答

如何在mysql中获得与max(date)对应的字段？

4条答案

相关问题

热门标签

最新问答