oracle多表查询去重复解决方案

oracle多表查询去重复
3个表

PRODUCT_ID:商品编码
PRODUCT_NAME：商品名称
SRC：图片地址
MEMBER_NAME：用户账号

【PRODUCTS】商品表
ID PRODUCT_ID PRODUCT_NAME
1 001 商品1
2 002 商品2
3 003 商品3

【PRODUCT_PIC】商品图片表
ID PRODUCT_ID SRC
1 001 -
2 001 -
3 002 -
4 003 -
5 003 -

【SHOPPING_CART】购物车表
ID MEMBER_NAME PRODUCT_ID
1 member1 001
2 member1 002
3 member1 003
4 member2 001
5 member2 002

查询的是【购物车】表，根据PRODUCT_ID得到【商品表】的PRODUCT_NAME 和【商品图片表】的SRC

现在用的SQL语句是：


select s.*,p.*,pic.* from SHOPPING_CART s,PRODUCTS p,PRODUCT_PIC pic where s.PRODUCT_ID = p.PRODUCT_ID and s.PRODUCT_ID = pic.PRODUCT_ID and s.MEMBER_NAME = 用户账号

现在用的语句查询的数据有重复的，语句应该怎么写

[解决办法]
select t.*,t1.PRODUCT_NAME,t2. from SHOPPING_CART t left inner join PRODUCTS t1 on t1.PRODUCT_ID=t2.PRODUCT_ID left inner join PRODUCT_PIC t2 on t3.PRODUCT_PIC=t.PRODUCT_PIC
[解决办法]
select t.*,t1.PRODUCT_NAME,t2.src from SHOPPING_CART t left inner join PRODUCTS t1 on t1.PRODUCT_ID=t2.PRODUCT_ID left inner join PRODUCT_PIC t2 on t3.PRODUCT_PIC=t.PRODUCT_PIC
[解决办法]
distinct
[解决办法]


select t.* from 
(select t.*,t1.PRODUCT_NAME,t2.src from SHOPPING_CART t left inner join PRODUCTS t1 on t1.PRODUCT_ID=t2.PRODUCT_ID left inner join PRODUCT_PIC t2 on t3.PRODUCT_ID=t.PRODUCT_ID)ta group by ta.t1.PRODUCT_ID,ta.PRODUCT_NAME,ta.src

[解决办法]
select ta.* from
(select t.PRODUCT_ID,t1.PRODUCT_NAME,t2.src from SHOPPING_CART t left inner join PRODUCTS t1 on t1.PRODUCT_ID=t2.PRODUCT_ID left inner join PRODUCT_PIC t2 on t3.PRODUCT_ID=t.PRODUCT_ID)ta group by ta.t1.PRODUCT_ID,ta.PRODUCT_NAME,ta.src
------解决方案--------------------


select ta.* from 
 (select t.PRODUCT_ID,t1.PRODUCT_NAME,t2.src from SHOPPING_CART t 
left inner join PRODUCT_PIC t2 on t3.PRODUCT_ID=t.PRODUCT_ID 
left inner join PRODUCTS t1 on t1.PRODUCT_ID=t2.PRODUCT_ID 
)ta 
group by ta.t1.PRODUCT_ID,ta.PRODUCT_NAME,ta.src

oracle多表查询去重复解决方案

热点推荐