MySQL 查询返回意外结果

Question

我的 Preneed 表仅包含 3 行；

Preneed_tb
+----+-----------+----------+---------+----------+
| id | fullname  | plannum  | remarks | pre_type |
+----+-----------+----------+---------+----------+
|  1 | jAMES YAP | SDF12321 | FB      | HIP      |
|  2 | asdasdas  | 213      | 1gb     | CEA      |
|  3 | gdaae     | 12fvs    | dfcv    | CEA      |
+----+-----------+----------+---------+----------+

MySQL 查询返回具有相同数据的重复行:

select    fullname as 'FullName', 
          plannum as 'Plan_Number', 
          remarks as 'Remarks', 
          pre_type as 'Pre_Need_Type', 
          concat(x.id,'-PRENEED') as 'Identification' 
from      preneed_tb as x
left join filelocation 
on        filelocation.f_id = x.id
left join (select   max(f_logs.id),
                    f_id, 
                    log_status 
           from     f_logs 
           group by f_id) as y 
on         y.f_id = x.id
WHERE      concat(f_location,' ') like 'SFDSF %' 
and        concat(y.log_status, ' ') like 'IN STORA%'

我使用 x.id 因为我得到了当我尝试使用 preneed_tb 时，“字段列表”中出现未知列“preneed_tb.id”

示例结果:

+-----------+-------------+---------+---------------+----------------+
| FullName  | Plan_Number | Remarks | Pre_Need_Type | Identification |
+-----------+-------------+---------+---------------+----------------+
| jAMES YAP | SDF12321    | FB      | HIP           | 1-PRENEED      |
| jAMES YAP | SDF12321    | FB      | HIP           | 1-PRENEED      |
| jAMES YAP | SDF12321    | FB      | HIP           | 1-PRENEED      |
+-----------+-------------+---------+---------------+----------------+

然后当我使用这个查询时，它返回预期的结果:

 select   fullName,
          Plan_Number,
          Remarks, 
          Pre_Need_Type, 
          Identification 
    from     (select fullname as 'FullName', 
                      plannum as 'Plan_Number', 
                     remarks as 'Remarks', 
                     pre_type as 'Pre_Need_Type', 
                     concat(preneed_tb.id,'-PRENEED') as 'Identification' 
              from   preneed_tb) as x
left join filelocation 
on filelocation.f_id = x.identification
left join (select   max(f_logs.id),
                        f_id, 
                        log_status 
               from     f_logs 
               group by f_id) as y 
    on         y.f_id = x.identification
    WHERE      concat(f_location,' ') like 'SFDSF %' 
    and        concat(y.log_status, ' ') like 'IN STORA%'

示例结果:

+-----------+-------------+---------+---------------+----------------+
| FullName  | Plan_Number | Remarks | Pre_Need_Type | Identification |
+-----------+-------------+---------+---------------+----------------+
| jAMES YAP | SDF12321    | FB      | HIP           | 1-PRENEED      |
+-----------+-------------+---------+---------------+----------------+

谁能给我解释一下吗？我很困惑，因为它返回重复的条目，但我的表中只有一个匹配项

Answer 1

您想要删除重复的行，请使用不同的子句

  select distinct 
     fullname as 'FullName'
    , plannum as 'Plan_Number'
    , remarks as 'Remarks'
    , pre_type as 'Pre_Need_Type'
    , concat(preneed_tb.id,'-PRENEED') as 'Identification' 
  from preneed_tb as x
  left join filelocation on filelocation.f_id = x.id
  left join (select max(f_logs.id),f_id, log_status 
                from f_logs group by f_id) as y on y.f_id = x.id
  WHERE concat(f_location,' ') like 'SFDSF %' 
  and concat(y.log_status, ' ') like 'IN STORA%'"