mysql两表查询的问题。

Question

那么问题来了，工资是属于敏感和金钱组的，为啥这样不出来呢，语句如下。和上图一样~

select f.field_name,g.group_name from dmp_field as f,dmp_field_group
 as g where f.group_id=g.id;

如果各位大神觉得我描述不清楚，我想要的结果

已经解决
ps：最后还是加个关联表吧，多对多。。
感谢各位~

select f.field_name,f.id,f.is_system,(select group_concat(g.group_name) from dmp_field_group as g where find_in_set (g.id,f.group_id)) as group_name from dmp_field as f 【where true】

再加两张图片：FIND_IN_SET 和 GROUP_CONCAT

足够多表关联了~还有，一定要大写啊。刚刚被老大骂了~

Answer 1

SELECT f.field_name,(SELECT GROUP_CONCAT(g.group_name)  FROM dmp_field_group as g WHERE find_in_set(g.id,f.group_id)) AS group_name FROM dmp_field AS f

It must be written and can be similar to this, but generally there will be an intermediate table to record the corresponding relationship!

Answer 2

There is something wrong with the design of the database. Although I don’t quite understand it either. But generally a many-to-many relationship must have more than two data tables.
Relationships are usually stored in separate tables.
Landlord. I'm not sure about your situation. But I don't usually do that. I added one more table to your database.
Here's what I did.

select f.*, group_concat(g.name)
from f, fg, g
where f.id = fg.fid and fg.gid = g.id
group by f.fname;

Here are the results. I just need it tomorrow. So I did an experiment with sqlite.

Answer 3

The id in group does not have the value "53,54"

Answer 4

f.group_id=g.id Should I use in? I saw a comma separating the two IDs. I was just guessing! !

Answer 5

I wrote an article before.
Probably define a function yourself.
Article link: http://segmentfault.com/a/1190000002731999

SELECT f.`field_name` AS `字段名称`,
    getGroupNames(f.`group_id`) AS `分组`
FROM dmp_field f
WHERE f.`group_id` IS NOT NULL