mysql - 用于照片排名的 MySQL QUERY

Question

我有 2 张桌子：

CREATE TABLE `psPhotosRating` (
  `id_photo_rating` int(11) NOT NULL,
  `id_user` int(11) NOT NULL,
  `id_uploaded_files` int(11) NOT NULL,
  `rating` int(2) NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;



CREATE TABLE `psUploadedFiles2` (
  `id_uploaded_files` int(10) UNSIGNED NOT NULL,
  `enable` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0',
  `id_user` int(11) NOT NULL DEFAULT '0',
  `file_path` varchar(150) COLLATE utf8_unicode_ci NOT NULL,
  `file_name` varchar(75) COLLATE utf8_unicode_ci NOT NULL,
  `creation_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `category` bigint(20) NOT NULL DEFAULT '0',
  `tags` text COLLATE utf8_unicode_ci,
  `description` mediumtext COLLATE utf8_unicode_ci,
  `promo_in_front` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0',
  `count` bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


ALTER TABLE `psPhotosRating`
  ADD PRIMARY KEY (`id_photo_rating`);


ALTER TABLE `psPhotosRating`
  MODIFY `id_photo_rating` int(11) NOT NULL AUTO_INCREMENT;


ALTER TABLE `psUploadedFiles2`
  MODIFY `id_uploaded_files` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;
COMMIT;

psUploadedFiles2 - 这是“照片数据库” psPhotosRating - 为 psUploadedFiles2 中的每张照片投票的表格

不是每张照片都有投票。

我需要一个 SQL 查询，显示按 psPhotosRating 等级（投票数）排序的图像列表（psUploadedFiles2）。

有谁知道该怎么做？

标签： mysqlsqljoinleft-join

Answer 1

我想你可以在这里加入第二张桌子并计算结果：

SELECT count(rat.id_uploaded_files ) as rating, ps.* 
FROM psUploadedFiles2 ps
JOIN psPhotosRating rat ON ps.id_uploaded_files = rat.id_uploaded_files
ORDER BY rating DESC;