MySQL INNER JOIN은 두 번째 테이블에서 하나의 행만 선택합니다.

MySQL INNER JOIN은 두 번째 테이블에서 하나의 행만 선택합니다.

---

나는 users테이블과 테이블을 가지고 있으며 payments, 각 사용자에 대해 지불이있는 사용자는 payments테이블 에 여러 관련 지불이있을 수 있습니다 . 결제 한 모든 사용자를 선택하고 최근 결제 만 선택하고 싶습니다. 이 SQL을 시도하고 있지만 이전에 중첩 SQL 문을 시도한 적이 없으므로 내가 뭘 잘못하고 있는지 알고 싶습니다. 도움을 주셔서 감사합니다

SELECT u.* 
FROM users AS u
    INNER JOIN (
        SELECT p.*
        FROM payments AS p
        ORDER BY date DESC
        LIMIT 1
    )
    ON p.user_id = u.id
WHERE u.package = 1

---

에 대한 최신 날짜를 얻으려면 하위 쿼리가 있어야합니다 user ID.

SELECT  a.*, c.*
FROM users a 
    INNER JOIN payments c
        ON a.id = c.user_ID
    INNER JOIN
    (
        SELECT user_ID, MAX(date) maxDate
        FROM payments
        GROUP BY user_ID
    ) b ON c.user_ID = b.user_ID AND
            c.date = b.maxDate
WHERE a.package = 1

---

SELECT u.*, p.*
FROM users AS u
INNER JOIN payments AS p ON p.id = (
    SELECT id
    FROM payments AS p2
    WHERE p2.user_id = u.id
    ORDER BY date DESC
    LIMIT 1
)

이 솔루션은 동일한 사용자 및 날짜로 일부 결제가있을 때 올바르게 작동하기 때문에 허용 된 답변 보다 낫습니다 .

---

SELECT u.*, p.*, max(p.date)
FROM payments p
JOIN users u ON u.id=p.user_id AND u.package = 1
GROUP BY u.id
ORDER BY p.date DESC

이 sqlfiddle을 확인하십시오.

---

   SELECT u.* 
        FROM users AS u
        INNER JOIN (
            SELECT p.*,
             @num := if(@id = user_id, @num + 1, 1) as row_number,
             @id := user_id as tmp
            FROM payments AS p,
                 (SELECT @num := 0) x,
                 (SELECT @id := 0) y
            ORDER BY p.user_id ASC, date DESC)
        ON (p.user_id = u.id) and (p.row_number=1)
        WHERE u.package = 1

---

쿼리에 두 가지 문제가 있습니다.

1. Every table and subquery needs a name, so you have to name the subquery INNER JOIN (SELECT ...) AS p ON ....
2. The subquery as you have it only returns one row period, but you actually want one row for each user. For that you need one query to get the max date and then self-join back to get the whole row.

Assuming there are no ties for payments.date, try:

    SELECT u.*, p.* 
    FROM (
        SELECT MAX(p.date) AS date, p.user_id 
        FROM payments AS p
        GROUP BY p.user_id
    ) AS latestP
    INNER JOIN users AS u ON latestP.user_id = u.id
    INNER JOIN payments AS p ON p.user_id = u.id AND p.date = latestP.date
    WHERE u.package = 1

---

Matei Mihai given a simple and efficient solution but it will not work until put a MAX(date) in SELECT part so this query will become:

SELECT u.*, p.*, max(date)
FROM payments p
JOIN users u ON u.id=p.user_id AND u.package = 1
GROUP BY u.id

And order by will not make any difference in grouping but it can order the final result provided by group by. I tried it and it worked for me.

---

@John Woo's answer helped me solve a similar problem. I've improved upon his answer by setting the correct ordering as well. This has worked for me:

SELECT  a.*, c.*
FROM users a 
    INNER JOIN payments c
        ON a.id = c.user_ID
    INNER JOIN (
        SELECT user_ID, MAX(date) as maxDate FROM
        (
            SELECT user_ID, date
            FROM payments
            ORDER BY date DESC
        ) d
        GROUP BY user_ID
    ) b ON c.user_ID = b.user_ID AND
           c.date = b.maxDate
WHERE a.package = 1

I'm not sure how efficient this is, though.

---

My answer directly inspired from @valex very usefull, if you need several cols in the ORDER BY clause.

    SELECT u.* 
    FROM users AS u
    INNER JOIN (
        SELECT p.*,
         @num := if(@id = user_id, @num + 1, 1) as row_number,
         @id := user_id as tmp
        FROM (SELECT * FROM payments ORDER BY p.user_id ASC, date DESC) AS p,
             (SELECT @num := 0) x,
             (SELECT @id := 0) y
        )
    ON (p.user_id = u.id) and (p.row_number=1)
    WHERE u.package = 1

참고URL : https://stackoverflow.com/questions/12526194/mysql-inner-join-select-only-one-row-from-second-table