工作中同僚提的一個問題:
有一張使用者表,表裡面三個字段,分别是使用者ID,使用者姓名,和使用者薪水,表裡面有100萬條資料,如何用一句SQL查詢出
薪水小于1000,薪水=1000,1000<薪水<5000,薪水=5000,薪水>5000的人。寫出的SQL效率越高越好。
測試:
CREATE TABLE
test
(
id
int(11) NOT NULL AUTO_INCREMENT,
money
int(11) DEFAULT NULL,
PRIMARY KEY (
id
)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
插入100W條資料;進行寫sql。
最開始打算使用最普通的方式寫:
SELECT '<1000',count() from (
SELECT
id
FROM
test
where
money >=
and money <
) f1
UNION ALL
SELECT '=1000',count() from (
SELECT
id
FROM
test where money=
) f2
UNION ALL
SELECT '1000<money<5000',count() from (
SELECT
id
FROM
test
where money> and money <
) f3
UNION ALL
SELECT '=5000',count() from (
SELECT
id
FROM
test
where money =
) f4
UNION ALL
SELECT '>5000',count() from (
SELECT
id
FROM
test
where
money >
) f5
剛開始想到的方式。。。用了1.103s ,肯定是效率不達标的。
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLiQ3chVEa0V3bT9CX5RXa2Fmcn9CXwczLcVmds92czlGZvwVP9EUTDZ0aRJkSwk0LcxGbpZ2LcBDM08CXlpXazRnbvZ2LcRlMMVDT2EWNvwFdu9mZvwVP9cnTycGVONTRq1EMNRFZ2hmMZZXUYpVd1kmYr50MZV3YyI2cKJDT29GRjBjUIF2LcRHelR3LcJzLctmch1mclRXY39DNwEjMxADNwEjNxUDM3EDMy8CX0Vmbu4GZzNmLn9Gbi1yZtl2Lc9CX6MHc0RHaiojIsJye.jpg)
後面公司開發老大發出的sql:
SELECT
f,
COUNT()
FROM
(
SELECT
IF (
money < ,
,
IF (
money = ,
,
IF (
money < ,
,
IF (money = , , )
)
)
) AS f
FROM
test
) t
GROUP BY
f
隻用了0.413s,很厲害;
可以從sql中看出使用了if的語句,很大程度提高了效率。
sql的效果是對每一條都執行判斷然後附上對應條件的值。
這樣寫應該好看懂一點點
SELECT
f,
COUNT()
from (
SELECT
IF (money < , ,
IF(money = ,,
IF(money < ,,
IF(money = ,,)
)
)
) f
FROM
test
) r GROUP BY f
sql中的if語句是一層一層嵌套執行着的。