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

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

转载作者：行者123 更新时间：2023-11-30 21:39:37

我有 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

最佳答案

在连接子句上使用所有三列，如 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

关于MYSQL - 无法正确加入 Group By 2 Columns，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/52306332/

文章推荐： c - 请解释一下代码？

文章推荐： c# - RenderTexture 到 Texture2D 非常慢

文章推荐： c - 我想用c语言将两个不同的结构相乘

文章推荐： c - 执行结束时修改文件内容

Linq group by + where for each group
我想编写一个 linq 表达式，该表达式将返回不包含特定值的 ID。例如，我想返回所有不具有 Value = 30 的不同 ID。 ID, Value 1, 10 1, 20 1, 30 2,
正则表达式 : Grouping in a group
我正在尝试使用 Regexp 匹配 Nmap 命令的输出。可以有两种不同的格式。第一种格式(当 nmap 可以找到主机名时) Nmap scan report for 2u4n32t-n4 (192
mysql - "Mixing of GROUP columns .. with no GROUP columns is illegal if there is no GROUP BY clause"
我正在 Visual Studio 2012 上使用 C# 开发一个软件。我使用 MySQL Connector 6.9.1 进行 MySQL 连接。我的软件在我的操作系统(Win8 x64)上运行顺
Django 嵌套组 : Groups in Groups
在 Django 中(使用 django.contrib.auth 时)我可以添加一个 Group到另一个 Group ?即一个Group成为另一个成员(member) Group ? 如果是这样，我
grouping - LOOP AT... GROUP BY 使用动态组键
我试图通过使用动态组参数对数据进行分组来循环。我们可以在循环的 WHERE 条件上使用动态查询，但我不知道是否可以在组条件中使用动态字符串。以下是用户决定按哪个字段分组，然后根据决定放置其他逻辑的
python 正则表达式 : group of groups?
我有这样的字符串 s = 'MR1|L2-S1x' 模式总是相同的:一个或两个字符，在 [|.+:x-] 中可选地后跟一个数字和一个分隔符。此模式可以重复 6 次。所以匹配模式很明确。 p = r'
mysql - 如何在不触发 "Mixing of GROUP columns [...] with no GROUP columns is illegal if there is no GROUP BY clause"的情况下获取最小列值？
我有一个带有时间戳字段“bar”的表“foo”。如何仅获取查询的最旧时间戳，例如: SELECT foo.bar from foo？我尝试执行以下操作: SELECT MIN(foo.bar) fro
python - auth.User.groups : (fields. E304) 'User.groups' 的反向访问器与 'UserManage.groups' 的反向访问器冲突
在我的 Django 项目中，我有一个 user_manage 应用程序。我在 user_manage 应用的 model.py 中创建了一个名为 UserManage 的模型: from djan
xslt-2.0 - 当总体中的某个项目与 "group-by"中的 "for-each-group"不匹配时，是否可以将其分配给具有空 current-group-key() 的组？
所以我有这样的输入: 还有一个模板指令，例如: 看来我只获得了 foo 和 bar 的组。 (为什么？我预计我可能会得到第三组 current-group-key() = '')。
django - user.groups.add(group) 或 group.user_set.add(user)，哪个更好，为什么？或它们之间的区别
我正在尝试扩展 django.contrib.auth 并遇到将用户添加到组中的情况，这可以通过两种方式完成。我只是想知道为什么会这样，以及其中一种相对于另一种的优势是什么。最佳答案他们做完全相同
php - 如果没有 GROUP BY 子句，则将 GROUP 列(MIN()、MAX()、COUNT()、...)与没有 GROUP 列的混合是非法的
我使用的是旧的 PHP 脚本，并且此查询有错误。由于我没有使用 mysql 的经验，因此无法修复它。 "SELECT COUNT(p.postid) AS pid, p.*, t.* FROM ".T
iphone - 将 Group of Persons 添加到 ABAddressBookRef，group added ok，但是 person 没有添加到 Group？
我有几行 Objective-C 代码，例如: ABAddressBookRef addressBook; CFErrorRef error = NULL; addressBook = ABAddre
mysql - 如果没有 GROUP BY 子句，则将 GROUP 列(MIN()、MAX()、COUNT()...)与没有 GROUP 列的混合是非法的。玛丽亚数据库错误
我正在使用 MariaDB IMDB 电影数据集，我试图解决以下问题。电影表包含 id、名称、排名和年份列 A decade is a sequence of 10 consecutive years
sql - 利斯塔格 : group within group
让我从数据开始，以便更好地描述我的需求。我有一个名为 SUPERMARKET 的表，其中包含以下字段: Field 1: StoreID Field 2: ProductCategory Field
group-by - 带有子查询的 DISTINCT 或 GROUP BY
你好我有这个查询: SELECT DISTINCT a.id, a.runcd, (SELECT SUM(b.CALVAL) FROM GRS b WHERE b.PCode=11000 AND a.
group-by - 如何在 Marklogic 中使用 Group By？
我想在 xquery 中使用 Group By。有人可以告诉我如何在 Marklogic 中使用 Group By 吗？最佳答案或者，您可以使用 xdmp:xslt-invoke 调用 XSLT或
group-by - Sequelize group by 与关联包括 id
因此，当通过 from sequelize 请求组时，如下所示: return models.WorkingCalendar .findAll({
SQL SUM Group by - 基于另一个表中的 'group'
我希望我解释正确。我有 2 个表，有第一个表(table1) +------------+------+-------+-------+ | Date | Item | Block |
sql - "HAVING ... GROUP BY"和 "GROUP BY ... HAVING"之间的区别
我的表 MYTABLE 有 2 列:A 和 B 我有以下代码片段: SELECT MYTABLE.A FROM MYTABLE HAVING SUM(MYTABLE.B) > 100
group-by - Mysql 加速 max() group by
我有一个简单的行分组查询，需要 0.0045 秒。 300.000 行从表 GROUP BY cid 中选择 cid 当我添加 MAX() 进行查询时，需要 0.65 秒才能返回。从表 GROUP

行者123

个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城

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