关于生成数据统计表单的问题

Question

现有以下一个表:(ID做主键)TABLE A
ID ROWS
--------
1  AAA
2  BBB
3  CCC
4  DDD
5  EEE
6  FFFTABLE B
ID COLS
--------
1  aa
2  bb
3  cc
4  dd
5  ee
6  ffTABLE C
ID PAPER UNITS
------------------------------
1  AAA   aa(1), bb(2), ee(3),
2  BBB   cc(1), aa(3),
3  CCC   dd(2), ee(1),
4  DDD   aa(1), cc(2), ff(2),
5  EEE   bb(2), dd(1), ff(1),
6  BBB   bb(2), aa(1),
7  DDD   aa(2), dd(3),
8  EEE   cc(1), ff(2),希望得到以下统计结果:
RESULT:
TABLE D
PAPER  aa  bb  cc  dd  ee  ff  SUM
-----------------------------------
AAA    1   2   0   0   3   0   6
BBB    4   2   1   0   0   0   7
CCC    0   0   0   2   1   0   3
DDD    3   0   2   3   0   2   10
EEE    0   2   1   1   0   3   7
FFF    0   0   0   0   0   0   0即根据表C统计出ROWS表中记录所对应的统计数据最后的SUM,为求该行的和每个行和列相交的结果为累计的统计和请问查询语句该怎么写???(尽量不要用变量或视图.)

Answer 1

源表数据：   
ID    NAME    CODE
1       A          AA
2       A          BB
3       B          CC
4       B          DD
5       C          EE
6       C          FF
7       D          GG
8       D          HH
9       E          II
10     E          JJ
希望统计成如下格式：
CODE       A           C           D           E           F           合计    
---------- ----------- ----------- ----------- ----------- ----------- ----- 
AA         1           0           0           0           0           10%
BB         1           0           0           0           0           10%
CC         0           1           0           0           0           10%
DD         0           1           0           0           0           10%
EE         0           0           1           0           0           10%
FF         0           0           1           0           0           10%
GG         0           0           0           1           0           10%
HH         0           0           0           1           0           10%
II         0           0           0           0           1           10%
JJ         0           0           0           0           1           10%实现步骤如下：
--建立测试数据
declare @tmp1 table
(
 id int,
 name varchar(10),
 code varchar(10)
)
insert into @tmp1
select 1,'A','AA'
UNION
select 2,'A','BB'
UNION
select 3,'B','CC'
UNION
select 4,'B','DD'
UNION
select 5,'C','EE'
UNION
select 6,'C','FF'
UNION
select 7,'D','GG'
UNION
select 8,'D','HH'
UNION
select 9,'E','II'
UNION
select 10,'E','JJ'--返回结果
SELECT  CODE,
 sum(A) as 'A',
 sum(B) as 'C',
 sum(C) as 'D',
 sum(D) as 'E',
 sum(E) as 'F',
 left(CAST(COUNT(CODE) AS DECIMAL(10,2))/(SELECT COUNT(*) FROM @TMP1)*100,2) +'%' AS '合计'
FROM 
(SELECT CODE ,
 CASE WHEN NAME='A' THEN count(CODE) ELSE 0 end as A,
 CASE WHEN NAME='B' THEN count(CODE) ELSE 0 end AS B,
 CASE WHEN NAME='C' THEN count(CODE) ELSE 0 end AS C,
 CASE WHEN NAME='D' THEN count(CODE) ELSE 0 end AS D,
 CASE WHEN NAME='E' THEN count(CODE) ELSE 0 end AS E
FROM @tmp1
GROUP BY CODE,name) AS C
GROUP   BY CODE

Answer 2

create table tb1(id int,rows varchar(20))
insert tb1 select 1 , 'AAA' 
insert tb1 select 2 , 'BBB' 
insert tb1 select 3 , 'CCC' 
insert tb1 select 4 , 'DDD' 
insert tb1 select 5 , 'EEE' 
insert tb1 select 6 , 'FFF'create table tb2(id int,cols varchar(20))
insert tb2 select 1 , 'aa' 
insert tb2 select 2 , 'bb' 
insert tb2 select 3 , 'cc' 
insert tb2 select 4 , 'dd' 
insert tb2 select 5 , 'ee' 
insert tb2 select 6 , 'ff' create table tb3(id int,paper varchar(20),units varchar(100))
insert tb3 select 1 , 'AAA' ,  'aa(1), bb(2), ee(3),' 
insert tb3 select 2 , 'BBB' ,  'cc(1), aa(3),' 
insert tb3 select 3 , 'CCC' ,  'dd(2), ee(1),' 
insert tb3 select 4 , 'DDD' ,  'aa(1), cc(2), ff(2),' 
insert tb3 select 5 , 'EEE' ,  'bb(2), dd(1), ff(1),' 
insert tb3 select 6 , 'BBB' ,  'bb(2), aa(1),' 
insert tb3 select 7 , 'DDD' ,  'aa(2), dd(3),' 
insert tb3 select 8 , 'EEE' ,  'cc(1), ff(2),'declare @sql varchar(8000)
set @sql='select a.rows'
select @sql=@sql+',sum(case when charindex('''+cols+''',c.units)>0 then 1 else 0 end) ['+cols+']'
from tb2exec (@sql+' from tb1 a,tb3 c where a.rows=c.paper group by a.rows')drop table tb1,tb2,tb3/*
rows                 aa          bb          cc          dd          ee          ff
-------------------- ----------- ----------- ----------- ----------- ----------- -----------
AAA                  1           1           0           0           1           0
BBB                  2           1           1           0           0           0
CCC                  0           0           0           1           1           0
DDD                  2           0           1           1           0           1
EEE                  0           1           1           1           0           2(5 行受影响)
*/

Answer 3

好像是跟table c的units括号里的数有关.
看看先.

Answer 4

先将t3拆成 如下
AAA aa(1)
AAA bb(2)
AAA ee(3)
BBB cc(1)
BBB aa(3)
..这样的形式,然后,连表后再行转列. 我就不写了.
关于列拆分成行,及行转列,参考
http://topic.csdn.net/u/20080123/18/9731d130-0d4b-4c11-8d89-f2c3ca331f0c.html就是列分行 + 行转列的两步操作.
t3表结构直接设计成groupid perid num
AAA aa 1
AAA bb 2
这样的结构,明晰多了.

Answer 5

不用变量无法做动态行列转换，没办法，用了一个变量。
create table tb1(id int,rows varchar(20))
insert tb1 select 1 , 'AAA' 
insert tb1 select 2 , 'BBB' 
insert tb1 select 3 , 'CCC' 
insert tb1 select 4 , 'DDD' 
insert tb1 select 5 , 'EEE' 
insert tb1 select 6 , 'FFF'create table tb2(id int,cols varchar(20))
insert tb2 select 1 , 'aa' 
insert tb2 select 2 , 'bb' 
insert tb2 select 3 , 'cc' 
insert tb2 select 4 , 'dd' 
insert tb2 select 5 , 'ee' 
insert tb2 select 6 , 'ff' create table tb3(id int,paper varchar(20),units varchar(100))
insert tb3 select 1 , 'AAA' ,  'aa(1), bb(2), ee(3),' 
insert tb3 select 2 , 'BBB' ,  'cc(1), aa(3),' 
insert tb3 select 3 , 'CCC' ,  'dd(2), ee(1),' 
insert tb3 select 4 , 'DDD' ,  'aa(1), cc(2), ff(2),' 
insert tb3 select 5 , 'EEE' ,  'bb(2), dd(1), ff(1),' 
insert tb3 select 6 , 'BBB' ,  'bb(2), aa(1),' 
insert tb3 select 7 , 'DDD' ,  'aa(2), dd(3),' 
insert tb3 select 8 , 'EEE' ,  'cc(1), ff(2),'declare @s varchar(1000)
select @s = 'select rows'
select @s = @s +',sum(case when id = '+rtrim(id)+' then s else 0 end) as ['+cols+']' from tb2 order by id
select @s = @s +',isnull(sum(s),0) as [sum] from (
select c.rows,d.id,d.cols,d.s from tb1 c left join (
select a.cols,a.id,b.paper,
convert(int,substring(units,charindex(''('',units,charindex(cols,units))+1
,charindex('')'',units,charindex(cols,units))-charindex(cols,units)-3)) s
from tb2 a, tb3 b where charindex(cols,units)>0
) d on c.rows = d.paper 
) e group by rows'
print @s
exec(@s)
/*
rows                 aa          bb          cc          dd          ee          ff          sum         
-------------------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- 
AAA                  1           2           0           0           3           0           6
BBB                  4           2           1           0           0           0           7
CCC                  0           0           0           2           1           0           3
DDD                  3           0           2           3           0           2           10
EEE                  0           2           1           1           0           3           7
FFF                  0           0           0           0           0           0           0
*/
drop table tb1,tb2,tb3
--select charindex(',','f,dsdsg,h,',4)

Answer 6

create table A(ID int , ROWS varchar(10))
insert into A values(1,  'AAA') 
insert into A values(2,  'BBB') 
insert into A values(3,  'CCC') 
insert into A values(4,  'DDD') 
insert into A values(5,  'EEE') 
insert into A values(6,  'FFF') 
create table B(ID int , COLS varchar(10))
insert into B values(1,  'aa') 
insert into B values(2,  'bb') 
insert into B values(3,  'cc') 
insert into B values(4,  'dd') 
insert into B values(5,  'ee') 
insert into B values(6,  'ff') 
create table C(ID int , PAPER varchar(10) , UNITS varchar(50))
insert into C values(1,  'AAA',   'aa(1), bb(2), ee(3),') 
insert into C values(2,  'BBB',   'cc(1), aa(3),') 
insert into C values(3,  'CCC',   'dd(2), ee(1),') 
insert into C values(4,  'DDD',   'aa(1), cc(2), ff(2),') 
insert into C values(5,  'EEE',   'bb(2), dd(1), ff(1),') 
insert into C values(6,  'BBB',   'bb(2), aa(1),') 
insert into C values(7,  'DDD',   'aa(2), dd(3),') 
insert into C values(8,  'EEE',   'cc(1), ff(2),') 
go--建立一临时表
SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO tmp FROM syscolumns a, syscolumns b --静态SQL，指units/cols固定为aa,bb,cc,dd,ee,ff
select paper , 
  sum(case units_1 when 'aa' then cast(units_2 as int) else 0 end) [aa],
  sum(case units_1 when 'bb' then cast(units_2 as int) else 0 end) [bb],
  sum(case units_1 when 'cc' then cast(units_2 as int) else 0 end) [cc],
  sum(case units_1 when 'dd' then cast(units_2 as int) else 0 end) [dd],
  sum(case units_1 when 'ee' then cast(units_2 as int) else 0 end) [ee],
  sum(case units_1 when 'ff' then cast(units_2 as int) else 0 end) [ff],
  sum(cast(units_2 as int)) [sum]
from
(
  select paper , units_1 = left(units , charindex('(',units) - 1) , units_2 = substring(units , charindex('(',units) + 1 , charindex(')',units) - charindex('(',units) - 1) from
  (SELECT m.PAPER, UNITS = rtrim(ltrim(SUBSTRING(m.[UNITS], n.id, CHARINDEX(',', m.[UNITS] + ',', n.id) - n.id))) FROM c m, tmp n WHERE SUBSTRING(',' + m.[UNITS], n.id, 1) = ',') t where charindex('(',units) > 0 and charindex(')',units) > 0
) o
group by paperdrop table A,B,C,tmp
/*
paper      aa          bb          cc          dd          ee          ff          sum         
---------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- 
AAA        1           2           0           0           3           0           6
BBB        4           2           1           0           0           0           7
CCC        0           0           0           2           1           0           3
DDD        3           0           2           3           0           2           10
EEE        0           2           1           1           0           3           7（所影响的行数为 5 行）
*/

Answer 7

create table A(ID int , ROWS varchar(10))
insert into A values(1,  'AAA') 
insert into A values(2,  'BBB') 
insert into A values(3,  'CCC') 
insert into A values(4,  'DDD') 
insert into A values(5,  'EEE') 
insert into A values(6,  'FFF') 
create table B(ID int , COLS varchar(10))
insert into B values(1,  'aa') 
insert into B values(2,  'bb') 
insert into B values(3,  'cc') 
insert into B values(4,  'dd') 
insert into B values(5,  'ee') 
insert into B values(6,  'ff') 
create table C(ID int , PAPER varchar(10) , UNITS varchar(50))
insert into C values(1,  'AAA',   'aa(1), bb(2), ee(3),') 
insert into C values(2,  'BBB',   'cc(1), aa(3),') 
insert into C values(3,  'CCC',   'dd(2), ee(1),') 
insert into C values(4,  'DDD',   'aa(1), cc(2), ff(2),') 
insert into C values(5,  'EEE',   'bb(2), dd(1), ff(1),') 
insert into C values(6,  'BBB',   'bb(2), aa(1),') 
insert into C values(7,  'DDD',   'aa(2), dd(3),') 
insert into C values(8,  'EEE',   'cc(1), ff(2),') 
go--建立一临时表
SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO tmp FROM syscolumns a, syscolumns b --静态SQL，指units/cols固定为aa,bb,cc,dd,ee,ff
select A.rows,isnull(P.aa,0) aa,isnull(P.bb,0) bb,isnull(P.cc,0) cc,isnull(P.dd,0) dd,isnull(P.ee,0) ee,isnull(P.ff,0) ff,isnull(P.[sum],0) [sum] from A
left join
(
select paper , 
  sum(case units_1 when 'aa' then cast(units_2 as int) else 0 end) [aa],
  sum(case units_1 when 'bb' then cast(units_2 as int) else 0 end) [bb],
  sum(case units_1 when 'cc' then cast(units_2 as int) else 0 end) [cc],
  sum(case units_1 when 'dd' then cast(units_2 as int) else 0 end) [dd],
  sum(case units_1 when 'ee' then cast(units_2 as int) else 0 end) [ee],
  sum(case units_1 when 'ff' then cast(units_2 as int) else 0 end) [ff],
  sum(cast(units_2 as int)) [sum]
from
(
  select paper , units_1 = left(units , charindex('(',units) - 1) , units_2 = substring(units , charindex('(',units) + 1 , charindex(')',units) - charindex('(',units) - 1) from
  (SELECT m.PAPER, UNITS = rtrim(ltrim(SUBSTRING(m.[UNITS], n.id, CHARINDEX(',', m.[UNITS] + ',', n.id) - n.id))) FROM c m, tmp n WHERE SUBSTRING(',' + m.[UNITS], n.id, 1) = ',') t where charindex('(',units) > 0 and charindex(')',units) > 0
) o
group by paper
) p
on A.rows = p.paperdrop table A,B,C,tmp/*
rows       aa          bb          cc          dd          ee          ff          sum         
---------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- 
AAA        1           2           0           0           3           0           6
BBB        4           2           1           0           0           0           7
CCC        0           0           0           2           1           0           3
DDD        3           0           2           3           0           2           10
EEE        0           2           1           1           0           3           7
FFF        0           0           0           0           0           0           0
（所影响的行数为 6 行）
*/

Answer 8

Warning: Null value is eliminated by an aggregate or other SET operation.