使用joins和distinct的高效SQL存储过程

Question

我正在尝试以一种有效的方式来执行下面的存储过程。此过程用于显示属于所有类别的市场中的企业的汽车计数。

我让category表作为主要选择，然后我进入cars表来计算汽车数量并加入类别中的业务，这是它真正效率低下的地方，然后我检查所选的市场/位置，如果没有选择所有市场。这就是了。

Create PROC  [USP_CAT]
  @MKT int

AS

BEGIN

    SELECT *,
      (
        SELECT COUNT(CAR_ID)
        FROM CARS
        INNER JOIN BUSINESS ON CAR_BIZ=BIZ_ID
        JOIN BIZCATS ON BC_BIZ = BIZ_ID AND BC_CAT = CAT_ID 
        WHERE  BIZ_APPROVED = 1 AND BIZ_EXPIRES >= getDate()  AND 
        (
          SELECT COUNT(BLOC_MKT)
          FROM BIZLOCS
          WHERE BIZ_ID = BLOC_BIZ AND (BLOC_MKT = @MKT OR @MKT = -1)
        )>0
      ) AS CAT_CAR_COUNT
    FROM CATS
    WHERE CAT_HIDE = 0
    ORDER BY CAT_ORDER asc

END

任何建议，以整理这一点是非常感谢。

Answer 1

我认为EXISTS (http://msdn.microsoft.com/en-us/library/ms188336.aspx)可能会有所帮助。

Create PROC  [USP_CAT]
  @MKT int

AS

BEGIN

    SELECT *,
      (
        SELECT COUNT(CAR_ID)
        FROM CARS
        INNER JOIN BUSINESS ON CAR_BIZ=BIZ_ID
        JOIN BIZCATS ON BC_BIZ = BIZ_ID AND BC_CAT = CAT_ID 
        WHERE  BIZ_APPROVED = 1 AND BIZ_EXPIRES >= getDate()  AND EXISTS
        (
          SELECT BLOC_MKT
          FROM BIZLOCS
          WHERE BIZ_ID = BLOC_BIZ AND (BLOC_MKT = @MKT OR @MKT = -1)
        )
      ) AS CAT_CAR_COUNT
    FROM CATS
    WHERE CAT_HIDE = 0
    ORDER BY CAT_ORDER asc

END