我有用户上传到我的数据库中的照片记录。他们可以将上传的照片中的一张指定为封面照片。
我想查询我的数据库,为每个用户名返回一张照片。如果他们有封面照片,请退回那张。如果没有,请拉出一张普通照片。指定封面照片的列为tinyint,长度为1。该列为"0"(不是封面照片)或"1"(封面照片)。
我有以下返回用户名列表的所有照片
$usernames = array('bob', 'ted', 'jim');
$usernames = implode( "', '", $usernames );
$query = "SELECT * FROM Photos WHERE username IN('$usernames')";
但我需要将它与以下内容结合起来,如果有封面照片,它会得到一张封面照片,如果没有,它会获得一张普通照片。
$query = "SELECT * FROM Photos WHERE username = 'bob' ORDER BY cover DESC LIMIT 1";
我想我需要使用GROUP BY函数,但我不知道如何设置它。有什么建议吗?
列为id(bigint)、name(varchar(255))、cover(tinyint)、username(varchar(30))
假设您有一个用户表,而照片表有一个id列
SELECT p.*
FROM Users u
INNER JOIN Photo p ON p.id = (
SELECT id FROM Photos p2
WHERE p2.username = u.username
ORDER BY cover DESC
LIMIT 1
)
WHERE u.username IN('$usernames');
如果每个用户只想要一张照片(如果有封面,则为正常,如果没有封面)
SELECT max( cover ) , username, photo
FROM Photos
WHERE username IN('$usernames')
GROUP BY username
如果你想要所有的普通照片,只有一张封面照片(如果存在的话),你可以做
SELECT * FROM Photos WHERE cover = 1 AND username IN ('$usernames') GROUP BY username
UNION
SELECT * FROM Photos WHERE cover = 0 AND username IN ('$usernames')
如果每条记录都有一个唯一标识符(id
),则可以执行以下操作:
SELECT P1.* FROM Photos P1
WHERE P1.id IN (SELECT P2.id FROM Photos P2
WHERE P2.username =P1.username
ORDER BY P2.cover DESC LIMIT 1)
AND P1.username IN ('$usernames')
ORDER BY P1.username
在Mysql中,您需要执行以下操作:
select p.*
from Photos p left outer join
(select username, min(photoid) as minphotoid
from photos p
where cover = 0
group by username
) pmin
on p.username = pmin.username and
(p.cover = 1 or p.photoid = pmin.minphotoid)
where p.username in ('$usernames')
这本质上是在没有封面或封面的情况下找到一张照片(最小的一张)。相关子查询对此有一些变化,可能会更快。
顺便问一下,('$usernames')中的p.username在mysql中工作吗?在其他数据库中,这只适用于$usernames中的一个用户名。