构造SQL语句吧。我觉得先GROUP BY 再UNION应该有戏 可以参考以下:select A.DiscPort, A.Operator, Sum(A.E20) E20, Sum(A.E40) E40, Sum(A.E45) E45, Sum(A.F20) F20, Sum(A.F40) F40, Sum(A.F45) F45, Sum(A.FR20) FR20, Sum(A.FR40) FR40, Sum(A.FR45) FR45, Sum(A.Weight) Weight from ( select Case When (EWFlag=1) And (Size='20') And (SubString(Type,1,1)<>'R') Then 1 Else 0 End F20, Case When (EWFlag=1) And (Size='20') And (SubString(Type,1,1)='R') Then 1 Else 0 End FR20, Case When (EWFlag=1) And (Size='40') And (SubString(Type,1,1)<>'R') Then 1 Else 0 End F40, Case When (EWFlag=1) And (Size='40') And (SubString(Type,1,1)='R') Then 1 Else 0 End FR40, Case When (EWFlag=1) And (Size='45') And (SubString(Type,1,1)<>'R') Then 1 Else 0 End F45, Case When (EWFlag=1) And (Size='45') And (SubString(Type,1,1)='R') Then 1 Else 0 End FR45, Case When (EWFlag=0) And (Size='20') Then 1 Else 0 End E20, Case When (EWFlag=0) And (Size='40') Then 1 Else 0 End E40, Case When (EWFlag=0) And (Size='45') Then 1 Else 0 End E45 ,Weight, DiscPort, Operator from container ) A group by A.DiscPort, A.Operator order by A.DiscPort, A.Operator
俺看不懂,不知用FASTREPORT可以解决么。
//要你自己用Delphi生成这样的语句use tempdb go create table test( 编号 varchar(20), 姓名 varchar(20), 性别 varchar(2), 省份 varchar(20), 文化程度 varchar(20), 身份 varchar(20) ) go insert into test(编号, 姓名, 性别, 省份, 文化程度, 身份) values('001', 'a001', '男', '上海', '本科', '工人'); insert into test(编号, 姓名, 性别, 省份, 文化程度, 身份) values('002', 'a002', '男', '北京', '本科', '工人'); insert into test(编号, 姓名, 性别, 省份, 文化程度, 身份) values('003', 'a003', '男', '山东', '中专', '农民'); insert into test(编号, 姓名, 性别, 省份, 文化程度, 身份) values('004', 'a004', '女', '浙江', '中专', '农民'); insert into test(编号, 姓名, 性别, 省份, 文化程度, 身份) values('005', 'a005', '女', '北京', '高中', '学生'); go create view testview1 as select ('省份') as 类别,省份 as 分组, sum(case 性别 when '男' then 1 else 0 end) as 男, sum(case 性别 when '女' then 1 else 0 end) as 女, sum(case 身份 when '干部' then 1 else 0 end) as 干部, sum(case 身份 when '工人' then 1 else 0 end) as 工人, sum(case 身份 when '农民' then 1 else 0 end) as 农民, sum(case 身份 when '学生' then 1 else 0 end) as 学生 from test group by 省份go create view testview2 as select ('文化程度') as 类别,文化程度 as 分组, sum(case 性别 when '男' then 1 else 0 end) as 男, sum(case 性别 when '女' then 1 else 0 end) as 女, sum(case 身份 when '干部' then 1 else 0 end) as 干部, sum(case 身份 when '工人' then 1 else 0 end) as 工人, sum(case 身份 when '农民' then 1 else 0 end) as 农民, sum(case 身份 when '学生' then 1 else 0 end) as 学生 from test group by 文化程度go select * from testview1 union select * from testview2 order by 类别
但我不知怎么写,就是编写好了,怎样将结果显示在quickrep中呢。
既然省份等内容是可变的,又要用QuickReport,那就复杂多了. 你可以这样: Select distinct 省份 from TheTable; Select distinct 身份 from TheTable; Select distinct 文化程度 from TheTable; 等到这些数据后,再用 zswang的方法,不过SQL要根据上面得到的数据动态生成.不过,这样作结果SQL语句太复杂了,将来如果需要改动时…… 我觉得还是不要用QuickReport,SQL写成: Select Count(*) as 人数,省份,身份,性别,文化程度 From TheTable Group By 省份,身份,性别,文化程度 这是标准的统计查询语句。 然后你自己设计格式用printer输出到打印机。
zfmich() 说得对,用UNION就可以。Select * from TheTable...... group by 省份 union Select * from TheTable ..... group by 文化程度 很简单的。
可以参考以下:select A.DiscPort, A.Operator, Sum(A.E20) E20, Sum(A.E40) E40, Sum(A.E45) E45,
Sum(A.F20) F20, Sum(A.F40) F40, Sum(A.F45) F45,
Sum(A.FR20) FR20, Sum(A.FR40) FR40, Sum(A.FR45) FR45, Sum(A.Weight) Weight
from (
select
Case When (EWFlag=1) And (Size='20') And (SubString(Type,1,1)<>'R') Then 1
Else 0 End F20,
Case When (EWFlag=1) And (Size='20') And (SubString(Type,1,1)='R') Then 1
Else 0 End FR20,
Case When (EWFlag=1) And (Size='40') And (SubString(Type,1,1)<>'R') Then 1
Else 0 End F40,
Case When (EWFlag=1) And (Size='40') And (SubString(Type,1,1)='R') Then 1
Else 0 End FR40,
Case When (EWFlag=1) And (Size='45') And (SubString(Type,1,1)<>'R') Then 1
Else 0 End F45,
Case When (EWFlag=1) And (Size='45') And (SubString(Type,1,1)='R') Then 1
Else 0 End FR45,
Case When (EWFlag=0) And (Size='20') Then 1
Else 0 End E20,
Case When (EWFlag=0) And (Size='40') Then 1
Else 0 End E40,
Case When (EWFlag=0) And (Size='45') Then 1
Else 0 End E45
,Weight, DiscPort, Operator from container
) A
group by A.DiscPort, A.Operator
order by A.DiscPort, A.Operator
go
create table test(
编号 varchar(20),
姓名 varchar(20),
性别 varchar(2),
省份 varchar(20),
文化程度 varchar(20),
身份 varchar(20)
)
go
insert into test(编号, 姓名, 性别, 省份, 文化程度, 身份)
values('001', 'a001', '男', '上海', '本科', '工人');
insert into test(编号, 姓名, 性别, 省份, 文化程度, 身份)
values('002', 'a002', '男', '北京', '本科', '工人');
insert into test(编号, 姓名, 性别, 省份, 文化程度, 身份)
values('003', 'a003', '男', '山东', '中专', '农民');
insert into test(编号, 姓名, 性别, 省份, 文化程度, 身份)
values('004', 'a004', '女', '浙江', '中专', '农民');
insert into test(编号, 姓名, 性别, 省份, 文化程度, 身份)
values('005', 'a005', '女', '北京', '高中', '学生');
go
create view testview1 as
select ('省份') as 类别,省份 as 分组,
sum(case 性别 when '男' then 1 else 0 end) as 男,
sum(case 性别 when '女' then 1 else 0 end) as 女,
sum(case 身份 when '干部' then 1 else 0 end) as 干部,
sum(case 身份 when '工人' then 1 else 0 end) as 工人,
sum(case 身份 when '农民' then 1 else 0 end) as 农民,
sum(case 身份 when '学生' then 1 else 0 end) as 学生
from test
group by 省份go
create view testview2 as
select ('文化程度') as 类别,文化程度 as 分组,
sum(case 性别 when '男' then 1 else 0 end) as 男,
sum(case 性别 when '女' then 1 else 0 end) as 女,
sum(case 身份 when '干部' then 1 else 0 end) as 干部,
sum(case 身份 when '工人' then 1 else 0 end) as 工人,
sum(case 身份 when '农民' then 1 else 0 end) as 农民,
sum(case 身份 when '学生' then 1 else 0 end) as 学生
from test
group by 文化程度go
select * from testview1
union
select * from testview2
order by 类别
你可以这样:
Select distinct 省份 from TheTable;
Select distinct 身份 from TheTable;
Select distinct 文化程度 from TheTable;
等到这些数据后,再用 zswang的方法,不过SQL要根据上面得到的数据动态生成.不过,这样作结果SQL语句太复杂了,将来如果需要改动时……
我觉得还是不要用QuickReport,SQL写成:
Select Count(*) as 人数,省份,身份,性别,文化程度
From TheTable
Group By 省份,身份,性别,文化程度
这是标准的统计查询语句。
然后你自己设计格式用printer输出到打印机。
union
Select * from TheTable ..... group by 文化程度
很简单的。