精华内容
下载资源
问答
  • mysql交叉表查询解决方案整理
    2021-01-18 23:11:47

    交叉表是一种常用的分类汇总查询。使用交叉表查询,可以显示表中某个字段的汇总值,并将它们分组,其中一组列在数据表的左侧,另一组列在数据表的上部。行和列的交叉处可以对数据进行多种汇总计算,如:求和、平均值、记数、最大值、最小值等。使用交叉表查询数据非常直观明了,被广泛应用。交叉表查询也是数据库的一个特点。

    以下例子收集自csdn整理

    数据样本:

    create table tx(

    id int primary key,

    c1 char(2),

    c2 char(2),

    c3 int

    );

    insert into tx values

    (1,'A1','B1',9),

    (2,'A2','B1',7),

    (3,'A3','B1',4),

    (4,'A4','B1',2),

    (5,'A1','B2',2),

    (6,'A2','B2',9),

    (7,'A3','B2',8),

    (8,'A4','B2',5),

    (9,'A1','B3',1),

    (10,'A2','B3',8),

    (11,'A3','B3',8),

    (12,'A4','B3',6),

    (13,'A1','B4',8),

    (14,'A2','B4',2),

    (15,'A3','B4',6),

    (16,'A4','B4',9),

    (17,'A1','B4',3),

    (18,'A2','B4',5),

    (19,'A3','B4',2),

    (20,'A4','B4',5);

    方法一:

    mysql> SELECT

    -> IFNULL(c1,'total') AStotal,-> SUM(IF(c2='B1',c3,0)) ASB1,-> SUM(IF(c2='B2',c3,0)) ASB2,-> SUM(IF(c2='B3',c3,0)) ASB3,-> SUM(IF(c2='B4',c3,0)) ASB4,-> SUM(IF(c2='total',c3,0)) AStotal-> FROM(-> SELECT c1,IFNULL(c2,'total') AS c2,SUM(c3) ASc3-> FROMtx-> GROUP BYc1,c2-> WITHROLLUP-> HAVING c1 IS NOT NULL

    -> ) ASA-> GROUP BYc1-> WITHROLLUP;+-------+------+------+------+------+-------+

    | total | B1 | B2 | B3 | B4 | total |

    +-------+------+------+------+------+-------+

    | A1 | 9 | 2 | 1 | 11 | 23 |

    | A2 | 7 | 9 | 8 | 7 | 31 |

    | A3 | 4 | 8 | 8 | 8 | 28 |

    | A4 | 2 | 5 | 6 | 14 | 27 |

    | total | 22 | 24 | 23 | 40 | 109 |

    +-------+------+------+------+------+-------+

    5 rows in set, 1 warning (0.00 sec)

    方法二:

    mysql> selectc1,-> sum(if(c2='B1',C3,0)) ASB1,-> sum(if(c2='B2',C3,0)) ASB2,-> sum(if(c2='B3',C3,0)) ASB3,-> sum(if(c2='B4',C3,0)) AS B4,SUM(C3) ASTOTAL-> fromtx-> group byC1-> UNION

    -> SELECT 'TOTAL',sum(if(c2='B1',C3,0)) ASB1,-> sum(if(c2='B2',C3,0)) ASB2,-> sum(if(c2='B3',C3,0)) ASB3,-> sum(if(c2='B4',C3,0)) AS B4,SUM(C3) FROMTX->;+-------+------+------+------+------+-------+

    | c1 | B1 | B2 | B3 | B4 | TOTAL |

    +-------+------+------+------+------+-------+

    | A1 | 9 | 2 | 1 | 11 | 23 |

    | A2 | 7 | 9 | 8 | 7 | 31 |

    | A3 | 4 | 8 | 8 | 8 | 28 |

    | A4 | 2 | 5 | 6 | 14 | 27 |

    | TOTAL | 22 | 24 | 23 | 40 | 109 |

    +-------+------+------+------+------+-------+

    5 rows in set (0.00sec)

    mysql>

    方法三:

    mysql> select ifnull(c1,'total'),-> sum(if(c2='B1',C3,0)) ASB1,-> sum(if(c2='B2',C3,0)) ASB2,-> sum(if(c2='B3',C3,0)) ASB3,-> sum(if(c2='B4',C3,0)) AS B4,SUM(C3) ASTOTAL-> fromtx-> group by C1 withrollup ;+--------------------+------+------+------+------+-------+

    | ifnull(c1,'total') | B1 | B2 | B3 | B4 | TOTAL |

    +--------------------+------+------+------+------+-------+

    | A1 | 9 | 2 | 1 | 11 | 23 |

    | A2 | 7 | 9 | 8 | 7 | 31 |

    | A3 | 4 | 8 | 8 | 8 | 28 |

    | A4 | 2 | 5 | 6 | 14 | 27 |

    | total | 22 | 24 | 23 | 40 | 109 |

    +--------------------+------+------+------+------+-------+

    5 rows in set (0.00sec)

    mysql>

    更多相关内容
  • 5.7.1 使用向导创建交叉表查询

    千次阅读 2021-08-11 09:20:36
    使用交叉表查询计算和重构数据,可以简化数据分析,交叉表查询计算数据的总和、平均值、计数或其他类型的总计值,并将它们分组。一组列在数据表左侧作为交叉表的行字段,另一组列在数据表的顶端作为交叉表的列字段。...

    示例ACCESS数据库下载链接:     

    https://download.csdn.net/download/ngbshzhn/20979935

        使用交叉表查询计算和重构数据,可以简化数据分析,交叉表查询计算数据的总和、平均值、计数或其他类型的总计值,并将它们分组。一组列在数据表左侧作为交叉表的行字段,另一组列在数据表的顶端作为交叉表的列字段。
        在实际应用中,交叉表查询用于解决在一对多的关系中,对“多"实现分组求和的问题。例如,在“成绩”表中,每个学生所有课程的成绩都是按顺序依次显示在一张表中,在实际正作中,常常需要以学号和姓名为行,以每门课程为列来显示每门课程的成绩和几门课程的总成绩,这种情况就需要使用交叉表查询来实现了。
        设计交叉表查询可以首先使用交叉表查询向导,以便快速生成一个基本的交叉查询对象,然后,再进入查询设计视图对交叉表查询对象进行修改。
        例:在“教学管理”数据库中列出学生每门课程的成绩和各门的总分,显示的信息包括学号、姓名、课程名和总评成绩。
        由于成绩表中,并不包含上述全部信息,这些信息来自三个表。交叉表查询向导不支持从多个表中选择字段,所以需要使用查询设计视图创建一个包含上述信息的查询。查询由“学生“、  ”课程’’表和“成绩“表组成。即从“学生”表中获取“姓名”字段,从“课程”表获取“课程名“字段以及从“成绩”表获取“学号”和“总评成绩”字段。
        操作步骤如下:
        (1)打开“教学管理”数据库,在“创建”选项卡上的“查询’’组中,单击“查询设计”按钮,打开“查询设计视图”窗口。
        (2)在打开的“显示表”对话框中,选择“学生”表、  “课程”表和“成绩”表,单击“添加”按钮,把它们添加到查询视图中的对象窗格中。
        (3)把“姓名”、  “学号’’、  “课程号"和“总评成绩”字段分别从各个表(或查询)中拖到查询设计网格中。
        (4)保存。

         以上4步是为了创建交叉表查询向导做准备工作。
        (5)在“创建”选项卡上的“查询”组中,单击“查询向导”按钮。
        (6)在打开的“新建查询”对话框中,选中“交叉表查询向导”,单击“确定”按钮。


        (7)在打开的“请指定哪个表或查询中含有交叉表查询结果所需的字段”对话框中,在“视图“区,选中“查询"。在数据源列表中,选择“查询1”。


        (8)在打开的“请确定用哪些字段的值作为行标题”对话框中,在“可用字段”窗格中,依次双击“学号““姓名¨,这两个字段被发送到“选定字段”的窗格中。

     

        (9)在打开的“请确定用哪个字段的值作为列标题’’对话框中,系统自动选择“课程 名”作为列标题,且“总计”放在计算位置处。

        (10)在打开的“请确定为每个列和行的交叉点计算出什么数字∞对话框中,在“函数”列中”总数“,然后单击“下一步”。


        (11)在打开的“请指定查询的名称”对话框中,在“请指定查询的名称"文本框中,输入“查询1_交叉表”,选中“修改查询”,然后单击“完成”。

        (12)这时打开查询设计视图,s把最后一列中的“字段’’,行单元格中的“总计”删除掉,然后运行。 

    展开全文
  • 使用交叉表查询,显示源于表中某个字段的汇总值,并将它们分组,其中一组列在数据表的左侧,另一组列在数据表的上部。行和列的交叉处可以对数据进行多种汇总计算,如:求和、平均值、记数、最大值、最小值等。使用...
  • 在MySQL中实现交叉表查询1(静态交叉表) 一、什么是交叉表 交叉表查询是将来源于某个表中的字段进行分组,一组列在交叉表左侧,一组列在交叉表上部,并在交叉表行与列交叉处显示表中某个字段的各种计算值。比如: ...

    在MySQL中实现交叉表查询1(静态交叉表)

    一、什么是交叉表

    交叉表查询是将来源于某个表中的字段进行分组,一组列在交叉表左侧,一组列在交叉表上部,并在交叉表行与列交叉处显示表中某个字段的各种计算值。比如:

    create table stu(
        s_id int auto_increment,
        name char(20) not null default '',
        gender char(5) not null default '',
        age int not null default 0,
        class char(10) not null default '',
        primary key(s_id)
    );
    
    insert into stu(name,gender,age,class) 
    values('张平','男',20,'物流1班'),('王刚','男',21,'物流1班'),('刘静','女',18,'物流1班'),
    ('张静静','女',21,'物流1班'),('刘涛','男',19,'物流1班'),('王晓刚','男',20,'营销2班'),
    ('王红','女',17,'营销2班'),('刘梅','女',19,'营销2班'),('张军','男',20,'营销2班'),
    ('张成','男',19,'营销2班'),('张晓媛','女',18,'营销2班'),('张华','男',21,'国贸1班'),
    ('王晓红','女',17,'国贸1班'),('张晓梅','女',19,'国贸1班'),('张涛','男',18,'国贸1班');
    select * from stu;
    

    根据class和gender两个字段进行分组查询,统计每个班级的男生和女生的平均分:

    select class,gender,avg(age) from stu group by class,gender;
    

    查询结果如下:
    在这里插入图片描述
    根据以上查询结果可以生成交叉表,其中class分组作为行,gender分组作为列,结果如下图所示:
    在这里插入图片描述
    使用交叉表查询来计算和重构数据,可以简化数据分析。交叉表查询计算数据的总和,平均值,计数及其他类型的统计,并将它们分组,一组列在数据表左侧作为交叉表的行字段,另一组列在数据表的顶端作为交叉表的列字段。

    交叉表分为静态交叉表和动态交叉表。其中静态交叉表中的列是固定的,因此相对容易实现;而动态交叉表中的列需要动态生成。

    二、静态交叉表的实现

    MySQL不提供直接创建交叉表的命令,如果需要创建交叉表可以采用以下步骤:

    step1 创建三张表,student(学生)、course(课程)、score(成绩),命令如下:

    create table student(
        s_id int primary key auto_increment comment '学生学号',
        s_name char(20) comment '学生姓名',
        age int comment '学生年龄'
    );
    
    create table course(
        c_id int primary key auto_increment comment '课程编号',
        c_name char(20) comment '课程名称',
        credits int comment '课程学分'
    );
    
    create table score(
        s_id int comment '学生学号',
        c_id int comment '课程编号',
        score int comment '成绩',
        primary key(s_id,c_id)
    );
    

    step2 为以上三张表插入必要的数据,命令如下:

    insert into student
    values(1001,'张平',20),(1002,'王刚',21),(1003,'张静静',19),
    (1004,'王涛',20),(1005,'王鹏飞',19);
    
    insert into course
    values(1,'数据库',4),(2,'数据结构',4),(3,'管理学',3),(4,'英语',4),(5,'电子商务',3);
    
    insert into score 
    values(1001,1,80),(1001,2,90),(1001,3,77),(1001,4,87),(1001,5,69),
    (1002,1,87),(1002,2,67),(1002,3,78),(1002,4,98),(1002,5,78),
    (1003,1,66),(1003,2,77),(1003,3,88),(1003,4,99),(1003,5,66),
    (1005,1,81),(1005,2,83),(1005,3,62),(1005,4,68),(1005,5,72),
    (1004,1,72),(1004,2,60),(1004,3,84),(1004,4,88),(1004,5,74);
    

    step3 创建交叉表,显示每个学生每一门课程的分数,其中表的左侧显示学生姓名,每一门课程生成一列,在学生姓名和课程交叉处显示某个学生某门课程的成绩,命令如下:

    select IFNULL(s_name,'总分') as '姓名',sum(if(c_name='数据库',score,0)) as '数据库',
    sum(if(c_name='数据结构',score,0)) as '数据结构',
    sum(if(c_name='管理学',score,0)) as '管理学',sum(if(c_name='英语',score,0)) as '英语',
    sum(if(c_name='电子商务',score,0)) as '电子商务',sum(score) as '总分'
    from (select s_name,c_name,score 
          from student s,course c,score sc where s.s_id=sc.s_id and c.c_id=sc.c_id) tmp
    group by s_name
    with rollup;
    

    说明:Mysql中的with rollup是用来在分组统计数据的基础上再进行统计汇总,即用来得到group by的汇总信息。查询结果如下图所示:
    在这里插入图片描述
    也可以用union生成汇总行,代码如下:

    select s_name,sum(if(c_name='数据库',score,0)) as '数据库',
    sum(if(c_name='数据结构',score,0)) as '数据结构',
    sum(if(c_name='管理学',score,0)) as '管理学',sum(if(c_name='英语',score,0)) as '英语',
    sum(if(c_name='电子商务',score,0)) as '电子商务',sum(score) as '总分'
    from (select s_name,c_name,score 
          from student s,course c,score sc where s.s_id=sc.s_id and c.c_id=sc.c_id) tmp
    group by s_name
    union
    select '总分',sum(if(c_id=1,score,0)),sum(if(c_id=2,score,0)),sum(if(c_id=3,score,0)),
    sum(if(c_id=4,score,0)),sum(if(c_id=5,score,0)),sum(score)
    from score;
    

    查询结果如下图所示:
    在这里插入图片描述
    也可以不使用子查询,语法格式如下:

    select IFNULL(s_name,'总分') as '姓名',sum(if(c_name='数据库',score,0)) as '数据库',
    sum(if(c_name='数据结构',score,0)) as '数据结构',
    sum(if(c_name='管理学',score,0)) as '管理学',sum(if(c_name='英语',score,0)) as '英语',
    sum(if(c_name='电子商务',score,0)) as '电子商务',sum(score) as '总分'
    from student s,course c,score sc where s.s_id=sc.s_id and c.c_id=sc.c_id
    group by s_name
    with rollup;
    

    展开全文
  • MySQL讲义第33讲——select 查询之静态...交叉表查询计算数据的总和,平均值,计数及其他类型的统计,并将它们分组,一组列在数据表左侧作为交叉表的行字段,另一组列在数据表的顶端作为交叉表的列字段。 一、数据准备

    MySQL讲义第 33 讲——select 查询之静态交叉表查询

    交叉表查询是将来源于某个表中的字段进行分组,一组列在交叉表左侧,一组列在交叉表上部,并在交叉表行与列交叉处显示表中某个字段的各种计算值。交叉表分为静态交叉表和动态交叉表,其中静态交叉表中的列是固定的,而动态交叉表中的列需要动态生成。

    使用交叉表查询来计算和重构数据,可以简化数据分析。交叉表查询计算数据的总和,平均值,计数及其他类型的统计,并将它们分组,一组列在数据表左侧作为交叉表的行字段,另一组列在数据表的顶端作为交叉表的列字段。

    一、数据准备

    有下面三张表,表结构和数据如下:

    mysql> select * from users;
    +---------+-----------+-------------+-----------+
    | user_id | user_name | phone       | addr      |
    +---------+-----------+-------------+-----------+
    | 2020101 | 张华      | 15937310588 | Xinxiang  |
    | 2020102 | 张毅德    | 13783730566 | Xinxiang  |
    | 2020103 | 刘选德    | 13602313277 | Beijing   |
    | 2020104 | 赵紫龙    | 13703713731 | Zhengzhou |
    | 2020105 | 关云常    | 13637312446 | Xinxiang  |
    | 2020106 | 刘蓓      | 15037130526 | Zhengzhou |
    | 2020107 | 刘晓璐    | 18237150566 | Zhengzhou |
    +---------+-----------+-------------+-----------+
    7 rows in set (0.00 sec)
    
    mysql> select * from goods;
    +----------+------------+---------+------------+
    | goods_id | goods_name | price   | categories |
    +----------+------------+---------+------------+
    |     1101 | 帽子       |   58.00 | 服装       |
    |     1102 | 裤子       |  150.00 | 服装       |
    |     1103 | 运动鞋     |  580.00 | 服装       |
    |     1104 | 西服       | 1508.00 | 服装       |
    |     2201 | 香蕉       |    2.58 | 水果       |
    |     2202 | 苹果       |    4.98 | 水果       |
    |     2203 | 葡萄       |    6.88 | 水果       |
    |     3301 | 笔记本     |   25.00 | 文具       |
    |     3302 | 钢笔       |   25.80 | 文具       |
    +----------+------------+---------+------------+
    9 rows in set (0.00 sec)
    
    mysql> select * from orders;
    +----------+---------+----------+----------+---------------------+
    | order_id | user_id | goods_id | quantity | order_date          |
    +----------+---------+----------+----------+---------------------+
    |        1 | 2020101 |     1101 |     1.00 | 2020-10-22 09:05:21 |
    |        2 | 2020101 |     1102 |     1.00 | 2020-10-20 00:00:00 |
    |        3 | 2020101 |     2201 |     2.50 | 2020-10-21 00:00:00 |
    |        4 | 2020101 |     2202 |     4.50 | 2020-10-21 00:00:00 |
    |        5 | 2020101 |     2203 |     2.50 | 2020-10-20 00:00:00 |
    |        6 | 2020101 |     3301 |     2.00 | 2020-10-20 00:00:00 |
    |        7 | 2020102 |     2201 |     3.50 | 2020-10-22 00:00:00 |
    |        8 | 2020102 |     2202 |     2.00 | 2020-10-22 00:00:00 |
    |        9 | 2020103 |     2202 |     2.00 | 2020-10-19 00:00:00 |
    |       10 | 2020103 |     2203 |     1.20 | 2020-10-19 00:00:00 |
    |       11 | 2020103 |     3302 |     1.00 | 2020-10-20 00:00:00 |
    |       12 | 2020103 |     1101 |     1.00 | 2020-10-19 00:00:00 |
    |       13 | 2020103 |     1102 |     1.00 | 2020-10-20 00:00:00 |
    |       14 | 2020104 |     1102 |     1.00 | 2020-10-20 00:00:00 |
    |       15 | 2020104 |     2201 |     1.80 | 2020-10-19 00:00:00 |
    |       16 | 2020104 |     2202 |     3.20 | 2020-10-19 00:00:00 |
    |       17 | 2020104 |     3302 |     1.00 | 2020-10-20 00:00:00 |
    |       18 | 2020105 |     1103 |     1.00 | 2020-10-21 00:00:00 |
    |       19 | 2020105 |     1104 |     1.00 | 2020-10-21 00:00:00 |
    |       20 | 2020105 |     2201 |     1.80 | 2020-10-22 00:00:00 |
    |       21 | 2020105 |     2202 |     3.20 | 2020-10-22 00:00:00 |
    |       22 | 2020105 |     2203 |     2.00 | 2020-10-21 00:00:00 |
    |       23 | 2020105 |     3302 |     1.00 | 2020-10-21 00:00:00 |
    |       24 | 2020106 |     1102 |     1.00 | 2020-10-22 00:00:00 |
    |       25 | 2020106 |     1103 |     1.00 | 2020-10-22 00:00:00 |
    |       26 | 2020106 |     1104 |     1.00 | 2020-10-22 00:00:00 |
    |       27 | 2020106 |     2201 |     2.00 | 2020-10-21 00:00:00 |
    |       28 | 2020106 |     2202 |     3.50 | 2020-10-21 00:00:00 |
    |       29 | 2020106 |     2203 |     5.50 | 2020-10-21 00:00:00 |
    +----------+---------+----------+----------+---------------------+
    29 rows in set (0.00 sec)
    

    二、交叉表的形式

    交叉表又称为行列转换,就是把下面的表1 转换为表2(交叉表)。

    select stu_name,c_name,score
    from stu s,course c,score sc where s.stu_id=sc.stu_id and c.c_id=sc.c_id;
    --################################  表1  #############################################
    +-----------+-----------------------+-------+
    | stu_name  | c_name                | score |
    +-----------+-----------------------+-------+
    | 王占峰    | 管理学原理            |    88 |
    | 王占峰    | 政治经济学            |    75 |
    | 王占峰    | 数据库系统原理        |    89 |
    | 王占峰    | 企业管理概论          |    95 |
    | 刘国强    | 管理学原理            |    65 |
    | 刘国强    | 政治经济学            |    79 |
    | 刘国强    | 数据库系统原理        |    72 |
    | 刘国强    | 企业管理概论          |    91 |
    | 王艳艳    | 管理学原理            |    84 |
    | 王艳艳    | 政治经济学            |    76 |
    | 王艳艳    | 数据库系统原理        |    68 |
    | 王艳艳    | 企业管理概论          |    92 |
    | 赵牡丹    | 管理学原理            |    64 |
    | 赵牡丹    | 政治经济学            |    75 |
    | 赵牡丹    | 数据库系统原理        |    81 |
    | 赵牡丹    | 企业管理概论          |    90 |
    +-----------+----------------------+-------+
    --################################  表2  #############################################
    +-----------+--------------+---------------+-------------------+------------------+--------+
    | stu_name  | 管理学原理    | 政治经济学    | 数据库系统原理      | 企业管理概论      | 总分   |
    +-----------+---------------+---------------+------------------+------------------+--------+
    | 刘国强    |            65 |            79 |                72 |               91 |    307 |
    | 王占峰    |            88 |            75 |                89 |               95 |    347 |
    | 王艳艳    |            84 |            76 |                68 |               92 |    320 |
    | 赵牡丹    |            64 |            75 |                81 |               90 |    310 |
    +-----------+---------------+---------------+-----------------------+--------------+--------+
    

    三、静态交叉表的实现

    生成交叉表的基本方法为:
    (1)按照交叉表的行字段进行分组(比如上例中的 stu_name)。
    (2)对要转换为列的行字段(比如上例中的 c_name)进行统计,在统计函数中使用 IF 函数对数据进行筛选,生成列字段。

    例如:

    1、根据 user、goods 和 orders 三张表查询每个用户每种类别的商品的消费金额

    第一步:根据 u.user_name 和 categories 两个字段分组,统计消费金额

    mysql> SELECT
        -> u.user_name as 用户姓名,
        -> categories as 消费类别,
        -> sum(o.quantity * g.price) as 总金额
        -> FROM
        -> users u JOIN orders o ON u.user_id = o.user_id 
        -> JOIN goods g ON o.goods_id = g.goods_id
        -> GROUP BY u.user_name,categories;
    +--------------+--------------+-----------+
    | 用户姓名     | 消费类别     | 总金额    |
    +--------------+--------------+-----------+
    | 关云常       | 文具         |   25.8000 |
    | 关云常       | 服装         | 2088.0000 |
    | 关云常       | 水果         |   34.3400 |
    | 刘蓓         | 服装         | 2238.0000 |
    | 刘蓓         | 水果         |   60.4300 |
    | 刘选德       | 文具         |   25.8000 |
    | 刘选德       | 服装         |  208.0000 |
    | 刘选德       | 水果         |   18.2160 |
    | 张华         | 文具         |   50.0000 |
    | 张华         | 服装         |  208.0000 |
    | 张华         | 水果         |   46.0600 |
    | 张毅德       | 水果         |   18.9900 |
    | 赵紫龙       | 文具         |   25.8000 |
    | 赵紫龙       | 服装         |  150.0000 |
    | 赵紫龙       | 水果         |   20.5800 |
    +--------------+--------------+-----------+
    15 rows in set (0.00 sec)
    

    第二步:对消费类别字段分别按文具、服装、水果为条件进行筛选,然后转换为列,代码如下:

    mysql> SELECT
        -> u.user_name as 用户,
        -> sum(if(categories = '服装',o.quantity * g.price,0)) as 服装,
        -> sum(if(categories = '文具',o.quantity * g.price,0)) as 文具,
        -> sum(if(categories = '水果',o.quantity * g.price,0)) as 水果,
        -> sum(o.quantity * g.price) as 总金额
        -> FROM
        -> users u JOIN orders o ON u.user_id = o.user_id 
        -> JOIN goods g ON o.goods_id = g.goods_id
        -> GROUP BY u.user_name;
    +-----------+-----------+---------+---------+-----------+
    | 用户      | 服装      | 文具    | 水果    | 总金额    |
    +-----------+-----------+---------+---------+-----------+
    | 关云常    | 2088.0000 | 25.8000 | 34.3400 | 2148.1400 |
    | 刘蓓      | 2238.0000 |  0.0000 | 60.4300 | 2298.4300 |
    | 刘选德    |  208.0000 | 25.8000 | 18.2160 |  252.0160 |
    | 张华      |  208.0000 | 50.0000 | 46.0600 |  304.0600 |
    | 张毅德    |    0.0000 |  0.0000 | 18.9900 |   18.9900 |
    | 赵紫龙    |  150.0000 | 25.8000 | 20.5800 |  196.3800 |
    +-----------+-----------+---------+---------+-----------+
    6 rows in set (0.00 sec)
    

    第三步:添加总计行

    mysql> SELECT
        -> IFNULL(u.user_name,'总计') as 用户,
        -> sum(if(categories = '服装',o.quantity * g.price,0)) as 服装,
        -> sum(if(categories = '文具',o.quantity * g.price,0)) as 文具,
        -> sum(if(categories = '水果',o.quantity * g.price,0)) as 水果,
        -> sum(o.quantity * g.price) as 总金额
        -> FROM
        -> users u JOIN orders o ON u.user_id = o.user_id 
        -> JOIN goods g ON o.goods_id = g.goods_id
        -> GROUP BY u.user_name
        -> WITH rollup;
    +-----------+-----------+----------+----------+-----------+
    | 用户      | 服装      | 文具     | 水果     | 总金额    |
    +-----------+-----------+----------+----------+-----------+
    | 关云常    | 2088.0000 |  25.8000 |  34.3400 | 2148.1400 |
    | 刘蓓      | 2238.0000 |   0.0000 |  60.4300 | 2298.4300 |
    | 刘选德    |  208.0000 |  25.8000 |  18.2160 |  252.0160 |
    | 张华      |  208.0000 |  50.0000 |  46.0600 |  304.0600 |
    | 张毅德    |    0.0000 |   0.0000 |  18.9900 |   18.9900 |
    | 赵紫龙    |  150.0000 |  25.8000 |  20.5800 |  196.3800 |
    | 总计      | 4892.0000 | 127.4000 | 198.6160 | 5218.0160 |
    +-----------+-----------+----------+----------+-----------+
    7 rows in set (0.00 sec)
    --说明:使用 IFNULL 函数把最后一行第一列的 NULL 替换成 '总计'。
    
    展开全文
  • MYSQL多表查询

    2021-01-19 06:09:03
    课堂练习以及知识点的加法含义:将两个按行合并在一起,用union将两个中的结果合并在一起案例select 课程号,课程名称from courseunionselect 课程号,课程名称from course1用union all可以保留重复行select ...
  • Mysql5 实现交叉表查询

    千次阅读 2015-12-15 17:04:17
    交叉表、行列转换和交叉查询经典 一、什么是交叉表交叉表”对象是一个网格,用来根据指定的条件返回值。数据显示在压缩行和列中。这种格式易于比较数据并辨别其趋势。它由三个元素组成:  行  列  摘要...
  • 然后新建一张学生(T_User),拥有两个字段:id、姓名,学生的id匹配A里的学生id,查询语句通过连接实现(不考虑内外或者交叉连接的问题了),代码如下: 1 select UName, 2 (select AVG(Grades) from ...
  • “学生”中有“学号”、“姓名”、“性别”和“入学成绩”等字段。执行如下SQL命令后的结果是( )。 Selectav“学生”中有“学号”、“姓名”、“性别”和“入学成绩”等字段。执行如下SQL命令后的结果是( )。 ...
  • 交叉表查询语法比较

    2009-01-12 09:05:14
    使用交叉表查询可以让汇总数据更容易阅读,交叉表查询计算总和、平均值或其他聚合函数 ,然后按照两组值对结果进行分组:一组值垂直分布在数据表的一侧,而另一组值水平分布在数据表的顶端。在运行交叉表查询时,...
  • 接触到交叉分组,如果使用Excel可以使用数据透视进行组合交叉分组,但在MySQL中如何创建呢?交叉分组表交叉分组是一种常用的分类汇总表格,可以显示多变量之间的关系。其表格形式的行和列标签为一个或多个...
  • - - 查询table1中的所有信息 条件查询(多列,多条件查询) 从学生中检索出2班性别为女性的学生的姓名和电话 Select Sname,Stel from t_student Where Sclass = 2 and Ssex =‘f’; 选择无重复行 distinct...
  • 交叉表、行列转换和交叉查询经典

    千次阅读 2016-11-23 09:44:45
    交叉表、行列转换和交叉查询经典 一、什么是交叉表交叉表”对象是一个网格,用来根据指定的条件返回值。数据显示在压缩行和列中。这种格式易于比较数据并辨别其趋势。它由三个元素组成: ...
  • 交叉表

    千次阅读 2012-11-16 00:15:08
    使用交叉表查询,显示源于表中某个字段的汇总值,并将它们分组,其中一组列在数据表的左侧,另一组列在数据表的上部。行和列的交叉处可以对数据进行多种汇总计算,如:求和、平均值、记数、最大值、最小值等。使用...
  • 对于普通的表格展示数据,相信大家都非常熟悉了,今天给大家介绍的是BI领域的分析利器-交叉表,这个在BI分析场景中使用占比最多的分析利器。通过交叉表对数据的承载和管理,用户可以一目了然地分析出各种场景指标...
  • MySQL多表查询

    2021-02-08 05:58:44
    =、>、 带in关键字的子查询 #查询平均年龄在25岁以上的部门名 select id, name from department where id in (select dep_id from employee group by dep_id having avg(age) > 25);#查看技术部门员工姓名 select ...
  • 聚合查询 ...对于统计总数、平均数这类计算,SQL提供了专门的聚合函数,使用聚合函数进行查询,就是聚合查询,它可以快速获得结果。 仍然以查询students一共有多少条记录为例,我们可以使用SQL内置的...
  • 表查询 使用单个select 语句从多个表格中取出相关的查询结果,多表连接通常是建立在有相互关系的父子表上; 多表查询时,一定要找到两个表中相互关联的字段,并且作为条件使用 交叉连接 第一个表格的所有行 乘以 第二...
  • 6. SQL 多表查询

    千次阅读 多人点赞 2019-11-24 11:39:17
    的联结 JOIN2.1 交叉联结 CROSS JOIN2.2 内联结 INNER JOIN2.3 左联结 LEFT JOIN2.4 右联结 RIGHT JOIN2.5 全联结 FULL JOIN2.6 小结3. 联结的应用3.1 案例13.2 案例23.3 案例34. case 表达式4....
  • sql多表查询

    2019-11-24 21:18:55
    1,sql多表查询分为:交叉连接,内连接,外连接 2,sql多表查询语法: 补充:select * from A cross join B;也是交叉连接,其与select * from A,B;查询结果是一样的。 3,sql多表查询解释 补充: A.内...
  • ReportStudio入门教程(三十) - 交叉表

    千次阅读 2014-04-16 13:55:27
    使用交叉表查询,显示源于表中某个字段的汇总值,并将它们分组,其中一组列在数据表的左侧,另一组列在数据表的上部。 行和列的交叉处可以对数据进行多种汇总计算,如:求和、平均值、记数、最大值、最小值等。 ...
  • 将字段中所有的数据聚合在一条中1、sum(字段名) :求总和2、avg(字段名) :求平均值3、max(字段名) :求最大值4、min(字段名) :求最小值5、count(字段名、*) :统计行数----2、按部门编号,查询平均薪水 ,并且平均...
  • pandas.read_sql_quuery(sql,con,index_col=None,coerce_float=True) read_sql是两者的综合,既能够读取数据库中的某一个,也能够实现查询操作。 pandas.read_sql(sql, con, index_col=None, coerce_float=True, ...
  • 【数据库实验】实验三、 多表查询

    千次阅读 2021-06-16 16:01:21
    实验三、 多表查询1.实验目的2.实验内容3. 实验步骤2) 在“SQL对象资源管理器”窗口中选择要操作的数据库,如“XSCJ”数据库。3) 在KC表中查询学分低于6的课程信息,并按课程号升序排列。4) 在XS_KC表中按学号...
  • 由于查询需求的复杂程度不同,可分为单表查询和多表查询。两种查询将在后面依次进行介绍。 在介绍单表查询和多表查询之前,首先我们提出select 语句查询基本格式为: SELECT 属性列表 FROM 表名 [WHERE 条件...
  • Oracle多表查询,四种连接方式

    万次阅读 多人点赞 2018-03-22 09:26:24
    一、聚合函数:(都会忽略null数据)  1、常用的有5种:将字段中所有的数据聚合在一条中  1、sum(字段名) :求总和  2、avg(字段名) :求平均值  3、max(字段名) :求最大值  4、min(字段名) :求...
  • 表查询(1)选择表中的若干列① 查询指定列② 查询全部列③ 查询经过计算的值❶ 算术表达式❷ 字符串常量及函数❸ 使用列别名改变查询结果的列标题(2)选择表中的若干元组(行)① 关键词DISTINCT去掉表中重复的...
  • MySQL进阶(分组查询,多连接查询

    千次阅读 多人点赞 2021-10-15 10:38:05
    接着上一期的数据库继续讲,上回我们说到简单的排序查询,接下来我们继续进阶,分组查询,多连接查询。在学习分组查询之前不得不提到函数这个概念。因为分组查询常常是跟着分组函数联系起来的。 一 常见函数...
  • MySQL语法,增改删查、多表查询

    千次阅读 2020-05-30 09:17:56
    目录数据库、数据表常用操作记录的增改删查where子句查询常用多表查询union 结果集合并子查询join 连接查询   mysql关键字不区分大小写   数据库、数据表常用操作 show databases; --查看所有的数据库 use...
  • sql指令和多关系 ### 1. 排序 (order by) 数据库的查询(排序:order by) 默认是按升序排列的,但是升序ASC最好写上,代码可读性! SELECT * FROM 表名 ORDER BY 排序字段 ASC(升序)|DESC(降序); 如果排序列的值...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 19,826
精华内容 7,930
关键字:

交叉表查询平均分