如何在MySQL中订购/限制内部联接？

I am bad at SQL. I need to get certain information out of my database. This information is a list of User ID's and their username under a single account. There are 3 tables involved.

CUSTOMER

• customer_id

CUSTOMER_RELATIONSHIP

• relationship_type
• child_id
• parent_id

USER_NAME

• user_name -- String value of username
• customer_id -- foreign key
• start_stamp -- timestamp
• end_stamp -- timestamp

My USER_NAME database has many entries in it because it is possible to change a username. The start_stamp is when user_name has been activated, end_stamp is when it has ended. Usually, on a user that is open, there is an entry in the database where end_stamp is null (because user_name is currently active). However, if a user is closed, USER_NAME.end_stamp may not be null because the username is recently retired. Initially I was running a query where end_stamp is null, but I was not seeing the closed users under my account. I was wondering if there is a way to return the most recent user_name from my user_name table ( and only returning one ). Here is my query:

select distinct
    users.customer_id,
    accounts.customer_id,
    USER_NAME.user_name
from CUSTOMER users
    inner join CUSTOMER_RELATIONSHIP cr on cr.child_id = users.customer_id
        and cr.relationship_type = 1 -- Account/User relationship
    inner join CUSTOMER accounts on accounts.customer_id = cr.parent_id
        and accounts.customer_id = 25 -- given ID
    inner join USER_NAME on USER_NAME.customer_id = users.customer_id
        and USER_NAME.end_stamp is null

This is not returning usernames for closed users. If I remove the "and USER_NAME.end_stamp is null" it will return data I do not want ( usernames that are expired ). I do, however, want to be able to see the most recently associated user_name for a closed user.

How can I write a query that will order that USER_NAME inner join and get the entry with either 1) a null end_stamp or 2) the most recent start_stamp?

I would suggest a correlated subquery:

select c.*,
       (select un.user_name
        from user_name un
        where un.customer_id = c.customer_id
        order by (end_stamp is null) desc, end_stamp desc
        limit 1
       ) as most_recent_user_name
from customers c;

It sounds like you want the most recent name for each user, whether they are closed or not. If that's the case, then you can do this:

SELECT customer_id, acct_cust_id, user_name
FROM (
  select DISTINCT
      users.customer_id,
      accounts.customer_id AS acct_cust_id,
      USER_NAME.user_name,
      ROW_NUMBER() OVER(PARTITION BY users.customer_id ORDER BY USER_NAME.end_stamp DESC) AS MyRowNum
  from CUSTOMER users
      inner join CUSTOMER_RELATIONSHIP cr on cr.child_id = users.customer_id
          and cr.relationship_type = 1 -- Account/User relationship
      inner join CUSTOMER accounts on accounts.customer_id = cr.parent_id
          and accounts.customer_id = 25 -- given ID
      inner join USER_NAME on USER_NAME.customer_id = users.customer_id
    ) src
    WHERE MyRowNum = 1  

• Removed the end_stamp IS NULL clause in the last INNER JOIN
• added ROW_NUMBER to rank UserName values for each customer_id by end_stamp); not sure what DB you're on, but you may need more logic in the ORDER BY clause to make sure the rows with NULL customer_id values get ranked at the top

You may also be able to remove that DISTINCT in there.