create table TEST_1
(
LOC_ID NUMBER,
PROD_ID NUMBER,
COUNT NUMBER,
ID NUMBER
)insert into TEST_1 (LOC_ID, PROD_ID, COUNT, ID)
values (101, 111, 2, 1);insert into TEST_1 (LOC_ID, PROD_ID, COUNT, ID)
values (102, 111, 3, 2);insert into TEST_1 (LOC_ID, PROD_ID, COUNT, ID)
values (103, 111, 4, 3);insert into TEST_1 (LOC_ID, PROD_ID, COUNT, ID)
values (104, 222, 1, 4);insert into TEST_1 (LOC_ID, PROD_ID, COUNT, ID)
values (105, 222, 2, 5);insert into TEST_1 (LOC_ID, PROD_ID, COUNT, ID)
values (106, 333, 1, 6);问题:如何查出prod_id相同条数〉1的所有字段
需要结果:
101 111 2 1
102 111 3 2
103 111 4 3
104 222 1 4
105 222 2 5
(
LOC_ID NUMBER,
PROD_ID NUMBER,
COUNT NUMBER,
ID NUMBER
)insert into TEST_1 (LOC_ID, PROD_ID, COUNT, ID)
values (101, 111, 2, 1);insert into TEST_1 (LOC_ID, PROD_ID, COUNT, ID)
values (102, 111, 3, 2);insert into TEST_1 (LOC_ID, PROD_ID, COUNT, ID)
values (103, 111, 4, 3);insert into TEST_1 (LOC_ID, PROD_ID, COUNT, ID)
values (104, 222, 1, 4);insert into TEST_1 (LOC_ID, PROD_ID, COUNT, ID)
values (105, 222, 2, 5);insert into TEST_1 (LOC_ID, PROD_ID, COUNT, ID)
values (106, 333, 1, 6);问题:如何查出prod_id相同条数〉1的所有字段
需要结果:
101 111 2 1
102 111 3 2
103 111 4 3
104 222 1 4
105 222 2 5
select *
from test_1
where test_1.prod_id in (
select t.prod_id
from test_1 t
group by t.prod_id
having count(prod_id) > 1)
目前的困境就是,prod_id不是唯一索引!,有可能取出多余数据!
http://blog.csdn.net/zftang/article/details/6601500