Use of the mysql function IF and GROUP_CONCAT

In mysql, IF and GROUP_CONCAT use the recently used functions in mysql, and record www.2cto.com 1 and IF (expr1, expr2, expr3) are similar to common three-object operations. Expr1 is an expression. If TRUE, expr2 is returned; otherwise, the following data is returned: SQL code 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'); for example, to query the return status of a, 1 indicates yes, and 0 indicates no: SQL code SELECT IF (a = 1, 'yes', 'no') as flag FROM a sometimes needs to compare two columns of data, When a and B are compared, their values are calculated: java code 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 code 1 3 1 1 This completes statistics by type. 2. GROUP_CONCAT returns non-NULL in a group of data as a concatenation string, which is often used together with a group. To put it simply, it refers to row-to-column conversion. The following data: SQL code 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,) 'values ('1', '4'); return the SQL code id a ----------- 1 | 0, 1, 4 2 |, so we can use this function to obtain the SQL code SELECT id, GROUP_CONCAT (a) FROM table2 group by id; to see the GROUP_CONCAT Syntax: SQL code GROU P_CONCAT ([DISTINCT] expr [, expr...] [order by {unsigned_integer | col_name | expr} [ASC | DESC] [, col_name...] [SEPARATOR str_val]) refer to the doc of this function: DISTINCT can be used to remove duplicates, order by sorting, and SEPARATOR to specify separators (the default value is ",") if the following data is available in Java code insert into 'table2 (id, a) 'values ('1', '0'); insert into 'table2 (id,) 'values ('1', '1'); insert into 'table2 (id, a) 'values ('2', '0'); insert into 'table2 (id, a) 'values ('2', '3'); INSE Rt into 'table2 (id, a) 'VALUES ('1', '4'); insert into 'table2 (id, a)' VALUES ('2 ', '3'); the SQL code SELECT id, GROUP_CONCAT (DISTINCT a ORDER BY a DESC SEPARATOR '-') must be displayed in descending ORDER of a and cannot be repeated '-') FROM table2 group by id; www.2cto.com: SQL code id a ----------------------- 1 4-1-0 2 3-0 with this function, we can handle some business tasks, for example, if there are two tables, one of which has one-to-many table IDs associated with the rid of table 4 in the other, you need to calculate the specific versions of ipad and mac, then we can directly use SQL to implement the Java code -- table3 (id, nam E) 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' Table4 (id, rid, name) 'values ('5', '2', 'air'); insert into 'table4 (id, rid, name) 'values ('6', '2', 'mini '); SQL code SELECT. id,. name, GROUP_CONCAT (B. name) as version FROM table3 a JOIN table4 B ON. id = B. rid group by. id; --- id name version 1 ipad ipad1, ipad2, ipad3 2 mac pro, air, mini Note: 1. The connection length is limited by the group_concat_max_len parameter, that is to say, not all of the returned results will be returned, but the default value is 1024, which is also very long. Of course, it may limit the type of the current concat field and max_all at the same time. Restrictions on owed_packet 2. Binary and non-binary strings returned by the connection, depending on the type of the current connection. It is possible that TEXT or BLOB will be returned if there are more than 512 entries. If the connection is int or another type, it is best to CONVERT it to Char first, such AS using the function CAST (expr AS type), CONVERT (expr, type ), see the Java code in the CAST document -- cast select cast (id as CHAR) FROM table4; -- Convert SELECT Convert (id, CHAR) FROM table4;