重复

重复

关注
发信
javascript - Vuejs SyntaxError:意外的标识符
javascript - 使用JavaScript操纵Razor模型
css - 适应嵌套Divs以下内容
css - 从div到div具有 float 效果的Float
jquery - bootstrap 自定义模态效果
html - 样式标签栏是否类似于Chrome？
javascript - 更改babylon.js中 Material 的颜色
javascript - RhinoJS是否支持websockets API？
javascript - 如何更改浏览页面的<input>？
javascript - 如何等待直到多个EventEmitter实例在Node.js中发出同一事件？
mysql - 用相同变量但来自不同行的值替换变量的值
javascript - 在Chrome的密码字段上使用setCustomValidity的不可读文本
backbone.js - Backbone 收集创造成功
javascript - CSS动画无法正常播放
php - 301重定向与目录结构不匹配

mysql - 内部联接返回重复记录

扫码查看

我编写此查询来从3个不同的表中检索数据。

在这里[我选择的所有列均来自第一张和第二张表]，根据我的实际需要检索数据：

SELECT DISTINCT SD.salary_component,SS.posting_date,SS.payroll_entry,
                        SD.parentfield,SD.amount,SS.employee,SS.name
FROM`tabSalary Detail` SD
INNER JOIN  `tabSalary Slip` SS
    ON SS.name=SD.parent
INNER JOIN `tabGL Entry` GL
    ON GL.against_voucher=SS.payroll_entry
WHERE SS.employee='EMP0110' AND
                     SS.posting_date='2020-03-11' AND
                     SD.parentfield != 'indebtedness' AND
                     SS.payroll_entry='HR-PRUN-2019-01027';


所需的查询结果是

AAAA    2020-03-11  HR-PRUN-2019-01027  earnings    500.000000 EMP0110 Sal Slip-EMP0110-1911-04

BBBB    2020-03-11  HR-PRUN-2019-01027  earnings    300.000000 EMP0110  Sal Slip-EMP0110-1911-04

CCCC    2020-03-11  HR-PRUN-2019-01027  earnings    500.000000 EMP0110  Sal Slip-EMP0110-1911-04

DDDD    2020-03-11  HR-PRUN-2019-01027  earnings    3600.000000 EMP0110 Sal Slip-EMP0110-1911-04


=======

但是，当我在第三张表中选择两列时，出现了问题，并且重复了数据：

SELECT DISTINCT SD.salary_component,SS.posting_date,SS.payroll_entry,
                        SD.parentfield,SD.amount,SS.employee,SS.name
                        GL.credit,GL.debit
FROM`tabSalary Detail` SD
INNER JOIN  `tabSalary Slip` SS
    ON SS.name=SD.parent
INNER JOIN `tabGL Entry` GL
    ON GL.against_voucher=SS.payroll_entry
WHERE SS.employee='EMP0110' AND
                     SS.posting_date='2020-03-11' AND
                     SD.parentfield != 'indebtedness' AND
                     SS.payroll_entry='HR-PRUN-2019-01027';


第二个查询结果是54个重复的行

AAAA    2020-03-11  HR-PRUN-2019-01027  earnings    500.000000  EMP0110 Sal Slip-EMP0110-1911-04    0.000000    300.000000
AAAA    2020-03-11  HR-PRUN-2019-01027  earnings    500.000000  EMP0110 Sal Slip-EMP0110-1911-04    300.000000  0.000000

AAAA    2020-03-11  HR-PRUN-2019-01027  earnings    500.000000  EMP0110 Sal Slip-EMP0110-1911-04    0.000000    500.000000
AAAA    2020-03-11  HR-PRUN-2019-01027  earnings    500.000000  EMP0110 Sal Slip-EMP0110-1911-04    500.000000  0.000000

AAAA    2020-03-11  HR-PRUN-2019-01027  earnings    500.000000  EMP0110 Sal Slip-EMP0110-1911-04    0.000000    3600.000000
AAAA    2020-03-11  HR-PRUN-2019-01027  earnings    500.000000  EMP0110 Sal Slip-EMP0110-1911-04    3600.000000 0.000000

AAAA    2020-03-11  HR-PRUN-2019-01027  earnings    500.000000  EMP0110 Sal Slip-EMP0110-1911-04    0.000000    445.890000
AAAA    2020-03-11  HR-PRUN-2019-01027  earnings    500.000000  EMP0110 Sal Slip-EMP0110-1911-04    445.890000  0.000000

AAAA    2020-03-11  HR-PRUN-2019-01027  earnings    500.000000  EMP0110 Sal Slip-EMP0110-1911-04    0.000000    336.990000
AAAA    2020-03-11  HR-PRUN-2019-01027  earnings    500.000000  EMP0110 Sal Slip-EMP0110-1911-04    336.990000  0.000000

AAAA    2020-03-11  HR-PRUN-2019-01027  earnings    500.000000  EMP0110 Sal Slip-EMP0110-1911-04    0.000000    95.000000
AAAA    2020-03-11  HR-PRUN-2019-01027  earnings    500.000000  EMP0110 Sal Slip-EMP0110-1911-04    95.000000   0.000000

AAAA    2020-03-11  HR-PRUN-2019-01027  earnings    500.000000  EMP0110 Sal Slip-EMP0110-1911-04    0.000000    1150.000000
AAAA    2020-03-11  HR-PRUN-2019-01027  earnings    500.000000  EMP0110 Sal Slip-EMP0110-1911-04    1150.000000 0.000000
===================================================================================================================================
BBBB    2020-03-11  HR-PRUN-2019-01027  earnings    300.000000  EMP0110 Sal Slip-EMP0110-1911-04    0.000000    300.000000
BBBB    2020-03-11  HR-PRUN-2019-01027  earnings    300.000000  EMP0110 Sal Slip-EMP0110-1911-04    300.000000  0.000000

BBBB    2020-03-11  HR-PRUN-2019-01027  earnings    300.000000  EMP0110 Sal Slip-EMP0110-1911-04    0.000000    500.000000
BBBB    2020-03-11  HR-PRUN-2019-01027  earnings    300.000000  EMP0110 Sal Slip-EMP0110-1911-04    500.000000  0.000000

BBBB    2020-03-11  HR-PRUN-2019-01027  earnings    300.000000  EMP0110 Sal Slip-EMP0110-1911-04    0.000000    3600.000000
BBBB    2020-03-11  HR-PRUN-2019-01027  earnings    300.000000  EMP0110 Sal Slip-EMP0110-1911-04    3600.000000 0.000000

BBBB    2020-03-11  HR-PRUN-2019-01027  earnings    300.000000  EMP0110 Sal Slip-EMP0110-1911-04    0.000000    445.890000
BBBB    2020-03-11  HR-PRUN-2019-01027  earnings    300.000000  EMP0110 Sal Slip-EMP0110-1911-04    445.890000  0.000000

BBBB    2020-03-11  HR-PRUN-2019-01027  earnings    300.000000  EMP0110 Sal Slip-EMP0110-1911-04    0.000000    336.990000
BBBB    2020-03-11  HR-PRUN-2019-01027  earnings    300.000000  EMP0110 Sal Slip-EMP0110-1911-04    336.990000  0.000000

BBBB    2020-03-11  HR-PRUN-2019-01027  earnings    300.000000  EMP0110 Sal Slip-EMP0110-1911-04    0.000000    95.000000
BBBB    2020-03-11  HR-PRUN-2019-01027  earnings    300.000000  EMP0110 Sal Slip-EMP0110-1911-04    95.000000   0.000000

BBBB    2020-03-11  HR-PRUN-2019-01027  earnings    300.000000  EMP0110 Sal Slip-EMP0110-1911-04    0.000000    1150.000000
BBBB    2020-03-11  HR-PRUN-2019-01027  earnings    300.000000  EMP0110 Sal Slip-EMP0110-1911-04    1150.000000 0.000000
===================================================================================================================================
CCCC    2020-03-11  HR-PRUN-2019-01027  earnings    500.000000  EMP0110 Sal Slip-EMP0110-1911-04    0.000000    300.000000
CCCC    2020-03-11  HR-PRUN-2019-01027  earnings    500.000000  EMP0110 Sal Slip-EMP0110-1911-04    300.000000  0.000000

CCCC    2020-03-11  HR-PRUN-2019-01027  earnings    500.000000  EMP0110 Sal Slip-EMP0110-1911-04    0.000000    500.000000
CCCC    2020-03-11  HR-PRUN-2019-01027  earnings    500.000000  EMP0110 Sal Slip-EMP0110-1911-04    500.000000  0.000000

CCCC    2020-03-11  HR-PRUN-2019-01027  earnings    500.000000  EMP0110 Sal Slip-EMP0110-1911-04    0.000000    3600.000000
CCCC    2020-03-11  HR-PRUN-2019-01027  earnings    500.000000  EMP0110 Sal Slip-EMP0110-1911-04    3600.000000 0.000000

CCCC    2020-03-11  HR-PRUN-2019-01027  earnings    500.000000  EMP0110 Sal Slip-EMP0110-1911-04    0.000000    445.890000
CCCC    2020-03-11  HR-PRUN-2019-01027  earnings    500.000000  EMP0110 Sal Slip-EMP0110-1911-04    445.890000  0.000000

CCCC    2020-03-11  HR-PRUN-2019-01027  earnings    500.000000  EMP0110 Sal Slip-EMP0110-1911-04    0.000000    336.990000
CCCC    2020-03-11  HR-PRUN-2019-01027  earnings    500.000000  EMP0110 Sal Slip-EMP0110-1911-04    336.990000  0.000000

CCCC    2020-03-11  HR-PRUN-2019-01027  earnings    500.000000  EMP0110 Sal Slip-EMP0110-1911-04    0.000000    95.000000
CCCC    2020-03-11  HR-PRUN-2019-01027  earnings    500.000000  EMP0110 Sal Slip-EMP0110-1911-04    95.000000   0.000000

CCCC    2020-03-11  HR-PRUN-2019-01027  earnings    500.000000  EMP0110 Sal Slip-EMP0110-1911-04    0.000000    1150.000000
CCCC    2020-03-11  HR-PRUN-2019-01027  earnings    500.000000  EMP0110 Sal Slip-EMP0110-1911-04    1150.000000 0.000000
===================================================================================================================================
DDDD    2020-03-11  HR-PRUN-2019-01027  earnings    3600.000000 EMP0110 Sal Slip-EMP0110-1911-04    0.000000    300.000000
DDDD    2020-03-11  HR-PRUN-2019-01027  earnings    3600.000000 EMP0110 Sal Slip-EMP0110-1911-04    300.000000  0.000000

DDDD    2020-03-11  HR-PRUN-2019-01027  earnings    3600.000000 EMP0110 Sal Slip-EMP0110-1911-04    0.000000    500.000000
DDDD    2020-03-11  HR-PRUN-2019-01027  earnings    3600.000000 EMP0110 Sal Slip-EMP0110-1911-04    500.000000  0.000000

DDDD    2020-03-11  HR-PRUN-2019-01027  earnings    3600.000000 EMP0110 Sal Slip-EMP0110-1911-04    0.000000    3600.000000
DDDD    2020-03-11  HR-PRUN-2019-01027  earnings    3600.000000 EMP0110 Sal Slip-EMP0110-1911-04    3600.000000 0.000000

DDDD    2020-03-11  HR-PRUN-2019-01027  earnings    3600.000000 EMP0110 Sal Slip-EMP0110-1911-04    0.000000    445.890000
DDDD    2020-03-11  HR-PRUN-2019-01027  earnings    3600.000000 EMP0110 Sal Slip-EMP0110-1911-04    445.890000  0.000000

DDDD    2020-03-11  HR-PRUN-2019-01027  earnings    3600.000000 EMP0110 Sal Slip-EMP0110-1911-04    0.000000    336.990000
DDDD    2020-03-11  HR-PRUN-2019-01027  earnings    3600.000000 EMP0110 Sal Slip-EMP0110-1911-04    336.990000  0.000000

DDDD    2020-03-11  HR-PRUN-2019-01027  earnings    3600.000000 EMP0110 Sal Slip-EMP0110-1911-04    0.000000    95.000000
DDDD    2020-03-11  HR-PRUN-2019-01027  earnings    3600.000000 EMP0110 Sal Slip-EMP0110-1911-04    95.000000   0.000000

DDDD    2020-03-11  HR-PRUN-2019-01027  earnings    3600.000000 EMP0110 Sal Slip-EMP0110-1911-04    0.000000    1150.000000
DDDD    2020-03-11  HR-PRUN-2019-01027  earnings    3600.000000 EMP0110 Sal Slip-EMP0110-1911-04    1150.000000 0.000000


有什么问题，我应该搜索哪些主题来理解？
最后，什么是最佳解决方案？

最佳答案

我强烈怀疑您想要GROUP BY：

SELECT SD.salary_component, SS.posting_date, SS.payroll_entry,
       SD.parentfield, SD.amount, SS.employee, SS.name,
       SUM(GL.credit), SUM(GL.debit)
FROM `tabSalary Detail` SD INNER JOIN
     `tabSalary Slip` SS
      ON SS.name = SD.parent INNER JOIN
      `tabGL Entry` GL
      ON GL.against_voucher=SS.payroll_entry
WHERE SS.employee = 'EMP0110' AND
      SS.posting_date = '2020-03-11' AND
      SD.parentfield <> 'indebtedness' AND
      SS.payroll_entry = 'HR-PRUN-2019-01027'
GROUP BY SD.salary_component, SS.posting_date, SS.payroll_entry,
         SD.parentfield, SD.amount, SS.employee, SS.name;

09-25 18:24
查看更多
Powered by LMLPHP ©2025 重复 0.027061