精华内容
下载资源
问答
  • mysql实现合并重复数据
    2021-07-27 15:24:06

    首先记录下业务的背景:

    在处理公司业务时,发现有很多会员信息是存在重复的,我们会员以手机号为关键信息,所以当存在两个或多个同样手机号的信息时,就需要合并会员信息,并且把多个信息的积分账户合并😆

    • 让我们来回顾下会用到的基础知识
    1.  group by
       分组查询,可以配合聚集函数统计数据
    eg:select count(*) from 表名 group by 手机号
     
      
    2.in 
      一般用到有子查询的语句中从一个select语句中获取值的范围
    select * from 表A 字段名A in (select 字段名A from 表A where 。。。)
    3.五种聚集函数回顾
      min() 最小值
      max() 最大值
      count() 计算行数,统计数量
      sum() 求和
      avg() 平均值

    先思考一下,你能写出几种查询重复数据的方法?

    这里我给出三种思路:

    1.语句最长但是最好理解的:
    select * from hy 
    where 手机 in (SELECT 手机 from hy GROUP BY 手机 HAVING COUNT(1)>1) 
    AND
    id NOT in (SELECT MIN(id) id from hy GROUP BY 手机 HAVING COUNT(1)>1)
    2.第二种写法
     select * from hy where id not in (SELECT h.id FROM(SELECT MIN(id) id from hy GROUP BY 手机 ) h)
    3.第三种写法
     SELECT
    	* 
     FROM
    	table_name AS ta 
     WHERE
    	ta.唯一键 <> ( SELECT max( tb.唯一键 ) FROM table_name AS tb WHERE ta.判断重复的列 = tb.判断重复的列 )

    最后附上存储过程

    BEGIN
     DECLARE sum_jf INT;   -- 存放积分总和
     DECLARE phone VARCHAR(64); -- 存放手机
     DECLARE i INT UNSIGNED DEFAULT 0; -- 为循环准备的变量
    DECLARE j INT UNSIGNED DEFAULT 0; -- 循环控制变量
    /*得到循环次数存放变量i ,有几个重复的手机号就有几次循环*/
    SELECT COUNT(*) into i FROM (SELECT count(手机) c from hy GROUP BY `手机` HAVING COUNT(1)>1)d;
    
        /*查询积分总和结果赋值到sum_jf*/ 
    WHILE j<i DO
    
    /* 1.先拿到本次手机号2.根据重复手机号去计算总积分 3.更新积分字段 */
    SELECT `手机` INTO phone from hy GROUP BY `手机` HAVING COUNT(1)>1 LIMIT j,1; -- 1
    SET sum_jf=(SELECT sum(`剩余积分`)  FROM hy WHERE 手机=phone);
    SELECT sum_jf,phone;
    UPDATE hy SET 剩余积分=sum_jf WHERE 手机 = phone;
    SET j = j+1;
    
     END WHILE;
    
    /*删除多余得数据只保留一条*/
        DELETE FROM hy WHERE id not in (SELECT c.id from (SELECT min(id) id from hy GROUP BY `手机`)c);
    END

    更多相关内容
  • 第一种方案: 使用递归算法,也是使用频率最多的,大部分开源程序也是这么处理,不过一般都只用到四级分类。这种算法的数据库结构设计最为简单。category表中一个字段id,一个字段fid(父id)。这样可以根据WHERE id =...
  • MySQL触发器和SUM()

    2021-07-19 19:03:33
    check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT SUM(income) FROM family WHERE family.id_student=student.id_student' at line 4 UPDATE I try add ...

    I have two tables student and family. In student I have column st_income and total_income. In family I have income. Total_income is sum of st_income from student and sum of income from family where family.id_student=student.id_student.

    I want to update total_income by trigger and I made this

    CREATE TRIGGER family_income_update AFTER UPDATE ON `family`

    FOR EACH ROW UPDATE student SET total_income= ((SELECT SUM(income)

    FROM family WHERE family.id_student=student.id_student)+

    (SELECT st_income FROM student WHERE student.id_student=NEW.id_student))

    WHERE student.id_student=NEW.id_student

    MySQL accepted this trigger, but when I want to update filed income in table family I get this communicate:

    1093 - You can't specify target table 'student' for update in FROM clause

    I don't know how resolve this.

    UPDATE

    I tried this:

    CREATE TRIGGER family_income_update AFTER UPDATE ON `family`

    FOR EACH ROW

    SET @familyIncome = SELECT SUM(income) FROM family WHERE family.id_student=student.id_student

    SET @studentIncome= SELECT st_income FROM student WHERE student.id_student=NEW.id_student

    SET @totalIncome=@familyIncome+@studentIncome

    UPDATE student SET total_income=@totalIncome WHERE student.id_student=NEW.id_student

    but I got this answers:

    1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT SUM(income) FROM family WHERE family.id_student=student.id_student' at line 4

    UPDATE

    I try add to total_income value of st_income. Both column are in the same table. I tried this:

    CREATE TRIGGER `st_income_update` AFTER UPDATE ON `student`

    FOR EACH ROW BEGIN

    UPDATE student

    SET total_income = total_income + (NEW.st_income - OLD.st_income)

    WHERE student.id_student = NEW.id_student;

    END

    But it causes error.

    解决方案

    This is what's known as the "Mutating Table" problem. What it boils down to is that a row trigger is not allowed to access other rows in the same table because there's no guarantee that rows will be updated in a specific order, among other things.

    First off, you really shouldn't be trying to store total_income when you can calculate it easily when needed. That said, I think you can do what you want by doing something like

    CREATE TRIGGER family_income_update

    AFTER UPDATE ON family

    FOR EACH ROW

    BEGIN

    UPDATE student

    SET total_income = total_income + (NEW.income - OLD.income)

    WHERE student.id_student = NEW.id_student;

    END;

    The idea is to modify student.total_income relative to its previous value, since you can only reference family.income relative to its previous value.

    展开全文
  • SOL-feature FULL OUTER JOIN没有在MySQL实现(我上次看过),所以你必须要使用UNION. FULL OUTER JOIN将加入所有三个基本查询而不会丢失行. 回答后续问题 SELECT event_id, sum(amount) AS total FROM ( (SELECT ...

    这个问题留下了解释的空间.要UNION所有三个查询的结果行,然后选择具有最高“金额”的5行:

    (SELECT event_id, count(*) AS amount

    FROM pageview

    GROUP BY event_id

    ORDER BY pageviews DESC, rand()

    LIMIT 1000)

    UNION ALL

    (SELECT event_id, count(*)

    FROM upvote

    GROUP BY event_id

    ORDER BY upvotes DESC, rand()

    LIMIT 1000)

    UNION ALL

    (SELECT event_id, count(*)

    FROM attending

    GROUP BY event_id

    ORDER BY attendants DESC, rand()

    LIMIT 1000)

    ORDER BY 2 DESC

    LIMIT 5;

    The manual:

    To apply ORDER BY or LIMIT to an individual SELECT, place the clause

    inside the parentheses that enclose the SELECT.

    UNION ALL,因此不会删除重复项.

    如果要为每个event_id添加计数,则此查询应执行此操作:

    SELECT event_id, sum(amount) AS total

    FROM (

    (SELECT event_id, count(*) AS amount

    FROM pageview

    GROUP BY event_id

    ORDER BY pageviews DESC, rand()

    LIMIT 1000)

    UNION ALL

    (SELECT event_id, count(*)

    FROM upvote

    GROUP BY event_id

    ORDER BY upvotes DESC, rand()

    LIMIT 1000)

    UNION ALL

    (SELECT event_id, count(*)

    FROM attending

    GROUP BY event_id

    ORDER BY attendants DESC, rand()

    LIMIT 1000)

    ) x

    GROUP BY 1

    ORDER BY sum(amount) DESC

    LIMIT 5;

    这里棘手的部分是并非所有三个基本查询都会出现每个event_id.因此,您必须注意JOIN不会完全丢失行,并且添加不会变为NULL.

    使用UNION ALL,而不是UNION.您不想删除相同的行,而是想要添加它们.

    x是AS x的简写 – 表别名.子查询需要具有名称.这里可以是任何其他名称.

    SOL-feature FULL OUTER JOIN没有在MySQL中实现(我上次看过),所以你必须要使用UNION. FULL OUTER JOIN将加入所有三个基本查询而不会丢失行.

    回答后续问题

    SELECT event_id, sum(amount) AS total

    FROM (

    (SELECT event_id, count(*) / 100 AS amount

    FROM pageview ... )

    UNION ALL

    (SELECT event_id, count(*) * 5

    FROM upvote ... )

    UNION ALL

    (SELECT event_id, count(*) * 10

    FROM attending ... )

    ) x

    GROUP BY 1

    ORDER BY sum(amount) DESC

    LIMIT 5;

    或者,如果您想以多种方式使用基本计数:

    SELECT event_id

    ,sum(CASE source

    WHEN 'p' THEN amount / 100

    WHEN 'u' THEN amount * 5

    WHEN 'a' THEN amount * 10

    ELSE 0

    END) AS total

    FROM (

    (SELECT event_id, 'p'::text AS source, count(*) AS amount

    FROM pageview ... )

    UNION ALL

    (SELECT event_id, 'u'::text, count(*)

    FROM upvote ... )

    UNION ALL

    (SELECT event_id, 'a'::text, count(*)

    FROM attending ... )

    ) x

    GROUP BY 1

    ORDER BY 2 DESC

    LIMIT 5;

    展开全文
  • 查询sql SELECT z.username,SUM(z.payprice) FROM ( SELECT up.username,SUM(payprice) AS payprice FROM userpay up JOIN (SELECT username FROM userpay GRO...

    查询sql

    SELECT z.username,SUM(z.payprice) FROM (
    SELECT up.username,SUM(payprice) AS payprice FROM userpay up
    JOIN (SELECT username FROM userpay GROUP BY username ) n
    WHERE up.username = n.username GROUP BY up.username
    UNION ALL
    SELECT n.username ,SUM(payprice) AS payprice
    FROM userpay up JOIN (SELECT username FROM userpay GROUP BY username) n
    WHERE up.`username` IN (SELECT u1.username FROM userinfo u1 WHERE u1.`parent_name` = n.username)
    GROUP BY n.username
    ) z GROUP BY z.username

    查询的结果集

    155930_QyYu_1260823.png

     

    SELECT * FROM userpay

    155849_lHas_1260823.png

    SELECT * FROM userinfo

    160021_79hi_1260823.png

     

    转载于:https://my.oschina.net/u/1260823/blog/715081

    展开全文
  • MYSQL实现不关联表横向拼接MYSQL实现不关联表横向拼接?从五张表里面查询数据记录,具体信息如下:其中,超速、疲劳驾驶、禁入区域、异地车辆滞留、空载分别存储在五张不同的报警数据表中(暂且就叫A、B、C、D、E表吧)...
  • 当我们掌握了Mysql的基本操作,比如建表语句,简单的sql语句等,我们还可以对mysql本身做一些配置操作,甚至是对Mysql进行优化。更多mysql高级知识请移步ki4网mysql教程频道。1、mysql的配置文件。在windows中是my....
  • 企业工资管理系统的设计与实现(MySQL)(含开题报告,毕业论文说明书10000字,答辩ppt,程序代码,数据库)摘要企业单位的工资信息和数据纷乱复杂,整理统计费力费时。要做好对企业的管理,企业就必须快速准确地获取自己...
  • Hello,大家好,今天跟大家分享下我们如何在excel中实现隔行求和,工作中我们想要求和的数据可能是每隔一列相互错开的,因为数据区域不是连续的,所以很多人会一行一行地进行计算,十分的浪费时间,今天就跟大家分享...
  • MySQL命令大全

    2018-01-15 11:19:17
    MySql的用户管理是通过User表来实现的,添加新用户常用的方法有两个,一是在User表插入相应的数据行,同时设置相应的权限;二是通过GRANT命令创建具有某种权限的用户。其中GRANT的常用用法如下: grant all on mydb...
  • 基于mysql数据库银行转账功能实现

    千次阅读 2020-04-28 20:46:17
    运用mysql数据库实现银行账户转账功能。 研究思路 1 创建数据库和数据表以及数据表中账户号码和余额等信息。 2 python连接数据库 3 将银行卡号作为key值,判断转入和转出账户是否都存在。 4 判断转出账户待转出金额...
  • 【需求】某用户只能查看其自己信息及其下级信息,涉及通过该用户所在部门获取其下所有部门(多层)id集合。步骤一:对数据库进行设置:set global log_bin_trust_function_creators=TRUE;... 【命令行界面】执行以上语句...
  • 1 WAL归档# 在自动的WAL检查点之间的日志文件段的最大数量checkpoint_segments = # 在自动WAL检 2021-01-16 12:21:49 一、验证postgresql增量合并的方案结果:没有有效可行的增量合并方案,暂时放弃二、梳理...
  • mysql 分组后排序

    千次阅读 2021-11-28 22:25:40
    1、主要借助 SUBSTRING_INDEX 、 GROUP_CONCAT 函数 SELECT product_id, branch, SUBSTRING_INDEX(GROUP_CONCAT(t.stock ORDER BY t.stock DESC ),',',1) stock...2、通过关联查询及COUNT函数实现 SELECT * FROM (SE
  • mysql 查询结果补0的方法

    千次阅读 2020-07-20 16:46:29
    Mysql补零的方法 select lpad(IdcardNo,8,0) from user; 这里就是查询user表中的ID卡号,不足8位的补零
  • MySQL触发器demo

    2021-01-18 21:52:50
    最近在带浙大本科生的数据库系统的上机课,我手里拿到的教学文档非常的老,本人及不习惯老文档里的sql server的操作,就让大家安装的MySQL,这就有了个问题:MySQL上很多SQLserver的操作都无法实现,写触发器时大家...
  • 三个mysql的daemon均为上家的slave,均为下家的master,环形复制,如此,则生生不息。每个环路上的master分别有自己的slave,解决mysql的效率和可用性的问题。遗憾的是,Jeremy的想象力够丰富,但是当时mysql的最新...
  • 一、背景MySQL作为最流行的关系型数据库产品之一,当数据规模增大遭遇性能瓶颈时,最容易想到的解决方案就是分库分表。无论是进行水平拆分还是垂直拆分,第一步必然需要数据迁移与同步。由此可以衍生出一系列数据...
  • 函数在MySQL中,函数分为两类:系统函数(内置函数)和自定义函数不管是内置函数还是用户自定义函数,都是使用select函数名(参数列表)内置函数字符串函数Mysql8 字符串函数char_length():判断字符串的字符数length():...
  • 需求:从1开始直到用户传入对应的值为止,自动求和。(前提是凡是5的倍数都不要) 设计 1、创建函数 2、需要一个形参:确定要累加到什么位置 3、需要定义一个变量来保存对应的结果:set @变量名; 使用局部变量来操作:...
  • MHA自动切换流程

    2021-01-19 14:38:00
    MHA的全名叫做mysql-master-ha,配置后可以在10-30秒内完成master自动切换,切换过程如下:1. 检测master的状态,方法是一秒一次“ SELECT 1 As Value”,发现没有响应后会重复3次检查,如果还没有响应,shutdown并...
  • MySQL

    千次阅读 2022-05-08 23:44:00
    MySQL
  • python- Django mysql操作

    2021-01-26 02:59:40
    它会跟我我们设计的类自动帮我们生成数据库中的表格,同时可以让我们通过类和对象的操作来对应操作数据库中的表和数据。使用django进行数据库开发的步骤如下:1. 配置数据库连接信息2. 在models.py中定义模型类3. ...
  • 让我们首先创建一个表-mysql>createtableDemoTable809(Priceint);使用插入命令在表中插入一些记录-mysql>insertintoDemoTable809values(40);mysql>insertintoDemoTable809values(50);mysql>insertinto.....
  • mysql Decimal 运算;

    千次阅读 2020-09-28 10:15:08
    MySQLDECIMAL数据类型用于在数据库中存储精确的数值。我们经常将DECIMAL数据类型用于保留准确精确度的列,例如会计系统中的货币数据。 要定义数据类型为DECIMAL的列,请使用以下语法: column_nameDECIMAL(P,D...
  • mysql两列相加减将数值添加到新列

    千次阅读 2021-03-08 11:53:12
    对于mysql中某表选取两列进行加减法,并将得到的结果输出到新的列中 仅使用select只能查询,本方法可以将得出的数据导出 步骤一:新建列C alter table 表名 add column 新列名 double DEFAULT null comment ; #注意...
  • =、、is null、is not null、or 函数,如:sum()、round()等等 手动/自动类型转换,如:id = "1",本来是数字,给写成字符串了 SQL 案例 优化四:索引不要放在范围查询右边 举例 比如复合索引:a->b->c,当 where a=...
  • 数据库MySQL详解

    万次阅读 多人点赞 2018-07-24 20:03:47
    全网最详细MySQL教程,2021.1再次更新70%的内容,MySQL 8.0 + Navicat 15
  • MySQL 简介

    千次阅读 2022-05-03 15:20:36
    MySQL 操作MySQL 操作库查看数据库创建数据库修改数据库删除数据库MySQL 操作表创建表查看表修改表删除表MySQL 操作记录添加记录查看记录MySQL 修改记录删除记录 1. 数据库介绍 数据库(Database)是“按照数据结构...
  • 学习MySQL这一篇就够了

    万次阅读 多人点赞 2020-07-28 17:21:34
    DBS:数据库管理系统,又称为数据库软件或者数据库产品,用于创建和管理DB,常见的有MySQL、Oracle、DB2、SQL Server SQL:结构化查询语言,用于和数据库通信的语言,不是某个数据库软件特有的,而是几乎所有的主流...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 10,914
精华内容 4,365
关键字:

mysql如何实现自动求和

mysql 订阅