mysql中函數IF,GROUP_CONCAT的使用
mysql中最近用到的函數，記錄下 www.2cto.com
1、IF(expr1,expr2,expr3)與我們常用的三目運算類似。expr1是一個表達式，如果TRUE，返回expr2否則為expr3
如下數據：
Sql代碼
INSERT INTO a(id,a,b) VALUES ('1', '1', '1');
INSERT INTO a(id,a,b) VALUES ('2', '1', '0');
INSERT INTO a(id,a,b) VALUES ('3', '1', '0');
INSERT INTO a(id,a,b) VALUES ('4', '1', '0');
INSERT INTO a(id,a,b) VALUES ('5', '0', '0');
INSERT INTO a(id,a,b) VALUES ('6', '0', '1');
比如要查詢a的返回狀態，1代表是，0代表否有：
Sql代碼
SELECT IF(a=1,'是','否') as flag FROM a
有時需要比較兩列數據，如同時比較a、b其取值通過(1,1),(1,0),(0,1),(0,0)來統計：
Java代碼
SELECT
SUM(IF (a=1 AND b= 1, 1, 0)) as flag1,
SUM(IF (a=1 AND b= 0, 1, 0)) as flag2,
SUM(IF (a=0 AND b= 1, 1, 0)) as flag3,
SUM(IF (a=0 AND b= 0, 1, 0)) as flag4
FROM a
Java代碼
1 3 1 1
這樣就完成了按照類型來統計。
2、GROUP_CONCAT將一組數據中的non-NULL作為串聯的字符串返回，常與group在一起使用。簡單的說就是行轉列，如下數據：
Sql代碼
INSERT INTO `table2(id, a)` VALUES ('1', '0');
INSERT INTO `table2(id, a)` VALUES ('1', '1');
INSERT INTO `table2(id, a)` VALUES ('2', '0');
INSERT INTO `table2(id, a)` VALUES ('2', '3');
INSERT INTO `table2(id, a)` VALUES ('1', '4');
這裡需要返回
Sql代碼
id a
-----------
1 |0,1,4
2 |0,3
那麼我們可以通過該函數來獲取
Sql代碼
SELECT id, GROUP_CONCAT(a)
FROM table2
GROUP BY id;
來看看GROUP_CONCAT語法：
Sql代碼
GROUP_CONCAT([DISTINCT] expr [,expr ...]
[ORDER BY {unsigned_integer | col_name | expr}
[ASC | DESC] [,col_name ...]]
[SEPARATOR str_val])
參考該函數的doc ：可以DISTINCT去重， ORDER BY排序，SEPARATOR 來指定分隔符(默認為“,”)如有下面數據
Java代碼
INSERT INTO `table2(id, a)` VALUES ('1', '0');
INSERT INTO `table2(id, a)` VALUES ('1', '1');
INSERT INTO `table2(id, a)` VALUES ('2', '0');
INSERT INTO `table2(id, a)` VALUES ('2', '3');
INSERT INTO `table2(id, a)` VALUES ('1', '4');
INSERT INTO `table2(id, a)` VALUES ('2', '3');
我們需要顯示出來的按照a降序、不能重復：
Sql代碼
SELECT id, GROUP_CONCAT(DISTINCT a ORDER BY a DESC SEPARATOR '-')
FROM table2
GROUP BY id;
www.2cto.com
這樣輸出結果：
Sql代碼
id a
-----------------------
1 4-1-0
2 3-0
有了這個函數我們就可以處理一些業務上的事情了，比如現在有兩張表其中一張table3的id一對多與另一張表table4的rid關聯，現在要統計ipad和mac的具體版本，那麼我們就可以直接用sql實現了
Java代碼
-- table3(id, name)
INSERT INTO `table3(id, name)` VALUES ('1', 'ipad');
INSERT INTO `table3(id, name)` VALUES ('2', 'mac');
-- table4(id, rid, name)
INSERT INTO `table4(id, rid, name)` VALUES ('1', '1', 'ipad1');
INSERT INTO `table4(id, rid, name)` VALUES ('2', '1', 'ipad2');
INSERT INTO `table4(id, rid, name)` VALUES ('3', '1', 'ipad3');
INSERT INTO `table4(id, rid, name)` VALUES ('4', '2', 'pro');
INSERT INTO `table4(id, rid, name)` VALUES ('5', '2', 'air');
INSERT INTO `table4(id, rid, name)` VALUES ('6', '2', 'mini');
Sql代碼
SELECT
a.id,
a.name,
GROUP_CONCAT(b.name) as version
FROM table3 a JOIN table4 b ON a.id = b.rid
GROUP BY a.id;
---
id name version
1 ipad ipad1,ipad2,ipad3
2 mac pro,air,mini
注意事項：
1、連接的長度受group_concat_max_len參數限制，也就是說這個返回這個長度不是所有都會返回，但是默認為1024也很長了，當然具體可能會到當前concat字段的類型限制同時和max_allowed_packet的限制
2、連接返回二進制和非二進制string，依賴當前連接的類型。有可能超過512個後就返回TEXT或BLOB。如果連接的是int或其他最好先轉成Char，如使用函數CAST(expr AS type), CONVERT(expr,type)，見CAST文檔
Java代碼
-- CAST
SELECT CAST(id as CHAR) FROM table4;
-- Convert
SELECT Convert(id, CHAR) FROM table4;
