使用distinct后获取重复项

Question

我有三个表，一个是映射tale，我使用distinct关键字，尽管得到了重复的值

SELECT
  mer.store_name,      mpr.`merchant_code` ,    mpr.`terminal_num` ,
  mpr.`rec_fmt` ,      mpr.`bat_nbr` ,          mpr.`Card_Type` ,
  mpr.`card_num` ,     mpr.`transaction_date` , mpr.`settle_date` ,
  mpr.`approval_code`, mpr.`intnl_amt` ,        mpr.`domestic_amt` ,
  mpr.`transid` ,      mpr.`upvalue` ,          mpr.`merchant_trackid` ,
  mpr.`MSF` ,          mpr.`service_tax` ,      mpr.`edu_cess` ,
  mpr.`net_amount` ,   mpr.`debit_credit_type`, mpr.`UDF1` ,
  mpr.`UDF2` ,         mpr.`UDF3` ,             mpr.`UDF4` ,
  mpr.`UDF5` ,         mpr.`seq_num` ,          mpr.`arn_no`
FROM
  `mpr_reports` mpr, merchantreports mer, storename_tid sid
WHERE (
  mer.Store_Name = sid.Store_Name
  AND sid.terminal_num = mpr.terminal_num
  AND mpr.`rec_fmt` = 'cvd'
  OR mpr.`rec_fmt` = 'bat'
)

Answer 1

考虑将已发布的查询修改为如下所示...

SELECT mer.store_name
     , mpr.merchant_code 
     , mpr.terminal_num 
     , mpr.rec_fmt 
     , mpr.bat_nbr 
     , mpr.Card_Type 
     , mpr.card_num 
     , mpr.transaction_date 
     , mpr.settle_date 
     , mpr.approval_code 
     , mpr.intnl_amt 
     , mpr.domestic_amt 
     , mpr.transid 
     , mpr.upvalue 
     , mpr.merchant_trackid 
     , mpr.MSF 
     , mpr.service_tax 
     , mpr.edu_cess 
     , mpr.net_amount 
     , mpr.debit_credit_type 
     , mpr.UDF1 
     , mpr.UDF2 
     , mpr.UDF3 
     , mpr.UDF4 
     , mpr.UDF5 
     , mpr.seq_num 
     , mpr.arn_no
  FROM mpr_reports mpr
  JOIN storename_tid sid
    ON sid.terminal_num = mpr.terminal_num
  JOIN merchantreports mer
    ON mer.Store_Name = sid.Store_Name
 WHERE mpr.rec_fmt IN ('cvd','bat')
 ;

Answer 2

关于SQL join，您可能会有一些有趣的here...

这是我们学过一次的东西，要在我们的一生中使用。

关于您的特定查询，测试第一个示例，

SELECT
  mer.store_name,
  mpr.`merchant_code`,
  mpr.`terminal_num`,
  mpr.`rec_fmt`,
  mpr.`bat_nbr`,
  mpr.`Card_Type`,
  mpr.`card_num`,
  mpr.`transaction_date`,
  mpr.`settle_date`,
  mpr.`approval_code`,
  mpr.`intnl_amt`,
  mpr.`domestic_amt`,
  mpr.`transid`,
  mpr.`upvalue`,
  mpr.`merchant_trackid`,
  mpr.`MSF`,
  mpr.`service_tax`,
  mpr.`edu_cess`,
  mpr.`net_amount`,
  mpr.`debit_credit_type`,
  mpr.`UDF1`,
  mpr.`UDF2`,
  mpr.`UDF3`,
  mpr.`UDF4`,
  mpr.`UDF5`,
  mpr.`seq_num`,
  mpr.`arn_no`
FROM merchantreports mer
INNER JOIN storename_tid sid ON (sid.Store_Name = mer.Store_Name)
INNER JOIN `mpr_reports` mpr ON (sid.terminal_num = mpr.terminal_num)
WHERE mpr.rec_fmt IN ('cvd','bat');

这只是一个谦虚的建议，考虑使用外键来连接您的表。索引"Store_Name“和"terminal_num”列也是一种选择，但遵循scriptures中编写的内容会更有效(参见小节)。5.4.5)。