MySQL database, there is a the userinfo (personnel) tables and dept (department), the userinfo table there is a field the userinfo. Dept to save a person's primary sector, secondary sector and tertiary sector id, separated by commas, the dept table have dept. Id primary key (id) and dept., the name (division name)
Q: how to use an SQL query each staff of information, especially the department of each level information?

CodePudding user response:

The CREATE TABLE [User]
(
UserId INT,
The UserName VARCHAR (50),
Depart VARCHAR (50)
)

The CREATE TABLE Depart
(
DepartId INT,
DepartName VARCHAR (50)
)

INSERT INTO dbo. [User]
(UserId, UserName, Depart)
SELECT 1, 'zhang', '1, 2, 3' UNION
SELECT 2, 'bill', '4' 6 '

INSERT INTO dbo. Depart
(DepartId, DepartName)
SELECT 1, 'shenzhen' UNION
SELECT 2, 'development' UNION
SELECT 3, 'to develop a set of' UNION
SELECT 4, 'Beijing' UNION
SELECT 5, 'marketing' UNION
SELECT 6, part of the 'market'

SELECT *
The FROM
(
SELECT A. *, the SUBSTRING (a. d. epart, 0, CHARINDEX (', ', a. d. epart, 0)) AS FirstDepart, SUBSTRING (a. d. epart, CHARINDEX (', ', a. d. epart, 0) + 1, CHARINDEX (', ', a. d. epart, CHARINDEX (', ', a. d. epart, 0)) - 1) AS SecondDepart,
The SUBSTRING (REVERSE (a. d. epart), 0, CHARINDEX (', ', REVERSE (a. d. epart), 0)) AS ThreeDepart
The FROM dbo. [User] AS A
) AS A
INNER JOIN dbo. Depart AS B ON A.F irstDepart=B.D epartId
INNER JOIN dbo. Depart AS C ON A.S econdDepart=C.D epartId
INNER JOIN dbo. Depart AS D ON A.T hreeDepart=D.D epartId

CodePudding user response:

Does not provide a screenshot, I made a few data
Said the train of thought:
If you [the userinfo. Dept] each of the data, there are two commas, that can use the above code I

The SUBSTRING function mainly used CHARINDEX function

Is relatively simple, first find the first comma left for the first department
Then find the second comma for the second on the left side of the unit (note the SUBSTRING function of the second parameter line)
The third sector, to the field inversion, just like to find the first sector

CodePudding user response:

No effect but appreciate it

CodePudding user response:

When reference.
This is for dept field length inconsistent statements:
SELECT Anderson d, A.n ame, a. d. ept, GROUP_CONCAT (B.n ame ORDER BY Anderson, d2 separator ', ') AS the deptName
The FROM
(
The SELECT B.i d, B.n ame, B.d ept, A.h elp_topic_id AS Id2, SUBSTRING_INDEX (SUBSTRING_INDEX (B.d ept, 'and' help_topic_id + 1), ', ', 1) AS dept2
The FROM help_topic AS A
INNER JOIN the userinfo AS B ON A.h elp_topic_id & lt; (LENGTH (B.d ept) - LENGTH (the REPLACE (B.d ept, ', ', ')) + 1)
) AS A
INNER JOIN dept AS B ON a. d. ept2=CONCAT (B.i d, ' ')
GROUP BY Anderson, d, A.n ame, a. d. ept