精华内容
下载资源
问答
  • 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

    展开全文
  • 用来连接两个表的条件称为连接条件或连接谓词,其一般格式如下: [&lt;表名1&gt;.]&lt;列名1&gt;&lt;比较运算符&gt;[&lt;表名2&gt;.]&lt;列名2&gt; 【提示】 当比较...

    1.连接查询

    内连接

    内连接时用比较运算符比较要连接列的值的连接。

    1.等值查询

    用来连接两个表的条件称为连接条件或连接谓词,其一般格式如下:

    [<表名1>.]<列名1><比较运算符>[<表名2>.]<列名2>

    【提示】

    当比较运算符为“=”时,称为等值连接。如下:

    [<表名1>.]<列名1>BETWEEN[<表名2>.]<列名2>AND[<表名2>.]<列名3>

    如果是按照两个表中的相同属性进行等值连接,且目标列中去掉了重复的属性列,但保留了所有不重复的属性列,则称之为自然连接。

    2.非等值连接

    使用>、<、>=、<=等运算符作为连接条件的连接称为非等值连接。但在实际应用中很少使用非等值连接,并且通常非等值连接只有与自身连接同时使用才有意义。

    3.自身连接

    连接操作不仅可以在两个表之间进行,也可以是一个表与其自己进行连接,这种连接称为表的自身连接。自身连接一般很少用于查询数据,主要用于INSERT、UPDATE语句中对一个表中满足特定条件的行进行操作的情况。

    外连接

    在通常的连接操作中,只有满足连接条件的记录才能作为结果输出。

    1.左外连接

    左外连接包括第一个命名表(“左表”,出现在JOIN子句的最左边)中的所有行,但不包括右表中的不满足条件的行。

    2.右外连接

    右外连接包括第二个命名表(“右表”,出现在JOIN子句的最右边)中的所有行,但不包括左表中不满足条件的行。

    3.完整外部连接

    完整外部连接将包括所有连接表中的所有行,不论它们是否匹配。

    交叉连接

    连接运算中还有一种特殊情况,即卡氏积连接,卡氏积是不带连接谓词的连接。两个表的卡氏积即是两表中记录的交叉乘积,即其中以表中的每一记录都要与另一个表中的每一记录做拼接,因此结果表往往很大。

    2.子查询

    在SQL语言中,一个SELECT-FROM-WHERE语句称为一个查询块。将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语中的查询称为嵌套查询或子查询。

    子查询类型

    1.使用IN或NOT IN的子查询

    带有IN谓词的子查询是指父查询与子查询之间用IN进行连接,判断某个属性列指是否在子查询的结果中。由于在嵌套查询中,子查询的结果往往是一个集合,所以谓词IN是嵌套查询中最经常使用的谓词。

    【提示】

    • 实现同一个查询可以采用多种方法,当然不同过的方法其执行效率可能会有差别,甚至会差别很大。数据库用户可以根据自己的需要进行合理的选择。
    • 连接总是可以表示为子查询,子查询经常(但不总是)可以表示为连接。
    • 在一些必须检查存在性的情况中,使用连接会产生更好的性能。

    2.使用比较运算符的子查询

    带有比较运算符的子查询是指父查询与子查询之间用比较运算符进行连接。当用户能确切知道内层查询放回的是单值时,可以用>、<、=、>=、<=、!=或<>等比较运算符。单值情况下使用=,多值情况下使用IN或NOT IN谓词。

    3.使用ANY或ALL的子查询

    子查询返回单值时可以用比较运算符,而使用ANY或ALL谓词时则必须同时使用比较运算符,其含义如下表:

    带有ANY或ALL谓词的相关连词
    连词 含义
    >ANY 大于子查询结果中的某个值(大于最小)
    <ANY 小于子查询结果中的某个值(小于最大)
    >=ANY 大于等于子查询结果中的某个值
    <=ANY 小于等于子查询结果中的某个值
    =ANY 等于子查询结果中的某个值
    !=ANY或<>ANY 不等于子查询结果中的某个值
    >ALL 大于子查询结果中的所有值(大于最大的)
    <ALL 小于子查询结果中的所有值(小于最小的)
    >=ALL 大于等于子查询结果中的所有值
    <=ALL 小于等于子查询结果中的所有值
    =ALL 等于子查询结果中的所有值(通常没有实际意义)
    !=ALL或<>ALL 不等于子查询结果中的任何一个值

    【提示】

    • 事实上,用聚合函数实现子查询通常比直接用ANY或ALL查询效率要高
    • 如果将上例中的ALL改为ANY,即只需要比最大的小即可。反过来,如果是大于ANY,则只需要大于最小的即可。

    4.使用EXISTS的子查询

    EXISTS代表存在量词“彐”。带有EXISTS谓词的子查询不返回任何实际数据,它只产生逻辑真值“True”或逻辑假值“False”。

    【提示】

    • 使用存在量词EXISTS后,若内层查询结果非空,则外层的WHERE子句返回真值,否则返回假值。
    • 有EXISTS引出的子查询,其目标列表达式通常都用“*”,因为带EXISTS的子查询只返回真值或假值,给出列名也没有实际意义。
    • 这类查询与前面的不相关子查询有一个明显区别,即子查询的查询条件依赖于外层父查询的某个属性值,我们称这类查询为相关子查询。

    6.INSERT语句中的子查询

    插入子查询结果的INSERT语句的基本语句格式如下所示:

    INSERT
    INTO<表名>[(属性列1)[,<属性列2>...]]
    子查询

    其功能是一次将子查询的结果全部插入到指定的表中。

    【提示】

    • 目标子句中的列必须与被插入表中所指定的被插入列一一对应,名称可以不同,但类型必须一致。
    • 必须首先建立好目标表。

    7.UPDATE语句中的子查询

    子查询也可以嵌套在UPDATE语句中,用于构造执行修改操作的条件。

    8.子查询规则

    子查询也是使用SELECT语句组成的,所以在使用SELECT语句时应注意的问题也同样适用于子查询。同时子查询还受一下条件的限制:

    (1)通过比较运算符引入的子查询的选择列表只能包括一个表达式或列名称。

    (2)如果外部查询的WHERE子句包括某个列名,则该字句必须与子查询选择列表中的该列兼容。

    (3)子查询的选择列表中不允许出现ntext、text和image数据类型。

    (4)无修改的比较运算符引入的子查询不能包括GROUP BY和HAVING子句。

    (5)包括GROUP BY的子查询不能使用DISTINCT关键字。

    (6)不能指定COMPUTE和INTO子句。

    (7)只有同时指定了TOP,才可以指定ORDER BY。

    (8)由子查询创建的视图不能更新。

    (9)通过EXISTS引入的子查询的选择列表由星号(*)组成,而不使用单个列名。

    (10)当=、!=、<、<=、>或>=用在主查询中时,ORDER BY子句和GROUP BY子句不能用在内层查询中,因为内层查询返回一个以上的值不可被外层查询处理。

    联合查询

    每一个SELECT语句都能获得一个或一组记录。若要把多个SELECT语句的结果合并为一个结果,则可用集合操作来完成。集合操作主要包括并操作UNION、交操作INTERSECT和差操作MINUS。

    使用UNION将多个查询结果合并起来,形成一个完整的查询结果时,系统会自动去掉重复的记录。默认情况下,使用UNION运算符的结果集会从所联合的查询中删除重复行,而如果使用了ALL子句,则重复行也会显示出来。

    【提示】

    • 参加UNION在操作的各数据项(字段名、算数表达式、聚合函数)数目必须相同。
    • 对应项的数据类型必须相同,或者可以进行显式或隐式转换。
    • 各语句中对应的结果集列出现的顺序必须相同。

    交叉表查询

    1.PIVOT

    PIVOT通过将表达式某一列中的唯一值转换为输出中的多个列来旋转表值表达式,并在必要时对最终输出中所需的任何其余列值执行聚合。

    PIVOT指定在FROM子句中,其基本语句格式如下:

    SELECT<不旋踵的列>,
        [第1个旋转列]AS<别名>,
        [第2个旋转列]AS<别名>,
        ...
        [最后一个旋转列]AS<别名>,
    FROM
        (<SELECT 生成的数据结果集>)
        AS<查询结果集的别名>
    PIVOT
    (
        <聚合函数>(<聚合列>)
    FOR
    [<被转换为表头的列>]
      IN([第1个旋转列],[第2个旋转列],
      ...[最后一个旋转列])
    )AS<旋转表的别名>
    <可选的ORDER BY子句>;

    【提示】

    • PIVOT和UNPIVOT运算符需要SQL Server 2005以上版本才可以支持。
    • 数据库的兼容级别必须在90以上,可以使用EXEC sp_dbcmptlevel修改数据库兼容级别。

    2.UNPIVOT

    UNPIVOT与PIVOT执行相反的操作,将表值表达式的列转换为列值。但UNPIVOT不完全是PIVOT的逆操作。PIVOT会执行一次聚合,从而将多个可能的行合并为输出中的单个行。而UNPIVOT不会重现原始表值表达式的结果,因为行已经被合并了。另外,UNPIVOT的输入中的空值不会显示在输出中,而在执行PIVOT操作之前输入中可能有原始的空值。

    UNPIVOT指定在FROM子句中,其基本语句格式如下:

    SELECT<不旋转的列>,<旋转后的列名>,<待旋转列名下的列值列名>
    FROM
        (<SELECT 生成的数据结果集>)
        AS<查询结果集的别名>
    UNPIVOT
    (
        <待旋转列名下的列值列名>
    FOR
    [<旋转后的列名>]
        IN([第1个待旋转列的列名],[第2个待旋转列的列名],
        ...[最后一个待旋转列的列名])
    )AS<旋转表的别名>
    <可选的ORDER BY子句>;
    

     

    展开全文
  • 交叉表

    2017-01-05 21:53:32
     “交叉表”对象是一个网格,用来根据指定的条件返回值。数据显示在压缩行和列中。这种格式易于比较数据并辨别其趋势。它由三个元素组成: 行列摘要字段交叉表”中的行沿水平方向延伸(从一侧到另一...
    今天有人问我什么是交叉表,汗!一时还不好解释,就上网搜了一下,找到了一些资料:

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

    • 摘要字段

    什么是交叉表

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

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

    什么是交叉表

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

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

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

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

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

    千次阅读 2006-06-13 16:40:00
    sql中没有类似ACCESS的标准交叉表处理功能. 使用CASE和GROUP子句才是正确的选择(sql联机帮助也是这样说的,官方推荐) 动态列的问题,可以通过动态sql语句实现. --参考: if exists (select * from dbo.sysobjects ...
  • SqlServer如何生成动态交叉表查询

    千次阅读 2004-07-27 14:03:00
    SqlServer如何生成动态交叉表查询 fuxc(原作) 关键字 交叉表 SQL 存储过程 VB+MS SqlServer,是我们目前开发数据库应用系统最常用的模式,翻翻以前的老帖子,有一些SqlServer的问题经常被提出来,但正确解答甚...
  • 首先你要知道什么是交叉表,又是在什么情况下需要交叉表交叉表”对象是一个网格,用来根据指定的条件返回值。 数据显示在压缩行和列中。这种格式易于比较数据并辨别其趋势。它由三个元素组成: 行 列 ...
  • 什么是交叉表

    2016-05-04 10:21:40
    交叉表”对象是一个网格,用来根据指定的条件返回值。数据显示在压缩行和列中。这种格式易于比较数据并辨别其趋势。它由三个元素组成: 行 列 摘要字段交叉表”中的行沿水平方向延伸(从一侧到另一侧)。...
  • /* ...3.确定交叉点:两个表中哪些数据时相同的,即确定判断的条件 */ --语法: --查的字段:select... --从那几个表中查:from 1 as 别名1 left(或 inner、right) join 2 as 别名2 on 交叉条件
  • Qlikview 处理交叉表数据 数据来源于crossTable的时候,如何将数据做...Month, 表示将要新加的字段的列明,Orders 为明细列字段名,参数2表示,Load 指定的前2个字段是分组字段; 即表字段列表为 S...
  • A、B两张,通过关联字段复制B中数据到A中 update A a set a.A2 = (select b.B2 from B b where b.B1=a.A1) where exists (select 1 from B where B.B1=a.A1) 写成 update A a set a.A2 = (select b.B2 from B...
  • 生成交叉表的SQL语句

    2012-09-24 08:35:00
    /*--生成交叉表的简单通用存储过程 根据指定的表名,纵横字段,统计字段,自动生成交叉表 并可根据需要生成纵横两方向的合计 注意,横向字段数目如果大于纵向字段数目,将自动交换纵横字段 如果不要此功能,则去掉交换...
  • JasperReport报表开发之转置交叉表

    千次阅读 2015-07-24 09:05:35
    使用Jasper或BIRT等报表工具时,常会碰到一些非常规的统计,用报表工具本身或SQL都难以处理,比如源数据不符合交叉表的要求,需要转置后再呈现。集算器具有结构化强计算引擎,集成简单,可以协助报表工具方便地实现...
  • (转)什么是交叉表

    2011-09-19 22:49:45
    使用交叉表查询,显示源于表中某个字段的汇总值,并将它们分组,其中一组列在数据表的左侧,另一组列在数据表的上部。行和列的交叉处可以对数据进行多种汇总计算,如:求和、平均值、记数、最大值、最小值等。使用...
  • 一、 什么是透视表? 二、为什么要使用pivot_table? 、pivot_table api认识 四、如何使用pivot_table? 五、pivot_table vs. groupby 六、轴转换(透视功能)unstack 七、交叉表crosstab 八、小结
  • - - 查询table1中的所有信息 条件查询(多列,多条件查询) 从学生中检索出2班性别为女性的学生的姓名和电话 Select Sname,Stel from t_student Where Sclass = 2 and Ssex =‘f’; 选择无重复行 distinct...
  • 比如查询购物车 cart 中 good_ids 字段中是否存在想要查询的商品good_id 例如: good_ids 有 1,2,3,22,334 商品id, 想查询 3是否在该字段里面 $good_id = 3; $condition[] = ['exp', "instr(CONCAT( ',', ...
  • SQL动态交叉表

    千次阅读 2008-06-15 21:21:00
    思路是: 首先检索列头信息,形成一游标,然后遍历游标,将上面静态交叉表实现过程中使用Case语句判断的内容用游标里的值替代,形成一条新的Sql查询语句,然后执行并返回结果。下面是一通用的实现动态交叉表的...
  • ReportStudio入门教程(十) - 交叉表

    千次阅读 2014-04-16 13:55:27
    使用交叉表查询,显示源于表中某个字段的汇总值,并将它们分组,其中一组列在数据表的左侧,另一组列在数据表的上部。 行和列的交叉处可以对数据进行多种汇总计算,如:求和、平均值、记数、最大值、最小值等。 ...
  • setANSI_NULLSONsetQUOTED_IDENTIFIERONgo/**//*--生成交叉表的简单通用存储过程根据指定的表名,纵横字段,统计字段,自动生成交叉表并可根据需要生成纵横两方向的合计注意,横向字段数目如果大于纵向字段数目,将自动...
  • ACCESS交叉表(行列互换)

    千次阅读 2014-06-19 11:18:25
    用于创建交叉表查询?(交叉表查询:这种查询用于对记录计算总计、平均值、计数或其他类型总计,然后按照两类信息对结果进行分组:一组信息分布在数据表的左侧,另一组分布在数据表的顶端。)。 语法 TRANSFORM ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 28,361
精华内容 11,344
关键字:

交叉表查询需要指定3个字段