select 类型=LX
,一队=sum(case QX when '一队' then 1 else 0 end)
,二队=sum(case QX when '二队' then 1 else 0 end)
,三队=sum(case QX when '三队' then 1 else 0 end)
,合计=count(*)
from 表A group by LX
,一队=sum(case QX when '一队' then 1 else 0 end)
,二队=sum(case QX when '二队' then 1 else 0 end)
,三队=sum(case QX when '三队' then 1 else 0 end)
,合计=count(*)
from 表A group by LX
解决方案 »
- 拼接SQL语句
- 请问我想去掉一个表中一个字段的标识,用sql语句怎么写?
- 我想让一字符字段的值如“1号”……“10号”,按前面的数值顺序排列,不考虑后面的字符,在SQL应该怎么写
- sql server 2008中数据库生成脚本的问题
- 菜鸟问题:一个简单的查询语句!
- 问一下,SQL SERVER 里聚集索引和聚簇索引是不是一个东西?
- 到底该如何跨网段访问SQL Server,有一个明确的方案吗?只要能答出1000分也没问题
- 熟悉VF的哥哥姐姐请进
- 急呀!有关于汇总!!帮帮忙
- 图形存储
- 一个比较难的存储过程
- 请高手帮下忙,我要从表里取出数据按降序排列,(而且我要取中间第5条到第10条的数据)不是第1条 能做到吗,谢谢
select @s=@s+',['+QX+']=sum(case QX when '''+QX+''' then 1 else 0 end)'
from 表
group by QXexec('select LX as 类型"+@s+'
from 表
group by LX ')
--如果QX是不固定的,则用
declare @s varchar(8000)
set @s='select 类型=LX'
select @s=@s+',['+rtrim(QX)+']=sum(case QX when '''+rtrim(QX)+''' then 1 else 0 end)'
from 表A group by QX
exec(@s+'
,合计=count(*)
from 表A group by LX')
declare @s varchar(8000)set @s=''
select @s=@s+',['+QX+']=sum(case QX when '''+QX+''' then 1 else 0 end)'
from 表
group by QXexec('select LX as 类型"+@s+',count(*) as 合计
from 表
group by LX ')
,一队=sum(case QX when '一队' then 1 else 0 end)
,二队=sum(case QX when '二队' then 1 else 0 end)
,三队=sum(case QX when '三队' then 1 else 0 end)
,合计=count(*)
from 表A group by LX
UNion all
select 类型='合计',
,一队=sum(case QX when '一队' then 1 else 0 end)
,二队=sum(case QX when '二队' then 1 else 0 end)
,三队=sum(case QX when '三队' then 1 else 0 end)
from 表A
,一队=sum(case QX when '一队' then 1 else 0 end)
,二队=sum(case QX when '二队' then 1 else 0 end)
,三队=sum(case QX when '三队' then 1 else 0 end)
,合计=count(*)
from 表A group by LX with rollup
--如果QX是不固定的,则用
declare @s varchar(8000)
set @s='select 类型=case when grouping(LX)=1 then ''合计'' else LX end'
select @s=@s+',['+rtrim(QX)+']=sum(case QX when '''+rtrim(QX)+''' then 1 else 0 end)'
from 表A group by QX
exec(@s+'
,合计=count(*)
from 表A group by LX with rollup')
,一队=sum(case QX when '一队' then 1 else 0 end)
,二队=sum(case QX when '二队' then 1 else 0 end)
,三队=sum(case QX when '三队' then 1 else 0 end)
,合计=count(*)
from 表A group by LX
--如果QX是不固定的,则用
declare @s varchar(8000)
set @s='select 类型=case when grouping(LX)=1 then ''合计'' else LX end'
select @s=@s+',['+rtrim(QX)+']=sum(case QX when '''+rtrim(QX)+''' then 1 else 0 end)'
from 表A group by QX
exec(@s+'
,合计=count(*)
from 表A group by LX with rollup')group by LX 是否应改为group by case when grouping(LX)=1 then ''合计'' else LX end
create table 表A(LX varchar(10),QX varchar(10))
insert 表A select 'a','一队'
union all select 'b','二队'
union all select 'c','三队'
union all select 'c','一队'
union all select 'a','二队'
union all select 'b','三队'
union all select 'c','一队'
union all select 'a','二队'
union all select 'b','三队'
union all select 'a','一队'
union all select 'b','二队'
go--QX固定的情况
select 类型=case when grouping(LX)=1 then '合计' else LX end
,一队=sum(case QX when '一队' then 1 else 0 end)
,二队=sum(case QX when '二队' then 1 else 0 end)
,三队=sum(case QX when '三队' then 1 else 0 end)
,合计=count(*)
from 表A group by LX with rollup
--如果QX是不固定的,则用
declare @s varchar(8000)
set @s='select 类型=case when grouping(LX)=1 then ''合计'' else LX end'
select @s=@s+',['+rtrim(QX)+']=sum(case QX when '''+rtrim(QX)+''' then 1 else 0 end)'
from 表A group by QX
exec(@s+'
,合计=count(*)
from 表A group by LX with rollup')
go--删除测试
drop table 表A/*--测试结果类型 一队 二队 三队 合计
---------- ----------- ----------- ----------- -----------
a 2 2 0 4
b 0 2 2 4
c 2 0 1 3
合计 4 4 3 11(所影响的行数为 4 行)
类型 二队 三队 一队 合计
---------- ----------- ----------- ----------- -----------
a 2 0 2 4
b 2 2 0 4
c 0 1 2 3
合计 4 3 4 11--*/