解决方案 »
- 刚开始学关于防注入的,一个低级问题
- 数据库操作错误,系统提示如下
- 分组 条件 求值
- 在VB中我用access,下面的查询语句能正确执行,但升迁成SQL SERVER 2000后却报错。如何在SQL SERVER 2000也能实现下列语句的效果?select * from 书店 ORDER BY 类型='物理' , 类型='化学' ,
- 自动备份二天前的数据
- 求优化
- 如何取得sp_msget_jobstate返回值
- 如何把手工递增的字段,改为自动递增
- 安装SQL SERVER时出现:安装程序初始化错误,错误运行页内操作。
- SQL Server中的完全比较问题
- 能否在一条语句里写两组条件?!
- sql中如何查找当前时间小于当日的六点,并且日期在指定的昨晚10点到今早六点之前的数据?
http://blog.csdn.net/szstephenzhou/article/details/7091819
--> 测试数据:[A1]
go
if object_id('[A1]') is not null
drop table [A1]
go
create table [A1](
[id] int,
[num] int,
[class] varchar(5)
)
go
insert [A1]
select 1,10,'类别1' union all
select 1,101,'类别2' union all
select 2,20,'类别1' union all
select 2,50,'类别1'--> 测试数据:[B1]
go
if object_id('[B1]') is not null
drop table [B1]
create table [B1](
[id] int,
[num] int,
[class] varchar(5)
)
go
insert [B1]
select 1,100,'类别1' union all
select 1,1001,'类别2' union all
select 1,1000,'类别3' union all
select 2,20,'类别1' union all
select 2,20,'类别2'go
create table #t(
id int,
numa int,
numb int,
class varchar(5)
)
insert #t
select b.id,sum(isnull(a.num,0)) as numa,sum(isnull(b.num,0)) as numb,b.class
from [B1] b left join [A1] a
on b.id=a.id group by b.id,b.class
select * from #tdeclare @str varchar(1000)
set @str=''
select @str=@str+','+class+'=sum(case when class='
+QUOTENAME(class,'''')+'then numa+numb else 0 end)'
from #t group by class
set @str='select id'+@str+' from #t group by id'
exec(@str)
----------------------------------
id 类别1 类别2 类别3
1 311 2113 2111
2 110 110 0