关于ORACLE的Tricky GROUP BY问题（oracle group by limit）

25-02-10 16

针对关于ORACLE的TrickyGROUPBY问题和oraclegroupbylimit这两个问题，本篇文章进行了详细的解答，同时本文还将给你拓展laravel解决groupBy时出现的错误isn'

针对关于ORACLE的Tricky GROUP BY问题和oracle group by limit这两个问题，本篇文章进行了详细的解答，同时本文还将给你拓展laravel 解决groupBy时出现的错误 isn't in Group By问题、MySQL GROUP BY / ORDER BY问题、mysql group by问题之ONLY_FULL_GROUP_BY特性、Mysql常见的group by order by问题等相关知识，希望可以帮助到你。

本文目录一览：

关于ORACLE的Tricky GROUP BY问题（oracle group by limit）
laravel 解决groupBy时出现的错误 isn't in Group By问题
MySQL GROUP BY / ORDER BY问题
mysql group by问题之ONLY_FULL_GROUP_BY特性
Mysql常见的group by order by问题

关于ORACLE的Tricky GROUP BY问题（oracle group by limit）

我目前面临的问题是我的Oracle知识无法解决，我绝对不是数据库专家，这就是为什么我问您是否有解决SQL查询问题的想法。

这是我的问题，我有两个表，让它们分别称为DEVICE_TABLE和COUNT_TABLE

COUNT_TABLE看起来像：

    DEVICE (Int) PK         |       QUANTITY (Int)- - - - - - - - - - - - - - - - - - - - - - - - - - -        1001                |              4- - - - - - - - - - - - - - - - - - - - - - - - - - -        1002                |             20- - - - - - - - - - - - - - - - - - - - - - - - - - -         1003                |              1…

DEVICE_TABLE看起来像：

      ID (Int) PK            |      WiFi (String)            |     Email (String)          |   Bluetooth(String)           |   …- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -        1001                |             Yes               |               No            |                 No            |   …- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -        1002                |             Yes               |               Yes           |                 No            |   …- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -        1003                |             Unknown           |               Unknown       |                 Yes           |   ……

约束是：

DEVICE_TABLE.ID = COUNT_TABLE.DEVICE

WiFi，电子邮件，蓝牙’‘是只能是渊''''，o’‘或``未知’‘的字符串。

最后，预期的SQL请求结果是（基于我的示例）：

         Feature        |            Yes           |              No            |            Unknown          - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -         WiFi            |             24           |                 0          |                 1                  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -        Email            |             20           |                 4          |                 1                  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -     Bluetooth           |              1           |                24          |                 0                   - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - …

简而言之，此请求的目的是汇总与特定功能兼容的所有设备数。

如果您对如何实现此目标有任何线索，请先谢谢您！（也许不可能…）

答案1

小编典典

在Oracle 11中，可以将该pivot子句与该unpivot子句一起使用：

with count_table as (     select 1001 device_id,  4 quantity from dual union all     select 1002 device_id, 20 quantity from dual union all     select 1003 device_id,  1 quantity from dual ),device_table as (     select 1001 id, ''Yes''     wifi, ''No''       email, ''No''  bluetooth from dual union all     select 1002 id, ''Yes''     wifi, ''Yes''      email, ''No''  bluetooth from dual union all     select 1003 id, ''Unknown'' wifi, ''Unknown''  email, ''Yes'' bluetooth from dual )----------------------------------------select * from (      select        feature,        yes_no_unknown,        sum(quantity)  quantity      from          count_table  c join          device_table d on c.device_id = d.id      unpivot  ( yes_no_unknown                 for feature in (wifi, email, bluetooth)      )       group by       feature,      yes_no_unknown)  pivot ( sum (quantity)        for yes_no_unknown in (''Yes'' as yes, ''No'' as no, ''Unknown'' as unknown));

或者，您可能希望将两个现有表连接到包含三个所需行的值的第三个表。也可能更容易阅读：

with count_table as (     select 1001 device_id,  4 quantity from dual union all     select 1002 device_id, 20 quantity from dual union all     select 1003 device_id,  1 quantity from dual ),device_table as (     select 1001 id, ''Yes''     wifi, ''No''       email, ''No''  bluetooth from dual union all     select 1002 id, ''Yes''     wifi, ''Yes''      email, ''No''  bluetooth from dual union all     select 1003 id, ''Unknown'' wifi, ''Unknown''  email, ''Yes'' bluetooth from dual )----------------------------------------select   f.txt,   sum(case when ( f.txt = ''wifi''      and d.wifi      = ''Yes'' ) or                 ( f.txt = ''email''     and d.email     = ''Yes'' ) or                 ( f.txt = ''bluetooth'' and d.bluetooth = ''Yes'' )             then   c.quantity            else   0 end      ) yes,   sum(case when ( f.txt = ''wifi''      and d.wifi      = ''No'' ) or                 ( f.txt = ''email''     and d.email     = ''No'' ) or                 ( f.txt = ''bluetooth'' and d.bluetooth = ''No'' )             then   c.quantity            else   0 end      ) no,   sum(case when ( f.txt = ''wifi''      and d.wifi      = ''Unknown'' ) or                 ( f.txt = ''email''     and d.email     = ''Unknown'' ) or                 ( f.txt = ''bluetooth'' and d.bluetooth = ''Unknown'' )             then   c.quantity            else   0 end      ) unknownfrom    count_table  c                                   join    device_table d on c.device_id = d.id     cross   join   (        select ''wifi''      txt from dual union all        select ''email''     txt from dual union all        select ''bluetooth'' txt from dual   ) fgroup by     f.txt;

laravel 解决groupBy时出现的错误 isn't in Group By问题

很多人在群里问一个问题：

在laravel5.3版本之后使用groupBy的时候会出现一个问题，类似于:

`QueryException in Connection.php line 770:

SQLSTATE[42000]: Syntax error or access violation: 1055 ''ezhenduan2.app_game_answer_record.id'' isn''t in GROUP BY (SQL: select id, quality, uid from app_game_answer_record group by uid)`

但是放在mysql管理工具中就没有错误，这个原因是因为 laravel配置中的strict配置问题，因为在配置中配置为true的时候，laravel的groupBy会为所有的例分组，设置为false之后，他只会为你指定的列分组，就可以解决这个问题。

以上这篇laravel 解决groupBy时出现的错误 isn''t in Group By问题就是小编分享给大家的全部内容了，希望能给大家一个参考，也希望大家多多支持。

您可能感兴趣的文章:

解决laravel groupBy 对查询结果进行分组出现的问题
浅谈laravel框架sql中groupBy之后排序的问题
解决laravel5.4下的group by报错的问题

MySQL GROUP BY / ORDER BY问题

我有一个这样的MySQL查询：

   SELECT         MAX(messages.ID) as maxID,         messages.from,         messages.read,               users.userName      FROM         messages      LEFT OUTER JOIN         users              ON              users.ID = messages.from      WHERE       messages.to = ?      GROUP BY       messages.from      ORDER BY       maxID DESC

虽然这可以正常工作，并且消息是按降序发送的用户分组的消息，但是现在我想对message.read行进行ORDER时遇到了问题。我的代码如下：

   SELECT         MAX(messages.ID) as maxID,         messages.from,         messages.read,               users.userName      FROM         messages      LEFT OUTER JOIN         users              ON              users.ID = messages.from      WHERE       messages.to = ?      GROUP BY       messages.from      ORDER BY             messages.read ASC,       maxID DESC

现在，messages.read返回用户发送的第一条消息的行的值。但是我需要用户发送的最新（最高ID）的值。我知道GROUP
BY按表中的第一个分组，那么我该如何解决呢？

谢谢您，菲舍尔（Fischer）

答案1

小编典典

我知道GROUP BY按表中的第一个分组，那么我该如何解决呢？

这不是真的。它将返回一个随机行。实际上，这通常是主键顺序的第一行，但不能保证。除MySQL以外的数据库均不允许这种做法，并且会引发错误。

无论如何，检索未读消息的最高ID的一种方法是将消息表连接两次。第二个联接仅查找未读消息。例如：

SELECT  max(msg.ID) as MaxID,       messages.from,       max(unread.ID) as MaxUnreadID,       users.userNameFROM    usersjoin    messages msgon      users.ID = msg.from        and messages.to = ?left join            messages unreadon      users.ID = unread.from        and messages.to = ?        and unread.read = 0GROUP BY        users.ID,       users.userName

mysql group by问题之ONLY_FULL_GROUP_BY特性

背景：

执行 group by 语句时，没有办法select * 出来所有的字段，以至于再对中间某些字段计算时无法推断，影响结果，具体如下：

报错内容
Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggre

原因分析
MysqL 5.7默认启用ONLY_FULL_GROUP_BY特性，即：对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个sql是不合法的，因为列不在GROUP BY从句中，也就是说查出来的列必须在group by后面出现否则就会报错，或者这个字段出现在聚合函数里面。

解决方法
在MysqL的配置文件的最后强制设置sql_mode，把默认的ONLY_FULL_GROUP_BY去掉。

sql_mode的查看命令：select version(), @@sql_mode;

默认的sql_mode：ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

sql_mode的设置命令：

SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

解决效果：

注意：单次有效，重新启动MysqL后失效。

net stop MysqL #windows 停止MysqL服务

net start MysqL #windows 启动MysqL服

Mysql常见的group by order by问题

1.问题

对一列进行分组，根据若干个字段排序

2.案例

3.SQL

3.1 建表及记录

CREATE TABLE `Orders` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `shop_id` varchar(10) COLLATE utf8mb4_bin DEFAULT NULL,
  `pay_date` datetime DEFAULT NULL,
  `pay_no` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

INSERT INTO `Orders`(`id`, `shop_id`, `pay_date`, `pay_no`) VALUES (1, ''1001'', ''2020-01-11 16:43:25'', 5);
INSERT INTO `Orders`(`id`, `shop_id`, `pay_date`, `pay_no`) VALUES (2, ''1001'', ''2020-01-10 16:43:57'', 3);
INSERT INTO `Orders`(`id`, `shop_id`, `pay_date`, `pay_no`) VALUES (3, ''1002'', ''2020-01-17 16:44:09'', 4);
INSERT INTO `Orders`(`id`, `shop_id`, `pay_date`, `pay_no`) VALUES (4, ''1002'', ''2020-01-11 16:44:24'', 2);
INSERT INTO `Orders`(`id`, `shop_id`, `pay_date`, `pay_no`) VALUES (5, ''1002'', ''2020-01-11 16:44:24'', 1);
INSERT INTO `Orders`(`id`, `shop_id`, `pay_date`, `pay_no`) VALUES (6, ''1002'', ''2020-01-12 16:44:24'', 0);

3.2 最终sql

方法1：GROUP_CONCAT实现(数据库版本[5.7.16])

select * from Orders 
where id in (
select SUBSTRING_INDEX(GROUP_CONCAT(id ORDER BY pay_date,pay_no),'','',1) from Orders
GROUP BY shop_id
)

方法2：子查询实现(数据库版本[5.6.16])

select t.* from (
    select * from Orders ORDER BY pay_date ,pay_no 
)t
GROUP BY t.shop_id;

方法2有可能是不行的，不知道是数据库版本问题还是哪里设置没有

3.3 注意点

GROUP_CONCAT有长度限制，记录过多需要注意设置以下配置 SET @@global.group_concat_max_len=数字

我们今天的关于关于ORACLE的Tricky GROUP BY问题和oracle group by limit的分享已经告一段落，感谢您的关注，如果您想了解更多关于laravel 解决groupBy时出现的错误 isn't in Group By问题、MySQL GROUP BY / ORDER BY问题、mysql group by问题之ONLY_FULL_GROUP_BY特性、Mysql常见的group by order by问题的相关信息，请在本站查询。

本文标签：