mysql GROUP BY 思路

Question

我有以下场景:有 1 个包含书籍的表和两对包含销售订单和采购订单交易的表 (HD/IT)，这些交易通过销售订单 ID 连接。表结构如下:

CREATE TABLE `books` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `isbn` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `it_id` int(11) NOT NULL,
  `kind` tinyint(4) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `books` (`id`, `isbn`, `it_id`, `kind`) VALUES
(1, '12345',    1,  1),
(2, '12345',    1,  2),
(3, '67890',    2,  1),
(4, '1111111',  2,  2);

CREATE TABLE `porders_hd` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `dt` date NOT NULL,
  `so_id` int(11) DEFAULT NULL,
  `customer` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `porders_hd` (`id`, `dt`, `so_id`, `customer`) VALUES
(1, '2017-07-02',   1,  1),
(2, '2017-08-03',   NULL,   3);

CREATE TABLE `porders_it` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `hd_id` int(11) NOT NULL,
  `isbn` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `dscr` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `qty` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `porders_it` (`id`, `hd_id`, `isbn`, `dscr`, `qty`) VALUES
(1, 1,  '12345',    'Book 1',   1),
(2, 2,  '1111111',  'Book 2',   1);

CREATE TABLE `sorders_hd` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `dt` date NOT NULL,
  `customer` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `sorders_hd` (`id`, `dt`, `customer`) VALUES
(1, '2017-07-01',   1),
(2, '2017-08-01',   2);

CREATE TABLE `sorders_it` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `hd_id` int(11) NOT NULL,
  `isbn` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `dscr` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `qty` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `sorders_it` (`id`, `hd_id`, `isbn`, `dscr`, `qty`) VALUES
(1, 1,  '12345',    'Book 1',   1),
(2, 2,  '67890',    'Book 2',   1);

总结起来有:* 1 个销售订单 (#1) 也存在于采购订单 (#1) 中* 1 个销售订单 (#2) 仍待处理* 1 个没有销售订单的采购订单 (#2)

我希望能够获取每本书的 isbn 的所有销售和采购订单，并且连接的 SO 和 PO 必须在同一行中。输出必须如下所示:

so_id   so_date     po_id   po_date     isbn    dscr
NULL    NULL        2       2017-08-03  1111111 Book 2
1       2017-07-01  1       2017-07-02  12345   Book 1
2       2017-08-01  NULL    NULL        67890   Book 3

我尝试使用如下查询来获取行:

SELECT 
GROUP_CONCAT(so_id) so_id,
GROUP_CONCAT(so_date) so_date,
GROUP_CONCAT(po_id) po_id,
GROUP_CONCAT(po_date) po_date,
isbn,
dscr
FROM (

SELECT
hd.so_id        so_id,
NULL        so_date,
hd.id   po_id,
hd.dt po_date,
bk.isbn,
it.dscr
            FROM porders_hd hd,
                 porders_it it,
                 books bk
           WHERE it.hd_id = hd.id
             AND bk.isbn  = it.isbn
             AND kind = 2
UNION
SELECT
hd.id   so_id,
hd.dt so_date,
NULL        po_id,
NULL        po_date,
bk.isbn,
it.dscr
            FROM sorders_hd hd,
                 sorders_it it,
                 books bk
           WHERE it.hd_id = hd.id
             AND bk.isbn  = it.isbn
             AND kind = 1

) as table1
 GROUP BY isbn, so_id, po_id

但由于缺少信息，我得到以下结果:

so_id   so_date     po_id   po_date     isbn    dscr
NULL    NULL        2       2017-08-03  1111111 Book 2
1       2017-07-01  NULL    NULL        12345   Book 1
1       NULL        1       2017-07-02  12345   Book 1
2       2017-08-01  NULL    NULL        67890   Book 3

有什么想法可以实现吗？

Answer 1

我认为这就是您所追求的，但我无法从您的代码中找出 kind 的作用。但这里有一个查询，针对每本书，获取关联的 po 行项目，找到相应的 so 行项目并连接标题行，以便日期可用。请注意我的假设，即销售订单不能与相应的 PO 一起存在。

SELECT books.isbn, books.descr, sorders_hd.id, sorders_hd.dt, porders_hd.id, porders_hd.dt
FROM book 
join porders_it on porders_it.isbn = books.isbn
join porders_hd on porders_hd.id = porders_it.hd_id
left outer join sorders_it on sorders_it.hd_id=porders_hd.so_id and sorders_it.isbn = porders_it.isbn
left outer join sorders_hd on sorders_hd.id = sorders_it.hd_it

您可以规范化您的表，这样 descr 就不需要重复，并且还可以在其他表中使用 book.id 而不是 isbn。