数据表字段为:id,fid,name想查询得到如下结果:即array('id'=>'id_fid',……)$rowsIndex=array('66'=>'66_0','67'=>'67_0','68'=>'68_67','69'=>'69_67','70'=>'70_69','71'=>'71_0','72'=>'72_71','73'=>'73_71','74'=>'74_71','75'=>'75_0','76'=>'76_75','77'=>'77_75','78'=>'78_75','79'=>'79_75','80'=>'80_0','81'=>'81_80','82'=>'82_80','83'=>'83_80','84'=>'84_80','85'=>'85_0','86'=>'86_85','87'=>'87_0','88'=>'88_87','89'=>'89_0','90'=>'90_89','91'=>'91_89','92'=>'92_0','93'=>'93_92'
);请问查询语句该怎么写?CONCAT_WS 怎么用?
);请问查询语句该怎么写?CONCAT_WS 怎么用?
假设你的表 tbl (id,col)
如下
id col
66 66_0
67 67_0
...则mysql> create table tbl
-> (
-> id int primary key,
-> col varchar(10)
-> );
Query OK, 0 rows affected (0.13 sec)mysql> insert into tbl values
-> (66,'66_0' ),
-> (67,'67_0' ),
-> (68,'68_67'),
-> (69,'69_67'),
-> (70,'70_69'),
-> (71,'71_0' ),
-> (72,'72_71'),
-> (73,'73_71'),
-> (74,'74_71'),
-> (75,'75_0' ),
-> (76,'76_75'),
-> (77,'77_75'),
-> (78,'78_75'),
-> (79,'79_75'),
-> (80,'80_0' ),
-> (81,'81_80'),
-> (82,'82_80'),
-> (83,'83_80'),
-> (84,'84_80'),
-> (85,'85_0' ),
-> (86,'86_85'),
-> (87,'87_0' ),
-> (88,'88_87'),
-> (89,'89_0' ),
-> (90,'90_89'),
-> (91,'91_89'),
-> (92,'92_0' ),
-> (93,'93_92');
Query OK, 28 rows affected (0.09 sec)
Records: 28 Duplicates: 0 Warnings: 0mysql> select GROUP_CONCAT(concat(QUOTE(id),'=>',QUOTE(col)))
-> from tbl;
+--------------------------------------------------------------------
| '66'=>'66_0','67'=>'67_0','68'=>'68_67','69'=>'69_67','70'=>'70_69',
0','72'=>'72_71','73'=>'73_71','74'=>'74_71','75'=>'75_0','76'=>'76_75
7_75','78'=>'78_75','79'=>'79_75','80'=>'80_0','81'=>'81_80','82'=>'82
>'83_80','84'=>'84_80','85'=>'85_0','86'=>'86_85','87'=>'87_0','88'=>'
'=>'89_0','90'=>'90_89','91'=>'91_89','92'=>'92_0','93'=>'93_92' |,
CONCAT_WS() 代表 CONCAT With Separator ,是CONCAT()的特殊形式。 第一个参数是其它参数的分隔符。分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串,也可以是其它参数。如果分隔符为 NULL,则结果为 NULL。函数会忽略任何分隔符参数后的 NULL 值。mysql> SELECT CONCAT_WS(',','First name','Second name','Last Name'); -> 'First name,Second name,Last Name'mysql> SELECT CONCAT_WS(',','First name',NULL,'Last Name'); -> 'First name,Last Name'CONCAT_WS()不会忽略任何空字符串。 (然而会忽略所有的 NULL)。