SQL查询:检索具有不同条件的列
问题描述:
我需要从不同表中检索具有不同条件的多个列。 什么,我有如下SQL查询:检索具有不同条件的列
SELECT
(SELECT COUNT(*) FROM Table1 WHERE Price >= 0 Price < 10) AS 'Group1',
(SELECT COUNT(*) FROM Table1 WHERE Price >= 10 AND Price < 20) AS 'Group2',
(SELECT COUNT(*) FROM Table1 WHERE Price >= 20 AND Price < 30) AS 'Group3',
(SELECT COUNT(*) FROM Table2 WHERE Price >= 0 Price < 10) AS 'Group4',
(SELECT COUNT(*) FROM Table2 WHERE Price >= 10 AND Price < 20) AS 'Group5',
(SELECT COUNT(*) FROM Table2 WHERE Price >= 20 AND Price < 30) AS 'Group6'
只是想知道有没有什么更好的方式来重组为上述条件更好的查询?
答
有,
SELECT SUM(CASE WHEN Price >= 0 Price < 10 THEN 1 END) AS 'Group1',
SUM(CASE WHEN Price >= 10 AND Price < 20 THEN 1 END) AS 'Group2',
SUM(CASE WHEN Price >= 20 AND Price < 30 THEN 1 END) AS 'Group3',
SUM(CASE WHEN Price >= 0 Price < 10 THEN 1 END) AS 'Group4',
SUM(CASE WHEN Price >= 10 AND Price < 20 THEN 1 END) AS 'Group5',
SUM(CASE WHEN Price >= 20 AND Price < 30 THEN 1 END) AS 'Group6'
FROM table2
答
用j W.加起来table1的上述精彩查询
SELECT SUM(CASE WHEN T1.Price >= 0 Price < 10 THEN 1 END) AS 'Group1',
SUM(CASE WHEN T1.Price >= 10 AND Price < 20 THEN 1 END) AS 'Group2',
SUM(CASE WHEN T1.Price >= 20 AND Price < 30 THEN 1 END) AS 'Group3',
SUM(CASE WHEN T2.Price >= 0 Price < 10 THEN 1 END) AS 'Group4',
SUM(CASE WHEN T2.Price >= 10 AND Price < 20 THEN 1 END) AS 'Group5',
SUM(CASE WHEN T2.Price >= 20 AND Price < 30 THEN 1 END) AS 'Group6'
FROM table1 T1, table2 T2
+0
太棒了。谢谢。 – My2ndLovE 2013-04-10 07:25:36
+0
快乐...... – user2135380 2013-04-10 07:33:14
为什么好,有什么区别?问题中还有2个表格。 – Alex 2013-04-10 06:10:23
@Alexey它只会传递一次表格,而不是调用6'SELECT'语句 – 2013-04-10 06:12:30
@J W但由于价格限制,它不会执行全面扫描。所以在一起看起来都一样。 – Alex 2013-04-10 06:14:17