select ProductName,max(ProductPicture) from Product a,ProductPicture b where a.ProductID=b.ProductID(+) group by ProductName having max(ProductPicture)>0
SELECT p.productid, pp.productpictureid from product p left join (Select distinct productpicture) pp on p.productid=pp.productid
更正。 SELECT p.productid, pp.productpictureid from product p left join (Select distinct ProductID from productpicture A left Join Select B.* from productpicture as B) pp on p.productid=pp.productid
再更正。SELECT p.productid, pp.productpictureid,pp.ProductPicture from product p left join (Select distinct ProductID from productpicture A left Join Select B.* from productpicture as B) pp on p.productid=pp.productid
SELECT P1.ProductID,P2.ProductName,MIN(P2.ProductPicture) FROM Product P1,ProductPicture P2 WHERE P1.ProductID = P2.ProductID GROUP BY P1.ProductID,P2.ProductName
以下代码已经测试通过。 先写一个函数判断是否有图片: CREATE OR REPLACE FUNCTION HavePicture( ProductID_v NUMBER ) RETURN VARCHAR2 IS lsbl NUMBER(10); BEGIN SELECT count(*) INTO lsbl FROM ProductPicture WHERE ProductID=ProductID_v; IF lsbl=0 THEN RETURN 'no show'; ELSE RETURN 'show'; END IF; EXCEPTION WHEN OTHERS THEN RETURN 'no show'; END; / 然后: SELECT ProductID,ProductName,HavePicture(ProductID) AS Picture FROM Product; 就可以得到你想要的结果。
我并非要显示show与no show字符,我要显示图片拉。。
select p.productid, pp.productpictureid from product p,) select * from ProductPicture where ProductPictureid in(select distinct ProductPictureid from ProductPicture)) pp where p.productid=pp.productid and count(pp.productpictureid)<>0
SELECT p.productid, pp.productpictureid,pp.ProductPicture from product p left join (Select distinct A.ProductID from productpicture A left Join Select B.* from productpicture as B on A.ProductID=B.ProductID) pp on p.productid=pp.productid
select a.productId , a.productname , max(b.productpictureid ) from product a , productpicture b where a.productid = b.productpicture(+) group by a.productId , a.productname
最简单最有效最易理解的答案: SQL> select ProductID,ProductName,(select ProductPicture from ProductPicture where ProductID=Product.ProductID and rownum<2) from Product; 该结贴了!!!
select ProductID,ProductName,(select ProductPicture from ProductPicture where ProductID=Product.ProductID and rownum<2) from Product;
left join (Select distinct productpicture) pp
on p.productid=pp.productid
SELECT p.productid, pp.productpictureid from product p
left join
(Select distinct ProductID from productpicture A left Join Select B.* from productpicture as B) pp on p.productid=pp.productid
left join
(Select distinct ProductID from productpicture A left Join Select B.* from productpicture as B) pp on p.productid=pp.productid
FROM Product P1,ProductPicture P2
WHERE P1.ProductID = P2.ProductID
GROUP BY P1.ProductID,P2.ProductName
错误信息:合计参数min()不能有memo, OLE,或超链接对象。如果用select(select)
则错误信息:子查询memo或OLE对象无效我用的是access,图片是OLE对象。
错误信息:from子句语法错误
先写一个函数判断是否有图片:
CREATE OR REPLACE FUNCTION HavePicture(
ProductID_v NUMBER
) RETURN VARCHAR2
IS
lsbl NUMBER(10);
BEGIN
SELECT count(*) INTO lsbl FROM ProductPicture WHERE ProductID=ProductID_v;
IF lsbl=0 THEN
RETURN 'no show';
ELSE
RETURN 'show';
END IF;
EXCEPTION
WHEN OTHERS THEN
RETURN 'no show';
END;
/
然后:
SELECT ProductID,ProductName,HavePicture(ProductID) AS Picture FROM Product;
就可以得到你想要的结果。
select * from ProductPicture where ProductPictureid in(select distinct ProductPictureid from ProductPicture)) pp where p.productid=pp.productid and count(pp.productpictureid)<>0
left join
(Select distinct A.ProductID from productpicture A left Join Select B.* from productpicture as B on A.ProductID=B.ProductID) pp on p.productid=pp.productid
from product a , productpicture b
where a.productid = b.productpicture(+)
group by a.productId , a.productname
SQL> select ProductID,ProductName,(select ProductPicture from ProductPicture where ProductID=Product.ProductID and rownum<2) from Product;
该结贴了!!!
后来索性写了个function, 非常好用。