看起来非常简单的需求:
+------+---------+----------+--------+------+---------+-------+
| 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?

解决方案 »

  1.   

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

  2.   

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

  3.   

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

  4.   

    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
      

  5.   


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