看起来非常简单的需求:
+------+---------+----------+--------+------+---------+-------+
| 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?
+------+---------+----------+--------+------+---------+-------+
| 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?
from tb
where tagid in (2001,2007)
group by ownid
having count(distinct tagid)=2
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
确实是且的关系,但是用and是求不出结果的..