我有一张表，其类别和品牌如下:
| id| category | brand | date | ........
| 1 | A | A1 | 12-DEC-09 | ........
| 1 | A | A1 | 12-DEC-09 |........
| 1 | A | A1 | 13-DEC-09 |........
| 2 | A | A2 | 14-DEC-09 |........
| 2 | B | B1 | 14-DEC-09 |........
| 2 | B | B2 | 14-DEC-09 |........
| 2 | B | B3 | 14-DEC-09 |........
我需要显示与品牌超过1的结果。 品牌是以品类为基础的。
我需要这样的结果:
| 1 | A | A1 | 12-DEC-09 | ......
| 1 | A | A1 | 12-DEC-09 |........
| 1 | A | A1 | 13-DEC-09 |........
我只得到类别，品牌和它的总数与我的查询。
select
category,
brand,
count(*) as total
from
tbl_category
group by category, brand
但我需要像上面这样的结果。
您可以使用窗口函数:
select c.*
from (select c.*, count(*) over (partition by category, brand) as cnt
from tbl_category c
) c
where cnt > 1;
