具有联接和区别的高效SQL存储过程

Question

我正在尝试一种有效的方法来完成下面的存储过程。此程序用于显示属于某个市场中所有类别的企业的汽车数量。

我将类别表作为主要选择，然后我进入汽车表，对汽车进行计数，并在类别中加入他们的业务，这是它真正低效的地方，然后我检查所选的市场/地点，如果没有，则检查所有市场。给你。

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

我认为存在(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