MYSQL - 无法正确加入 Group By 2 Columns

Question

我有 3 个表要按 2 列进行 2 组查询。但是当我尝试加入(左/内)查询 1 和查询 2 时，它会导致我重复并超出预期的行数。以前我加入查询与 group by 1 列没有问题。数据库很大所以我离开它Here .

查询 1(42 条记录):

SELECT custlist.Custcat as cn,itmnocate.GradeCategory as gc,custlist.Custtype ct,
       SUM(CASE WHEN MONTH(date1) in (1,2,3)  THEN dt2.quantity/1000 ELSE NULL END) AS Q1,
       SUM(CASE WHEN MONTH(date1) in (4,5,6)  THEN dt2.quantity/1000 ELSE NULL END) AS Q2,
       SUM(CASE WHEN MONTH(date1) in (7,8,9)  THEN dt2.quantity/1000 ELSE NULL END) AS Q3,
       SUM(CASE WHEN MONTH(date1) in (10,11,12)  THEN dt2.quantity/1000 ELSE NULL END) AS Q4
FROM (
SELECT 
    date1,region,quantity,unit,customeracc,location,item,
    (CASE WHEN (data2.region = 'central' OR data2.region = 'eastern' OR data2.region = 'northern' OR data2.region = 'southern' )
    THEN CONCAT('PENINSULAR',' ',data2.customeracc)
    WHEN (data2.region = 'kota kinabalu' OR data2.region = 'lahad datu' OR data2.region = 'sandakan' OR data2.region = 'tawau' )
    THEN CONCAT('SABAH',' ',data2.customeracc)
    ELSE concat('SARAWAK',' ',data2.customeracc) END) vlookrefdata 
FROM data2
) dt2
  INNER JOIN custlist on dt2.vlookrefdata = custlist.Vlookref 
  INNER JOIN itmnocate ON dt2.item = itmnocate.ItemNumber
WHERE
     date1 >= DATE_FORMAT('2018-06-11', '%Y-01-01')- INTERVAL 1 YEAR AND date1 <= DATE_FORMAT('2018-06-11', '%Y-12-31') - INTERVAL 1 YEAR
  AND
   dt2.unit = 'KG'
  AND
   dt2.customeracc not in (select Customeraccount from custlist WHERE Custcat = 'bcsb')  
  AND
   custlist.Custtype = 'JV Cos'
  AND
   custlist.Area = 'PENINSULAR' 
GROUP BY itmnocate.GradeCategory,custlist.Custcat

查询 2(37 条记录):

SELECT custlist.Custcat as cn,itmnocate.GradeCategory as gc,custlist.Custtype ct,
       SUM(CASE WHEN MONTH(date1) in (1,2,3)  THEN dt2.quantity/1000 ELSE NULL END) AS Q1,
       SUM(CASE WHEN MONTH(date1) in (4,5,6)  THEN dt2.quantity/1000 ELSE NULL END) AS Q2,
       SUM(CASE WHEN MONTH(date1) in (7,8,9)  THEN dt2.quantity/1000 ELSE NULL END) AS Q3,
       SUM(CASE WHEN MONTH(date1) in (10,11,12)  THEN dt2.quantity/1000 ELSE NULL END) AS Q4
FROM (
SELECT 
    date1,region,quantity,unit,customeracc,location,item,
    (CASE WHEN (data2.region = 'central' OR data2.region = 'eastern' OR data2.region = 'northern' OR data2.region = 'southern' )
    THEN CONCAT('PENINSULAR',' ',data2.customeracc)
    WHEN (data2.region = 'kota kinabalu' OR data2.region = 'lahad datu' OR data2.region = 'sandakan' OR data2.region = 'tawau' )
    THEN CONCAT('SABAH',' ',data2.customeracc)
    ELSE concat('SARAWAK',' ',data2.customeracc) END) vlookrefdata 
FROM data2
) dt2
  INNER JOIN custlist on dt2.vlookrefdata = custlist.Vlookref 
  INNER JOIN itmnocate ON dt2.item = itmnocate.ItemNumber
WHERE
   date1 >= DATE_FORMAT('2018-06-11', '%Y-01-01') AND month(date1) <= month('2018-06-11')
  AND
   dt2.unit = 'KG'
  AND
   dt2.customeracc not in (select Customeraccount from custlist WHERE Custcat = 'bcsb')  
  AND
   custlist.Custtype = 'JV Cos'
  AND
   custlist.Area = 'PENINSULAR' 

GROUP BY itmnocate.GradeCategory,custlist.Custcat

查询 1&2 连接(253 条记录):

SELECT a.q1,a.q2,a.q3,a.q4,a.cn,a.gc,a.ct,b.q1,b.q2,b.q3,b.q4
FROM
(
SELECT custlist.Custcat as cn,itmnocate.GradeCategory as gc,custlist.Custtype ct,
       SUM(CASE WHEN MONTH(date1) in (1,2,3)  THEN dt2.quantity/1000 ELSE NULL END) AS Q1,
       SUM(CASE WHEN MONTH(date1) in (4,5,6)  THEN dt2.quantity/1000 ELSE NULL END) AS Q2,
       SUM(CASE WHEN MONTH(date1) in (7,8,9)  THEN dt2.quantity/1000 ELSE NULL END) AS Q3,
       SUM(CASE WHEN MONTH(date1) in (10,11,12)  THEN dt2.quantity/1000 ELSE NULL END) AS Q4
FROM (
SELECT 
    date1,region,quantity,unit,customeracc,location,item,
    (CASE WHEN (data2.region = 'central' OR data2.region = 'eastern' OR data2.region = 'northern' OR data2.region = 'southern' )
    THEN CONCAT('PENINSULAR',' ',data2.customeracc)
    WHEN (data2.region = 'kota kinabalu' OR data2.region = 'lahad datu' OR data2.region = 'sandakan' OR data2.region = 'tawau' )
    THEN CONCAT('SABAH',' ',data2.customeracc)
    ELSE concat('SARAWAK',' ',data2.customeracc) END) vlookrefdata 
FROM data2
) dt2
  INNER JOIN custlist on dt2.vlookrefdata = custlist.Vlookref 
  INNER JOIN itmnocate ON dt2.item = itmnocate.ItemNumber
WHERE
     date1 >= DATE_FORMAT('2018-06-11', '%Y-01-01')- INTERVAL 1 YEAR AND date1 <= DATE_FORMAT('2018-06-11', '%Y-12-31') - INTERVAL 1 YEAR
  AND
   dt2.unit = 'KG'
  AND
   dt2.customeracc not in (select Customeraccount from custlist WHERE Custcat = 'bcsb')  
  AND
   custlist.Custtype = 'JV Cos'
  AND
   custlist.Area = 'PENINSULAR' 
GROUP BY itmnocate.GradeCategory,custlist.Custcat
)a
inner join
(
SELECT custlist.Custcat as cn,itmnocate.GradeCategory as gc,custlist.Custtype ct,
       SUM(CASE WHEN MONTH(date1) in (1,2,3)  THEN dt2.quantity/1000 ELSE NULL END) AS Q1,
       SUM(CASE WHEN MONTH(date1) in (4,5,6)  THEN dt2.quantity/1000 ELSE NULL END) AS Q2,
       SUM(CASE WHEN MONTH(date1) in (7,8,9)  THEN dt2.quantity/1000 ELSE NULL END) AS Q3,
       SUM(CASE WHEN MONTH(date1) in (10,11,12)  THEN dt2.quantity/1000 ELSE NULL END) AS Q4
FROM (
SELECT 
    date1,region,quantity,unit,customeracc,location,item,
    (CASE WHEN (data2.region = 'central' OR data2.region = 'eastern' OR data2.region = 'northern' OR data2.region = 'southern' )
    THEN CONCAT('PENINSULAR',' ',data2.customeracc)
    WHEN (data2.region = 'kota kinabalu' OR data2.region = 'lahad datu' OR data2.region = 'sandakan' OR data2.region = 'tawau' )
    THEN CONCAT('SABAH',' ',data2.customeracc)
    ELSE concat('SARAWAK',' ',data2.customeracc) END) vlookrefdata 
FROM data2
) dt2
  INNER JOIN custlist on dt2.vlookrefdata = custlist.Vlookref 
  INNER JOIN itmnocate ON dt2.item = itmnocate.ItemNumber
WHERE
   date1 >= DATE_FORMAT('2018-06-11', '%Y-01-01') AND month(date1) <= month('2018-06-11')
  AND
   dt2.unit = 'KG'
  AND
   dt2.customeracc not in (select Customeraccount from custlist WHERE Custcat = 'bcsb')  
  AND
   custlist.Custtype = 'JV Cos'
  AND
   custlist.Area = 'PENINSULAR'   
GROUP BY itmnocate.GradeCategory,custlist.Custcat
)b on a.cn=b.cn

Answer 1

在连接子句上使用所有三列，如 a.cn=b.cn 和 a.ct=b.ct 和 a.gc=b.gc

SELECT a.q1,a.q2,a.q3,a.q4,a.cn,a.gc,a.ct,b.q1,b.q2,b.q3,b.q4
FROM
(
SELECT custlist.Custcat as cn,itmnocate.GradeCategory as gc,custlist.Custtype ct,
       SUM(CASE WHEN MONTH(date1) in (1,2,3)  THEN dt2.quantity/1000 ELSE NULL END) AS Q1,
       SUM(CASE WHEN MONTH(date1) in (4,5,6)  THEN dt2.quantity/1000 ELSE NULL END) AS Q2,
       SUM(CASE WHEN MONTH(date1) in (7,8,9)  THEN dt2.quantity/1000 ELSE NULL END) AS Q3,
       SUM(CASE WHEN MONTH(date1) in (10,11,12)  THEN dt2.quantity/1000 ELSE NULL END) AS Q4
FROM (
SELECT 
    date1,region,quantity,unit,customeracc,location,item,
    (CASE WHEN (data2.region = 'central' OR data2.region = 'eastern' OR data2.region = 'northern' OR data2.region = 'southern' )
    THEN CONCAT('PENINSULAR',' ',data2.customeracc)
    WHEN (data2.region = 'kota kinabalu' OR data2.region = 'lahad datu' OR data2.region = 'sandakan' OR data2.region = 'tawau' )
    THEN CONCAT('SABAH',' ',data2.customeracc)
    ELSE concat('SARAWAK',' ',data2.customeracc) END) vlookrefdata 
FROM data2
) dt2
  INNER JOIN custlist on dt2.vlookrefdata = custlist.Vlookref 
  INNER JOIN itmnocate ON dt2.item = itmnocate.ItemNumber
WHERE
     date1 >= DATE_FORMAT('2018-06-11', '%Y-01-01')- INTERVAL 1 YEAR AND date1 <= DATE_FORMAT('2018-06-11', '%Y-12-31') - INTERVAL 1 YEAR
  AND
   dt2.unit = 'KG'
  AND
   dt2.customeracc not in (select Customeraccount from custlist WHERE Custcat = 'bcsb')  
  AND
   custlist.Custtype = 'JV Cos'
  AND
   custlist.Area = 'PENINSULAR' 
GROUP BY itmnocate.GradeCategory,custlist.Custcat
)a
inner join
(
SELECT custlist.Custcat as cn,itmnocate.GradeCategory as gc,custlist.Custtype ct,
       SUM(CASE WHEN MONTH(date1) in (1,2,3)  THEN dt2.quantity/1000 ELSE NULL END) AS Q1,
       SUM(CASE WHEN MONTH(date1) in (4,5,6)  THEN dt2.quantity/1000 ELSE NULL END) AS Q2,
       SUM(CASE WHEN MONTH(date1) in (7,8,9)  THEN dt2.quantity/1000 ELSE NULL END) AS Q3,
       SUM(CASE WHEN MONTH(date1) in (10,11,12)  THEN dt2.quantity/1000 ELSE NULL END) AS Q4
FROM (
SELECT 
    date1,region,quantity,unit,customeracc,location,item,
    (CASE WHEN (data2.region = 'central' OR data2.region = 'eastern' OR data2.region = 'northern' OR data2.region = 'southern' )
    THEN CONCAT('PENINSULAR',' ',data2.customeracc)
    WHEN (data2.region = 'kota kinabalu' OR data2.region = 'lahad datu' OR data2.region = 'sandakan' OR data2.region = 'tawau' )
    THEN CONCAT('SABAH',' ',data2.customeracc)
    ELSE concat('SARAWAK',' ',data2.customeracc) END) vlookrefdata 
FROM data2
) dt2
  INNER JOIN custlist on dt2.vlookrefdata = custlist.Vlookref 
  INNER JOIN itmnocate ON dt2.item = itmnocate.ItemNumber
WHERE
   date1 >= DATE_FORMAT('2018-06-11', '%Y-01-01') AND month(date1) <= month('2018-06-11')
  AND
   dt2.unit = 'KG'
  AND
   dt2.customeracc not in (select Customeraccount from custlist WHERE Custcat = 'bcsb')  
  AND
   custlist.Custtype = 'JV Cos'
  AND
   custlist.Area = 'PENINSULAR'   
GROUP BY itmnocate.GradeCategory,custlist.Custcat
)b on a.cn=b.cn and a.ct=b.ct and a.gc=b.gc