时间戳

首页 > 解决方案 > 左连接 MYSQL 查询中的重复项

mysql - 左连接 MYSQL 查询中的重复项

问题描述

请帮忙，我怎样才能删除重复项

每当我在每个表上有超过两行时，它们就会重复

每当（助手）表行增加时，（getter_tbl）结果就会不断重复

SELECT DISTINCT h.status as h_status, h.ph_amount as h_ph_amount, h.help_id as h_help_id, h.h_unique_id as h_h_unique_id, h.user_id as h_user_id, h.ph_date as h_ph_date, h.ph_commitment as h_ph_commitment, h.ph_type as h_ph_type,
g.status as g_status, g.user_id as g_user_id, g.getter_tbl_id as g_getter_tbl_id, g.gh_committment as g_gh_committment, g.gh_type as g_gh_type, g.status as g_status, g.method as g_method, g.amount as g_amount,
u.nickname as u_nickname, u.user_id as u_user_id
FROM helper AS h
LEFT JOIN getter_tbl AS g ON h.user_id = g.user_id
LEFT JOIN user AS u ON u.user_id = '{$user_id}'
WHERE h.user_id = '{$user_id}' AND g.user_id='{$user_id}' AND h.status='open' OR g.status='open'
GROUP BY h.help_id, g.getter_tbl_id, u.user_id

这是我的代码

foreach($query as $queryRows){
    if($queryRows['g_status'] == 'open'){
      print '
        <div class="block-content block-content-full text-left text-black" style="padding: 10px 15px 1px; ">
        <div class="font-size-sm text-black-op" style=" color: black !important; "><strong>Amount:</strong> '.number_format($queryRows['g_gh_committment'], 0).'</div>
        <div class="font-size-sm text-black-op" style=" color: black !important; "><strong>Balance:</strong> '.number_format($queryRows['g_gh_committment'], 0).'</div>

      ';
    }
    if($queryRows['h_status'] == 'open'){
      print '
        <div class="block-content block-content-full text-left text-black" style="padding: 10px 15px 1px; ">
        <div class="font-size-sm text-black-op" style=" color: black !important; "><strong>Amount:</strong> '.number_format($queryRows['h_ph_amount'], 0).'</div>
        <div class="font-size-sm text-black-op" style=" color: black !important; "><strong>Balance:</strong> '.number_format($queryRows['h_ph_commitment'], 0).'</div>

      ';
    }
}

标签： mysql

解决方案

在评论中很难说清楚 - 所以我提供了一个潜在的答案。

你重复你的user_id几次。也许这就是您复制结果的原因。

您的 SQL 语法也可能有错误。我假设你的 WHERE 子句应该是 AND (h.status=open OR g.status=open)。在这种情况下，您希望在这些状态中的任何一个处于打开状态时获取用户。

这行得通吗？

SELECT DISTINCT 

h.status as h_status, 
h.ph_amount as h_ph_amount, 
h.help_id as h_help_id, 
h.h_unique_id as h_h_unique_id, 
h.ph_date as h_ph_date, 
h.ph_commitment as h_ph_commitment, 
h.ph_type as h_ph_type,
g.status as g_status, 
g.getter_tbl_id as g_getter_tbl_id, 
g.gh_committment as g_gh_committment, 
g.gh_type as g_gh_type, 
g.status as g_status, 
g.method as g_method, 
g.amount as g_amount,
u.nickname as u_nickname,
u.user_id as u_user_id

FROM helper AS h
LEFT JOIN getter_tbl AS g ON h.user_id = g.user_id
LEFT JOIN user AS u ON u.user_id = h.user_id

WHERE 
u.user_id = '{$user_id}' AND 
(h.status='open' OR g.status='open')

GROUP BY h.help_id, g.getter_tbl_id, u.user_id

注意：它只会返回 u_user_id（并且 3 个不同变量中的 id 不同）

推荐阅读