带有not not子句的SQL select查询

debugcn 发表于 Dev

垃圾

我有这样的表结构

CREATE TABLE `fb_requests`
(                                              
     `id` int(60) NOT NULL AUTO_INCREMENT,
     `user_id` int(60) DEFAULT NULL,
     `fb_user_id` varchar(255) DEFAULT NULL,
     `request_id` varchar(255) DEFAULT NULL,
     `game_selected` int(60) DEFAULT NULL,
     `accept_status` int(60) DEFAULT NULL COMMENT '0 = pending 1 = accept', 2 = reject
     `created_date` datetime DEFAULT NULL,
     `modified_date` datetime DEFAULT NULL,
     PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=187 DEFAULT CHARSET=latin1

有一个accept_status包含0,1,2状态的列

为了获得所需的数据，我正在编写三个查询，第一个查询获取接受状态= 1行

select 
    fb_user_id, accept_status 
from  
    fb_requests 
where
    user_id = 17 
    and accept_status = 1 
    and game_selected = 2 
group by 
    fb_user_id;

第二个查询获取接受状态= 2

select 
    fb_user_id, accept_status 
from  
    fb_requests 
where
    user_id = 17 
    and accept_status = 2 
    and game_selected = 2 
group by 
    fb_user_id;

在第三个查询中，我需要获取早期查询中没有的行，因此我使用了一个not in子句。

select 
    fb_user_id, accept_status 
from 
    fb_requests 
where
    user_id = 17 
    and accept_status = 0 
    and game_selected = 5 
    and fb_user_id not in (select fb_user_id 
                           from  fb_requests 
                           where user_id = 17
                             and accept_status = 2  
                             and game_selected = 5 
                             and fb_user_id not in (select fb_user_id 
                                                    from fb_requests 
                                                    where user_id = 17
                                                      and accept_status = 1
                                                      and game_selected = 5)
                          )  
group by 
       fb_user_id

但是在第三次查询的情况下，有些事情是不正确的，没有得到想要的结果。不确定这是将两个not in子句组合在一起的方式。我需要获得一个在这些查询之间没有重复的fb_user_id的数组。

我期待这种类型的查询首先

$query = select fb_user_id,accept_status from fb_requests where
                          user_id= 17 and accept_status=0 and game_selected=2 and
                          fb_user_id not in (select fb_user_id from  fb_requests where user_id= 17
                          and accept_status=1  and game_selected=2 )  group by fb_user_id

and then $query not in select fb_user_id from  fb_requests where user_id= 17
                          and accept_status=2  and game_selected=2

请帮助我找到解决方案。谢谢

范ric

试试这个：

select 
    fb_user_id, accept_status 
from 
    fb_requests 
where
    user_id = 17 
    and accept_status = 0 
    and game_selected = 5 
    and fb_user_id not in (select fb_user_id 
                           from fb_requests 
                           where user_id = 17
                             and (accept_status = 2 or accept_status = 1)  
                             and game_selected = 5)  
group by 
    fb_user_id, accept_status

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。