SELECT
(SELECT COUNT(*) FROM table1 WHERE id1=1001) AS a1,
(SELECT COUNT(*) FROM table1 WHERE id2=2001) AS a2,
(SELECT COUNT(*) FROM table1 WHERE id1=1001 AND id2=2001) AS a3,
(SELECT SUM(num1) FROM table1 WHERE id1=1001) AS b1,
(SELECT SUM(num1) FROM table1 WHERE id2=2001) AS b2,
(SELECT SUM(num1) FROM table1 WHERE id1=1001 AND id2=2001) AS b3,
(SELECT SUM(num2) FROM table1 WHERE id1=1001) AS c1,
(SELECT SUM(num2) FROM table1 WHERE id2=2001) AS c2,
(SELECT SUM(num2) FROM table1 WHERE id1=1001 AND id2=2001) AS c3这样就可以获得a1,a2,a3,b1,b2,b3,c1,c2,c3这9个值,有什么方法可以把这个sql语句进行优化么?就是让速度再快一些
(SELECT COUNT(*) FROM table1 WHERE id1=1001) AS a1,
(SELECT COUNT(*) FROM table1 WHERE id2=2001) AS a2,
(SELECT COUNT(*) FROM table1 WHERE id1=1001 AND id2=2001) AS a3,
(SELECT SUM(num1) FROM table1 WHERE id1=1001) AS b1,
(SELECT SUM(num1) FROM table1 WHERE id2=2001) AS b2,
(SELECT SUM(num1) FROM table1 WHERE id1=1001 AND id2=2001) AS b3,
(SELECT SUM(num2) FROM table1 WHERE id1=1001) AS c1,
(SELECT SUM(num2) FROM table1 WHERE id2=2001) AS c2,
(SELECT SUM(num2) FROM table1 WHERE id1=1001 AND id2=2001) AS c3这样就可以获得a1,a2,a3,b1,b2,b3,c1,c2,c3这9个值,有什么方法可以把这个sql语句进行优化么?就是让速度再快一些
SELECT
(SELECT COUNT(*) as a1, sum(num1) as b1, sum(num2) as c1 FROM table1 WHERE id1=1001)
(SELECT COUNT(*) as a2, sum(num1) as b2, sum(num2) as c2 FROM table1 WHERE id2=2001)
(SELECT COUNT(*) as a3, sum(num1) as b3, sum(num2) as c3 FROM table1 WHERE id1=1001 AND id2=2001) AS c3)
)
并且在表的id1, id2建立符合簇索引就可以了。