由于数据量比较大,而且要在一页显示出来,所以查询的语句很多,大约有3000多条,并且每条都有单价*数量这样的计算,如下:select a.quantity*a.price as total_amount1 from apple a,clother b where a.item_id=b.id and box_id=10310321
.....
.............
.........................
.....................................
select a.quantity*a.price as total_amount1 from apple a,clother b where a.item_id=b.id and box_id=10313906最终的结果导致花费了很长时间,不知道有没有办法优化呢?
.....
.............
.........................
.....................................
select a.quantity*a.price as total_amount1 from apple a,clother b where a.item_id=b.id and box_id=10313906最终的结果导致花费了很长时间,不知道有没有办法优化呢?
这里涉及到的字段有索引吗,没有的加上试下
where a.item_id=b.id and box_id between 10310321 and 10313906
where a.item_id=b.id and box_id between 10310321 and 10313906
order by box_id
呢
既然计算得多,可在DB上加一个公式,并实体存储它
where a.item_id=b.id and box_id in ( 10310321 ,..., 10313906)
order by box_id
在插入数据时就把它计算出来, 到时要取的时候, 直接拿Total就好了.
1:数据库设计方法 正如yenange所说
2:SQL语不能用得太死了
这样的
如:select a.quantity*a.price as total_amount1 from apple a,clother b
where a.item_id=b.id and box_id in ( 10310321 ,..., 10313906)
order by box_id
又如
select a.quantity*a.price as total_amount1 from apple a,clother b
where a.item_id=b.id and box_id in (select box_id from apple a where 条件组合)
order by box_id
exec('select a.quantity*a.price as total_amount1 from apple a,clother b where a.item_id=b.id and box_id in('+@box_IDs+')'