MYSQL，帮忙写个SQL语句

[谁学逆向工程]

第一个字段是借书证件的证件号，第二个字段是图书的编号。
现在要查看哪个证件同时借了 112266 和 449901

[luoyoumou]

select LIbCardNo
from tb t1
where exists (select t2.LibCardNo from tb t2
                       where t2.libCardNo=t1.libCardNo
                            and t2.TotalNo in(112266, 449901)
                         group by t2.LibCard
                            having count(t2.TotalNo)=2);

-- 注意：如果TotalNO字段是int类型，将其in修改为： in('112266', '449901')

[ACMAIN_CHM]

select a.借书证件的证件号
from table1 a,table1 b
where a.借书证件的证件号=b.借书证件的证件号
and a.图书的编号=112266 and b.图书的编号=449901

[zxhx]

select LibCardNo from
(select LibCardNo from tb
where TotalNo='112266') a,

(select LibCardNo from tb
where TotalNo='449901') b
where a.LibCardNo = b.LibCardNo 

[hithink]

select a.LibCardNo
from table1 as a,table1 as b
where a.LibCardNo=b.LibCardNo and a.TotalNo='112266' and b.TotalNo='449901';

[hithink]

4楼那个帖子是我的，我很后悔用了笛卡尔积，而且帖子又不能编辑，使用inner join在表元组超多时效率会有很大提高

[hithink]

用嵌套子查询效率是最高的，

select LibCardNo
from table1
where TotalNo='112266' and LibCardNo in (
        select LibCardNo
        from table1
        where TotalNo='449901'
);