开发者

How to join nearly identical several queries into one?

Assume I have an order_dummy table where order_dummy_id, order_id, user_i开发者_开发知识库d, book_id, author_id are stored. You may complain the logic of my table but I somehow need to do it that way. I want to execute following queries.

SELECT * 
FROM order_dummy
WHERE order_id = 1
AND user_id = 1
AND book_id = 1
ORDER BY `order_dummy_id` DESC
LIMIT 1

SELECT * 
FROM order_dummy
WHERE order_id = 1
AND user_id = 1
AND book_id = 2
ORDER BY `order_dummy_id` DESC
LIMIT 1

SELECT * 
FROM order_dummy
WHERE order_id = 1
AND user_id = 1
AND book_id = 3
ORDER BY `order_dummy_id` DESC
LIMIT 1

Please keep in mind that several numbers of same book is included in one order. Therefore, I list order_dummy_id by descending and limit 1 so only LATEST ORDER of A BOOK is shown. But my goal is to show other books in that way in one table. I used group by like this ...

SELECT * 
FROM order_dummy
WHERE order_id = 1
AND user_id = 1
GROUP BY book_id

but it only shows order_dummy_id with ascending result. I have no idea anymore. Looking forward your kindness help!


To get the results of multiple selects in one go use UNION ALL:

(
    SELECT * 
    FROM order_dummy
    WHERE order_id = 1
    AND user_id = 1
    AND book_id = 1
    ORDER BY `order_dummy_id` DESC
    LIMIT 1
)
UNION ALL
(
    SELECT * 
    FROM order_dummy
    WHERE order_id = 1
    AND user_id = 1
    AND book_id = 2
    ORDER BY `order_dummy_id` DESC
    LIMIT 1
)
UNION ALL
(
    SELECT * 
    FROM order_dummy
    WHERE order_id = 1
    AND user_id = 1
    AND book_id = 3
    ORDER BY `order_dummy_id` DESC
    LIMIT 1
)


You need to normalize the relationship between your tables. book id should not be in order row since we can have multiple books per order. You need to have a table called "ordered items" and store your books there along with ordered quantity - no need for multiple rows.

To answer your question, I can point you to SQL operator IN:

select * from table where id in (1,2,3)

Also, depending on the context you can use OR operator

select * from table where id = 1 or id = 2 or id =3
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜