Witam.
Mam 2 tabele:
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 - tabela jest bazą danych zdjęć
psPhotosRating - tabla z głosami oddanymi na psUploadedFiles2 psUploadedFiles2
Nie każdy obrazek ma oddane na niego głosy.
Potrzebuję zapytanie SQL wyświetlające listę zdjęć (psUploadedFiles2) posortowanych po rankingu z psPhotosRating (średniej głosów).
średnia głosów = ilość głosów / wartość głosów (psPhotosRating => rating)
Wie ktoś może jak ułożyć takie zapytanie?
Chciałbym żeby ranking był liczony jako suma głosów oddanych na zdjęcie (psPhotosRating -> suma rating)) / ilość głosów oddanych na dane zdjęcie.
Wynik miałby być posortowany według wartości uzyskanej w powyższym dzieleniu.
W tabeli psPhotosRating mam przykładowo rekordy:
INSERT INTO `psPhotosRating` (`id_photo_rating`, `id_user`, `id_uploaded_files`, `rating`, `timestamp`) VALUES
(1, 36, 11, 4, '2019-02-06 09:09:08'),
(2, 112, 5, 5, '2019-02-06 09:54:34'),
(3, 16, 11, 1, '2019-02-04 11:20:24');
Próbowałem coś takiego:
SELECT (SELECT CONCAT(file_path, '/', file_name) AS image FROM psUploadedFiles B WHERE category = user.id_user AND enum =3 ORDER BY number ASC LIMIT 1) AS image2, user.username, user.url_address, files.*, CONCAT(file_path, '/', file_name) AS image, (SELECT COUNT(id_photo_rating) FROM psPhotosRating WHERE id_uploaded_files = photos.id_uploaded_files) AS numbers, (SELECT SUM(rating) FROM psPhotosRating WHERE id_uploaded_files = photos.id_uploaded_files) AS summary, (SELECT ROUND(AVG(rating),2) FROM psPhotosRating WHERE id_uploaded_files = photos.id_uploaded_files) AS avgratting FROM psPhotosRating photos JOIN psUploadedFiles2 files ON files.id_uploaded_files = photos.id_uploaded_files JOIN psUser user ON user.id_user = files.id_user GROUP BY id_uploaded_files ORDER BY avgratting DESC;
ale nie do końca działa to poprawnie