问mysql表的设计

EN

SELECT 
    c.*                        -- columns you need from Celebrity
  , p.*                        -- and Photo tables
FROM 
    celebrity AS c
  LEFT JOIN 
    ( SELECT celebrity_id
           , MIN(photo_id) AS photo_id
      FROM celebrity_photo
      GROUP BY celebrity_id
    ) AS cp
      ON cp.celebrity_id = c.celebrity_id
  LEFT JOIN 
    photo AS p
      ON p.photo_id = cp.photo_id ;

SELECT 
    c.*                        -- columns you need from Celebrity
  , p.*                        -- and Photo tables
FROM 
    celebrity AS c
  LEFT JOIN 
    photo AS p
      ON p.photo_id = 
        ( SELECT cp.photo_id 
          FROM celebrity_photo AS cp
          WHERE cp.celebrity_id = c.celebrity_id 
          ORDER BY RAND()
              LIMIT 1
        ) ;

[CELEB]  
celeb_id
celeb_name  

[CELEB_PHOTO]  
celeb_photo_id
celeb_id
photo_name

SELECT * FROM `CELEB_PHOTO` WHERE `celeb_id` = $celeb_id ORDER BY RAND() LIMIT 1 

SELECT * FROM (SELECT RAND() RND_INDEX, P.PHOTO_ID, P.PHOTO_LOCATION, C.ID, C.NAME
FROM PHOTOS P, CELEBRITY_PHOTOS CP, CELEBRITY C
WHERE P.PHOTO_ID = CP.PHOTO_ID AND CP.ID = DESIRED_CELEBRITY AND CP.ID = C.ID) A ORDER BY RND_INDEX LIMIT 1