如何在multi-select语句中使用mysql round函数

63lcw9qa 于 2021-06-20 发布在 Mysql

关注(0)|答案(1)|浏览(234)

SELECT bp.bizid, bp.usrid, bp.website,
ROUND((SELECT SUM(rating) FROM ratings WHERE bizid=bp.bizid)/(SELECT COUNT(*) FROM ratings WHERE bizid=bp.bizid), 1) AS 'ratings', 
(SELECT COUNT(*) FROM bzreviews WHERE bizid=bp.bizid) AS 'ttlreviews', bp.phoneno, als.bizname, 
(SELECT COUNT(*) FROM endorsment WHERE bizid=bp.bizid) AS 'endorses', als.imgname, bp.`location`, bp.`ownership`, 
(SELECT COUNT(*) FROM follows WHERE bizid=bp.bizid) AS 'followers', bp.categories, bp.openhours, bp.bizdecri FROM bizprofile AS bp 
INNER JOIN alluser AS als ON bp.usrid=als.userid WHERE als.usertype='Business'

**我分享这个是为了帮助可能需要它的人。我用count来计算

评论（count（））关注者（count（））
以及mysql数据库中的endorsmentcount（）。为了找到评级，我使用round函数帮助将总评级（sum（rating））和评级数（count（））算出后的数字四舍五入到小数点后一位

sql mysql

来源：https://stackoverflow.com/questions/52667409/how-to-use-mysql-round-function-in-a-multi-select-statement

1条答案

按热度按时间

k2fxgqgv1#

因为 SELECT 语句在中使用无效 ROUND 功能。
您将需要使用传递表方法

SELECT 
 *
 , ROUND(alias.ratings, 1) AS ratings
FROM (

    SELECT
      bp.bizid
    , bp.usrid
    , bp.website,
    , ((SELECT SUM(rating) FROM ratings WHERE bizid=bp.bizid)/(SELECT COUNT(*) FROM ratings WHERE bizid=bp.bizid)) AS 'ratings'
    , (SELECT COUNT(*) FROM bzreviews WHERE bizid=bp.bizid) AS 'ttlreviews'
    , bp.phoneno, als.bizname
    , (SELECT COUNT(*) FROM endorsment WHERE bizid=bp.bizid) AS 'endorses'
    , als.imgname, bp.`location`, bp.`ownership`
    , (SELECT COUNT(*) FROM follows WHERE bizid=bp.bizid) AS 'followers'
    , bp.categories
    , bp.openhours
    , bp.bizdecri
    FROM
      bizprofile AS bp 
    INNER JOIN
      alluser AS als
    ON
     bp.usrid = als.userid
    WHERE
     als.usertype = 'Business'
) AS alias

赞(0）回复(0）举报 2021-06-21

我来回答

如何在multi-select语句中使用mysql round函数

1条答案

相关问题

热门标签

最新问答