MySQL ORDEN POR múltiples columnas ASC y DESC

Tengo 2 tablas de MYSQL, usuarios y puntuaciones. Detalle:

  • tabla de usuarios:

introduzca la descripción de la imagen aquí

  • tabla de puntuaciones:

introduzca la descripción de la imagen aquí

Mi intención es obtener 20 lista de usuarios que tienen punto campo sort DESC (descendente) combine avg_time campo sort ASC (ascendente). Utilizo la consulta: 

SELECT users.username, scores.point, scores.avg_time
FROM scores, users
WHERE scores.user_id = users.id
GROUP BY users.username
ORDER BY scores.point DESC, scores.avg_time
LIMIT 0, 20

El resultado es:

introduzca la descripción de la imagen aquí

El resultado es incorrecto porque la primera línea es exactamente punto = 100 y avg_time = 60.

Mi resultado deseado es:

username    point    avg_time
demo123      100        60
demo123456   100       100
demo         90        120

He intentado muchas veces con diferentes consultas, pero el resultado sigue siendo incorrecto. ¿Podría darme algunas soluciones?

Gracias de antemano!

Author: Tan Viet, 2012-12-04
Source

4 answers

Ok, CREO que entiendo lo que quiere ahora, y permítanme aclarar para confirmar antes de la consulta. Desea 1 registro para cada usuario. Para cada usuario, desea su MEJOR registro de puntuación de PUNTOS. De los mejores puntos por usuario, desea el que tenga el mejor tiempo promedio. Una vez que tenga todos los usuarios "mejores" valores, desea que los resultados finales se ordenen con los mejores puntos primero... Casi como el ranking de una competición.

Así que ahora la consulta. Si la declaración anterior es precisa, debe comenzar con obtener el mejor punto / tiempo promedio por persona y asignar un "Rango" a esa entrada. Esto se hace fácilmente usando MySQL @ variables. Luego, solo incluya una cláusula de TENER para mantener solo esos registros clasificados 1 para cada persona. Finalmente aplicar el orden por de los mejores puntos y el tiempo promedio más corto.

select
      U.UserName,
      PreSortedPerUser.Point,
      PreSortedPerUser.Avg_Time,
      @UserRank := if( @lastUserID = PreSortedPerUser.User_ID, @UserRank +1, 1 ) FinalRank,
      @lastUserID := PreSortedPerUser.User_ID
   from
      ( select
              S.user_id,
              S.point,
              S.avg_time
           from
              Scores S
           order by
              S.user_id,
              S.point DESC,
              S.Avg_Time ) PreSortedPerUser
         JOIN Users U
            on PreSortedPerUser.user_ID = U.ID,
      ( select @lastUserID := 0,
               @UserRank := 0 ) sqlvars 
   having
      FinalRank = 1
   order by
      Point Desc,
      Avg_Time

Resultados manejados por SQLFiddle

Nota, debido a las variables inline @necesarias para obtener la respuesta, hay dos columnas adicionales al final de cada fila. Estos son sólo "sobrante" y puede ser ignorado en cualquier presentación de salida real que esté tratando de hacer... O, puede envolver todo por encima de un nivel más para obtener las pocas columnas que desea como

select 
      PQ.UserName,
      PQ.Point,
      PQ.Avg_Time
   from
      ( entire query above pasted here ) as PQ
 23
Author: DRapp,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2012-12-04 11:39:11

Creo que u miss entender acerca de la relación de tabla..

Usuarios: scores = 1: *

Solo join no es una solución.

Es esta su intención?

SELECT users.username, avg(scores.point), avg(scores.avg_time)
FROM scores, users
WHERE scores.user_id = users.id
GROUP BY users.username
ORDER BY avg(scores.point) DESC, avg(scores.avg_time)
LIMIT 0, 20

(esta consulta para obtener cada punto promedio de los usuarios y promedio avg_time por punto desc, asc) avg_time

Si desea obtener cada puntuación de clasificación? use left outer join 

SELECT users.username, scores.point, scores.avg_time
FROM scores left outer join users on scores.user_id = users.id
ORDER BY scores.point DESC, scores.avg_time
LIMIT 0, 20
 1
Author: namxee,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2012-12-04 02:33:22

@DrApp es un genio. Nunca entendí cómo codificó su SQL, así que intenté codificarlo en mi propio entendimiento.


    SELECT 
      f.username,
      f.point,
      f.avg_time
    FROM
      (
      SELECT
        userscores.username,
        userscores.point,
        userscores.avg_time
      FROM
        (
        SELECT
          users.username,
          scores.point,
          scores.avg_time
        FROM
          scores
        JOIN users
        ON scores.user_id = users.id
        ORDER BY scores.point DESC
        ) userscores
      ORDER BY
        point DESC,
        avg_time
      ) f
    GROUP BY f.username
    ORDER BY point DESC

Produce el mismo resultado usando GROUP BY en lugar de las variables user@.

 1
Author: Rad Apdal,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2014-07-02 15:19:41

Grupo por orden predeterminado por pk id，por lo que el resultado
nombre de usuario punto avg_time
demo123 100 90 ---> id = 4
demo123456 100 100 ---> id = 7
demo 90 120 --- > id = 1

 0
Author: babaoqi,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2012-12-04 08:57:28