我有一个数据库表起重机尾部（字段为：cf_did , cf_firstname, cf_cranetype）
cf_did cf_firstname cf_cranetype
1 Alexy 2,3
2 Thomas 11,6,3
3 Thomas 5,6,11,3
4 Thomasxc 1,6,9,4
5 Thomaseg 11,12,3
我有另一台起重机类型（cf_did,cf_cname）
cf_ctid cf_ctname
1 MOBILE CRANES
2 ROUGH TERRAIN CRANES
3 ALL TERRAIN CRANES
4 CRAWLER CRANES
5 YARD CRANES
6 ARICULATING BOOM CRANES
7 TRUCK CRANES: ARTICULATING BOOM
8 TRUCK CRANES: LATTICE BOOM
9 TRUCK CRANES: TELESCOPIC BOOM
10 TRUCK LOADER CRANES
11 ELEVATORS
12 ELEVATOR – HYDRAULIC
我想显示这样的值，例如：
id: name: cranetype
1 Alexy ROUGH TERRAIN CRANES,ALL TERRAIN CRANES
2 Thomas ELEVATORS,ARICULATING BOOM CRANES,ALL TERRAIN CRANES
等等
如何从表cranetype cf_ctnamefield（ROUGH TERRAIN CRANES，ALL TERRAIN CRENES）中获取表起重机尾部字段cf_cranetype值（2,3）名称。
我是教派$did=1。并使用此代码
$sql=mysql_query("SELECT * FROM `cf_directory` WHERE `cf_did` = '$did'",$con);
$row = mysql_fetch_array($sql);$cn= $row['11'];
$sql2=mysql_query("SELECT * FROM `cf_cranetype` WHERE `cf_ctid`= '$cn'",$con);
<?php while($row2 = mysql_fetch_array($sql2)){
$cranename=$row2['cf_ctname']; ?>
<?php echo $cranename;?>
但是输出是：
id: name: cranetype
1 Alexy ROUGH TERRAIN CRANES
我想要：
id: name: cranetype
1 Alexy ROUGH TERRAIN CRANES,ALL TERRAIN CRANES
您可以简单地将GROUP_CONCAT()与FIND_IN_SET()一起使用：
SELECT
cd.cf_did AS id,
cd.cf_firstname AS name,
GROUP_CONCAT(ct.cf_ctname SEPARATOR ', ') AS cranetype
FROM
cranedetails cd,
cranetype ct
WHERE
FIND_IN_SET(ct.cf_ctid, cd.cf_cranetype)
GROUP BY
cd.cf_did,
cd.cf_firstname
输出为：
+----+----------+---------------------------------------------------------------------------------------+
| id | name | cranetype |
+----+----------+---------------------------------------------------------------------------------------+
| 1 | Alexy | ROUGH TERRAIN CRANES, ALL TERRAIN CRANES |
| 2 | Thomas | ARICULATING BOOM CRANES, ELEVATORS, ALL TERRAIN CRANES |
| 3 | Thomas | ALL TERRAIN CRANES, YARD CRANES, ARICULATING BOOM CRANES, ELEVATORS |
| 4 | Thomasxc | CRAWLER CRANES, ARICULATING BOOM CRANES, TRUCK CRANES: TELESCOPIC BOOM, MOBILE CRANES |
| 5 | Thomaseg | ALL TERRAIN CRANES, ELEVATORS, ELEVATOR – HYDRAULIC |
+----+----------+---------------------------------------------------------------------------------------+
5 rows in set
如果只想按名称对结果进行分组（因为存在重复的名称），只需从查询中删除cd.cf_did并添加DISTINCT即可过滤重复的cranetype值，即：
SELECT
cd.cf_firstname AS name,
GROUP_CONCAT(DISTINCT ct.cf_ctname SEPARATOR ', ') AS cranetype
FROM
cranedetails cd,
cranetype ct
WHERE
FIND_IN_SET(ct.cf_ctid, cd.cf_cranetype)
GROUP BY
cd.cf_firstname
结果将是：
+----------+---------------------------------------------------------------------------------------+
| name | cranetype |
+----------+---------------------------------------------------------------------------------------+
| Alexy | ROUGH TERRAIN CRANES, ALL TERRAIN CRANES |
| Thomas | ELEVATORS, ALL TERRAIN CRANES, YARD CRANES, ARICULATING BOOM CRANES |
| Thomaseg | ELEVATORS, ELEVATOR – HYDRAULIC, ALL TERRAIN CRANES |
| Thomasxc | TRUCK CRANES: TELESCOPIC BOOM, MOBILE CRANES, CRAWLER CRANES, ARICULATING BOOM CRANES |
+----------+---------------------------------------------------------------------------------------+
4 rows in set
使用子查询获取此项以供参考http://dev.mysql.com/doc/refman/5.7/en/subqueries.html
对我来说，它看起来像是在存储一个由逗号连接的整数值（cranetype-ids）组成的字符串值（或VARCHAR）。
如果是这样的话，我会考虑重新设计您的数据库，使其具有一个表，例如
- 具有cf_nid、cf_firstname的cranenames
- 带有cf_ctid cf_ctname的起重机类型
- 带有cf_did、cf_nid、cf_ctid的起重机尾部
在裂隙尾部，为与裂隙名称相关联的每个裂隙类型创建一个条目。当然，cf_nid和cf_ctid应该是各自表的外键关系。
在当前的设计中，您必须分解cf_cranetype的字符串值才能获得一个整数数组（或看起来像整数的字符串），而您的第二个查询必须是这样的："SELECT * FROM cf_cranetype WHERE cf_ctid IN '" . join($YOUR_ARRAY, ',') . "'"