MySQL 没有从我的选择查询中返回任何结果

Question

我有以下多个字段，我想在其中显示我的费用和收入报表。我正在执行一个查询，以从 MySQL 查询返回描述、贷方、借方和余额。 SQL Fiddle

CREATE TABLE IF NOT EXISTS `trans` (
`ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`debit` decimal(15,4) NOT NULL,
`credit` decimal(15,4) NOT NULL,
`catid` smallint(5) NOT NULL,
`incomeid` bigint(20) unsigned NOT NULL DEFAULT '0',
`expenseid` bigint(20) unsigned NOT NULL DEFAULT '0',
`bankid` int(3) unsigned NOT NULL,
`date` date NOT NULL,
`updated_last` datetime NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=11 ;

--
-- Dumping data for table `trans`
--

INSERT INTO `trans` (`ID`, `debit`, `credit`, `catid`, `incomeid`, `expenseid`, `bankid`, `date`, `updated_last`) VALUES
(1, 0.0000, 2078.1000, 23, 1, 0, 2, '2015-04-01', '2015-04-30 14:16:37'),
(2, 0.0000, 2052.8200, 23, 2, 0, 2, '2015-04-02', '2015-04-30 14:17:23'),
(3, 0.0000, 4906.6200, 23, 3, 0, 2, '2015-04-02', '2015-04-30 14:17:06'),
(4, 0.0000, 12360.0500, 23, 4, 0, 1, '2015-04-02', '2015-04-30 12:18:15'),
(5, 0.0000, 10750.0000, 23, 5, 0, 2, '2015-04-03', '2015-04-30 12:25:31'),
(6, 0.0000, 2247.2000, 23, 6, 0, 1, '2015-04-03', '2015-04-30 12:29:45'),
(7, 0.0000, 4775.3000, 23, 7, 0, 2, '2015-04-04', '2015-04-30 12:37:40'),
(8, 0.0000, 2052.8200, 23, 8, 0, 2, '2015-04-04', '2015-04-30 14:16:05'),
(9, 2280.9100, 2280.9100, 23, 0, 1, 2, '2015-04-06', '2015-04-30 14:17:51'),
(10, 0.0000, 25000.0000, 23, 0, 2, 2, '2015-04-04', '2015-04-30 13:46:03');

CREATE TABLE IF NOT EXISTS `expense` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`type` int(11) unsigned NOT NULL,
`date` date NOT NULL,
`itemdesc` varchar(255) NOT NULL,
`quantity` varchar(30) NOT NULL,
`price` decimal(15,4) NOT NULL,
`grandtotal` decimal(15,4) NOT NULL,
`updated_last` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=11 ;

--
-- Dumping data for table `expense`
--

INSERT INTO `expense` (`id`, `type`, `date`, `itemdesc`, `quantity`, `price`, `grandtotal`, `updated_last`) VALUES
(1, 30, '2015-04-04', 'INT-CITY CASH DEP CHG INC ST-EC 300315', '1', 50.0000, 56.1800, '2015-05-01 02:35:37'),
(2, 30, '2015-04-04', 'IMPS P2P 562 508513121760#26-03- 300315', '1', 5.0000, 5.6200, '2015-05-01 02:37:18'),
(3, 29, '2015-04-08', 'mobile 1 Bill', '1', 599.0000, 673.0400, '2015-05-01 02:47:01'),
(4, 29, '2015-04-08', 'mobile 2 Bill Mar 2015', '1', 627.0000, 704.5000, '2015-05-01 02:58:08'),
(5, 38, '2015-04-10', 'staff 1 Salary Mar 2015', '1', 22000.0000, 22000.0000, '2015-05-01 03:02:21'),
(6, 38, '2015-04-10', 'staff 2 Salary Mar 2015', '1', 22000.0000, 22000.0000, '2015-05-01 03:03:40'),
(7, 29, '2015-04-11', 'landline 2 March 2015', '1', 388.9000, 436.9700, '2015-05-01 04:13:24'),
(8, 29, '2015-04-11', 'landline Mar 2015', '1', 605.2300, 680.0400, '2015-05-01 04:20:52'),
(9, 29, '2015-04-11', 'pager bill Mar 2015', '1', 591.4000, 664.5000, '2015-05-01 04:23:28'),
(10, 29, '2015-04-11', 'phone bill Mar 2015', '1', 198.7500, 223.3200, '2015-05-01 04:26:01');


CREATE TABLE IF NOT EXISTS `items` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`description` varchar(200) NOT NULL,
`catid` int(5) unsigned NOT NULL,
`status` tinyint(1) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=11 ;

--
-- Dumping data for table `items`
--

INSERT INTO `items` (`id`, `description`, `catid`, `status`) VALUES
(1, 'product 10000', 23, 1),
(2, 'product 50000', 23, 1),
(3, 'product 100000', 23, 1),
(4, 'product 500000', 23, 1),
(5, 'product High 10000', 23, 1),
(6, 'product High 50000', 23, 1),
(7, 'product High 100000', 23, 1),
(8, 'product High 500000', 23, 1),
(9, 'product Normal 10000', 23, 1),
(10, 'product Normal 25000', 23, 1);

CREATE TABLE IF NOT EXISTS `income` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`type` smallint(5) unsigned NOT NULL,
`date` date NOT NULL,
`itemdesc` int(11) unsigned NOT NULL DEFAULT '0',
`quantity` varchar(30) NOT NULL,
`price` decimal(15,4) NOT NULL,
`grandtotal` decimal(15,4) NOT NULL,
`updated_last` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=11 ;

--
-- Dumping data for table `income`
--

INSERT INTO `income` (`id`, `type`, `date`, `itemdesc`, `quantity`, `price`, `grandtotal`, `updated_last`) VALUES
(1, 23, '2015-04-01', 18, '10000', 0.1800, 2078.1000, '2015-04-30 14:16:37'),
(2, 23, '2015-04-02', 18, '10000', 0.1800, 2052.8200, '2015-04-30 14:17:23'),
(3, 23, '2015-04-02', 10, '25000', 0.1700, 4906.6200, '2015-04-30 14:17:06'),
(4, 23, '2015-04-02', 12, '100000', 0.1100, 12360.0500, '2015-04-30 12:18:15'),
(5, 23, '2015-04-03', 12, '100000', 0.1075, 10750.0000, '2015-04-30 12:25:31'),
(6, 23, '2015-04-03', 14, '10000', 0.2000, 2247.2000, '2015-04-30 12:29:45'),
(7, 23, '2015-04-04', 10, '25000', 0.1700, 4775.3000, '2015-04-30 12:37:40'),
(8, 23, '2015-04-04', 9, '10000', 0.1800, 2052.8200, '2015-04-30 14:16:05'),
(9, 23, '2015-04-06', 14, '10000', 0.2000, 2280.9100, '2015-04-30 14:17:51'),
(10, 23, '2015-04-04', 53, '250000', 0.1000, 25000.0000, '2015-04-30 13:46:03');

MySQL 查询:

set @depos=0;
set @total=0;
SELECT A.`date` , A.`debit` , A.`credit` , 
if( A.`credit` >0, @depos := A.`credit`, @depos := @depos + A.`credit` - A.`debit` ) AS depos_bal, 
@total := @total + A.`credit` - A.`debit` AS net_bal, C.`itemdesc`, D.`description` 
FROM `trans` A, `income` B, `expense` C, `items` D 
WHERE A.`expenseid` = C.`id` 
AND A.`incomeid` = B.`id` 
AND B.`itemdesc` = D.`id` 
AND A.`bankid` = '2'
ORDER BY A.`date` ASC, A.`ID` ASC

我的几个查询还不足以完成它。

如何使用查询来获取如下所示的所有行？

Date         Description    Debit      Credit         Balance
04-04-2015   Item 1                     4,775.30      27,445.28
04-04-2015   Item 2         56.18       27,389.10
07-04-2015   Item 3                      2,359.56     29,743.04

Answer 1

您的交易、收入和支出表之间没有匹配的值。因此，通过 AND 运算“其中 A.expenseid = C.id AND A.venueid = B。”将不会产生任何结果。

如果您运行这些查询，您将意识到:

select * from trans A 内部联接 venue B on A.venueid = B.id;

select * from trans A 内部联接 expense C on A.expenseid = C.id;

您应该在表中保留连接，而不是进行 AND 运算:

SELECT A.`date` , D.`description`, C.`itemdesc`, A.`debit` , A.`credit`, 
if( A.`credit` > 0, @depos := A.`credit`, @depos := @depos + A.`credit` - A.`debit` ) AS depos_bal, 
@total := @total + A.`credit` - A.`debit` AS net_bal
FROM `trans` A left join `income` B ON A.`incomeid` = B.`id` left join `expense` C on A.`expenseid` = C.`id` left join `items` D ON B.`itemdesc` = D.`id`
WHERE A.`bankid` = '2'
ORDER BY A.`date` ASC, A.`ID` ASc;