Geek asks and answers

Mysql integrates with sorting

I have two tables: user and library. The library contains books sorted in a specific way; the user can choose to sort their books in a specific way. the structure (and sample data) for the two tables will look like this:

Library

bookid position
10 1
12 2
14 3
16 4

user

userid bookid position 12669
1
12669 2

I want the query to return all books for the user, 12669, sorted by ie:


select bookid from the user, where userid = 12669 group by position

After he returns these sorted books, he should return other books (not present in the user) that are in the library. No brochure should be repeated. The result of this script will look like this:

12
10
14
16

: , , , user.position

, - . :
bookid u right join library l u.bookid = l.bookid, u.userid = 12669 u.position

. ""? .

+3
4

:)

SELECT l.bookid
FROM library l
LEFT OUTER JOIN user u on u.bookid = l.bookid
WHERE u.userid = 12669
ORDER BY isnull(u.position, 99999), l.position
+1

, , .

-, GROUP BY, ORDER BY.

. @Romil, USER WHERE . USER.

, , , USER.POSITION. IFNULL , USER. USER.POSITION, LIBRARY.POSITION .

select l.bookid 
from ( select * from user
        where userid = 12669 ) u
   right join library l 
     on (u.bookid = l.bookid) 
order by ifnull(u.position, 999999999) , l.position

NB: " " , 999999999 , ,.

" . ."

, . SqlFiddle, . : ?

+1
SELECT library.bookid bookid 
FROM   library 
       LEFT JOIN (SELECT * 
                  FROM   [user] 
                  WHERE  [USER].userid = 12669) users 
         ON [USERs].bookid = library.bookid 
ORDER  BY [USERs].userid DESC, 
          [USERs].position, 
          library.position
0

APC, Romil aF. , , , . :

(
SELECT bookid
FROM user
WHERE userid = 12669
ORDER BY position
)
UNION (
SELECT bookid
FROM library
)

The first query selects a booklet of ordered users, and the union operator selects every other book from the library and adds it to the result set. The location of this later group was not important.

0
source

More articles:


All Articles