user_id如果必須在一個 MySQL 查詢中使用其中status=1任何一個，我想回傳 1，否則回傳 0product_id 2,3,4
Table: user_status
status_id user_id product_id status
---------------------------------------------
1 27 2 1
2 27 3 0
3 27 4 1
4 32 2 1
5 35 4 1
6 40 2 0
7 40 3 1
8 41 2 0
9 41 3 1
10 45 4 0
我嘗試了以下查詢，
SELECT status FROM `user_status` WHERE `user_id` = '27' AND status = '1' AND product_id IN (2,9,11);
uj5u.com熱心網友回復：
您可以將 EXISTS 用于您的目的，因為它回傳 ture (1) 或 FALSE (0)
CREATE TABLE user_status (`status_id` int, `user_id` int, `product_id` int, `status` int) ; INSERT INTO user_status (`status_id`, `user_id`, `product_id`, `status`) VALUES (1, 27, 2, 1), (2, 27, 3, 0), (3, 27, 4, 1), (4, 32, 2, 1), (5, 35, 4, 1), (6, 40, 2, 0), (7, 40, 3, 1), (8, 41, 2, 0), (9, 41, 3, 1), (10, 45, 4, 0) ;
SELECT Exists(SELECT 1 FROM `user_status` WHERE `user_id` = '27' AND status = '1' AND product_id IN (2 , 9, 11)) answer;
1
SELECT Exists( SELECT 1 FROM `user_status` WHERE `user_id` = '49' AND status = '1' AND product_id IN (2 , 9, 11)) answer
0
db<>在這里擺弄
uj5u.com熱心網友回復：
您可以使用 HAVING 子句來獲得所需的結果
select user_id from user_status where product_id in (2,3,4) group by user_id having max(status) =1
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/504036.html