SELECT * FROM A JOIN B ON A.COL1=B.COL1 WHERE A.COL2=1 对于这种语句,如果表A和表B都非常大,而符合条件A.COL2=1的记录并不多,那速度会怎么样?是以A JOIN B ON A.COL1=B.COL1 为准还是以WHERE A.COL2=1为准?
--一个查询语句各个部分的执行顺序 --8) SELECT (9) DISTINCT (11) <TOP_specification> <select_list> --(1) FROM <left_table> --(3) <join_type> JOIN <right_table> --(2) ON <join_condition> --(4) WHERE <where_condition> --(5) GROUP BY <group_by_list> --(6) WITH {CUBE | ROLLUP} --(7) HAVING <having_condition> --(10) ORDER BY <order_by_list>
A JOIN B ON A.COL1=B.COL1 是表示两个表通过这两个字段来关联 而后面的where语句是条件。 速度的话你测试一下才知道呢
SELECT * FROM A JOIN B ON A.COL1=B.COL1 WHERE charindex(1,A.COL2) > 0 ---这个速度快点!
SELECT SUM(BH_COUNT*WR_Area) AS SUM_AREA,BH_Type,BH_Date INTO TEMP_TABLE FROM WMSX_InOutBillBody JOIN WMSX_WareRecord ON WMSX_WareRecord.WR_ID = WMSX_InOutBillBody.WR_ID JOIN WMSX_BillHead ON WMSX_InOutBillBody.BH_ID = WMSX_BillHead.BH_ID WHERE BH_Status = 1 GROUP BY BH_Type,BH_Date ORDER BY BH_Date DESC 那为了执行这个语句,该为哪个字段建索引呢?
上面少些了个条件 SELECT SUM(BH_COUNT*WR_Area) AS SUM_AREA,BH_Type,BH_Date INTO TEMP_TABLE FROM WMSX_InOutBillBody JOIN WMSX_WareRecord ON WMSX_WareRecord.WR_ID = WMSX_InOutBillBody.WR_ID JOIN WMSX_BillHead ON WMSX_InOutBillBody.BH_ID = WMSX_BillHead.BH_ID WHERE BH_Status = 1 AND BH_Date > '2009-07-01' GROUP BY BH_Type,BH_Date ORDER BY BH_Date DESC
再执行where 条件
--8) SELECT (9) DISTINCT (11) <TOP_specification> <select_list>
--(1) FROM <left_table>
--(3) <join_type> JOIN <right_table>
--(2) ON <join_condition>
--(4) WHERE <where_condition>
--(5) GROUP BY <group_by_list>
--(6) WITH {CUBE | ROLLUP}
--(7) HAVING <having_condition>
--(10) ORDER BY <order_by_list>
而后面的where语句是条件。
速度的话你测试一下才知道呢
---这个速度快点!
INTO TEMP_TABLE
FROM WMSX_InOutBillBody JOIN WMSX_WareRecord
ON WMSX_WareRecord.WR_ID = WMSX_InOutBillBody.WR_ID
JOIN WMSX_BillHead
ON WMSX_InOutBillBody.BH_ID = WMSX_BillHead.BH_ID
WHERE BH_Status = 1
GROUP BY BH_Type,BH_Date
ORDER BY BH_Date DESC
那为了执行这个语句,该为哪个字段建索引呢?
SELECT SUM(BH_COUNT*WR_Area) AS SUM_AREA,BH_Type,BH_Date
INTO TEMP_TABLE
FROM WMSX_InOutBillBody JOIN WMSX_WareRecord
ON WMSX_WareRecord.WR_ID = WMSX_InOutBillBody.WR_ID
JOIN WMSX_BillHead
ON WMSX_InOutBillBody.BH_ID = WMSX_BillHead.BH_ID
WHERE BH_Status = 1 AND BH_Date > '2009-07-01'
GROUP BY BH_Type,BH_Date
ORDER BY BH_Date DESC
WMSX_InOutBillBody.WR_ID 是关联WMSX_WareRecord.WR_ID的外键
WMSX_BillHead.BH_ID 是主键
WMSX_InOutBillBody.BH_ID 是关联WMSX_BillHead.BH_ID的外键,同时也是联合主键。