如何在mysq中查询排除条件行

apeeds0o 于 2021-06-20 发布在 Mysql

关注(0)|答案(4)|浏览(235)

我提到了以下表格：
表1

ID     cat_id   Date                   key
IY-1   UTV-12   2018-03-18 15:04:25    xxt-14-yt
IY-2   UTV-12   2018-01-01 18:25:19    xxt-15-yt
IY-3   UTV-14   2018-03-05 13:12:14    xxt-17-yt
IY-4   UTV-15   2018-04-03 17:26:11    xxt-14-yt

表2

Key         Value
xxt-14-yt   Pending
xxt-15-yt   Closed
xxt-17-yt   Open

在上表中 cat_id 为特定类别用户分配的唯一id，以及 key Map到状态。
我只想把那些行 cat_id 状态不等于 Closed 状态。
所需输出：

ID     cat_id   Date                   key
IY-3   UTV-14   2018-01-05 13:12:14    xxt-17-yt
IY-4   UTV-15   2018-07-03 17:26:11    xxt-14-yt

我使用下面提到的查询，但它不能正常工作。
查询：

select ID, cat_id, Date, key from Table1 t1
left join Table2 t2 on t1.key=t2.key
where t1.cat_id IN
(select cat_id from Table1 b1
left join Table2 b2 on b1.key=b2.key
where b2.Value!='Closed') and DATE(t1.Date)>='2018-03-01';

mysql

来源：https://stackoverflow.com/questions/52154172/how-to-query-excluding-conditional-rows-in-mysq

4条答案

按热度按时间

vwkv1x7d1#

我没有测试它，但我认为它应该工作：

select * from table1 a 
left join 
    (select cat_id from table1 a 
        join table2 b on a.key = b.Key 
        where b.Value = 'Closed') 
    b on a.cat_id = a.cat_id
where b.cat_id is null

更简单的方法是：

select * from table1
 where cat_id not in
    (select cat_id from table1 where key in
        (select key from table2 where Value = 'Closed'))

赞(0）回复(0）举报 2021-06-20

mwngjboj2#

请检查以下查询：

SELECT t1.ID, t1.cat_id, t1.Date, t1.key FROM
 Table1 t1 
 LEFT JOIN Table2 t2 ON t1.key=t2.key
WHERE t1.Date>='2018-03-01 00:00:00'
 AND t1.cat_id IN (select b1.cat_id FROM Table1 b1
 LEFT JOIN Table2 b2 ON b1.key=b2.key WHERE b2.Value!='Closed')

赞(0）回复(0）举报 2021-06-20

li9yvcax3#

据我所知，你想要唯一唯一的猫的身份证：

Select ID,cat_id,Date,key
From Table1
Where key IN (Select key from table2 where value <> 'Closed')
AND cat_id in (Select cat_id from 
                            (select cat_id,count(*) 
                             from table1 
                             group by cat_id 
                             having count(*)=1
                            ) 
               )

赞(0）回复(0）举报 2021-06-20

vaqhlq814#

尝试反转选择：

SELECT ID, cat_id, Date, key FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.key=t2.key
WHERE t1.cat_id NOT IN
(SELECT cat_id FROM Table1 b1
 LEFT JOIN Table2 b2 ON b1.key=b2.key
 WHERE b2.Value='Closed');

这将排除具有utv-12的两行，因为其中一行被标记为关闭：

赞(0）回复(0）举报 2021-06-20

我来回答

如何在mysq中查询排除条件行

4条答案

相关问题

热门标签

最新问答