select billid,goodsid from card group by billid,goodsid having count(1)>1
select billid,goodsid from card group by billid,goodsid having count(*)>1
SELECT * FROM CARD WHERE BILLID IN (SELECT BILLID FROM CARD HAVING COUNT(BILLID)>1) AND GOODSID IN (SELECT GOODSID FROM CARD HAVING COUNT(GOODSID)>1)
select billid,goodsid from card group by billid,goodsid having count(1) > 1
create table t( billid int, goodsid int ) insert t select 1,102 union all select 1,103 union all select 2,103 union all select 2,105 union all select 2,103 union all select 3,118 union all select 4,102 union all select 4,108select a.* from t a inner join( select billid,goodsid from t group by billid,goodsid having count(*)>=2 )b on a.billid=b.billid and a.goodsid=b.goodsid--result: billid goodsid ------------------- 2 103 2 103
select billid,goodsid from card group by billid,goodsid having count(*) >1
group by billid,goodsid
having count(*)>1
AND GOODSID IN (SELECT GOODSID FROM CARD HAVING COUNT(GOODSID)>1)
billid int,
goodsid int
)
insert t
select 1,102 union all
select 1,103 union all
select 2,103 union all
select 2,105 union all
select 2,103 union all
select 3,118 union all
select 4,102 union all
select 4,108select a.* from t a
inner join(
select billid,goodsid from t group by billid,goodsid having count(*)>=2
)b on a.billid=b.billid and a.goodsid=b.goodsid--result:
billid goodsid
-------------------
2 103
2 103