php - 如何在不重复的情况下从不同列的单独表中获取数据

Question

我有一张表(销售)和第二张表(付款)来跟踪每个销售员给管理员的付款

CREATE TABLE `sell` (
  `sell_id` int(10) NOT NULL,
  `salesman_id` int(10) NOT NULL,
  `total` decimal(16,2) DEFAULT '0.00',
  `advance` decimal(16,2) NOT NULL DEFAULT '0.00',
  `sell_date` date DEFAULT NULL
) ;

INSERT INTO `sell` (`sell_id`, `salesman_id`, `total`, `advance`, `sell_date`) VALUES
(1, 1, '2000.00', '1000.00', '2019-06-26'),
(2, 1, '5000.00', '3500.00', '2019-06-27'),
(3, 2, '3100.00', '3100.00', '2019-06-28'),
(4, 1, '500.00', '500.00', '2019-06-29'),
(5, 1, '1200.00', '1000.00', '2019-06-29');

CREATE TABLE `payment` (
  `sp_id` int(10) NOT NULL,
  `salesman_id` int(10) NOT NULL,
  `due` decimal(16,2) DEFAULT '0.00',
  `paid` decimal(16,2) NOT NULL DEFAULT '0.00',
  `payment_date` date DEFAULT NULL
) ;

INSERT INTO `payment` (`sp_id`, `salesman_id`, `due`, `paid`, `payment_date`) VALUES
(1, 2, '5000.00', '4000.00', '2019-06-26'),
(2, 1, '3000.00', '2000.00', '2019-06-27'),
(3, 3, '4000.00', '4000.00', '2019-06-27'),
(4, 1, '8500.00', '5000.00', '2019-06-28'),
(5, 2, '1200.00', '1000.00', '2019-06-29');

我想按日期将两个表的结果连接到一个查询顺序中，这样，销售员 1 的日期、到期、已付、已售出

+------------+----------+---------+------+---------+-------+
| date       |    Due   |   paid  | Sold | Sell_id | Sp_id |
+------------+----------+---------+------+---------+-------+
| 2019-06-26 |          |         | 2000 |     1   |       |
| 2019-06-27 | 3000     |   2000  |      |         |   2   |
| 2019-06-27 |          |         | 5000 |     2   |       |
| 2019-06-28 | 8500     |   5000  |      |         |   4   |
| 2019-06-29 |          |         | 500  |     4   |       |
| 2019-06-29 |          |         | 1200 |     5   |       |
+------------+----------+---------+------+---------+--------+

我的查询是

SELECT  sp.payment_date, sp.due, sp.paid, se.total, se.sell_id, sp.sp_id FROM payment sp
INNER JOIN ( SELECT sell_id, salesman_id, total, sell_date FROM sell WHERE salesman_id = 1) se ON sp.salesman_id = se.salesman_id
WHERE sp.salesman_id = 1
ORDER BY sp.payment_date , se.sell_date

我得到的结果，重复填充列中的所有空白区域

Answer 1

要得到你想要的结果，你需要一个UNION查询，分别从sell和payment中选择结果，然后按date(以及 sold 是否为非 NULL 以获得预期数据中的准确排序):

SELECT sell_date AS date, NULL AS due, NULL AS paid,
       total AS sold, sell_id, NULL AS sp_id
FROM sell
WHERE salesman_id = 1
UNION
SELECT payment_date, due, paid, NULL, NULL, sp_id
FROM payment 
WHERE salesman_id = 1
ORDER BY date, COALESCE(sold, 0)

输出:

date        due     paid    sold    sell_id     sp_id
2019-06-26  null    null    2000    1           null
2019-06-27  3000    2000    null    null        2
2019-06-27  null    null    5000    2           null
2019-06-28  8500    5000    null    null        4
2019-06-29  null    null    500     4           null
2019-06-29  null    null    1200    5           null

Demo on dbfiddle