mysql 针对竖线分割的字段进行操作

1. 图书

• 这里就是记录一下，没有什么特别之处。

  
  图书分配.png

-- 获取本校图书

SELECT fkBook FROM TbBookStock WHERE fkSchool='000Q' AND (nState=2 OR nState=3 OR nState=4) AND (fkBook !='')  GROUP BY fkBook

-- 获取本校所有图书分类标签

SELECT pkCode,sName FROM TbLabel WHERE pkCode IN ( SELECT DISTINCT(f.fkLabel) FROM ( SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(a.fkLabel,'|',b.help_topic_id+1),'|',-1) AS fkLabel
FROM
`TbBook` a
JOIN
mysql.help_topic b
ON b.help_topic_id < (LENGTH(a.fkLabel) - LENGTH(REPLACE(a.fkLabel,'|',''))+1)
AND a.fkLabel !='' AND a.`pkCode` IN (SELECT fkBook FROM TbBookStock WHERE fkSchool='000Q' AND (nState=2 OR nState=3 OR nState=4) AND (fkBook !='')  GROUP BY fkBook) ORDER BY a.fkLabel ) f);

-- 科目中已选则的图书


SELECT * FROM `TbBook` WHERE pkCode IN (SELECT DISTINCT(g.sBook) FROM ( SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(a.sBook,'|',b.help_topic_id+1),'|',-1) AS sBook
FROM
`TbBookList`  a
JOIN
mysql.help_topic b
ON b.help_topic_id < (LENGTH(a.sBook) - LENGTH(REPLACE(a.sBook,'|',''))+1)
AND pkCode = '001H' ORDER BY a.sBook) g);