精华内容
下载资源
问答
  • Mysql5 实现交叉表查询

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

    交叉表、行列转换和交叉查询经典
    一、什么是交叉表

    “交叉表”对象是一个网格,用来根据指定的条件返回值。数据显示在压缩行和列中。这种格式易于比较数据并辨别其趋势。它由三个元素组成:
        行
        列
        摘要字段
        “交叉表”中的行沿水平方向延伸(从一侧到另一侧)。在上面的示例中,“手套”(Gloves) 是一行。
        “交叉表”中的列沿垂直方向延伸(上下)。在上面的示例中,“美国”(USA) 是一列。
        汇总字段位于行和列的交叉处。每个交叉处的值代表对既满足行条件又满足列条件的记录的汇总(求和、计数等)。在上面的示例中,“手套”和“美国”交叉处的值是四,这是在美国销售的手套的数量。

    “交叉表”还可以包括若干总计:

        每行的结尾是该行的总计。在上面的例子中,该总计代表一个产品在所有国家/地区的销售量。“手套”行结尾处的值是 8,这就是手套在所有国家/地区销售的总数。

        注意:    总计列可以出现在每一行的开头。
        每列的底部是该列的总计。在上面的例子中,该总计代表所有产品在一个国家/地区的销售量。“美国”一列底部的值是四,这是所有产品(手套、腰带和鞋子)在美国销售的总数。

        注意:    总计列可以出现在每一行的顶部。
        “总计”(Total) 列(产品总计)和“总计”(Total) 行(国家/地区总计)的交叉处是总计。在上面的例子中,“总计”列和“总计”行交叉处的值是 12,这是所有产品在所有国家/地区销售的总数。

    二、行列转换和交叉查询:

    1: 列转为行:
    eg1:
    假设有张学生成绩表(CJ)如下
    name      subject         result
    张三         语文             80
    张三         数学             90
    张三         物理             85
    李四         语文             85
    李四         数学             92
    李四         物理             82
    相关sql语句:

    Create table CJ(name char(10),subject char(10),result int);
    insert into CJ(name,subject,result) values('张三','语文',99);
    insert into CJ(name,subject,result) values('张三','数学',86);
    insert into CJ(name,subject,result) values('张三','英语',75);
    insert into CJ(name,subject,result) values('李四','语文',78);
    insert into CJ(name,subject,result) values('李四','数学',85);
    insert into CJ(name,subject,result) values('李四','英语',78)

    select * from CJ

    想变成如下的交叉表    
    姓名        语文        数学        物理
    张三         99          90           85
    李四         85          92           82

    我们首先来看一下如何建立静态的交叉表,也就是说列数固定的交叉表,这种情况其实只要一句简单的Select查询就可以搞定:

    select name,sum(case when a.subject='语文' then result else null end) as "语文",
    sum(case when a.subject='数学' then result else null end) as "数学",
    sum(case when a.subject='英语' then result else null end) as "英语"
    from CJ a
    group by name;

    当要增加“总计”列:"合计总分"时,如下表所示:

    姓名        合计总分 语文        数学        物理
    张三          260             99          90          85
    李四          241             85          92          82

    只需增加sum(a.result) as "合计总分",sql如下:
    select name,sum(a.result) as "合计总分",
    sum(case when a.subject='语文' then result else null end) as "语文",
    sum(case when a.subject='数学' then result else null end) as "数学",
    sum(case when a.subject='英语' then result else null end) as "英语"
    from CJ a
    group by name;

    其中利用了CASE语句判断,如果是相应的列,则取需要统计的cj数值,否则取NULL,然后再合计。
    其中有两个常见问题说明一下:
    a、用NULL而不用0是有道理的,假如用0,虽然求和函数SUM可以取到正确的数,但类似COUNT函数(取记录个数),结果就不对了,因为Null不算一条记录,而0要算,同理空字串("")也是这样,总之在这里应该用NULL,这样任何函数都没问题。

    b、假如在视图的设计界面保存以上的查询,则会报错“没有输出列”,从而无法保存,其实只要在查询前面加上一段:Create View ViewName AS ...,ViewName是你准备给查询起的名称,...就是我们的查询,然后运行一下,就可以生成视图了,对于其他一些设计器不支持的语法,也可以这样保存。

    以上查询作用也很大,对于很多情况,比如产品销售表中按照季度统计、按照月份统计等列头内容固定的情况,这样就行了,但往往大多数情况下列头内容是不固定的,象City,用户随时可能删除、添加一些城市,这种情况就是我们所说的动态交叉表,在SQLServer中我们可以用存储过程来解决。下面我们补充一些知识:

    相关子查询

    相关子查询和普通子查询区别在于:相关子查询引用了外部查询的列。这种引用外部查询的能力意味着相关子查询不能自己独立运行,其中对于外部查询引用会使会使其无法正常执行。因此相关子查询的执行顺序如下:
    1.首先执行一遍外部查询
    2.对于外部查询的每一行分别执行一遍子查询,而且每次执行子查询时候都会引用外部的当前行的值。使用子查询的结果来确定外部查询的结果集。
    举个例子;
    SELECT t1.type
    FROM titles t1
    GROUP BY t1.type
    HAVING MAX(t1.advance) >=ALL
    (SELECT 2 * AVG(t2.advance)
    FROM titles t2
    WHERE t1.type = t2.type)
    这个结果返回最高预付款超过给定组中平均预付款两倍的书籍类型。
    再举个例子:
    要求返回每一个编号的最大值(列出id,name,score)
    ID Name(编号) Score(分数)
    1          a                   88
    2          b                   76
    3          c                   66
    4          c                   90
    5          b                   77
    6          a                   56
    7          b                   77
    8          c                   67
    9          a                   44
    select * from t a where score=
    (select Max(Score) from t b       where a.name=b.name)
    再给一个排位的sql语句
    SELECT (
    SELECT count(*) 1 as dd
    FROM [Test ] as a where a.[F2]<b.[F2] ) AS ord,b.[F1], b.[F2]
    FROM [Test ] as b
    order by b.[F2];
    好了关于sql的相关子查询先讲到这里。

    SQLServer中局部变量赋值方法
    有两种:
    一种: set @变量名 = 值
    二种: select @变量名 = 值

    第二种可以从某个表中得到数据再赋值给变量
    例: 从用户信息表中查询中cid为 20 的用户姓名将他赋值给变量 name
    declare @name varchar(10) --用户名
    select @name=userName from userInfo where cid = 20
    print 'cid为20的用户姓名:' + @name

    递归的select变量

    递归的select变量是指使用select语句和子查询将一个变量与其自身拼接起来。语法形式如下:select @variable = @variable + table.column from table---见《sql server2000宝典》:P354,这是一种很优美的查询方法.从而将基础表中垂直的列数据改为水平方向的数据。这样就可以替代游标。动态的交叉表这样就代替了传统的游标。

    SQL语句解决方法:

    写法一:

    declare @sql varchar(4000)
    set @sql = 'select name'
    select @sql = @sql + ',sum(case subject when '''+subject+''' then result end) as '+subject
                 from (select distinct subject from CJ) as a
    select @sql = @sql+' from CJ group by name'
    exec(@sql)

    写法二:

    declare @sql varchar(4000)
    set @sql = 'select name'
    select @sql = @sql + ',sum(case subject when '''+subject+''' then result end) as '+subject
               +' from CJ group by subject
    select @sql = @sql+' from CJ group by name'
    exec(@sql)

    具体不同的多种写法参见本文相关链接文章中的其他例子

    在Access中还提供了TransForm来实现行列转换
    TRANSFORM count(Result) AS number
    SELECT 姓名
    FROM 学生成绩表
    GROUP BY 姓名
    PIVOT Subject;

    TransForm 用法如下:
    =========================================================
    TRANSFORM aggfunction
    selectstatement
    PIVOT pivotfield [IN (value1[, value2[, ...]])]

    TRANSFORM 语句可分为以下几个部分:

    部分                 描述
    aggfunction 在选定数据上运作的 SQL 合计函数。
    selectstatement       SELECT 语句。
    pivotfield 在查询的结果集中创建列标题时用的字段或表达式。
    value1, value2 用来创建列标题的固定值。

    说明
    使用交叉表查询来摘要数据时,从指定的字段或表达式中选定值作为列标题,
    这样,可以用比选定查询更紧凑的格式来观察数据。
    TRANSFORM 是可选的,但在使用它时,要作为       SQL 字符串中的第一个语句。
    它出现在 SELECT 语句(指定作为行标题的字段的)之前,还出现在 GROUP BY 子句
    (指定行分组的)之前。可以有选择地包含其它子句,例如 WHERE 子句,它指定附
    加的选择或排序条件。也可以将子查询当作谓词,特别是在叉表查询的 WHERE 子句中。

    pivotfield 返回的值被用作查询结果集中的列标题。
    例如,在交叉表查询中,将根据销售图表按销售月份创建 12 个列。
    可以限制 pivotfield 用列在可选的 IN 子句中的固定值(value1, value2)来创建标题。
    也可以用没有数据存在的固定值来创建附加的列。

    2. 列行转换
    暂时保留

    3. 行列转换--加合并
    有表A,
    id pid
    1        1
    1        2
    1        3
    2        1
    2        2
    3        1
    如何化成表B:
    id      pid
    1       1,2,3
    2       1,2
    3       1

    创建一个合并的函数
    create function fmerg(@id int)
    returns varchar(8000)
    as
    begin
    declare @str varchar(8000)
    set @str=''
    select @str=@str+','+cast(pid as varchar) from 表A where id=@id
    set @str=right(@str,len(@str)-1)
    return(@str)
    End
    go

    --调用自定义函数得到结果
    select distinct id,dbo.fmerg(id) from 表A

    Java代码   收藏代码
    1. # Host: localhost    Database: test  
    2. # ------------------------------------------------------  
    3. # Server version 5.0.45-community-nt-log  
    4.   
    5. #  
    6. # Table structure for table sale  
    7. #  
    8.   
    9. DROP TABLE IF EXISTS `sale`;  
    10. CREATE TABLE `sale` (  
    11. `id` int(10) unsigned NOT NULL auto_increment,  
    12. `year` int(11) NOT NULL,  
    13. `quarter` int(11) NOT NULL,  
    14. `amount` decimal(15,2) NOT NULL,  
    15. PRIMARY KEY (`id`)  
    16. ) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;  
    17.   
    18. #  
    19. # Dumping data for table sale  
    20. #  
    21.   
    22. /*!40101 SET NAMES latin1 */;  
    23.   
    24. INSERT INTO `sale` VALUES (1,2004,1,2328);  
    25. INSERT INTO `sale` VALUES (2,2004,2,3822);  
    26. INSERT INTO `sale` VALUES (3,2004,3,7071);  
    27. INSERT INTO `sale` VALUES (4,2004,4,8931);  
    28. INSERT INTO `sale` VALUES (5,2005,1,2633);  
    29. INSERT INTO `sale` VALUES (6,2005,2,3910);  
    30. INSERT INTO `sale` VALUES (7,2005,3,237193);  
    31. INSERT INTO `sale` VALUES (8,2005,4,567444);  
    32. INSERT INTO `sale` VALUES (9,2006,1,12313);  

    插入数据后结果为:
    id    year    quarter    amount
    1    2004    1    2328.00
    2    2004    2    3822.00
    3    2004    3    7071.00
    4    2004    4    8931.00
    5    2005    1    2633.00
    6    2005    2    3910.00
    7    2005    3    237193.00
    8    2005    4    567444.00
    9    2006    1    12313.00

    交叉表查询语句:
    select a.year, 1d, 2d, 3d, 4d from
    (select distinct year from sale) a left join
    (select year, amount 1d from sale where quarter=1 group by year) a1d on a.year = a1d.year
    left join (select year, amount 2d from sale where quarter=2 group by year) a2d on a2d.year=a.year
    left join (select year, amount 3d from sale where quarter=3 group by year) a3d on a3d.year=a.year
    left join (select year, amount 4d from sale where quarter=4 group by year) a4d on a4d.year=a.year
    该语句查询某年的四个季度的amount,以行显示,显示结果:

    year    1d    2d    3d    4d
    2004    2328.00    3822.00    7071.00    8931.00
    2005    2633.00    3910.00    237193.00    567444.00
    2006    12313.00    NULL    NULL    NULL

    实现定长列的查询(即quarter的最大取值为4,定长为4列).

    展开全文
  • 交叉表、行列转换和交叉查询经典

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

    一、什么是交叉表

    “交叉表”对象是一个网格,用来根据指定的条件返回值。数据显示在压缩行和列中。这种格式易于比较数据并辨别其趋势。它由三个元素组成:

    • 摘要字段
    • “交叉表”中的行沿水平方向延伸(从一侧到另一侧)。在上面的示例中,“手套”(Gloves) 是一行。
    • “交叉表”中的列沿垂直方向延伸(上下)。在上面的示例中,“美国”(USA) 是一列。
    • 汇总字段位于行和列的交叉处。每个交叉处的值代表对既满足行条件又满足列条件的记录的汇总(求和、计数等)。在上面的示例中,“手套”和“美国”交叉处的值是四,这是在美国销售的手套的数量。

    “交叉表”还可以包括若干总计:

    • 每行的结尾是该行的总计。在上面的例子中,该总计代表一个产品在所有国家/地区的销售量。“手套”行结尾处的值是 8,这就是手套在所有国家/地区销售的总数。

      注意:    总计列可以出现在每一行的开头。

    • 每列的底部是该列的总计。在上面的例子中,该总计代表所有产品在一个国家/地区的销售量。“美国”一列底部的值是四,这是所有产品(手套、腰带和鞋子)在美国销售的总数。

      注意:    总计列可以出现在每一行的顶部。

    • “总计”(Total) 列(产品总计)和“总计”(Total) 行(国家/地区总计)的交叉处是总计。在上面的例子中,“总计”列和“总计”行交叉处的值是 12,这是所有产品在所有国家/地区销售的总数。

    二、行列转换和交叉查询:

    1: 列转为行:
    eg1:

    假设有张学生成绩表(CJ)如下
    name      subject         result
    张三         语文             80
    张三         数学             90
    张三         物理             85
    李四         语文             85
    李四         数学             92
    李四         物理             82

    相关sql语句:

    Create table CJ(name char(10),subject char(10),result int);

    insert into CJ(name,subject,result) values('张三','语文',99);
    insert into CJ(name,subject,result) values('张三','数学',86);
    insert into CJ(name,subject,result) values('张三','英语',75);
    insert into CJ(name,subject,result) values('李四','语文',78);
    insert into CJ(name,subject,result) values('李四','数学',85);
    insert into CJ(name,subject,result) values('李四','英语',78)
    select * from CJ


    想变成如下的交叉表    
    姓名        语文        数学        物理
    张三         99          90           85
    李四         85          92           82

    我们首先来看一下如何建立静态的交叉表,也就是说列数固定的交叉表,这种情况其实只要一句简单的Select查询就可以搞定:

    select name,sum(case when a.subject='语文' then result else null end) as "语文",
                            sum(case when a.subject='数学' then result else null end) as "数学",
                            sum(case when a.subject='英语' then result else null end) as "英语" 
               from CJ a 
               group by name;

    当要增加“总计”列:"合计总分"时,如下表所示:

    姓名        合计总分 语文        数学        物理
    张三          260             99          90          85
    李四          241             85          92          82

    只需增加sum(a.result) as "合计总分",sql如下:

    select name,sum(a.result) as "合计总分",
                            sum(case when a.subject='语文' then result else null end) as "语文",
                            sum(case when a.subject='数学' then result else null end) as "数学",
                            sum(case when a.subject='英语' then result else null end) as "英语" 
               from CJ a 
               group by name;

     

    其中利用了CASE语句判断,如果是相应的列,则取需要统计的cj数值,否则取NULL,然后再合计。
    其中有两个常见问题说明一下:
    a、用NULL而不用0是有道理的,假如用0,虽然求和函数SUM可以取到正确的数,但类似COUNT函数(取记录个数),结果就不对了,因为Null不算一条记录,而0要算,同理空字串("")也是这样,总之在这里应该用NULL,这样任何函数都没问题。

    b、假如在视图的设计界面保存以上的查询,则会报错“没有输出列”,从而无法保存,其实只要在查询前面加上一段:Create View ViewName AS ...,ViewName是你准备给查询起的名称,...就是我们的查询,然后运行一下,就可以生成视图了,对于其他一些设计器不支持的语法,也可以这样保存。

    以上查询作用也很大,对于很多情况,比如产品销售表中按照季度统计、按照月份统计等列头内容固定的情况,这样就行了,但往往大多数情况下列头内容是不固定的,象City,用户随时可能删除、添加一些城市,这种情况就是我们所说的动态交叉表,在SQLServer中我们可以用存储过程来解决。下面我们补充一些知识:

    相关子查询

    相关子查询和普通子查询区别在于:相关子查询引用了外部查询的列。这种引用外部查询的能力意味着相关子查询不能自己独立运行,其中对于外部查询引用会使会使其无法正常执行。因此相关子查询的执行顺序如下:
    1.首先执行一遍外部查询
    2.对于外部查询的每一行分别执行一遍子查询,而且每次执行子查询时候都会引用外部的当前行的值。使用子查询的结果来确定外部查询的结果集。
    举个例子;
    SELECT t1.type
    FROM titles t1
    GROUP BY t1.type
    HAVING MAX(t1.advance) >=ALL
            (SELECT 2 * AVG(t2.advance)
            FROM titles t2
            WHERE t1.type = t2.type)
    这个结果返回最高预付款超过给定组中平均预付款两倍的书籍类型。
    再举个例子:
    要求返回每一个编号的最大值(列出id,name,score)
    ID Name(编号) Score(分数)
    1          a                   88
    2          b                   76
    3          c                   66
    4          c                   90
    5          b                   77
    6          a                   56
    7          b                   77
    8          c                   67
    9          a                   44

    select * from t a where score=
    (select Max(Score) from t b       where a.name=b.name) 
    再给一个排位的sql语句
    SELECT ( 
    SELECT count(*) 1 as dd 
    FROM [Test ] as a where a.[F2]<b.[F2] ) AS ord,b.[F1], b.[F2] 
    FROM [Test ] as b 
    order by b.[F2];
    好了关于sql的相关子查询先讲到这里。


    SQLServer中局部变量赋值方法

    有两种: 
    一种: set @变量名 = 值 
    二种: select @变量名 = 值

    第二种可以从某个表中得到数据再赋值给变量 
    例: 从用户信息表中查询中cid为 20 的用户姓名将他赋值给变量 name 
    declare @name varchar(10) --用户名 
    select @name=userName from userInfo where cid = 20 
    print 'cid为20的用户姓名:' + @name


    递归的select变量

    递归的select变量是指使用select语句和子查询将一个变量与其自身拼接起来。语法形式如下:select @variable = @variable + table.column from table---见《sql server2000宝典》:P354,这是一种很优美的查询方法.从而将基础表中垂直的列数据改为水平方向的数据。这样就可以替代游标。动态的交叉表这样就代替了传统的游标。

    SQL语句解决方法:

    写法一:

    declare @sql varchar(4000)
    set @sql = 'select name'
    select @sql = @sql + ',sum(case subject when '''+subject+''' then result end) as '+subject
                 from (select distinct subject from CJ) as a
    select @sql = @sql+' from CJ group by name'
    exec(@sql)

    写法二:

    declare @sql varchar(4000)
    set @sql = 'select name'
    select @sql = @sql + ',sum(case subject when '''+subject+''' then result end) as '+subject
               +' from CJ group by subject
    select @sql = @sql+' from CJ group by name'
    exec(@sql)

    具体不同的多种写法参见本文相关链接文章中的其他例子

    在Access中还提供了TransForm来实现行列转换
    TRANSFORM count(Result) AS number 
    SELECT 姓名 
    FROM 学生成绩表 
    GROUP BY 姓名 
    PIVOT Subject;

    TransForm 用法如下:
    =========================================================
    TRANSFORM aggfunction 
    selectstatement 
    PIVOT pivotfield [IN (value1[, value2[, ...]])] 

    TRANSFORM 语句可分为以下几个部分: 

    部分                 描述 
    aggfunction 在选定数据上运作的 SQL 合计函数。 
    selectstatement       SELECT 语句。 
    pivotfield 在查询的结果集中创建列标题时用的字段或表达式。 
    value1, value2 用来创建列标题的固定值。 

    说明 
    使用交叉表查询来摘要数据时,从指定的字段或表达式中选定值作为列标题, 
    这样,可以用比选定查询更紧凑的格式来观察数据。 
    TRANSFORM 是可选的,但在使用它时,要作为       SQL 字符串中的第一个语句。 
    它出现在 SELECT 语句(指定作为行标题的字段的)之前,还出现在 GROUP BY 子句 
    (指定行分组的)之前。可以有选择地包含其它子句,例如 WHERE 子句,它指定附 
    加的选择或排序条件。也可以将子查询当作谓词,特别是在叉表查询的 WHERE 子句中。 

    pivotfield 返回的值被用作查询结果集中的列标题。 
    例如,在交叉表查询中,将根据销售图表按销售月份创建 12 个列。 
    可以限制 pivotfield 用列在可选的 IN 子句中的固定值(value1, value2)来创建标题。 
    也可以用没有数据存在的固定值来创建附加的列。 

    2. 列行转换
    暂时保留

    3. 行列转换--加合并
    有表A,
    id pid
    1        1
    1        2
    1        3
    2        1
    2        2
    3        1

    如何化成表B:
    id      pid
    1       1,2,3
    2       1,2
    3       1


    创建一个合并的函数
    create function fmerg(@id int)
    returns varchar(8000)
    as
    begin
    declare @str varchar(8000)
    set @str=''
    select @str=@str+','+cast(pid as varchar) from 表A where id=@id
    set @str=right(@str,len(@str)-1)
    return(@str)
    End
    go

    --调用自定义函数得到结果
    select distinct id,dbo.fmerg(id) from 表A

     

    相关链接:

    把列变成行的sql语句: http://blog.csdn.net/liaoxiaohua1981/archive/2006/05/30/763721.aspx
    应用SQL交叉表实现行列转换: http://blog.csdn.net/sivee/archive/2007/05/06/1598039.aspx
    oracle 行列转换: http://blog.csdn.net/gogogo520/archive/2005/10/10/498779.aspx
    行列转换例子: http://blog.csdn.net/zsl5305256/archive/2006/12/05/1430422.aspx
    动态SQL的使用例子, 行列转换: http://blog.csdn.net/hertcloud/archive/2007/04/05/1552626.aspx
    SqlServer如何生成动态交叉表查询: http://dev.csdn.net/article/12/12618.shtm
    SQL语句精典收藏http://hi.baidu.com/suofang/blog/item/35de9d23af3e5945ad34de8a.html

    展开全文
  • mysql数据库连表查询几种方法

    千次阅读 2020-07-22 14:41:24
    1.首先介绍连接分类(内连接,外连接,交叉连接)和连接方法(如下): A)内连接:join,inner join B)外连接:left join,left outer join,right join,right outer join,union ...2.创建几 3. ...

    1.首先介绍表连接分类(内连接,外连接,交叉连接)和连接方法(如下):

    A)内连接:join,inner join

    B)外连接:left join,left outer join,right join,right outer join,union

    C)交叉连接:cross join

    2.内连接

    查找两个表中ID相同的数据,查询结果会拼成一个表格,输出两个表中id都相同

    select a.*,b.* from tableA  a, tableB b where a.id=b.userid

    或者使用如下语句

    select a.*,b.* from tableA  a inner join tableB b on a.id=b.userid

    3.外链接

    外链接包括左链接和右链接 left join ,right join; 以关键字left(right)为参照物,用其左(右)为主表,此时必须满足on后面的查询条件的同时并且输出主表中的所有数据,(即使该数据不对应从表中的数据)

    左外链接

    select a.*,b.* from tableA a left join tableB b on a.id=b.userid

    右外链接

    select a.*,b.* from tableA a right join table B on a.id=b.userid

    全外连接:

    该外连接在MySQL数据库中不支持:其作用就是不仅满足on后面的条件,还要将两个表中不匹配的数据输出

    3.交叉链接

    select a.*,b.* from tableA a cross join tableB b

    左表(“cross join”关键字左边的表)中的每一行与右表(“cross join”关键字右边的表)中的所有行组合,交叉联接的结果是一个笛卡尔积。此时的数据匹配种类过于多样化,一般来讲:该方法的作用对一些需要通过对应ID寻找数据库信息的需求十分渺小

     

     

     

     

    展开全文
  • 关于SQL链接查询几种方式

    千次阅读 2014-11-25 17:20:51
    一、概述   通过连接运算符可以实现多个表查询。连接是关系数据库模型的主要特点,也是它区别于...多连接查询是使用Sql的基本操作,但连接的方式多种,熟练使用这些连接方式能够简化Sql语句,提高数据

    本文转载自http://www.cnblogs.com/albert-struggle/archive/2011/08/29/2158137.html

    一、概述  

      通过连接运算符可以实现多个表查询。连接是关系数据库模型的主要特点,也是它区别于其它类型数据库管理系统的一个标志。多表连接查询是使用Sql的基本操作,但连接的方式却有多种,熟练使用这些连接方式能够简化Sql语句,提高数据库运行效率。

      在关系数据库管理系统中,表建立时各数据之间的关系不必确定,常把一个实体的所有信息存放在一个表中。当检索数据时,通过连接操作查询出存放在多个表中的不同实体的信息。连接操作给用户带来很大的灵活性,他们可以在任何时候增加新的数据类型。为不同实体创建新的表,然后通过连接进行查询。 

      连接可以在SELECT 语句的FROM子句或WHERE子句中建立,似是而非在FROM子句中指出连接时有助于将连接操作与WHERE子句中的搜索条件区分开来。所以,在Transact-SQL中推荐使用这种方法。 

    二、基本语法与方法
      SQL-92标准所定义的FROM子句的连接语法格式为: 

      FROM join_table join_type join_table 
    [ON (join_condition)]

       其中join_table指出参与连接操作的表名,连接可以对同一个表操作,也可以对多表操作,对同一个表操作的连接又称做自连接。 

      join_type 指出连接类型,可分为三种:内连接外连接交叉连接内连接(INNER JOIN)使用比较运算符进行表间某(些)列数据的比较操作,并列出这些表中与连接条件相匹配的数据行。根据所使用的比较方式不同。

      1、内连接又分为等值连接自然连接不等连接三种。 

      2、外连接分为左外连接(LEFT OUTER JOIN或LEFT JOIN)、右外连接(RIGHT OUTER JOIN或RIGHT JOIN)和全外连接(FULL OUTER JOIN或FULL JOIN)三种。与内连接不同的是,外连接不只列出与连接条件相匹配的行,而是列出左表(左外连接时)、右表(右外连接时)或两个表(全外连接时)中所有符合搜索条件的数据行。 

      3、交叉连接(CROSS JOIN)没有WHERE 子句,它返回连接表中所有数据行的笛卡尔积,其结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。 

      连接操作中的ON (join_condition) 子句指出连接条件,它由被连接表中的列和比较运算符、逻辑运算符等构成。 

      无论哪种连接都不能对text、ntext和image数据类型列进行直接连接,但可以对这三种列进行间接连接。例如:

      SELECT p1.pub_id,p2.pub_id,p1.pr_info 

      FROM pub_info AS p1 INNER JOIN pub_info AS p2

      ON DATALENGTH(p1.pr_info)=DATALENGTH(p2.pr_info)

      (一)内连接 

      内连接查询操作列出与连接条件匹配的数据行,它使用比较运算符比较被连接列的列值。内连接分三种: 

      1、等值连接:在连接条件中使用等于号(=)运算符比较被连接列的列值,其查询结果中列出被连接表中的所有列,包括其中的重复列。 

      2、不等连接: 在连接条件使用除等于运算符以外的其它比较运算符比较被连接的列的列值。这些运算符包括>、>=、<=、<、!>、!<和<>。 

      3、自然连接:在连接条件中使用等于(=)运算符比较被连接列的列值,但它使用选择列表指出查询结果集合中所包括的列,并删除连接表中的重复列。 

      例,下面使用等值连接列出authors和publishers表中位于同一城市的作者和出版社:

      SELECT * 

      FROM authors AS a INNER JOIN publishers AS p

      ON a.city=p.city

       SELECT a.*,p.pub_id,p.pub_name,p.country 

    FROM authors AS a INNER JOIN publishers AS p

    ON a.city=p.city

      又如使用自然连接,在选择列表中删除authors 和publishers 表中重复列(city和state):

    (二)外连接 

      内连接时,返回查询结果集合中的仅是符合查询条件( WHERE 搜索条件或 HAVING 条件)和连接条件的行。而采用外连接时,它返回到查询结果集合中的不仅包含符合连接条件的行,而且还包括左表(左外连接时)、右表(右外连接时)或两个边接表(全外连接)中的所有数据行。


      1、left join或left outer join(左外连接)

      左外连接 :(也叫左外连接,给出两个表的配匹行,并且以左边的表为准,如果左边表有而右边表没有的行,则在右边表的相应行选择的列显示为NULL,允许左边的基准表对应右边表多条满足条件的记录)左连接就是返回左边的匹配行,不考虑右边的表是否有相应的行

    select field1,field2 from table1 left join table2

      
    on field1=field2(基准字段,可以多个)

      
    where table1.field3=table2.field3

      2、right join或right outer join(右外连接)

      右连接:(也叫右外连接,给出两个表的配匹行,并且以右边的表为准,如果右边表有而左边表没有的行,则在右边表的相应行选择的列显示为NULL,允许右边的基准表对应左边表多条满足条件的记录)

      语法与做链接差不多! 

    (三)交叉连接 
      交叉连接不带WHERE 子句,它返回被连接的两个表所有数据行的笛卡尔积,返回到结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。 

      例,titles表中有6类图书,而publishers表中有8家出版社,则下列交叉连接检索到的记录数将等于6*8=48行。 

      SELECT type,pub_name 

      
    FROM titles CROSS JOIN publishers

      
    ORDER BY type
    展开全文
  • --mysql数据库的十种查询方式 -- (1)查询时起别名 SELECT id AS '编号',NAME AS '姓名',age AS '年龄'  FROM student; -- (2)查询时添加常量列 SELECT id AS '编号',NAME AS '姓名',age AS '年龄', '软件工程' ...
  • 数据库的几种连接查询

    千次阅读 2017-08-22 12:21:10
    1:连接查询  通过连接运算符可以实现多个表查询。连接是关系数据库模型的主要特点,也是它区别于其它类型 数据库管理系统的一个标志。  在关系数据库管理系统中,建立时各数据之间的...为不同实体创建新的
  • MYSQL外键约束多表查询外连接内连接自连接子查询索引 -------------缪林 自2014-10月总结,转载请尊重原创   字段值类型:date:日期,time,时间,datetime:日期时间,timestamp时间戳,自动刷新,用于记录修改时间 ...
  • Oracle 中实现数据透视几种方法

    千次阅读 多人点赞 2020-07-29 17:54:52
    介绍如何在 Oracle 数据库中实现类似于 Excel 的数据透视(Pivot Table),包括 CASE 表达式和 GROUP BY 分组相结合、Oracle 特定的 PIVOT 子句以及 MODEL 子句等方法。
  • 如何利用FastReport创建交叉报表?

    千次阅读 2014-12-11 13:24:29
    本文我们将创建一个交叉报表,用于显示员工四年中的工资。要创建交叉报表,我们需要使用到FastReport ...“crosstest”包含几下几种类型的数据:   在Delphi中创建一个新项目,将“TTable”,
  • 【MES】MES与PLC握手的几种方式

    万次阅读 多人点赞 2018-04-24 00:00:00
    在汽车制造行业,MES与设备层深入的集成,而PLC集成是最主流的形式。本文主要介绍MES与PLC集成时的几种握手方式及特点。1定期抓取数据某些设备的关键工艺参数(如变速...
  • 数据库设计范式第一范式(1NF)第二范式(2NF)第三范式(3NF)数据库实例外键数据库实例注意关联查询概述分类内连接(inner join)外连接交叉连接 多设计-关联查询 为什么需要多设计? 多设计的目的就是为了消除冗余的...
  • 今天看到一位博友的文章,觉得不错,转载一下... 3、没有创建计算列导致查询不优化。 4、内存不足 5、网络速度慢 6、查询出的数据量过大(可以采用多次查询,其他的方法降低数据量) 7、锁或者死锁(这也是查询...
  • 索引创建方式

    千次阅读 2018-07-03 19:34:22
    今天的工作中因一张数据量在100W上下(后续数据量还会不断增加)欲优化查询速度,决定给中添加索引。oracle的索引分为5:唯一索引,组合索引,反向键索引,位图索引,基于函数的索引创建索引:CREATE [unique] ...
  • 3、没有创建计算列导致查询不优化。  4、内存不足  5、网络速度慢  6、查询出的数据量过大(可以采用多次查询,其他的方法降低数据量)  7、锁或者死锁(这也是查询慢最常见的问题,是程序设计的缺陷)  8、...
  • 第一:直接利用to_datetime()2. 第二:Series.dt三、分组聚合操作1. 分组(1)groupby()方法2. 聚合(1)agg()方法① 使用方法1② 使用方法2③ 使用方法3④ 使用方法4(2)在分组的基础上进行聚合操作(3)...
  • 连接查询 通过连接运算符可以实现多个表查询。连接是关系数据库模型的主要特点,也是它区别于其它类型数据库管理系统的一个标志。 在关系数据库管理系统中,建立时各数据...为不同实体创建新的,尔后通过连接
  • DQL查询数据DQLSelect完整的语法测试用的sql脚本 DQL (Data Query LANGUAGE:数据查询语言) 所有的查询操作都用它 Select 简单的查询,复杂的查询它都能用 数据库中最核心的语言,最重要的语句 使用频率最高的语句 ...
  • MySQL-5.7创建及查看数据库

    千次阅读 2018-09-26 17:12:06
    转自:... 1.创建数据库的三语句 创建一个新; CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name (create_definition,...) [table_options] [partition_options] ...
  • 数据库中的连接方式详解

    万次阅读 2019-06-13 10:52:48
    连接查询 通过连接运算符可以实现多个表查询。连接是关系数据库模型的主要特点,也是它区别于其它类型数据库管理系统的一个标志。 在 关系数据库管理系统中,建立...为不同实体创建新的,尔后通过连接进行查询。...
  • 包括子查询、分组查询、聚合函数查询、模糊查询,本文是介绍一下关系型数据库几种高级数据查询SQL语法,包括虚拟、去重复查询、组合查询、连接查询。 去重复(DISTINCT) DISTINCT:用于返回唯一不同的值,主要是...
  • 玩转SELECT语句(二)之多联合查询

    千次阅读 热门讨论 2014-10-12 23:18:27
    两个关系模式:职工(职工号,姓名,性别,年龄,职务,工资,部门号)    部门(部门号,部门名称,经理名,地址,电话)   试用SQL语句检索后勤部的所有人的姓名。   SELECT 姓名 FROM 职工,部门...
  • C语言中的指针和内存泄漏几种情况

    万次阅读 多人点赞 2016-12-14 16:30:19
    C语言中的指针和内存泄漏几种情况
  • 知识点:数据库的相关概念、创建数据库的方法、设计数据库、向数据库中插入数据、建立不同数据库之间的关系、删除数据库。 1、数据相关的一些概念 1.1 数据库里的数据是如何保存的? 数据库...
  • Mysql多表查询效率的研究(一)

    万次阅读 2017-08-18 21:44:10
    Mysql多表查询效率的研究(一)本文探究了mysql InnoDB引擎在多表查询的应用场景下,使用子、内连接和左联接运行速度的差别,并且比较了索引使用与否对查询效率的影响。 第一部分简略地概括了索引、子表查询、...
  • 浅谈几种主流数控机床的数据采集技术

    千次阅读 多人点赞 2020-03-19 21:08:46
    马上即将毕业了,就自己在学校做的项目中包含有几种主流数控系统需要进行数据采集,总结一下之前的一些经验和开发工作,也希望后面再做相关技术研究的时候少踩点坑,同时也算是一个阶段的总结吧,毕竟马上要从事一个...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 46,417
精华内容 18,566
关键字:

交叉表查询创建有几种方式