Mysql查询，三个表用group by左连接

发布于 2024-09-25 01:25:33 字数 854 浏览 6 评论 0原文

我的查询提供了错误的结果，我在这个查询中做错了什么吗？

SELECT   b.nBoutiqueID                       ,
         b.sBoutiqueName                     ,
         b.Status                            ,
         SUM(bs.nViewCount)      nViewCount       ,
         SUM(ps.nViewCount)      nProductViewCount,
         SUM(ps.nLinkClickCount) nLinkClickCount  ,
         SUM(ps.nWishListCount)  nWishListCount   ,
         SUM(ps.nReferredCount)  nReferredCount
FROM     boutique b
         LEFT JOIN boutique_stats bs
         ON       b.nBoutiqueID=bs.nBoutiqueID
         LEFT JOIN product_stats ps
         ON       ps.nBoutiqueID=b.nBoutiqueID
WHERE    b.bDeleted             =0
GROUP BY b.nBoutiqueID
ORDER BY ps.nProductID DESC

查询没有给出任何错误，但产生了错误的结果。我正在使用Mysql。

对于 nBoutiqueID=1 的特定实例，nViewCount 的最大总和应为 455，但它给出 95124。这是巨大的差异。有人知道为什么吗？

原文

I have query which provide wrong result, am I doing any thig wrong in this query

SELECT   b.nBoutiqueID                       ,
         b.sBoutiqueName                     ,
         b.Status                            ,
         SUM(bs.nViewCount)      nViewCount       ,
         SUM(ps.nViewCount)      nProductViewCount,
         SUM(ps.nLinkClickCount) nLinkClickCount  ,
         SUM(ps.nWishListCount)  nWishListCount   ,
         SUM(ps.nReferredCount)  nReferredCount
FROM     boutique b
         LEFT JOIN boutique_stats bs
         ON       b.nBoutiqueID=bs.nBoutiqueID
         LEFT JOIN product_stats ps
         ON       ps.nBoutiqueID=b.nBoutiqueID
WHERE    b.bDeleted             =0
GROUP BY b.nBoutiqueID
ORDER BY ps.nProductID DESC

Query giving not any error, but producing wrong result. I'm Using Mysql.

For particular Instance for nBoutiqueID=1 the max sum of nViewCount should be 455, but it gives 95124. that is huge difference. any one know why?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

云裳 2024-10-02 01:25:33

看来您正在获得查询的笛卡尔积...尝试从子查询中获取 SUM() 值...

SELECT
      b.nBoutiqueID, 
      b.sBoutiqueName, 
      b.Status, 
      bs.StatsViewCount,
      ps.ProductViewCount, 
      ps.ProductLinkClickCount, 
      ps.ProductWishListCount, 
      ps.ProductReferredCount 
   FROM     
      boutique b 
         LEFT JOIN ( select nBoutiqueID, sum( nViewCount ) as StatsViewCount
                        from boutique_stats 
                        group by nBoutiqueID ) bs 
            ON b.nBoutiqueID = bs.nBoutiqueID 
         LEFT JOIN ( select SUM(nViewCount) ProductViewCount, 
                             SUM(nLinkClickCount) ProductLinkClickCount, 
                             SUM(nWishListCount) ProductWishListCount, 
                             SUM(nReferredCount)  ProductReferredCount 
                        from product_stats 
                        group by nBoutiqueID ) ps 
            ON ps.nBoutiqueID=b.nBoutiqueID 
   WHERE    
      b.bDeleted = 0 
   ORDER BY 
      ps.nProductID DESC

It appears you are getting a sort-of Cartesian product of the query... Try getting your SUM() values from your sub-queries...

SELECT
      b.nBoutiqueID, 
      b.sBoutiqueName, 
      b.Status, 
      bs.StatsViewCount,
      ps.ProductViewCount, 
      ps.ProductLinkClickCount, 
      ps.ProductWishListCount, 
      ps.ProductReferredCount 
   FROM     
      boutique b 
         LEFT JOIN ( select nBoutiqueID, sum( nViewCount ) as StatsViewCount
                        from boutique_stats 
                        group by nBoutiqueID ) bs 
            ON b.nBoutiqueID = bs.nBoutiqueID 
         LEFT JOIN ( select SUM(nViewCount) ProductViewCount, 
                             SUM(nLinkClickCount) ProductLinkClickCount, 
                             SUM(nWishListCount) ProductWishListCount, 
                             SUM(nReferredCount)  ProductReferredCount 
                        from product_stats 
                        group by nBoutiqueID ) ps 
            ON ps.nBoutiqueID=b.nBoutiqueID 
   WHERE    
      b.bDeleted = 0 
   ORDER BY 
      ps.nProductID DESC

回复收藏 0 原文