【Mysql】Mysql似oracle分析函数sum over的实现

最新推荐文章于 2023-06-30 18:52:18 发布
最新推荐文章于 2023-06-30 18:52:18 发布
阅读量2.3k 收藏 4
点赞数 1
文章标签： 数据库
先看oracle怎么实现的
  1. select deptno,ename,sal,sum(sal) over(order by ename) from emp; --姓名排序连续求和
  2. select deptno,ename,sal,sum(sal) over(order by deptno) from emp; --所有部们排序连续求和
  3. select deptno,ename,sal,sum(sal) over(partition by deptno) from emp; ---各个部门的总和
  4. select deptno,ename,sal,sum(sal) over(partition by deptno order by ename) from emp; ---各个部门之间连续求和
  5. select deptno,ename,sal,sum(sal) over(order by deptno,ename) from emp;


  8. select deptno,ename,sal,
  9.       sum(sal) over (partition by deptno order by ename) 部门连续求和,--各部门的薪水"连续"求和
  10.       sum(sal) over (partition by deptno) 部门总和, -- 部门统计的总和，同一部门总和不变
  11.       100*round(sal/sum(sal) over (partition by deptno),4) "部门份额(%)",
  12.       sum(sal) over (order by deptno, ename) 连续求和, --所有部门的薪水"连续"求和
  13.       sum(sal) over () 总和, -- 此处sum(sal) over () 等同于sum(sal)，所有员工的薪水总和
  14.      100*round(sal/sum(sal) over (),4) "总份额(%)"
  15.      from emp


mysql的实现
  1. 如下：
  2. SELECT a.id,a.user_id,a.borrow_id, a.repayment_money,
    (SELECT SUM(repayment_money) FROM rb_repayment_period WHERE id<=a.id) "累加和",
  3. (SELECT AVG(repayment_money) FROM rb_repayment_period WHERE id<=a.id) "平均值" ,
    (SELECT SUM(repayment_money) FROM rb_repayment_period  WHERE borrow_id=a.borrow_id GROUP BY borrow_id) "每组和",
    (SELECT SUM(repayment_money) FROM rb_repayment_period) "全部和",
    (SELECT SUM(repayment_money) FROM rb_repayment_period  WHERE id<=a.id  GROUP BY borrow_id HAVING  borrow_id=a.`borrow_id` ) "每组累加和"    
    FROM rb_repayment_period a;

结果


原数据
  1. sql：
  2. CREATE TABLE `rb_repayment_period` (
      `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
      `borrow_id` int(11) DEFAULT '0' COMMENT '标的id',
      `user_id` int(11) DEFAULT '0' COMMENT '借款人id',
      `repayment_money` decimal(20,6) DEFAULT '0.000000' COMMENT '本次还款金额',
      `capital_money` decimal(20,6) DEFAULT '0.000000' COMMENT '本金',
      `expect_money` decimal(20,6) DEFAULT '0.000000' COMMENT '预期收益',
      `exceed_money` decimal(20,6) DEFAULT '0.000000' COMMENT '超额收益',
      `actual_rate` decimal(20,6) DEFAULT '0.000000' COMMENT '实际收益率',
      `third_company_money` decimal(20,6) DEFAULT '0.000000' COMMENT '第三方公司收益',
      `load_money` decimal(20,6) DEFAULT '0.000000' COMMENT '借款人利益',
      `repayment_time` int(3) DEFAULT '0' COMMENT '还款次数',
      `repayment_stage` int(3) DEFAULT '0' COMMENT '当前还款的阶段',
      `playform_money` decimal(20,6) DEFAULT '0.000000' COMMENT '平台收益',
      `add_datetime` timestamp NOT NULL DEFAULT '2016-04-24 03:49:30' COMMENT '操作时间',
      `memo_id_first` int(11) DEFAULT '0' COMMENT '备用id',
      `memo_dec_first` decimal(20,6) DEFAULT '0.000000' COMMENT '备用dec',
      `memo_str_first` varchar(500) DEFAULT NULL COMMENT '备用str1',
      `memo_str_second` varchar(500) DEFAULT NULL COMMENT '备用str2',
      `memo_date_first` timestamp NULL DEFAULT '2016-04-24 03:49:30' COMMENT '备用时间1',
      `memo_date_second` timestamp NULL DEFAULT '2016-04-24 03:49:30' COMMENT '备用时间2',
      `total_repay_money` decimal(20,6) DEFAULT '0.000000' COMMENT '累计还款总额',
      `repay_type` int(3) DEFAULT '0' COMMENT '还款类型',
      `left_capital_money` decimal(20,6) DEFAULT '0.000000' COMMENT '剩余本金',
      `left_expect_money` decimal(20,6) DEFAULT '0.000000' COMMENT '剩余收益',
      `left_money` decimal(20,6) DEFAULT '0.000000' COMMENT '剩余留用',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=60 DEFAULT CHARSET=utf8;
    /*!40101 SET character_set_client = @saved_cs_client */;


    --
    -- Dumping data for table `rb_repayment_period`
    --


    LOCK TABLES `rb_repayment_period` WRITE;
    /*!40000 ALTER TABLE `rb_repayment_period` DISABLE KEYS */;
    INSERT INTO `rb_repayment_period` VALUES (26,160,188,1000.000000,1000.000000,0.000000,0.000000,0.000000,0.000000,0.000000,1,2,0.0000
    00,'2016-04-24 07:43:38',0,0.000000,NULL,NULL,'2016-04-24 03:49:30','2016-04-24 03:49:30',0.000000,0,0.000000,0.000000,0.000000),(27
    ,160,188,100.000000,0.000000,100.000000,0.000000,0.000000,0.000000,0.000000,2,2,0.000000,'2016-04-24 07:45:26',0,0.000000,NULL,NULL,
    '2016-04-24 03:49:30','2016-04-24 03:49:30',0.000000,0,0.000000,0.000000,0.000000),(30,160,188,1000.000000,0.000000,87.500000,11.250
    000,0.000000,11.250000,890.000000,3,4,0.000000,'2016-04-24 08:09:11',0,0.000000,NULL,NULL,'2016-04-24 03:49:30','2016-04-24 03:49:30
    ',0.000000,0,0.000000,0.000000,0.000000),(42,163,187,4400.000000,2000.000000,375.000000,0.000000,0.000000,0.000000,2025.000000,1,3,0
    .000000,'2016-04-25 07:33:59',0,0.000000,NULL,NULL,'2016-04-25 07:33:59','2016-04-25 07:33:59',0.000000,0,0.000000,0.000000,0.000000
    ),(47,172,187,10000.000000,2000.000000,375.000000,12.500000,0.000000,12.500000,7600.000000,1,4,0.000000,'2016-04-26 02:48:05',0,0.00
    0000,NULL,NULL,'2016-04-26 02:48:05','2016-04-26 02:48:05',0.000000,0,0.000000,0.000000,0.000000),(48,174,187,10000.000000,2000.0000
    00,375.000000,12.500000,0.000000,12.500000,7600.000000,1,4,0.000000,'2016-04-26 03:23:41',0,0.000000,NULL,NULL,'2016-04-26 03:23:41'
    ,'2016-04-26 03:23:41',0.000000,0,0.000000,0.000000,0.000000),(49,157,187,3000.000000,1000.000000,120.000000,0.000000,0.000000,0.000
    000,1880.000000,1,3,0.000000,'2016-04-26 03:58:56',0,0.000000,NULL,NULL,'2016-04-26 03:58:56','2016-04-26 03:58:56',3000.000000,2,0.
    000000,0.000000,0.000000),(50,175,187,10000.000000,2000.000000,375.000000,12.500000,0.000000,12.500000,7600.000000,1,4,0.000000,'201
    6-04-26 05:29:48',0,0.000000,NULL,NULL,'2016-04-26 05:29:48','2016-04-26 05:29:48',10000.000000,2,0.000000,0.000000,0.000000),(54,17
    7,187,2000.000000,2000.000000,0.000000,0.000000,0.000000,0.000000,0.000000,1,2,0.000000,'2016-04-27 01:59:35',0,0.000000,NULL,NULL,'
    2016-04-27 01:59:35','2016-04-27 01:59:35',2000.000000,1,0.000000,375.000000,0.000000),(55,177,187,4000.000000,0.000000,375.000000,0
    .000000,360.000000,0.000000,3625.000000,2,3,0.000000,'2016-04-27 02:01:43',0,0.000000,NULL,NULL,'2016-04-27 02:01:43','2016-04-27 02
    :01:43',6000.000000,2,0.000000,0.000000,0.000000),(56,178,187,2100.000000,2000.000000,100.000000,0.000000,0.000000,0.000000,0.000000
    ,1,2,0.000000,'2016-04-27 03:43:43',0,0.000000,NULL,NULL,'2016-04-27 03:43:43','2016-04-27 03:43:43',2100.000000,1,0.000000,275.0000
    00,0.000000),(57,178,187,3000.000000,0.000000,275.000000,0.000000,378.000000,0.000000,2725.000000,2,3,0.000000,'2016-04-27 07:07:34'
    ,0,0.000000,NULL,NULL,'2016-04-27 07:07:34','2016-04-27 07:07:34',5100.000000,2,0.000000,0.000000,0.000000),(58,181,187,1000.000000,
    1000.000000,0.000000,0.000000,0.000000,0.000000,0.000000,1,1,0.000000,'2016-04-27 07:15:58',0,0.000000,NULL,NULL,'2016-04-27 07:15:5
    8','2016-04-27 07:15:58',1000.000000,1,1000.000000,375.000000,0.000000),(59,181,187,500.000000,500.000000,0.000000,0.000000,180.0000
    00,0.000000,0.000000,2,1,0.000000,'2016-04-27 07:26:34',0,0.000000,NULL,NULL,'2016-04-27 07:26:34','2016-04-27 07:26:34',1500.000000
    ,1,500.000000,375.000000,0.000000);


rownum的实现

  1. 环境：
  2. mysql> show create table tbl\G;
    *************************** 1. row ***************************
           Table: tbl
    Create Table: CREATE TABLE `tbl` (
      `id` int(11) NOT NULL,
      `col` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
  3. mysql> insert into tbl values (1,26),(2,46),(3,35),(4,68),(5,93),(6,92);
  4. mysql> select * from tbl
        -> ;
    +----+------+
    | id | col  |
    +----+------+
    |  1 |   26 |
    |  2 |   46 |
    |  3 |   35 |
    |  4 |   68 |
    |  5 |   93 |
    |  6 |   92 |
    +----+------+
    6 rows in set (0.00 sec)


  7. 实现一：

    1. mysql> select id,a.col,( select count(*) from tbl b where b.col<=a.col) as rank from tbl a order by rank;
      +----+------+------+
      | id | col  | rank |
      +----+------+------+
      |  1 |   26 |    1 |
      |  3 |   35 |    2 |
      |  2 |   46 |    3 |
      |  4 |   68 |    4 |
      |  6 |   92 |    5 |
      |  5 |   93 |    6 |
      +----+------+------+
      6 rows in set (0.00 sec)
    2. 瑕疵：当有重复的数据时就有bug了
    3. mysql> select id,a.col,(select count(*) from tbl b where b.col<=a.col ) as rank from tbl a order by rank;
      +----+------+------+
      | id | col  | rank |
      +----+------+------+
      |  1 |   26 |    2 |
      |  9 |   26 |    2 |
      |  3 |   35 |    4 |
      |  8 |   35 |    4 |
      |  2 |   46 |    5 |
      |  4 |   68 |    6 |
      |  6 |   92 |    7 |
      |  5 |   93 |    8 |
      +----+------+------+
      8 rows in set (0.00 sec)




    实现二：解决重复bug（先建立一张数字表Nums(a int) 插入1-100即可）
  9. 第一步求出个数
MySQL [interface_hd_com]> select a.col,COUNT(*) as count,( select count(*) from testtt b where b.col<a.col) as rownum from testtt a group by a.col;
 +------+-------+--------+
| col | count | rownum |
+------+-------+--------+
| 26 | 1 | 0 |
| 35 | 2 | 1 |
| 46 | 2 | 3 |
| 68 | 2 | 5 |
| 92 | 2 | 7 |
| 93 | 2 | 9 |
+------+-------+--------+
6 rows in set (0.00 sec)

第二步：
MySQL [interface_hd_com]> select Nums.a+c.rownum as rank ,col from (select a.col,COUNT(*) as count,( select count(*) from testtt b where b.col<a.col) as rownum from testtt a group by a.col) c,Nums where Nums.a<=count order by col;
+------+------+
| rank | col  |
+------+------+
|    1 |   26 |
|    2 |   35 |
|    3 |   35 |
|    4 |   46 |
|    5 |   46 |
|    6 |   68 |
|    7 |   68 |
|    8 |   92 |
|    9 |   92 |
|   10 |   93 |
|   11 |   93 |
+------+------+
11 rows in set (0.01 sec)





连续区间的实现（求连续id区间）

第一步：标示 mysql> SELECT id,@id:=@id+1  AS alias1 FROM tbl,(SELECT @id:=0) AS id;
+----+--------+
| id | alias1 |
+----+--------+
| 11 |      1 |
| 12 |      2 |
| 13 |      3 |
| 14 |      4 |
| 15 |      5 |
| 16 |      6 |
| 18 |      7 |
| 19 |      8 |
+----+--------+
8 rows in set (0.00 sec)
第二步：计算一下与标示的差值（如果是连续的，那么差值一样）
mysql> SELECT id,alias1,(id-alias1) AS diff FROM (SELECT id,@id:=@id+1  AS alias1 FROM tbl,(SELECT @id:=0) AS id) b;
+----+--------+------+
| id | alias1 | diff |
+----+--------+------+
| 11 |      1 |   10 |
| 12 |      2 |   10 |
| 13 |      3 |   10 |
| 14 |      4 |   10 |
| 15 |      5 |   10 |
| 16 |      6 |   10 |
| 18 |      7 |   11 |
| 19 |      8 |   11 |
+----+--------+------+
8 rows in set (0.00 sec)


第三步：根据差值分组找出最大最小即可
mysql> SELECT MIN(id) start_pos,MAX(id) end_pos
    -> FROM
    -> (SELECT id,alias1,(id-alias1) AS diff FROM (SELECT id,@id:=@id+1  AS alias1 FROM tbl,(SELECT @id:=0) AS id) b)
    -> AS c
    -> GROUP BY diff;
+-----------+---------+
| start_pos | end_pos |
+-----------+---------+
|        11 |      16 |
|        18 |      19 |
+-----------+---------+
2 rows in set (0.00 sec)



实验：求tel相同的连续段
MySQL [interface_hd_com]> select * from testtab;
 +------+--------+
| id | tel |
+------+--------+
| 1 | 187163 |
| 2 | 187163 |
| 3 | 187164 |
| 4 | 187164 |
| 5 | 187163 |
| 6 | 187163 |
| 7 | 187164 |
| 8 | 187163 |
| 9 | 19999 |
+------+--------+
9 rows in set (0.00 sec)
按照上面的思路求得
MySQL [interface_hd_com]> SELECT MIN(id) start_pos,MAX(id) end_pos,tel FROM (SELECT id,alias1,(id-alias1) AS diff,tel FROM (SELECT id,@id:=@id+1  AS alias1,tel FROM testtab,(SELECT @id:=0) AS id) b) as c GROUP BY diff,tel order by tel desc;
+-----------+---------+--------+
| start_pos | end_pos | tel    |
+-----------+---------+--------+
|         3 |       7 | 187164 |
|         1 |       8 | 187163 |
|         9 |       9 |  19999 |
+-----------+---------+--------+   ---这样是有bug的

发现这样是不行的，因为id是连续的，所以同一个tel的diff是相同的，但其实中间隔着别的tel
解决办法：分两次求在合并
MySQL [interface_hd_com]> SELECT MIN(id) start_pos,MAX(id) end_pos,tel FROM (SELECT id,alias1,(id-alias1) AS diff,tel FROM (SELECT id,@id:=@id+1 AS alias1,tel FROM testtab,(SELECT @id:=0) AS id where tel in (SELECT distinct(tel) from testtab where tel<>187164)) b) as c GROUP BY diff,tel order by tel desc;
 +-----------+---------+--------+
| start_pos | end_pos | tel |
+-----------+---------+--------+
| 1 | 2 | 187163 |
| 5 | 6 | 187163 |
| 8 | 8 | 187163 |
| 9 | 9 | 19999 |
+-----------+---------+--------+
4 rows in set (0.00 sec)

MySQL [interface_hd_com]> SELECT MIN(id) start_pos,MAX(id) end_pos,tel FROM (SELECT id,alias1,(id-alias1) AS diff,tel FROM (SELECT id,@id:=@id+1 AS alias1,tel FROM testtab,(SELECT @id:=0) AS id where tel in (187164)) b) as c GROUP BY diff,tel order by tel desc;
+-----------+---------+--------+
| start_pos | end_pos | tel |
+-----------+---------+--------+
| 3 | 4 | 187164 |
| 7 | 7 | 187164 |
+-----------+---------+--------+
2 rows in set (0.00 sec)
union 一下








还有一些是其他的博客写的方法也不错：
rownum的实现：http://blog.csdn.net/acmain_chm/article/details/4095531
http://blog.csdn.net/jgmydsai/article/category/3139929

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29096438/viewspace-2089520/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29096438/viewspace-2089520/

cml2016
关注
MySQL sum（） over（order by ……）
qq_45272190的博客
04-13 2522
源于牛客一道题。 1.每个人的综合成绩用A,B,C,D,E表示，90分以上都是A，8090分都是B，6070分为C，50~60为D，E为50分以下 因为每个名次最多1个人，比如有2个A，那么必定有1个A是第1名，有1个A是第2名(综合成绩同分也会按照某一门的成绩分先后)。 现在有班级成绩表(class_grade)如下: drop table if exists class_grade; CREATE TABLE class_grade ( grade varchar(32) NOT NULL, numbe
mysql partition by用法_闲话Mysql之sum() over()与sum() group by 有啥不同~
weixin_39634878的博客
11-30 4316
其实对于大部分学过同学来讲呢，sum() group by 算是老朋友了，作用就是利用sum()的聚合作用来统计具有某些共同特性的值的和，比如对于sum(考试分数) 来讲可以group by学号，班级，课程之类的。但是除此之外其实还有一个写法就是sum() over(partition)，partiotion在英文单词中也有分类的意思，接下来先让我们建立一个表格：### 创建表格 create t...
MySQL SUM(column3) OVER (PARTITION BY column1 ORDER BY column2)用法
weixin_45875358的博客
06-30 1396
结果将显示在每一行，而不是在整个结果集的末尾。这使得您可以在每一行上获取累积和的值。列的升序进行排序，并计算了每一行与前面行的累积和。可以实现对结果集中的某个列进行累加求和。计算每一天的销售累计金额。我们想要计算每一天的销售累计金额。可以计算每一行与前面行的累积和。，其中包含销售记录，包括销售日期(在上面的示例中，我们使用。分别是你要查询的列名。
mysql窗口函数 sum() over(order by)
qq_54494937的博客
08-09 1814
本题出题的题意其实主要是考察sum() over (order by ) 开窗函数，sum(a) over (order by b) 的含义是: 例如 a b 1 2 3 4 5 6 按照b列排序，将a依次相加，得到结果，如下: a bsum(a) over (order by b)： 1 2 1 3 4 1+3 5 6 1+3+5 ...
mysql分组后sum,sumover用法，以及与groupby的区别
weixin_39962394的博客
03-17 1432
1、sum over用法 sum(col1) over(partition by col2 order by col3 ) 以上的函数可以理解为：按col2 进行分组(partition )，每组以col3 进行排序(order)，并进行连续加总(sum) 表a，内容如下： B C D 02 02 1 02 03 2 02 04 3 02 05 4 02 01 5 02 06 6 021、sum ...
MySQL窗口函数OVER()
m0_67402235的博客
09-11 850
深知大多数初中级Java工程师，想要提升技能，往往是自己摸索成长或者是报班学习，但对于培训机构动则近万的学费，着实压力不小。自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！因此收集整理了一份《Java开发全套学习资料》送给大家，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。
sumover mysql_沃趣科技
weixin_33556941的博客
01-30 362
|MySQL分析函数实现还好MySQL8.0已经实现了与Oracle相同的分析函数。1.实现rownumSET @rn:=0;SELECT @rn:=@rn+1 AS rownum ,e.* FROM emp e;或者写成：SELECT @rn:=@rn + 1 AS rownum ,e.* FROM emp e ,(SELECT @rn:=0) c2.各种分析函数写法(MySQL实现分析语句时可...
Mysql 与 Oracle 基本语法的对照
yuyan64的博客
12-15 691
Mysql 与 Oracle 基本语法的对照 函数类型 oracle（11g） mysql（8.0.20） 语法 例子 窗口函数 over() over() 一致 Over()窗口函数最常见的搭配有以下几种： 1. rank(),dense_rank(),row_number() + over(partition by … order by …) 排名 2. sum(),avg(),count()聚合函数+over(partition .
ORACLE转MYSQL各种函数说明（ROW_NUMBER() ，COUNT()，DENSE_RANK()）
qq_15845499的博客
04-11 2248
ORACLE转MYSQL各种函数说明 涉及函数： ROW_NUMBER() OVER(order by x desc) ROW_NUMBER() OVER(PARTITION BY x ORDER BY y) COUNT() OVER(PARTITION BY x order by y desc) DENSE_RANK() OVER(ORDER BY x) 1、通用公式：（分组排序加序号） O...
MySQL —— 函数
weixin_46540606的博客
02-01 2363
概述 在MySQL中，为了提高代码重用性和隐藏实现细节，MySQL提供了很多函数，函数可以理解为别人封装好的模板代码 分类 在MySQL中，函数非常多，主要可以分为以下几类： 聚合函数 数学函数 字符串函数 日期函数 控制流函数 窗口函数 聚合函数 在MySQL中聚合函数主要是：count、sum、min、max、avg…在前面的学习中都有学习过，还有一个特殊的函数，是没有学习过的 —— group_concat()，该函数用于实现行的合并，group_concat()函数首先根据 group by指定
mysql窗口函数-累计求和sum over
王世洪的博客
01-30 5466
先上一个简单的demo，按时间周期累计求和： SELECT date, sales, sum( sales ) over ( ORDER BY date ) AS cum_sales FROM sales ORDER BY date ASC; 再来一个demo，按字段class（自定义）进行分类并累计求和： SELECT date, sales, sum( sales ) over ( PARTITION BY class ORDER BY date ) AS cum_sale
oracle转mysql过程中的sum over处理
sskicgah的专栏
07-10 4685
项目需要，原来的系统很多的统计分析，
MySQL - SUM() OVER() 函数用法详解
0x8g1T9E-
09-15 1726
MySQL - SUM() OVER() 函数用法详解 测试数据 select * from wmg_test; 按照 v2 排序，累计 n+n-1+....+1 select v1,v2,sum(v2) over(order by v2) as sum from wmg_test; 先分组，组内在进行上一个案例中的操作 select v1,v2,sum(v2) over(partition by v1 order by v2) as sum
mysql oracle over_MYSQL-实现ORACLE- row_number() over(partition by ) 分组排序功能
weixin_36212400的博客
02-07 50
DROP PROCEDUREIF EXISTSPROCE_USER_NEW_MSG;DROP TEMPORARY TABLEIF EXISTSTEM_USER_NEW_MSG;DELIMITER||CREATE PROCEDUREPROCE_USER_NEW_MSG (IN FRIST_RESULT INT,IN FETCH_SIZE INT)BEGINSELECTM.MESSAGE_ID,M.G...
Mysql中的sum() over (order by ) 开窗函数
xiyuanda的博客
12-15 2604
sum() over (order by ) 开窗函数，sum(a) over (order by b) 的含义是: 例如 a b 1 5 4 3 6 7 按照b列排序，将a依次相加，得到结果，如下: a b sum(a)over(order by b) 4 3 4 1 5 4+1 6 7 4+4+6 ...
sumover mysql_mysql之 sum() over(partition by)
weixin_30039311的博客
01-19 676
SELECT empno,ename,sal,IF(@deptno = deptno, @sal := @sal, @sal := s_sal) AS s_sal,@deptno := deptnoFROM(SELECT empno,ename,sal,IF(@deptno = deptno, @sal := @sal + sal, @sal := sal) AS s_sal,@deptno :=...
MySQL sum if 的使用
foreverling_ling的博客
08-24 3439
sum (column ) ： 把一列的值全部相加 sum( if ( 条件 , 2 , 0 ) ) ： 满足“条件”，加2，不满足条件，加0 例：表user如图统计每个用户count<5的次数select user_id,sum(IF(count<5,1,0)) as times from user group by user_id;结果如下：
oracle和mysql中over用法一样吗？
最新发布
09-13
Oracle和MySQL数据库管理系统中都支持`OVER`子句，这是SQL标准中的一部分，用于定义窗口函数。不过，两个数据库系统在一些细节上可能存在差异，尽管基本用法是类似的。以下是一些共通点和可能的差异： 共通点： 1. 在查询中，`OVER`子句通常与窗口函数一起使用，如`ROW_NUMBER()`, `RANK()`, `DENSE_RANK()`, `SUM()`, `AVG()`等。 2. `OVER`子句用于指定窗口函数如何对一系列行进行计算，这些行被称为窗口。 差异： 1. MySQL较新版本（MySQL 8.0及以上）才开始支持窗口函数，而Oracle支持窗口函数已经很长时间了。 2. 在某些窗口函数的实现上，两个数据库系统可能有不同的默认行为。例如，在处理窗口的起始和结束边界时，Oracle和MySQL可能有不同的默认值。 3. Oracle提供了`DBMS_RANDOM`包来生成随机数，而MySQL则直接使用`RAND()`函数，这些函数的使用场景可能会与`OVER`子句结合，但具体的语法和行为可能有差异。 具体的用法示例： ```sql -- 在Oracle中使用OVER子句 SELECT employee_id, salary, SUM(salary) OVER (PARTITION BY department_id) AS department_total FROM employees; -- 在MySQL中使用OVER子句 SELECT employee_id, salary, SUM(salary) OVER (PARTITION BY department_id ORDER BY salary RANGE BETWEEN INTERVAL 1 PRECEDING AND INTERVAL 1 FOLLOWING) AS moving_total FROM employees; ``` 在实际使用中，需要查阅相应数据库的官方文档，以获取最新和最准确的信息。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值