Question

我如何利用MySQL来计算一个LEFT JOIN?

我有两张表格,有时评级表没有照片的评级,因此我认为需要LEFT JOIN,但我还有一份COUNT声明。

<>光线>

id  name        src
1   car         bmw.jpg
2   bike        baracuda.jpg

<>Loves (picid is foreign key withphos id)

id  picid   ratersip
4   1       81.0.0.0
6   1       84.0.0.0
7   2       81.0.0.0

这里,用户只能把自己的图像与知识产权相加。

我想按照最高评级合并两个表格。新表格

<><>Combined

id  name    src            picid
1   car     bmw.jpg        1
2   bike    baracuda.jpg   2

(bmw)

MySQL代码:

SELECT * FROM photos 
LEFT JOIN ON photos.id=loves.picid 
ORDER BY COUNT (picid);

www.un.org/Depts/DGACM/index_spanish.htm 我的《公共卫生法》:。

$sqlcount = "SELECT p . *
FROM `pics` p
LEFT JOIN (

SELECT `loves`.`picid`, count( 1 ) AS piccount
FROM `loves`
GROUP BY `loves`.`picid`
)l ON p.`id` = l.`picid`
ORDER BY coalesce( l.piccount, 0 ) DESC";

$pics = mysql_query($sqlcount);

Answer 1

MySQL允许你通过id进行分类。页: 1

select
    p.*
from
    photos p 
    left join loves l on
        p.id = l.picid
group by
    p.id
order by
    count(l.picid)

尽管如此,我知道MySQL在group by 上确实是坏的,因此,你可以尝试将loves在座标上列入一个子座,以便优化:

select
    p.*
from
    photos p
    left join (select picid, count(1) as piccount from loves group by picid) l on
        p.id = l.picid
order by
    coalesce(l.piccount, 0)

我没有我的SQL试验,可以更快地进行测试,这样可以检验。

Answer 2

You need to use subqueries:

SELECT id, name, src FROM (
  SELECT photos.id, photos.name, photos.src, count(*) as the_count 
  FROM photos 
  LEFT JOIN ON photos.id=loves.picid 
  GROUP BY photos.id
) t 
ORDER BY the_count

Answer 3

select
      p.ID,
      p.name,
      p.src,
      PreSum.LoveCount
   from
      Photos p
         left join ( select L.picid,
                            count(*) as LoveCount
                        from
                           Loves L
                        group by
                           L.PicID ) PreSum
           on p.id = PreSum.PicID
   order by
      PreSum.LoveCount DESC

Answer 4

I believe you just need to join the data and do a count(*) in your select. Make sure you specify which table you want to use for ambigous columns. Also, don t forget to use a group by function when you do a count(*). Here is an example query that I run on MS SQL.

Select CmsAgentInfo.LOGID, LOGNAME, hCmsAgent.SOURCEID, count(*) as COUNT from hCmsAgent
LEFT JOIN CmsAgentInfo on hCmsAgent.logid=CmsAgentInfo.logid
where SPLIT =  990 
GROUP BY CmsAgentInfo.LOGID, LOGNAME, hCmsAgent.SOURCEID

成果就是这样。

77615   SMITH, JANE 1   36
29422   DOE, JOHN   1   648

Hope that helps. Good Luck.

友情链接