精华内容
下载资源
问答
  • 我所了解的创建动态交叉表有两种方法,如下图,有这样一张销售表 ,我想统计个人的销售业绩,如图二表示出来, 创建销售表 CREATE TABLE [dbo].[销售]( [ID] [int] NOT NULL, [员工姓名] [nvarchar](25) NULL, ...

    我所了解的创建动态交叉表有两种方法,如下图,有这样一张销售表 ,我想统计个人的销售业绩,如图二表示出来,

    创建销售表

    CREATE TABLE [dbo].[销售](
    	[ID] [int] NOT NULL,
    	[员工姓名] [nvarchar](25) NULL,
    	[所在部门] [nvarchar](15) NULL,
    	[销售业绩] [int] NULL
    ) ON [PRIMARY]

     图一,图二

    方法一:使用游标创建动态交叉表

    CREATE procedure Corss   
    @strTabName as varchar(50) = '销售',  
    @strCol as varchar(50) = '所在部门',  
    @strGroup as varchar(50) = '员工姓名',--分组字段  
    @strNumber as varchar(50) = '销售业绩', --被统计的字段  
    @strSum as varchar(10) = 'Sum' --运算方式  
    AS  
    DECLARE @strSql as varchar(1000), @strTmpCol as varchar(100)  
    EXECUTE ('DECLARE corss_cursor CURSOR FOR SELECT DISTINCT ' + @strCol + ' from ' + @strTabName + ' for read only ') --生成游标  
    begin  
    SET nocount ON   
    SET @strsql ='select ' + @strGroup + ', ' + @strSum + '(' + @strNumber + ') AS [' + @strNumber + ']' --查询的前半段  
    OPEN corss_cursor  
    while (0=0)  
    BEGIN  
    FETCH NEXT FROM corss_cursor --遍历游标,将列头信息放入变量@strTmpCol  
    INTO @strTmpCol  
    if (@@fetch_status<>0) break  
    SET @strsql = @strsql + ', ' + @strSum + '(CASE ' + @strCol + ' WHEN ''' + @strTmpCol + ''' THEN ' + @strNumber + ' ELSE Null END) AS ['  + @strTmpCol +  ']' --构造查询  
    END  
    SET @strsql = @strsql + ' from ' + @strTabname + ' group by ' + @strGroup --查询结尾  
    EXECUTE(@strsql) --执行  
    IF @@error <>0 RETURN @@error --如果出错,返回错误代码  
    CLOSE corss_cursor   
    DEALLOCATE corss_cursor RETURN 0 --释放游标,返回0表示成功  
    end

    输出动态交叉表

    
    DECLARE @RC int
    DECLARE @strTabName varchar(50)
    DECLARE @strCol varchar(50)
    DECLARE @strGroup varchar(50)
    DECLARE @strNumber varchar(50)
    DECLARE @strSum varchar(10)
    EXEC @RC = [db_Chapter5].[dbo].[Corss] DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT
    DECLARE @PrnLine nvarchar(4000)
    PRINT '存储过程: db_Chapter5.dbo.Corss'
    SELECT @PrnLine = '	返回代码 = ' + CONVERT(nvarchar, @RC)
    PRINT @PrnLine

    二:使用递归的select变量,创建动态交叉表

    
    DECLARE @strSql as varchar(1000) 
    
     select @strSql=isnull(@strSql, '')+char(13)+'   ,sum(case when [所在部门] = '''+[所在部门]+''' then [销售业绩] else null end) as ['+[所在部门]+']' 
      from (select distinct [所在部门] from 销售) a  
    SET @strsql ='select [员工姓名],sum([销售业绩]) as [ 销售业绩]  ' + @strSql + ' from 销售 group by [员工姓名]'
    
    exec(@strsql)

    个人更喜欢使用方法二,执行速度相比较游标很快,而且所需的代码量也少。

    展开全文
  • 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列).

    展开全文
  • SQL实现交叉表方法

    千次阅读 2008-01-17 16:23:00
    交叉一般来讲是分组统计的一种,形式更复杂,显示更清淅,但数据库本身并没有提供实现交叉表的功能,自己创建交叉表不仅要对过程、游标、临时表、动态SQL等非常熟悉,而且思路也要清淅,本例以PUBS.DBO.SALES表的...
    交叉一般来讲是分组统计的一种,形式更复杂,显示更清淅,但数据库本身并没有提供实现交叉表的功能,自己创建交叉表不仅要对过程、游标、临时表、动态SQL等非常熟悉,而且思路也要清淅,本例以PUBS.DBO.SALES表的数据做样本:
    CREATE PROCEDURE UP_TEST(
    @T1 VARCHAR(30),@T2 VARCHAR(30),
    @T3 VARCHAR(30),@T4 VARCHAR(30)) AS
    --T1 表名,T2,T3是交叉表的两上分类字段,T4是汇总字段
    --T2是行字段,T3列字段
    BEGIN
    DECLARE @SQL VARCHAR(7999),@FIELD VARCHAR(30)
    SELECT @SQL='SELECT DISTINCT '+@T3+' FROM '+@T1
    CREATE TABLE #FIELD(FIELD VARCHAR(30))
    --将列字段提取到临时表#FIELD中
    INSERT INTO #FIELD EXEC(@SQL)
    SELECT @SQL='CREATE TABLE CROSS_TEST('+@T2+' VARCHAR(30),'
    DECLARE CUR_FIELD CURSOR LOCAL FOR SELECT * FROM #FIELD
    OPEN CUR_FIELD
    FETCH CUR_FIELD INTO @FIELD
    WHILE @@FETCH_STATUS=0 BEGIN
    SELECT @FIELD='['+@FIELD+']'
    SELECT @SQL=@SQL+@FIELD+' DECIMAL(8,2) DEFAULT 0,'
    FETCH CUR_FIELD INTO @FIELD
    END
    SELECT @SQL=LEFT(@SQL,LEN(@SQL)-1)+')'
    --创建临时交叉表CROSS_TEST
    EXEC(@SQL)
    SELECT @SQL='INSERT INTO CROSS_TEST('+@T2+') SELECT DISTINCT '+@T2+' FROM '+@T1
    --将行数据存入交叉表#CROSS_TEST
    EXEC(@SQL)
    --创建分组数据表TEMP
    SELECT @SQL='CREATE TABLE TEMP('+@T2+' VARCHAR(30),'+@T3+' VARCHAR(30),'+@T4+' DECIMAL(8,2))'
    EXEC(@SQL)
    --将交叉汇总数据放入交叉表
    SELECT @SQL='SELECT '+@T2+','+@T3+', SUM(QTY) QTY FROM '+@T1 +' GROUP BY '+@T2+','+@T3
    INSERT INTO TEMP EXEC(@SQL)
    --将汇总数据写入交叉表
    DECLARE CUR_SUM CURSOR LOCAL FOR SELECT * FROM TEMP
    DECLARE @F1 VARCHAR(30),@F2 VARCHAR(30),@QTY DECIMAL(8,2),@Q1 VARCHAR(30)
    OPEN CUR_SUM
    FETCH CUR_SUM INTO @F1,@F2,@QTY
    WHILE @@FETCH_STATUS=0 BEGIN
    SELECT @F2='['+@F2+']',@Q1=CAST(@QTY AS VARCHAR(30))
    SELECT @SQL='UPDATE CROSS_TEST SET '+@F2+'='+@Q1+' WHERE '+@T2+'='''+@F1+''''
    EXEC(@SQL)
    FETCH CUR_SUM INTO @F1,@F2,@QTY
    END
    CLOSE CUR_SUM
    SELECT * FROM CROSS_TEST
    DROP TABLE TEMP
    DROP TABLE CROSS_TEST
    DROP TABLE #FIELD
    END
    --------------------------------------------------------
    EXEC UP_TEST 'SALES','TITLE_ID','STOR_ID','QTY'
    说明:字段加中括号为了处理字段中含有特殊字符,值得注意得是要实现交叉表的表必须有两个分类,本例只支持分类字段的数据类型是字符型的,最大的问题就是高亮显示这行的WHERE条件啦,字符类型字段查询时条件必须加单引号,如果是数值类型就可以直接写,所以数值类型的分类字段更容易实现一些,更可以融合在一个过程中。通常大家看到的交叉表都有行汇总与列汇总等信息,本例就没有实现,最后一点工作大家自己练练手吧。 
    展开全文
  • 交叉表、行列转换和交叉查询经典

    千次阅读 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存储过程创建动态交叉表 今天几乎看了一天关于SQL生成Crosstab的话题。从网上找了很多例子,也学到一些东西,现在简单总结: 我的有一个demand表: DROP TABLE IF EXISTS`xcvrs`.`demand`;CREATE TAB
  • WEBI交叉表与Dashboard解决方法

    千次阅读 2015-07-08 08:58:30
    WEBI交叉表与Dashboard解决方法  BI WS是一个很好的功能,甚至有些BI顾问将其称为革命性的。因为它允许我们将WEBI的某一个块儿作为web service发布。可是它有一个很明显的缺点,就是需要预先知道WEBI块的...
  • 透视表和交叉表

    千次阅读 2019-03-28 13:37:49
    透视表和交叉表透视表交叉表 透视表 透视表(pivot table)是常见的数据汇总工具,它根据一个或多个键对数据进行聚合,根据行和列上的分组键将数据分配到矩形区域中。pandas中使用pivot_table方法创建透视表, ...
  • mysql数据库创建表创建模等模板脚本 -- 用root用户登录系统,执行脚本   -- 创建数据库 create database mydb61 character set utf8 ;   -- 选择数据库 use mydb61;   -- 增加 dbuser1 用户
  • mysql多表查询创建视图

    万次阅读 2018-06-26 20:12:48
    1.union联合查询 将多个select语句的结果纵向组合 select * from stuinfo union select * from stuinfoo; union: 1.all #显示全部记录 2.distinct #(去除重复的值 他是默认) select * from stuinfo union all...
  • mysql数据库连表查询的几种方法

    千次阅读 2020-07-22 14:41:24
    1.首先介绍连接分类(内连接,外连接,交叉连接)和连接方法(如下): A)内连接:join,inner join B)外连接:left join,left outer join,right join,right outer join,union C)交叉连接:cross join 2....
  • 经常有人提到,用动态生成SQL语句的方法处理数据时,处理语句超长,无法处理的问题下面就讨论这个问题:/*-- 数据测试环境 --*/if exists (select * from dbo.sysobjects where id = object_id(N[tb]) and ...
  • 当然我个人认为没有什么开发工具是完美的,又或许是有一些高端的功能是普通人发现不了的,今天我们就来说一下在RS的一个常规交叉表中显示百分比的问题. 一:环境配置   操作系统 : Win10 专业版
  • Navicat查询创建工具怎么用?

    千次阅读 2017-03-22 11:05:22
    Navicat 作为专门用来管理数据库的工具,其中的查询创建工具可以视觉化创建查询,不需要 SQL 知识就能创建及编辑查询,数据库对象显示在左边窗格,右边窗格分为两部分:上面是图表设计窗格,下面是语法窗格。...
  • 数据库设计范式第一范式(1NF)第二范式(2NF)第三范式(3NF)数据库实例外键数据库实例注意关联查询概述分类内连接(inner join)外连接交叉连接 多设计-关联查询 为什么需要多设计? 多设计的目的就是为了消除冗余的...
  • postgresql 创建数据。临时

    千次阅读 2015-05-23 23:39:30
    CREATE TABLE -- 定义一个新 Synopsis CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name ( { column_name data_type [ DEFAULT default_expr ] [ column_constraint
  • Linq强大查询功能 using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Data; using
  • 知识点:数据库的相关概念、创建数据库方法、设计数据库、向数据库中插入数据、建立不同数据库之间的关系、删除数据库。 1、数据相关的一些概念 1.1 数据库里的数据是如何保存的? 数据库...
  • 表查询:即多个表关联查询,需要依据多表之间列关系将其连接起来,这种连接方式分为三种:内连接(inner join)、外连接(outer join)及交叉连接(cross join)。 为更好阐述这三种连接的关系,我们采用展示代码的方法...
  • MySQL数据库中的多表查询

    千次阅读 2019-05-11 17:58:07
    子查询与多表查询子查询多表查询外连接(outer join)left join(左连接)right join(右连接)full join(全连接)内连接(inner join)交叉连接(across join) 创建两个表,并添加如下数据(此表对以下讨论均适用) #...
  • 1. 表查询模型的基本思想: 表查询模型基本上基于这样一个基本思想:在多个已知属性上上相似的个体,在其他属性上也会相似。 比如个体A和个体B的三个属性x1,x2,x3相似,那么另外一个属性y很可能也相似。 注: ...
  • 用SQL语句创建表

    万次阅读 多人点赞 2016-07-28 23:22:41
    数据库中的所有数据存储在中。数据包括行和列。列决定了中数据的类型。行包含了实际的数据。  例如,数据库pubs中的authors有九个字段。其中的一个字段名为为au_lname,这个字段被用来存储作者的...
  • SQL 多联合查询

    千次阅读 2015-01-11 23:41:29
    通过连接运算符可以实现多个表查询。连接是关系数据库模型的主要特点,也是它区别于其它类型数据库管理系统的一个标志。 在关系数据库管理系统中,表建立时各数据之间的关系不必确定,常把一个实体的所有信息...
  • recursion数据如下: id   name parentid 1 食品分类 -1 2 肉类 1 3 蔬菜类 1 4 产品分类 -1 5 保健品 4 6 医药 4 7 建筑
  • sql指令和多关系 ### 1. 排序 (order by) 数据库的查询(排序:order by) 默认是按升序排列的,但是升序ASC最好写上,代码可读性! SELECT * FROM 表名 ORDER BY 排序字段 ASC(升序)|DESC(降序); 如果排序列的值...
  • –多连接查询 SELECT * FROM emp; SELECT * FROM dept; –笛卡尔积 15*4=60 条 SELECT emp.*,dept.deptno,dept.dname,dept.loc FROM emp,dept ORDER BY empno; –等值连接 SELECT emp.*,dept.deptno,de...
  • 二、多表查询 使用单个select 语句从多个表格中取出相关的查询结果,多表连接通常是建立在有相互关系的父子表上; 1交叉连接 第一个表格的所有行 乘以 第二个表格中的所有行,也就是笛卡尔积 创建一个消费者与...
  • 数据库中的所有数据存储在中。数据包括行和列。列决定了中数据的类型。行包含了实际的数据。... 通过定义字段,你可以创建一个新。每个字段有一个名字和一个特定的数据类型(数据类型在后面的“字段类型

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 29,657
精华内容 11,862
关键字:

创建交叉表查询的方法