SELECT A.DISH_NAME, NVL(COMMENTS.SL, 0) PINGLUN FROM (SELECT DISH_NAME, DISH_ID FROM JW_DISH) A, (SELECT A.DISH_ID, COUNT(B.COMMENT_HEART) SL FROM JW_ORDER_ARCH A, JW_COMMENT B WHERE B.SINGLE_ORDER_ID = A.SINGLE_ORDER_ID GROUP BY A.DISH_ID) COMMENTS WHERE COMMENTS.DISH_ID = A.DISH_ID(+)
SELECT A.DISH_NAME, NVL(COMMENTS.SL, 0) PINGLUN FROM (SELECT DISH_NAME, DISH_ID FROM JW_DISH) A, (SELECT A.DISH_ID, COUNT(B.COMMENT_HEART) SL FROM JW_ORDER_ARCH A, JW_COMMENT B WHERE B.SINGLE_ORDER_ID = A.SINGLE_ORDER_ID GROUP BY A.DISH_ID) COMMENTS WHERE COMMENTS.DISH_ID = A.DISH_ID(+) 谢谢你,你这个我也知道,还有没有别的方法能实现,
左连接 应该就这两种了left join on ,(+)
不用左连接能实现这个查询吗?要么你就改为子查询:select a.dish_name, (select nvl(comments.sl, 0) from (select a.dish_id, count(b.comment_heart) sl from jw_order_arch a, jw_comment b where b.single_order_id = a.single_order_id group by a.dish_id) comments where comments.dish_id = a.dish_id) pinglun from (select dish_name, dish_id from jw_dish) a
SELECT A.DISH_NAME, NVL(COMMENTS.SL, 0) PINGLUN
FROM (SELECT DISH_NAME, DISH_ID FROM JW_DISH) A,
(SELECT A.DISH_ID, COUNT(B.COMMENT_HEART) SL
FROM JW_ORDER_ARCH A, JW_COMMENT B
WHERE B.SINGLE_ORDER_ID = A.SINGLE_ORDER_ID
GROUP BY A.DISH_ID) COMMENTS
WHERE COMMENTS.DISH_ID = A.DISH_ID(+)
SELECT A.DISH_NAME, NVL(COMMENTS.SL, 0) PINGLUN
FROM (SELECT DISH_NAME, DISH_ID FROM JW_DISH) A,
(SELECT A.DISH_ID, COUNT(B.COMMENT_HEART) SL
FROM JW_ORDER_ARCH A, JW_COMMENT B
WHERE B.SINGLE_ORDER_ID = A.SINGLE_ORDER_ID
GROUP BY A.DISH_ID) COMMENTS
WHERE COMMENTS.DISH_ID = A.DISH_ID(+)
谢谢你,你这个我也知道,还有没有别的方法能实现,
(select nvl(comments.sl, 0)
from (select a.dish_id, count(b.comment_heart) sl
from jw_order_arch a, jw_comment b
where b.single_order_id = a.single_order_id
group by a.dish_id) comments
where comments.dish_id = a.dish_id) pinglun
from (select dish_name, dish_id from jw_dish) a