在保持逻辑的同时进行JOIN子查询-SubqueriestoJOINwhilekeepinglogic

1  

add an index to your dateStamp column.

在dateStamp列中添加索引。

use :

dateStamp > '2009-04-16' AND dateStamp <'2012-02-29'

instead of :

代替 :

dateStamp BETWEEN '2009-04-16' AND '2012-02-29'

it's more efficient.

它效率更高。

Finally, you can write a loop in PHP instead of subqueries.

最后,您可以在PHP中编写循环而不是子查询。

1  

It would be interesting to see if this answer a) works and b) is faster. Try this:

看看这个答案a)是否有效以及b)是否更快将会很有趣。尝试这个:

SELECT
    il.entryID,
    il.entryID AS iE,
    0 AS eE,
    'clarus1' AS locationID,
    il.dateStamp,
    il.amount AS income,
    NULL AS expense,
    il.reconciled,
    il.leaseID AS vendorID,
    il.incomeID AS expenseID
FROM
    structu_income.iLedger il
    INNER JOIN structu_assets.lease l ON il.leaseID = l.leaseID
    INNER JOIN structu_assets.unit u ON l.unitID = u.unitID AND u.locatiOnID= 'clarus1'
WHERE
    il.dateStamp BETWEEN '2009-04-16' AND '2012-02-29'
    AND il.incomeID IS NOT NULL
UNION
SELECT
    il.entryID,
    il.entryID AS iE,
    0 AS eE,
    'clarus1' AS locationID,
    il.dateStamp,
    il.amount AS income,
    NULL AS expense,
    il.reconciled,
    il.leaseID AS vendorID,
    il.incomeID AS expenseID
FROM
    structu_income.iLedger il
    INNER JOIN structu_assets.deed d ON il.locatiOnID= d.locationID AND d.ownerID = 'clarus'
WHERE
    il.dateStamp BETWEEN '2009-04-16' AND '2012-02-29'
    AND il.incomeID IS NOT NULL

The first SELECT query takes care of the first half of your OR conditions, and the second SELECT query adds in the results for the second half. And you shouldn't get duplicate rows with UNION so I believe you should get the same results.

第一个SELECT查询负责OR条件的前半部分,第二个SELECT查询添加后半部分的结果。你不应该用UNION获得重复的行,所以我相信你应该得到相同的结果。