时间戳

首页 > 解决方案 > MySQL - 如果我使用更多左连接，为什么查询不会对相同类型的数据求和？

mysql - MySQL - 如果我使用更多左连接，为什么查询不会对相同类型的数据求和？

问题描述

我的架构和示例数据集：

CREATE TABLE `bsbi_hedge_fund` (
  `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `fund_name` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `abn_number` varchar(13) COLLATE utf8_unicode_ci DEFAULT NULL,
  `parent_company_id` int(11) DEFAULT NULL,
  `email` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `contact_no` varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL,
  `address_one` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `address_two` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `city_name` varchar(60) COLLATE utf8_unicode_ci DEFAULT NULL,
  `state_name` varchar(60) COLLATE utf8_unicode_ci DEFAULT NULL,
  `zip_code` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
  `country_tbl_id` int(11) DEFAULT NULL,
  `status` varchar(10) COLLATE utf8_unicode_ci DEFAULT 'active',
  `created_by` int(11) DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  `modified_by` int(11) DEFAULT NULL,
  `modified_date` datetime DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `bsbi_hedge_fund`
--

INSERT INTO `bsbi_hedge_fund` (`id`, `fund_name`, `abn_number`, `parent_company_id`, `email`, `contact_no`, `address_one`, `address_two`, `city_name`, `state_name`, `zip_code`, `country_tbl_id`, `status`, `created_by`, `created_date`, `modified_by`, `modified_date`) VALUES
(1, 'iCAP Hedge Funds', '53616271062', 1, 'icaptrading@gmail.com', '0425175887', '68 Roy Marika Street', '', 'Bonner', 'ACT', '2914', 12, 'active', 1, '2020-02-20 07:02:51', NULL, NULL);


CREATE TABLE `bsbi_company` (
  `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `company_name` varchar(60) COLLATE utf8_unicode_ci DEFAULT NULL,
  `abn_number` varchar(13) COLLATE utf8_unicode_ci DEFAULT NULL,
  `acn_number` varchar(13) COLLATE utf8_unicode_ci DEFAULT NULL,
  `email` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `contact_no` varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL,
  `address_one` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `address_two` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `city_name` varchar(60) COLLATE utf8_unicode_ci DEFAULT NULL,
  `state_name` varchar(60) COLLATE utf8_unicode_ci DEFAULT NULL,
  `zip_code` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
  `country_tbl_id` int(11) DEFAULT NULL,
  `status` varchar(10) COLLATE utf8_unicode_ci DEFAULT 'active',
  `created_by` int(11) DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  `modified_by` int(11) DEFAULT NULL,
  `modified_date` datetime DEFAULT NULL,
  `allocated_money` float(17,5) DEFAULT '0.00000',
  `hedge_fund_allocation` float(17,5) NOT NULL,
  `investment_class_allocation` float(17,5) NOT NULL,
  `hedge_fund_money` float(10,3) DEFAULT '0.000',
  `inv_class_money` float(10,3) DEFAULT '0.000'
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `bsbi_company`
--

INSERT INTO `bsbi_company` (`id`, `company_name`, `abn_number`, `acn_number`, `email`, `contact_no`, `address_one`, `address_two`, `city_name`, `state_name`, `zip_code`, `country_tbl_id`, `status`, `created_by`, `created_date`, `modified_by`, `modified_date`, `allocated_money`, `hedge_fund_allocation`, `investment_class_allocation`, `hedge_fund_money`, `inv_class_money`) VALUES
(1, 'Investment and Capital Growth for Australian Professional ( ', '53616271062', '2123', 'abc@gmail.com', '2343', '68 Roy Marika Street', '', 'Bonner', 'ACT', '2914', 12, 'active', 1, '2020-02-20 07:01:26', 1, '2020-03-07 12:13:53', 22847.00000, 0.00000, 0.00000, 20000.000, 19000.000);


CREATE TABLE `bsbi_hedge_fund_journal` (
  `id` int(11) NOT NULL,
  `company_id` int(11) DEFAULT NULL,
  `hedge_fund_id` int(11) DEFAULT NULL,
  `amount` float(10,3) DEFAULT NULL,
  `tran_type` varchar(7) COLLATE utf8_unicode_ci DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  `modified_by` int(11) DEFAULT NULL,
  `modified_date` datetime DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `bsbi_hedge_fund_journal`
--

INSERT INTO `bsbi_hedge_fund_journal` (`id`, `company_id`, `hedge_fund_id`, `amount`, `tran_type`, `created_by`, `created_date`, `modified_by`, `modified_date`) VALUES
(1, 1, 1, 20000.000, 'credit', 1, '2020-03-07 11:45:40', NULL, NULL);

CREATE TABLE `bsbi_investment_allocation_class_journal` (
  `id` int(11) NOT NULL,
  `hedge_fund_id` int(11) DEFAULT NULL,
  `investment_class_id` int(11) DEFAULT NULL,
  `amount` float(10,3) DEFAULT NULL,
  `tran_type` varchar(7) COLLATE utf8_unicode_ci DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_date` datetime DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `bsbi_investment_allocation_class_journal`
--

INSERT INTO `bsbi_investment_allocation_class_journal` (`id`, `hedge_fund_id`, `investment_class_id`, `amount`, `tran_type`, `created_by`, `created_date`) VALUES
(1, 1, 1, 18000.000, 'credit', 1, '2020-03-07 11:46:09'),
(2, 1, 1, 2000.000, 'credit', 1, '2020-03-07 12:12:02'),
(3, 1, 1, 1000.000, 'debit', 1, '2020-03-07 12:13:53');

...和相同的小提琴（http://sqlfiddle.com/#!9/9cd46d/2）：

我编写了以下查询来从数据库中获取一些数据：

SELECT
hf.*, com.company_name company_name, com.id com_id,
IFNULL( SUM( hfc.amount ), 0 ) hedge_credit, IFNULL( SUM( hfd.amount ), 0 ) hedge_debit,
IFNULL( SUM( cjc.amount ), 0 ) class_credit, IFNULL( SUM( cjd.amount ), 0 ) class_debit

FROM bsbi_hedge_fund hf

INNER JOIN bsbi_company com ON hf.parent_company_id = com.id

LEFT JOIN bsbi_hedge_fund_journal hfc ON (hfc.hedge_fund_id = hf.id AND hfc.tran_type='credit')
LEFT JOIN bsbi_hedge_fund_journal hfd ON ( hfd.hedge_fund_id = hf.id AND hfd.tran_type = 'debit' )
LEFT JOIN bsbi_investment_allocation_class_journal cjc ON (cjc.hedge_fund_id = hf.id AND cjc.tran_type = 'credit' )
LEFT JOIN bsbi_investment_allocation_class_journal cjd ON (cjd.hedge_fund_id = hf.id AND cjd.tran_type = 'debit' )
ORDER BY hf.id ASC

这是此查询的输出：
查询 1 的结果

我面临的问题是：hedge_credit值是20000但它显示40000！同样class_debit是1000但它显示2000

在做了一些研发之后，我发现：bsbi_investment_allocation_class_journal表有两个条目具有tran_type = credit但由于某种未知原因，我的查询的以下行获取两行，而不是对值求和（我担心同样的事情会发生在其他左连接上如果他们也有多个相同类型的记录！） 

LEFT JOIN bsbi_investment_allocation_class_journal cjc ON (cjc.hedge_fund_id = hf.id AND cjc.tran_type = 'credit' )

如果我在上面的查询中添加Group By 子句，那么我得到：
在此处输入图像描述

请注意，class_credit有两个不同的值应该相加，但由于未知原因不能相加！！

谁能告诉我提出这个问题的查询中到底有什么问题？
- 谢谢

标签： mysqlsql

解决方案

连接生成满足连接条件的所有可能的行组合。因为您仅hfc.hedge_fund_id = hf.id用于连接表，所以您正在复制行。如果总共有三笔交易，每笔交易将增加三倍。

您需要从包含事务的表中选择更多数据以保持行的唯一性：时间戳或事务 ID。我通常会先选择我想要的唯一行，然后将非唯一数据（名称、国家/地区等）左连接到每一行。

推荐阅读