mysql - 在 concat 中为具有特定图像类型的图像添加查询，例如 MySql 中的配置文件或海报选择

Question

我有一个图像表，其中我有两个 imageType，分别在 imageType 字段中称为 profile 和 poster，并且我只有在存储所有图像 URL 的图像路径上。

如果可能的话，我想要这样的东西:

CONCAT('/images/', select i.path where i.imageType='**poster**') AS posters

CONCAT('/images/', select i.path where i.imageType='**profile**') AS profiles

SELECT DATE_FORMAT(f.filmReleaseDate,'%d %b %Y')filmReleaseDate, f.filmName, CONCAT('/images/', i.path) AS profiles, CONCAT('/images/', i.path) AS posters, f.filmDirector, url FROM films f
    INNER JOIN images i ON f.filmId = i.filmId
    WHERE f.filmReleaseDate >= NOW() 
    GROUP by i.filmId
    ORDER BY DATE(f.filmReleaseDate)
    LIMIT 0 , 10

Answer 1

你可以用例

SELECT DATE_FORMAT(f.filmReleaseDate,'%d %b %Y')filmReleaseDate, f.filmName, 
  case when  i.imageType='**profile**' then  CONCAT('/images/', i.path) end AS profiles, 
  case when  i.imageType='**poster**' then CONCAT('/images/', i.path) end AS posters, f.filmDirector, url FROM films f
    INNER JOIN images i ON f.filmId = i.filmId
    WHERE f.filmReleaseDate >= NOW() 
    GROUP by i.filmId
    ORDER BY DATE(f.filmReleaseDate)
    LIMIT 0 , 10

为了让两者结合在一起，尝试对同一个表进行内部连接

  SELECT DATE_FORMAT(f.filmReleaseDate,'%d %b %Y')filmReleaseDate, f.filmName, 
        CONCAT('/images/', i.path) AS profiles, 
        CONCAT('/images/', j.path) AS posters, f.filmDirector, url FROM films f
    INNER JOIN images i ON f.filmId = i.filmId
    INNER JOIN images j on (j.filmId = i.filmId)
    WHERE f.filmReleaseDate >= NOW() 
    AND i.imageType='**profile**'
    AND j.imageType='**poster**'
    GROUP by i.filmId
    ORDER BY DATE(f.filmReleaseDate)
    LIMIT 0 , 10