比如一个字段
CREATE TABLE inventory2 (
`InventoryName` varchar(50) NOT NULL)insert into inventory2 value('Quilt Cover Set','Cushion','Euro P/case','BATH TOWEL');result:
InventoryName
QCS
C
EP
BT如何能用一个语句把每个字段的头字段取出来
CREATE TABLE inventory2 (
`InventoryName` varchar(50) NOT NULL)insert into inventory2 value('Quilt Cover Set','Cushion','Euro P/case','BATH TOWEL');result:
InventoryName
QCS
C
EP
BT如何能用一个语句把每个字段的头字段取出来
解决方案 »
- MySQL的check约束只是一个摆设,不起作用,那岂不是很不方便,得在应用程序中去实现check
- mysql 把int转换为varchar怎么实现呢?
- 求高手一SQL语句,不知道能不能实现!!
- 如何解决 ./bin/my_print_defaults: cannot execute binary file
- 我在phpmyadmin中修改mysql密码后,应再也无法登录了,如何办呢?
- sum()函数计算时间
- 急文在select where中,两个datetime不能用=吗?
- Delphi怎么才能联上Mysql??
- 帮我分析分析,这个进程,
- RedHat Linux 7.2 下成功安装MySql后为什么不能使用?(在线守候)
- mysql复制的问题
- mysql问题
ATH TOWEL');
ERROR 1136 (21S01): Column count doesn't match value count at row 1
你提供的测试用例有误。建议先自己测试一下,然后再贴出来。
SUBSTRING_INDEX(a1.InventoryName,' ',b.id),' ',-1),1) ORDER BY id SEPARATOR '' ) AS ss
FROM inventory2 a1 LEFT JOIN zz.lsb1 b
ON (LENGTH(a1.InventoryName)-LENGTH(REPLACE(a1.InventoryName,' ','')))+1>=b.id
GROUP BY a1.`InventoryName`lsb1:字段ID,1-10000
`InventoryName` varchar(50));insert into inventory2 value ('Quilt Cover Set'),('Cushion'),('Euro P/case'),('BATH TOWEL')
insert into sn values
(1),(2),(3),....,(20);mysql> select a.InventoryName,group_concat(mid(a.InventoryName,b.id+(b.id>1),1)
SEPARATOR '') as ABB
-> from inventory2 a,sn b
-> where mid(a.InventoryName,b.id,1)=' '
-> or b.id=1
-> group by a.InventoryName;
+-----------------+------+
| InventoryName | ABB |
+-----------------+------+
| BATH TOWEL | TB |
| Cushion | C |
| Euro P/case | PE |
| Quilt Cover Set | QCS |
+-----------------+------+
4 rows in set (0.06 sec)mysql>
CREATE TABLE inventory2 (
`InventoryName` VARCHAR(50) NOT NULL);INSERT INTO inventory2 VALUE('Quilt Cover Set'),('Cushion'),('Euro P/case'),('BATH TOWEL');SELECT a1.`InventoryName`,GROUP_CONCAT(LEFT(SUBSTRING_INDEX(
SUBSTRING_INDEX(a1.InventoryName,' ',b.id),' ',-1),1) ORDER BY id SEPARATOR '' ) AS ss
FROM inventory2 a1 LEFT JOIN zz.lsb1 b
ON (LENGTH(a1.InventoryName)-LENGTH(REPLACE(a1.InventoryName,' ','')))+1>=b.id
GROUP BY a1.`InventoryName`
Query OK, 0 rows affected (0.03 sec)mysql> CREATE TABLE inventory2 (
-> `InventoryName` VARCHAR(50) NOT NULL);
Query OK, 0 rows affected (0.06 sec)mysql>
mysql> INSERT INTO inventory2 VALUE('Quilt Cover Set'),('Cushion'),('Euro P/c
'),('BATH TOWEL');
Query OK, 4 rows affected (0.02 sec)
Records: 4 Duplicates: 0 Warnings: 0mysql>
mysql>
mysql>
mysql> SELECT a1.`InventoryName`,GROUP_CONCAT(LEFT(SUBSTRING_INDEX(
-> SUBSTRING_INDEX(a1.InventoryName,' ',b.id),' ',-1),1) ORDER BY id SEPA
OR '' ) AS ss
-> FROM inventory2 a1 LEFT JOIN zz.lsb1 b
-> ON (LENGTH(a1.InventoryName)-LENGTH(REPLACE(a1.InventoryName,' ','')))
=b.id
-> GROUP BY a1.`InventoryName`
-> ;
+-----------------+------+
| InventoryName | ss |
+-----------------+------+
| BATH TOWEL | BT |
| Cushion | C |
| Euro P/case | EP |
| Quilt Cover Set | QCS |
+-----------------+------+
4 rows in set (0.00 sec)mysql>