mysql - SQL 选择内部选择

Question

我有 3 张 table 。

person{personid,name, etc}
bid{bidid,personid,etc}
rating{ratingid,bidid,rating}

出价被客户接受后，一个人就会获得评级。因此，1 出价 = 1 评级。然后该人再次出价，但评级不会显示。

我已经尝试过:

SELECT a.namausaha,ROUND(AVG(c.rating)) AS rating,a.kota,a.kontak,b.bidprice,a.mitraid
FROM tb_mitra a
JOIN tb_bid b ON b.mitraid=a.mitraid
LEFT JOIN tb_rating c ON c.bidid=b.bidid
WHERE b.orderid='OD004' AND b.statusbidid='1'
GROUP BY a.mitraid  

但它不起作用。

怎么做呢？我想显示每个人的评分。

Answer 1

由于左连接，某些评级返回null

试试这个

SELECT a.name,
  ROUND(AVG(CASE WHEN c.rating IS NULL THEN 1 ELSE c.rating END )) AS rating,
  a.etc,b.etc,a.personid
FROM person a
JOIN bid b ON b.personid=a.personid
LEFT JOIN rating c ON c.bidid=b.bidid
GROUP BY a.personid 

SQL Fiddle