tableAId PersonID Level LogTime
1 01 1 2011-8-29
3 05 3 2011-7-24
7 07 2 2011-8-25
tableCLevelId LevelName
1 级别一
2 级别二
3 级别三我想要的结果是用交叉表查询出一个(X行显示tableC的LevelName,有多少显示多少,y列显示tableA的PersonID,有多少显示多少还有就是对于X行的每行最后要来个对该行数量进行总计,对于y列的每列要在最后来个对该列的数字进行总计if object_id('[tableA]') is not null drop table [tableA]
go
create table [tableA]([Id] int,[PersonID] varchar(20),[Level] int,[LogTime] datetime)
insert [tableA]
select 1,'01',1,'2011-8-29' union all
select 3,'05',3,'2011-7-24' union all
select 7,'07',2,'2011-8-25'
--> 测试数据:[tableC]
if object_id('[tableC]') is not null drop table [tableC]
go
create table [tableC]([LevelId] int,[LevelName] varchar(6))
insert [tableC]
select 1,'级别一' union all
select 2,'级别二' union all
select 3,'级别三'
--------------开始查询--------------------------
declare @sql varchar(8000)
set @sql = 'select isnull(a.personid,''总计'') '
select @sql = @sql + ' , sum(case b.LevelName when ''' + LevelName + ''' then 1 else 0 end) [' + LevelName + ']'
from (select distinct LevelName from tablec) as a
set @sql = @sql + ', count(a.PersonID) as 总计 from tablea a join tablec b on a.Level=b.Levelid group by a.personid with rollup'
exec(@sql)
----------------结果----------------------------
/* 级别二 级别三 级别一 总计
-------------------- ----------- ----------- ----------- -----------
01 0 0 1 1
05 0 1 0 1
07 1 0 0 1
总计 1 1 1 3
上面的SQL语句可以实现效果
但是现在我想再加个where语句,where PersonID=07 and LogTime=2011-8-25 and Level=2
请问这个where语句应该放在上面的SQL语句中的哪里?
1 01 1 2011-8-29
3 05 3 2011-7-24
7 07 2 2011-8-25
tableCLevelId LevelName
1 级别一
2 级别二
3 级别三我想要的结果是用交叉表查询出一个(X行显示tableC的LevelName,有多少显示多少,y列显示tableA的PersonID,有多少显示多少还有就是对于X行的每行最后要来个对该行数量进行总计,对于y列的每列要在最后来个对该列的数字进行总计if object_id('[tableA]') is not null drop table [tableA]
go
create table [tableA]([Id] int,[PersonID] varchar(20),[Level] int,[LogTime] datetime)
insert [tableA]
select 1,'01',1,'2011-8-29' union all
select 3,'05',3,'2011-7-24' union all
select 7,'07',2,'2011-8-25'
--> 测试数据:[tableC]
if object_id('[tableC]') is not null drop table [tableC]
go
create table [tableC]([LevelId] int,[LevelName] varchar(6))
insert [tableC]
select 1,'级别一' union all
select 2,'级别二' union all
select 3,'级别三'
--------------开始查询--------------------------
declare @sql varchar(8000)
set @sql = 'select isnull(a.personid,''总计'') '
select @sql = @sql + ' , sum(case b.LevelName when ''' + LevelName + ''' then 1 else 0 end) [' + LevelName + ']'
from (select distinct LevelName from tablec) as a
set @sql = @sql + ', count(a.PersonID) as 总计 from tablea a join tablec b on a.Level=b.Levelid group by a.personid with rollup'
exec(@sql)
----------------结果----------------------------
/* 级别二 级别三 级别一 总计
-------------------- ----------- ----------- ----------- -----------
01 0 0 1 1
05 0 1 0 1
07 1 0 0 1
总计 1 1 1 3
上面的SQL语句可以实现效果
但是现在我想再加个where语句,where PersonID=07 and LogTime=2011-8-25 and Level=2
请问这个where语句应该放在上面的SQL语句中的哪里?
set @sql = 'select isnull(a.personid,''总计'') '
select @sql = @sql + ' , sum(case b.LevelName when ''' + LevelName + ''' then 1 else 0 end) [' + LevelName + ']'
from (select distinct LevelName from tablec) as a
set @sql = @sql + ', count(a.PersonID) as 总计 from tablea a join tablec b on a.Level=b.Levelid
where PersonID=07 and LogTime=2011-8-25 and Level=2 group by a.personid with rollup'
exec(@sql)
declare @sql varchar(8000)
set @sql = 'select isnull(a.personid,''总计'') '
select @sql = @sql + ' , sum(case b.LevelName when ''' + LevelName + ''' then 1 else 0 end) [' + LevelName + ']'
from (select distinct LevelName from tablec) as a
set @sql = @sql + ', count(a.PersonID) as 总计 from tablea a join tablec b on a.Level=b.Levelid where PersonID=07 and LogTime=2011-8-25 and Level=2
group by a.personid with rollup'
exec(@sql)
go
create table [tableA]([Id] int,[PersonID] varchar(20),[Level] int,[LogTime] datetime)
insert [tableA]
select 1,'01',1,'2011-8-29' union all
select 3,'05',3,'2011-7-24' union all
select 7,'07',2,'2011-8-25'
--> 测试数据:[tableC]
if object_id('[tableC]') is not null drop table [tableC]
go
create table [tableC]([LevelId] int,[LevelName] varchar(6))
insert [tableC]
select 1,'级别一' union all
select 2,'级别二' union all
select 3,'级别三'
--------------开始查询--------------------------
declare @sql varchar(8000)
set @sql = 'select isnull(a.personid,''总计'') '
select @sql = @sql + ' , sum(case b.LevelName when ''' + LevelName + ''' then 1 else 0 end) [' + LevelName + ']'
from (select distinct LevelName from tablec) as a
set @sql = @sql + ', count(a.PersonID) as 总计 from tablea a join tablec b on a.Level=b.Levelid where PersonID=07 and LogTime=''2011-8-25'' and Level=2
group by a.personid with rollup'
exec(@sql)
----------------结果----------------------------
/* 级别二 级别三 级别一 总计
-------------------- ----------- ----------- ----------- -----------
07 1 0 0 1
总计 1 0 0 1(2 行受影响)
*/
declare @sql varchar(8000)
set @sql = 'select isnull(a.personid,''总计'') '
select @sql = @sql + ' , sum(case b.LevelName when ''' + LevelName + ''' then 1 else 0 end) [' + LevelName + ']'
from (select distinct LevelName from tablec) as a
set @sql = @sql + ', count(a.PersonID) as 总计 from tablea a join tablec b on a.Level=b.Levelid '
+'where a.PersonID=''07'' and a.LogTime=''2011-8-25'' and a.[Level]=2 group by a.personid with rollup'
exec(@sql)
declare @sql varchar(8000)
set @sql = 'select isnull(a.personid,''总计'') '
select @sql = @sql + ' , sum(case b.LevelName when ''' + LevelName + ''' then 1 else 0 end) [' + LevelName + ']'
from (select distinct LevelName from tablec) as a
set @sql = @sql + ', count(a.PersonID) as 总计 from tablea a join tablec b on a.Level=b.Levelid
where a.PersonID=07 and a.LogTime=''2011-8-25'' and a.Level=2
group by a.personid with rollup'
exec(@sql)
insert [tableA]
select 1,'01',1,'2011-8-29' union all
select 3,'05',3,'2011-7-24' union all
select 7,'07',2,'2011-8-25'
--> 测试数据:[tableC]
if object_id('[tableC]') is not null drop table [tableC]
go
create table [tableC]([LevelId] int,[LevelName] varchar(6))
insert [tableC]
select 1,'级别一' union all
select 2,'级别二' union all
select 3,'级别三'
go--------------开始查询--------------------------declare @sql varchar(8000)
set @sql = 'select isnull(a.personid,''总计'') '
select @sql = @sql + ' , sum(case b.LevelName when ''' + LevelName + ''' then 1 else 0 end) [' + LevelName + ']'
from (select distinct LevelName from tablec) as a
set @sql = @sql + ', count(a.PersonID) as 总计 from tablea a join tablec b on a.Level=b.Levelid
where PersonID=''07'' and LogTime=''2011-8-25'' and Level=2
group by a.personid with rollup'
exec(@sql)
/*
级别二 级别三 级别一 总计
-------------------- ----------- ----------- ----------- -----------
07 1 0 0 1
总计 1 0 0 1(2 行受影响)*/
说错了,07是PersonID,不能去掉的
insert [tableA]
select 1,'01',1,'2011-8-29' union all
select 3,'05',3,'2011-7-24' union all
select 7,'07',2,'2011-8-25'
--> 测试数据:[tableC]
if object_id('[tableC]') is not null drop table [tableC]
go
create table [tableC]([LevelId] int,[LevelName] varchar(6))
insert [tableC]
select 1,'级别一' union all
select 2,'级别二' union all
select 3,'级别三'
go--------------开始查询--------------------------declare @sql varchar(8000)
set @sql = 'select case when a.personid is null then ''总计'' else '''' end '
select @sql = @sql + ' , sum(case b.LevelName when ''' + LevelName + ''' then 1 else 0 end) [' + LevelName + ']'
from (select distinct LevelName from tablec) as a
set @sql = @sql + ', count(a.PersonID) as 总计 from tablea a join tablec b on a.Level=b.Levelid
where PersonID=''07'' and LogTime=''2011-8-25'' and Level=2
group by a.personid with rollup'
exec(@sql)
/*
级别二 级别三 级别一 总计
---- ----------- ----------- ----------- -----------
1 0 0 1
总计 1 0 0 1(2 行受影响)*/
set @sql = 'select case when a.personid is null then ''总计'' else '''' end '
select @sql = @sql + ' , sum(case b.LevelName when ''' + LevelName + ''' then 1 else 0 end) [' + LevelName + ']'
from (select distinct LevelName from tablec) as a
set @sql = @sql + ', count(a.PersonID) as 总计 from tablea a join tablec b on a.Level=b.Levelid
where PersonID=''07'' and LogTime=''2011-8-25'' and Level=2
group by a.personid with rollup'
exec(@sql)