限制列数的交叉表

原创 2005年06月03日 14:27:00

限制列数的交叉数据报表

--示例数据:
CREATE TABLE test(
factoryid varchar(20),
bagid int,
roll int,
number numeric(9,1),
UNIQUE(bagid,roll))
INSERT test SELECT 'M-CS-11#6/GREEN',1,1, 86
UNION ALL   SELECT 'M-CS-11#6/GREEN',1,2, 59.5
UNION ALL   SELECT 'M-CS-11#6/GREEN',1,3, 31.2
UNION ALL   SELECT 'M-CS-11#6/GREEN',1,4, 42
UNION ALL   SELECT 'M-CS-11#6/GREEN',1,5, 31
UNION ALL   SELECT 'M-CS-11#6/GREEN',1,6, 114.3

UNION ALL   SELECT 'M-CS-11#6/GREEN',2,7, 101
UNION ALL   SELECT 'M-CS-11#6/GREEN',2,8, 83.9
UNION ALL   SELECT 'M-CS-11#6/GREEN',2,9, 97.5
UNION ALL   SELECT 'M-CS-11#6/GREEN',2,10,105.4

UNION ALL   SELECT 'M-CS-11#6/GREEN',3,11,103
UNION ALL   SELECT 'M-CS-11#6/GREEN',3,12,128.5
UNION ALL   SELECT 'M-CS-11#6/GREEN',3,13,74.7
UNION ALL   SELECT 'M-CS-11#6/GREEN',3,14,107

UNION ALL   SELECT 'M-CS-11#6/GREEN',4,15,73.4
UNION ALL   SELECT 'M-CS-11#6/GREEN',4,16,100
UNION ALL   SELECT 'M-CS-11#6/GREEN',4,17,141.5
GO

问题描述:
    bagid,roll值唯一,需要将列roll水平显示,并且每条记录只显示4列,多余的自动换行。对于示例数据,要求结果如下(rolls是记录数):
factoryid                        bagid   rolls    n1      n2        n3        n4          Total  
---------------------------- --------- -------- -------- --------- --------- ---------- --------------
M-CS-11#6/GREEN   1         6          86.0    59.5     31.2     42.0    
M-CS-11#6/GREEN   1                     31.0    114.3                               364.0
M-CS-11#6/GREEN   2         4         101.0   83.9     97.5     105.4    387.8
M-CS-11#6/GREEN   3         4         103.0   128.5   74.7     107.0    413.2
M-CS-11#6/GREEN   4         3         73.4     100.0   141.5                  314.9
Total                                                                                                           1479.9

(所影响的行数为 6 行)

--查询处理代码
SELECT a.factoryid,a.bagid,
    rolls=CASE 
        WHEN a.roll=0 THEN CAST(b.rolls as varchar)
        ELSE '' END,
    a.n1,a.n2,a.n3,a.n4,
    Total=CASE
        WHEN a.roll IS NULL THEN CAST(a.Total as varchar)
        WHEN a.roll=(b.rolls-1)/4 THEN CAST(b.Total as varchar)
        ELSE '' END
FROM(
        SELECT factoryid=CASE 
                WHEN GROUPING(factoryid)=1 THEN 'Total'
                ELSE factoryid END,
            bagid=CASE
                WHEN GROUPING(factoryid)=1 THEN ''
                ELSE CAST(bagid AS VARCHAR) END,
            n1=CASE
                WHEN GROUPING(factoryid)=1 THEN ''
                ELSE CAST(SUM(CASE roll%4 WHEN 0 THEN number END) AS VARCHAR) END,
            n2=CASE
                WHEN GROUPING(factoryid)=1 THEN ''
                ELSE ISNULL(CAST(SUM(CASE roll%4 WHEN 1 THEN number END) AS VARCHAR),'') END,
            n3=CASE
                WHEN GROUPING(factoryid)=1 THEN ''
                ELSE ISNULL(CAST(SUM(CASE roll%4 WHEN 2 THEN number END) AS VARCHAR),'') END,
            n4=CASE
                WHEN GROUPING(factoryid)=1 THEN ''
                ELSE ISNULL(CAST(SUM(CASE roll%4 WHEN 3 THEN number END) AS VARCHAR),'') END,
            Total=SUM(number),
            roll=roll/4
        FROM(
            SELECT factoryid,bagid,number,
                roll=(SELECT COUNT(DISTINCT roll) 
                    FROM test 
                    WHERE factoryid=a.factoryid
                        AND bagid=a.bagid
                        AND roll<a.roll)
            FROM test a
        )a GROUP BY factoryid,bagid,roll/4 WITH ROLLUP
        HAVING GROUPING(factoryid)=1 OR GROUPING(roll/4)=0
    )a
    LEFT JOIN(
        SELECT factoryid,bagid,
            rolls=COUNT(*),
            Total=SUM(number)
        FROM test
        GROUP BY factoryid,bagid
    )b ON a.factoryid=b.factoryid
        AND a.bagid=b.bagid
GO


原帖地址

限制列数的交叉表

限制列数的交叉数据报表 --示例数据:CREATE TABLE test(factoryid varchar(20),bagid int,roll int,number numeric(9,1),UN...
  • chenzhiya
  • chenzhiya
  • 2008年01月03日 14:46
  • 159

限制列数的交叉表

--示例数据:CREATE TABLE test(factoryid varchar(20),bagid int,roll int,number numeric(9,1),UNIQUE(bagid,r...
  • emailqjc
  • emailqjc
  • 2010年02月08日 12:50
  • 314

mysql对表列数和行大小的限制

列计数限制MySQL对于每个表具有4096个列的硬限制,但给定表的有效最大值可能较少。精确的列限制取决于几个因素: 表的最大行大小限制列的数量(可能是大小),因为所有列的总长度不能超过此大小。请参阅行...
  • Dr_Joseph
  • Dr_Joseph
  • 2017年09月27日 11:50
  • 615

innodb表最大列数限制

innodb 最大列数限制为1023,其中包含3个内部隐藏列,分别为:DB_ROW_ID(如果没有主键的情况), DB_TRX_ID事务id列,DB_ROLL_PTR回滚指针列。但是分配给用户自己所能...
  • sun_ashe
  • sun_ashe
  • 2016年09月04日 18:15
  • 477

EXCEL 行、列限制数

      Excel 07-2003一个工作表最多可有65536行,最多可有256列,一个工作簿中最多含有255个工作表,默认情况下是三个工作表;      Excel 2007及以后版本,...
  • youngmiffy
  • youngmiffy
  • 2016年11月14日 18:26
  • 643

excel中两列如何进行交叉排列组合

除非注明,文章均为网站运营博客原创,转载请注明本文地址:http://blog.52yuding.com/question/473.html...
  • Duke147
  • Duke147
  • 2014年05月20日 15:40
  • 4349

Sqlite3最大列数量限制

实际使用sqlite3数据库发现,用alter往表格里添加column,每次跑到2000左右总会出错, 写了个脚本测试发现确实如此: Traceback (most recent call las...
  • doyanger
  • doyanger
  • 2018年01月17日 11:25
  • 83

03_8Pandas_透视表与交叉表

import pandas as pd import numpy as npdataset_path = './starcraft.csv' df_data = pd.read_csv(dataset...
  • sinat_33761963
  • sinat_33761963
  • 2016年12月26日 17:13
  • 1358

oracle 动态交叉查询 行列互换 oracle动态交叉表

问题描述如下: 有张表cityorder,有如下几个列:id,amount,city,date。表中有以下一些记录: id amount city date 1 12 001 08-11-1...
  • heicm
  • heicm
  • 2011年03月17日 15:36
  • 4466

ReportStudio入门教程(三十) - 交叉表

我们前面的例子都是使用列表,
  • jolingogo
  • jolingogo
  • 2014年04月16日 13:55
  • 2358
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:限制列数的交叉表
举报原因:
原因补充:

(最多只允许输入30个字)