我试图在我的mysql数据库中查询5个单独的表,并显示与分配给他们的项目最多的演员。表结构如下:
item
itemid | item | description | brand | date | time | path |
actor
actorid | name | actorthumb | bio |
brand
brandid | brandname | description | image |
movie
movieid | title | genre | year | moviethumb | synopsis|
request
requestid | userid | itemid | brandid | movieid | actorid | content | requestdate |
我假设我需要将请求,参与者和项目表连接在一起,使用COUNT函数计算分配给参与者的项目数量,然后使用GREATEST来显示分配给他们的项目最多的参与者?
The query to join all the tables is
$query = "SELECT greatest i.*, a.*, b.*, m.*, r.* FROM item AS i, actor AS a, brand AS b, movie AS m, request AS r
WHERE r.itemid = i.itemid
AND r.actorid = a.actorid
AND r.brandid = b.brandid
AND r.movieid = m.movieid";
请确认最好的方法是什么?
SELECT a.actorid, a.name
FROM request r
INNER JOIN actor a
ON r.actorid = a.actorid
GROUP BY a.actorid, a.name
ORDER BY COUNT(DISTINCT r.itemid) DESC
LIMIT 5
The query to join all the tables is
$query = "SELECT i.*, a.*, b.*, m.*, r.* FROM item AS i, actor AS a, brand AS b, movie AS m, request AS r
WHERE r.itemid = i.itemid
AND r.actorid = a.actorid
AND r.brandid = b.brandid
AND r.movieid = m.movieid
ORDER BY count(i.*) DESC";