一个简单的sql语句想了1小时未果..求解答

[Sunny_kaka]

看起来非常简单的需求:
+------+---------+----------+--------+------+---------+-------+
| id | groupId | tagLibId | bySort | buyerId | ownerId | tagId |
+------+---------+----------+--------+------+---------+-------+
| 2001 | 1 | 1 | 0 | 1 | 16 | 2001 |
| 2007 | 2 | 11 | 0 | 2 | 16 | 2007 |
| 2008 | 2 | 12 | 0 | 3 | 16 | 2008 |
| 2014 | 3 | 22 | 0 | 4 | 16 | 2014 |
| 2013 | 3 | 21 | 0 | 6 | 16 | 2013 |
| 2002 | 1 | 2 | 0 | 14 | 16 | 2002 |
| 2003 | 1 | 3 | 0 | 15 | 16 | 2003 |
| 2018 | 4 | 51 | 0 | 16 | 16 | 2018 |
| 2004 | 1 | 4 | 0 | 17 | 16 | 2004 |
| 2009 | 2 | 13 | 0 | 18 | 16 | 2009 |
| 2001 | 1 | 1 | 0 | 25 | 1001 | 2001 |
| 2007 | 2 | 11 | 0 | 26 | 1001 | 2007 |
| 2002 | 1 | 2 | 0 | 30 | 1002 | 2002 |
| 2019 | 4 | 52 | 0 | 31 | 1002 | 2019 |
| 2008 | 2 | 12 | 0 | 35 | 1002 | 2008 |
| 2054 | 13 | 2 | 0 | 1001 | 1501 | 2054 |
| 2029 | 11 | 51 | 0 | 1002 | 1501 | 2029 |
| 2045 | 12 | 71 | 0 | 1003 | 1501 | 2045 |
| 2046 | 12 | 72 | 0 | 1004 | 1501 | 2046 |
| 2030 | 11 | 52 | 0 | 1005 | 1501 | 2030 |
| 2059 | 21 | 82 | 0 | 1007 | 2001 | 2059 |
| 2060 | 21 | 83 | 0 | 1008 | 2001 | 2060 |
| 2065 | 31 | 94 | 0 | 1009 | 2002 | 2065 |
| 2062 | 31 | 91 | 0 | 1010 | 2002 | 2062 |
| 2063 | 31 | 92 | 0 | 1011 | 2002 | 2063 |
| 2018 | 4 | 51 | 0 | 1501 | 1001 | 2018 |
| 2022 | 5 | 41 | 0 | 1502 | 1001 | 2022 |
| 2013 | 3 | 21 | 0 | 1503 | 1001 | 2013 |
| 2025 | 5 | 44 | 0 | 1504 | 1002 | 2025 |
| 2014 | 3 | 22 | 0 | 1505 | 1002 | 2014 |



假设这是个临时表,表名为t1.只需要关注id, ownerId, tagId三列

怎么样在不使用子查询和表连接的情况下用一条sql语句从该表中查出tagId既等于2001,又等于2007的ownerId?

[rucypli]

select ownid
from tb
where tagid in (2001,2007)
group by ownid
having count(distinct tagid)=2

[龑]

1楼正解，已验证

DROP TABLE IF EXISTS T1;

CREATE TABLE T1(2id INT,groupId INT,tagLibId INT,bySort INT,buyerId INT,ownerId INT,tagId INT);

INSERT INTO T1 VALUES
(2001,1,1,0,1,16,2001),
(2007,2,11,0,2,16,2007),
(2008,2,12,0,3,16,2008),
(2014,3,22,0,4,16,2014),
(2013,3,21,0,6,16,2013),
(2002,1,2,0,14,16,2002),
(2003,1,3,0,15,16,2003),
(2018,4,51,0,16,16,2018),
(2004,1,4,0,17,16,2004),
(2009,2,13,0,18,16,2009),
(2001,1,1,0,25,1001,2001),
(2007,2,11,0,26,1001,2007),
(2002,1,2,0,30,1002,2002),
(2019,4,52,0,31,1002,2019),
(2008,2,12,0,35,1002,2008),
(2054,13,2,0,1001,1501,2054),
(2029,11,51,0,1002,1501,2029),
(2045,12,71,0,1003,1501,2045),
(2046,12,72,0,1004,1501,2046),
(2030,11,52,0,1005,1501,2030),
(2059,21,82,0,1007,2001,2059),
(2060,21,83,0,1008,2001,2060),
(2065,31,94,0,1009,2002,2065),
(2062,31,91,0,1010,2002,2062),
(2063,31,92,0,1011,2002,2063),
(2018,4,51,0,1501,1001,2018),
(2022,5,41,0,1502,1001,2022),
(2013,3,21,0,1503,1001,2013),
(2025,5,44,0,1504,1002,2025),
(2014,3,22,0,1505,1002,2014);

SELECT ownerId FROM T1 WHERE tagid IN (2001,2007) GROUP BY ownerId HAVING COUNT(DISTINCT tagid)=2

[Sunny_kaka]

引用 1 楼 rucypli 的回复:

SQL code?12345select ownidfrom tbwhere tagid in (2001,2007)group by ownidhaving count(distinct tagid)=2

确实可行

[Sunny_kaka]

引用 3 楼 IceArmour 的回复:

既等于2001,又等于2007？and的关系？。。

确实是且的关系,但是用and是求不出结果的..

[WWWWA]

用你上述数据，要求结果是什么，一般要用EXISTS来解决

[IceArmour]

既等于2001,又等于2007？and的关系？。。