时间戳

首页 > 解决方案 > 想要获取每个类别的产品数量和随机产品图片。下面的查询工作正常，但性能很慢

sql - 想要获取每个类别的产品数量和随机产品图片。下面的查询工作正常，但性能很慢

问题描述

下面的查询获取类别 ID、标题、产品数量和随机产品图片。我想修改以下查询以提高其性能。下面的查询需要 420 毫秒才能执行。

SELECT category_ID,
       category_Name,
       ISNULL((SELECT TOP 1
                      product_image1
               FROM tbl_products
               WHERE product_image1 <> ''
                 AND product_isActive = 1
                 AND product_ID IN (SELECT refPrdCat_prdID
                                    FROM tbl_refPrdCatLink
                                    WHERE refPrdCat_catID = category_ID)
               ORDER BY NEWID()),
              '') AS Product_Image,
       ISNULL((SELECT COUNT(1)
               FROM tbl_products
               WHERE product_isActive = 1
                 AND product_ID IN (SELECT refPrdCat_prdID
                                    FROM tbl_refPrdCatLink
                                    WHERE refPrdCat_catID = category_ID)),
              '') AS countPrd
FROM tbl_category;

标签： sqlsql-server

解决方案

我发现 product_image1 <> '' 没有必要，因为你在外面使用 isnull，我也看到你在里面使用相同的查询，这就是我使用 cte 的原因。不确定它是否需要一些更改，尝试运行它，请通知我！

 with cte_table (ref) as (
        select refPrdCat_prdID from tbl_refPrdCatLink where refPrdCat_catID =category_ID
    )
    select 
    category_ID,category_Name,
    isnull(
    select top 1 product_image1 from tbl_products where product_isActive=1 and  product_ID in (select * from cte_table) order by newID() ,'') Product_Image,
    isnull(
    select count(1) from tbl_products where product_isActive=1 and  product_ID in  (select * from cte_table) order by newID() ,'') countPrd
    )
    from tbl_category

推荐阅读