请检查SQL模式并在SQL Fiddle上查询
如果有人可以纠正此问题,我将获得具有NULL值的重复记录。
问候
这就是我得到的:
| MEM_ID | MEM_EMAIL | GENDER | EDUCATION | PROFESSION |
|--------|----------------|--------|-----------|-------------|
| 1 | [email protected] | Male | (null) | (null) |
| 1 | [email protected] | (null) | Graduate | (null) |
| 1 | [email protected] | (null) | (null) | Engineer |
| 2 | [email protected] | Female | (null) | (null) |
| 2 | [email protected] | (null) | Graduate | (null) |
| 2 | [email protected] | (null) | (null) | Not Working |
但是我需要
| MEM_ID | MEM_EMAIL | GENDER | EDUCATION | PROFESSION |
|--------|----------------|--------|-----------|-------------|
| 1 | [email protected] | Male | Graduate | Engineer |
| 2 | [email protected] | Female | Graduate | Not Working |
|
我想你基本上是试图做一个支点上的数据。这是实现这一目标的一种方法。
SELECT M.mem_Id,
M.mem_email,
[Gender] = (select max( A.att_value)
from tbl_attributes A
inner join tbl_mem_att_values MAV
on MAV.att_id = A.att_id
inner join tbl_types T
on T.type_id = A.type_id
where T.type_name = 'Gender'
and MAV.mem_Id = M.mem_Id),
[Education] = (select max( A.att_value)
from tbl_attributes A
inner join tbl_mem_att_values MAV
on MAV.att_id = A.att_id
inner join tbl_types T
on T.type_id = A.type_id
where T.type_name = 'Education'
and MAV.mem_Id = M.mem_Id),
[Profession] = (select max( A.att_value)
from tbl_attributes A
inner join tbl_mem_att_values MAV
on MAV.att_id = A.att_id
inner join tbl_types T
on T.type_id = A.type_id
where T.type_name = 'Profession'
and MAV.mem_Id = M.mem_Id)
FROM tbl_members M
结果看起来像这样
EM_ID MEM_EMAIL GENDER EDUCATION PROFESSION
1 [email protected] Male Graduate Engineer
2 [email protected] Female Graduate Not Working
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句