我有以下情形:有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


有什么想法可以实现吗?

最佳答案

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

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


您可以对表进行规范化,以便不必重复描述,也可以在其他表中使用book.id而不是isbn。

关于mysql - mysql GROUP BY想法,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/45529789/

10-13 05:11