mysql - 在 MySQL 查询上使用 SELECT...UNION 不会返回正确的数据

Question

我有 2 个表(schOrd 和 schApp)和 2 个详细信息表(schOrdDett 和 schAppDett)。schOrdDett 和 schAppDett 有很多行，但我只需要 idProdotto=2862 的行

• 如果我查询 schOrdDett，它会正确给出 6 行。
• 如果我查询 schAppDett，它会正确给出 4 行。
• 如果我对这些查询使用 UNION，它会提供 7 行。

为什么？

这是我的代码 https://rextester.com/FZHB7363

DROP TABLE IF EXISTS schOrd;
DROP TABLE IF EXISTS schOrdDett;
DROP TABLE IF EXISTS schApp;
DROP TABLE IF EXISTS schAppDett;

CREATE TABLE `schOrd` (
      `id` int(11) NOT NULL,
      `rifDoc` varchar(50) NOT NULL,
      `rifData` date DEFAULT NULL,
      `ragSoc` varchar(100) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `schOrdDett` (
    `id` int(11) NOT NULL,
    `idDoc` int(11) NOT NULL DEFAULT '0',
    `idProdotto` int(11) NOT NULL DEFAULT '0',
    `codProdotto` text NOT NULL,
    `descProdotto` text NOT NULL,
    `qta` int(11) NOT NULL DEFAULT '0',
    `prezzo` decimal(11,3) NOT NULL DEFAULT '0.000',
    `merce` varchar(11) CHARACTER SET utf8 NOT NULL DEFAULT 'PArticoli',
    `splitVc` int(11) NOT NULL DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `schOrd` (`id`, `rifDoc`, `rifData`, `ragSoc`) VALUES
(729, '2019/O6034', '2019-04-15', 'COMPANY SRL'),
(728, '2019/O6033', '2019-04-15', 'COMPANY SRL');

INSERT INTO `schOrdDett` (`id`, `idDoc`, `idProdotto`, `codProdotto`, `descProdotto`, `qta`, `prezzo`, `merce`, `splitVc`) VALUES
(10833, 729, 2862, 'BB/A0/69', 'AMPL.REM', 1, '20.000', 'PArticoli', 0),
(10831, 729, 2862, 'BB/A0/69', 'AMPL.REM', 1, '26.500', 'PArticoli', 0),
(10832, 729, 2862, 'BB/A0/69', 'AMPL.REM', 1, '10.000', 'PArticoli', 0),
(10785, 728, 2862, 'BB/A0/69', 'AMPL.REM', 1, '10.000', 'PArticoli', 2),
(10784, 728, 2862, 'BB/A0/69', 'AMPL.REM', 1, '10.000', 'PArticoli', 2),
(10783, 728, 2862, 'BB/A0/69', 'AMPL.REM', 1, '10.000', 'PArticoli', 2);


CREATE TABLE `schApp` (
      `id` int(11) NOT NULL,
      `rifDoc` varchar(50) NOT NULL,
      `rifData` date DEFAULT NULL,
      `ragSoc` varchar(100) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `schAppDett` (
    `id` int(11) NOT NULL,
    `idDoc` int(11) NOT NULL DEFAULT '0',
    `idProdotto` int(11) NOT NULL DEFAULT '0',
    `codProdotto` text NOT NULL,
    `descProdotto` text NOT NULL,
    `qta` int(11) NOT NULL DEFAULT '0',
    `prezzo` decimal(11,3) NOT NULL DEFAULT '0.000',
    `merce` varchar(11) CHARACTER SET utf8 NOT NULL DEFAULT 'PArticoli',
    `splitVc` int(11) NOT NULL DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `schApp` (`id`, `rifDoc`, `rifData`, `ragSoc`) VALUES
(750, '2019/A6034', '2019-04-25', 'BUILD SRL'),
(828, '2019/A6033', '2019-04-25', 'BUILD SRL');

INSERT INTO `schAppDett` (`id`, `idDoc`, `idProdotto`, `codProdotto`, `descProdotto`, `qta`, `prezzo`, `merce`, `splitVc`) VALUES
(10833, 750, 2862, 'BB/A0/69', 'AMPL.REM', 1, '20.000', 'PArticoli', 0),
(10831, 750, 2862, 'BB/A0/69', 'AMPL.REM', 1, '26.500', 'PArticoli', 0),
(10785, 828, 2862, 'BB/A0/69', 'AMPL.REM', 1, '10.000', 'PArticoli', 3),
(10784, 828, 2862, 'BB/A0/69', 'AMPL.REM', 1, '10.000', 'PArticoli', 3);


/* This query return me 6 rows CORRECT*/
SELECT ORD.id, ORD.ragSoc, 'ORD' as 'typeDoc', DETTORD.idProdotto AS idProd, DETTORD.merce, DETTORD.qta, DETTORD.prezzo, DETTORD.splitVc 
FROM schOrdDett AS DETTORD, schOrd AS ORD
WHERE DETTORD.idDoc = ORD.id AND DETTORD.merce='PArticoli' AND DETTORD.idProdotto=2862;

/* This query return me 4 rows CORRECT*/
SELECT APP.id, APP.ragSoc, 'APP' as 'typeDoc', DETTAPP.idProdotto AS idProd, DETTAPP.merce, DETTAPP.qta, DETTAPP.prezzo, DETTAPP.splitVc 
FROM schAppDett AS DETTAPP, schApp AS APP
WHERE DETTAPP.idDoc = APP.id AND DETTAPP.merce='PArticoli' AND DETTAPP.idProdotto=2862;



/* This query should return me 10 rows... WHY RETURN ME 7 ROWS ????? */
SELECT ORD.id, ORD.ragSoc, 'ORD' as 'typeDoc', DETTORD.idProdotto AS idProd, DETTORD.merce, DETTORD.qta, DETTORD.prezzo, DETTORD.splitVc FROM schOrdDett AS DETTORD, schOrd AS ORD 
WHERE DETTORD.idDoc = ORD.id AND DETTORD.merce='PArticoli' AND DETTORD.idProdotto=2862 
UNION 
SELECT APP.id, APP.ragSoc, 'APP' as 'typeDoc', DETTAPP.idProdotto AS idProd, DETTAPP.merce, DETTAPP.qta, DETTAPP.prezzo, DETTAPP.splitVc 
FROM schAppDett AS DETTAPP, schApp AS APP 
WHERE DETTAPP.idDoc = APP.id AND DETTAPP.merce='PArticoli' AND DETTAPP.idProdotto=2862;

Answer 1

使用UNION ALL，因此它将包含重复的行

    SELECT
           ORD.id, ORD.ragSoc, 'ORD' as 'typeDoc', 
           DETTORD.idProdotto AS idProd, DETTORD.merce, 
           DETTORD.qta, DETTORD.prezzo, DETTORD.splitVc
      FROM schOrdDett AS DETTORD, schOrd AS ORD 
     WHERE
           DETTORD.idDoc = ORD.id AND
           DETTORD.merce='PArticoli' AND
           DETTORD.idProdotto=2862 
 UNION ALL
    SELECT APP.id, APP.ragSoc, 'APP' as 'typeDoc', 
           DETTAPP.idProdotto AS idProd, DETTAPP.merce, 
           DETTAPP.qta, DETTAPP.prezzo, DETTAPP.splitVc 
      FROM schAppDett AS DETTAPP, schApp AS APP 
     WHERE 
           DETTAPP.idDoc = APP.id AND
           DETTAPP.merce='PArticoli' AND
           DETTAPP.idProdotto=2862

输出:

1
    729
    COMPANY SRL
    ORD
    2862
    PArticoli
    1
    20,000
    0

2
    729
    COMPANY SRL
    ORD
    2862
    PArticoli
    1
    26,500
    0

3
    729
    COMPANY SRL
    ORD
    2862
    PArticoli
    1
    10,000
    0

4
    728
    COMPANY SRL
    ORD
    2862
    PArticoli
    1
    10,000
    2

5
    728
    COMPANY SRL
    ORD
    2862
    PArticoli
    1
    10,000
    2

6
    728
    COMPANY SRL
    ORD
    2862
    PArticoli
    1
    10,000
    2

7
    750
    BUILD SRL
    APP
    2862
    PArticoli
    1
    20,000
    0

8
    750
    BUILD SRL
    APP
    2862
    PArticoli
    1
    26,500
    0

9
    828
    BUILD SRL
    APP
    2862
    PArticoli
    1
    10,000
    3

10
    828
    BUILD SRL
    APP
    2862
    PArticoli
    1
    10,000
    3