为什么我在SQL的where条件后面加一个1 = 1 性能就变快多了,求解释!
我的SQL语句是这样写的:
Dim sqlda As SqlDataAdapter
Dim query As StringBuilder = New StringBuilder()
query.Append(" SELECT STORE_ID, CUSTOMER_ID, JANCODE, ITEM_KANA, ")
query.Append(" SALES_QUANTITY_1 - SALES_QUANTITY_2 AS SALES_QUANTITY, ")
query.Append(" SALES_PRICE_SUM_1 - SALES_PRICE_SUM_2 - DETAIL_DISCOUNT - PROPORTIONAL_DISCOUNT AS SALES_PRICE ")
query.Append(" FROM (SELECT SALES.STORE_ID AS STORE_ID, ")
query.Append(" ITEM.CUSTOMER_ID AS CUSTOMER_ID, ITEM.JANCODE AS JANCODE, ")
query.Append(" ITEM.ITEM_KANA AS ITEM_KANA, ")
query.Append(" MAX(SALES.RETURN_DIVISION) AS RETURN_DIVISION, ")
query.Append(" MAX(SALES.DELETE_DIVISION) AS DELETE_DIVISION, ")
query.Append(" SUM(CASE WHEN ((SALES.RETURN_DIVISION = 0) ")
query.Append(" AND (SALES.DELETE_DIVISION = 0)) OR ((SALES.RETURN_DIVISION = 1) ")
query.Append(" AND (SALES.DELETE_DIVISION = 1)) THEN SALES.SALES_PRICE_SUM ")
query.Append(" ELSE 0 END) AS SALES_PRICE_SUM_1, SUM(CASE WHEN ((SALES.RETURN_DIVISION = 0) ")
query.Append(" AND (SALES.DELETE_DIVISION = 1)) OR ((SALES.RETURN_DIVISION = 1) ")
query.Append(" AND (SALES.DELETE_DIVISION = 0)) THEN SALES.SALES_PRICE_SUM ")
query.Append(" ELSE 0 END) AS SALES_PRICE_SUM_2, SUM(CASE WHEN ((SALES.RETURN_DIVISION = 0) ")
query.Append(" AND (SALES.DELETE_DIVISION = 0)) OR ((SALES.RETURN_DIVISION = 1) ")
query.Append(" AND (SALES.DELETE_DIVISION = 1)) THEN SALES.SALES_QUANTITY ")
query.Append(" ELSE 0 END) AS SALES_QUANTITY_1, SUM(CASE WHEN ((SALES.RETURN_DIVISION = 0) ")
query.Append(" AND (SALES.DELETE_DIVISION = 1)) OR ((SALES.RETURN_DIVISION = 1) ")
query.Append(" AND (SALES.DELETE_DIVISION = 0)) THEN SALES.SALES_QUANTITY ")
query.Append(" ELSE 0 END) AS SALES_QUANTITY_2, SUM(SALES.DETAIL_DISCOUNT) AS DETAIL_DISCOUNT, ")
query.Append(" SUM(SALES.PROPORTIONAL_DISCOUNT) AS PROPORTIONAL_DISCOUNT ")
query.Append(" FROM SALES INNER JOIN ITEM ON SALES.ITEM_ID = ITEM.ITEM_ID ")
query.Append(" INNER JOIN STORE ON STORE.STORE_ID = SALES.SALES_ID ")
query.Append(" INNER JOIN CUSTOMER ON ITEM.CUSTOMER_ID = CUSTOMER.CUSTOMER_ID ")
query.Append(" WHERE SALES_DATE BETWEEN @SALES_DATE_START AND @SALES_DATE_END")
If storeid = 0 Then
If customer_id <> 0 Then
query.Append(" AND ITEM.CUSTOMER_ID = @CUSTOMER_ID")
Else
query.Append(" AND 1 = 1 ")
End If
query.Append(" GROUP BY SALES.STORE_ID, ITEM.JANCODE, ITEM.ITEM_KANA, ITEM.CUSTOMER_ID, ")
query.Append(" RETURN_DIVISION, DELETE_DIVISION, SALES_PRICE_SUM, ")
query.Append(" SALES_QUANTITY) AS AMOUNT ORDER BY ")
If orderflag = 0 Then
query.Append(" SALES_QUANTITY ")
Else
query.Append(" SALES_PRICE ")
End If
&n