我正在嘗試reviews從 MySQL 資料庫中查詢表。每行代表對產品的評論。相關列的資料存盤如下：
{ product_id: 25, rating: 1, recommend: 'false' },
{ product_id: 25, rating: 4, recommend: 'true' },
{ product_id: 25, rating: 3, recommend: 'true' },
{ product_id: 25, rating: 2, recommend: 'false' },
{ product_id: 25, rating: 1, recommend: 'false'}
我需要得到每種評分的總和（例如，有多少評論給了它“1”），然后計算有多少評論推薦“真”，有多少評論推薦“假”。我希望查詢回傳如下資料：
{ product_id: 25, rating: 1, total: 2, recommendTrue: 2, recommendFalse: 3},
{ product_id: 25, rating: 2, total: 1, recommendTrue: 2, recommendFalse: 3},
{ product_id: 25, rating: 3, total: 1, recommendTrue: 2, recommendFalse: 3},
{ product_id: 25, rating: 4, total: 1, recommendTrue: 2, recommendFalse: 3},
從技術上講，我不需要在每一行上都使用推薦真和推薦假，但是因為我是按評級型別分組的，所以冗余很好。
當我使用這個查詢 (A) 時：
'SELECT product_id, rating, COUNT(rating) as total'
' ' 'FROM reviews'
' ' 'WHERE reviews.product_id = ?'
' ' 'GROUP BY rating'
' ' 'LIMIT 50'
我得到了預期結果的一部分：
{ product_id: 25, rating: 1, total: 1 },
{ product_id: 25, rating: 3, total: 1 },
{ product_id: 25, rating: 2, total: 3 },
{ product_id: 25, rating: 5, total: 1 },
{ product_id: 25, rating: 4, total: 2 }
我現在需要計算 product_id 25 的所有評論中 True 和 False 推薦的總數。
我正在嘗試這個查詢（B）：
"SELECT r.product_id, COUNT(r.recommend='false') as F, COUNT(r.recommend='true') as T"
" " "FROM reviews AS r"
" " "WHERE r.product_id = ?"
" " "GROUP BY r.rating"
" " "LIMIT 50"
并得到這個結果：
{ product_id: 25, F: 1, T: 1, rating: 1, total: 1 },
{ product_id: 25, F: 1, T: 1, rating: 3, total: 1 },
{ product_id: 25, F: 3, T: 3, rating: 2, total: 3 },
{ product_id: 25, F: 1, T: 1, rating: 5, total: 1 },
{ product_id: 25, F: 2, T: 2, rating: 4, total: 2 }
推薦需要單獨計算 True 和 False，但不應單獨計算評級。我寫它的方式是計算每個評級的所有推薦（正確和錯誤）。
uj5u.com熱心網友回復：
您有兩個單獨的查詢：一個查詢真/假，一個查詢評分。
您可以將兩者與 join 陳述句結合使用。連接中的查詢將計算真/假，然后將結果與評級查詢相結合：
select product_id, rating, count(*), t, f
from reviews
join (
select
sum(if(recommend='true', 1, 0)) as t,
sum(if(recommend='false', 1, 0)) as f
from reviews
where product_id=?
) as q
where product_id=?
group by product_id, rating, t, f
請參閱db-fiddle。
uj5u.com熱心網友回復：
如果您在支持視窗函式的 MySQL 8 上，您可以嘗試使用SUM() OVER ()基本查詢的函式，然后將其作為子查詢進行COUNT()和分組。像這樣的東西：
SELECT product_id, rating,
COUNT(product_id) AS total,
recommendTrue,
recommendFalse
FROM
(SELECT product_id, rating,
SUM(recommend='true') OVER () AS recommendTrue,
SUM(recommend='false') OVER () AS recommendFalse
FROM reviews r
WHERE r.product_id = '25') A
GROUP BY product_id, rating, recommendTrue, recommendFalse
演示小提琴
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/427764.html
下一篇：了解jqJOIN()