create table wareHourse(id int,wareID varchar(10),wareName varchar(10))
insert into wareHourse values(1, '01', 'A')
insert into wareHourse values(2, '02', 'B')
insert into wareHourse values(3, '03', 'C')
insert into wareHourse values(4, '04', 'D')
create table Inventory(id int,InvID varchar(10),InvName varchar(10))
insert into Inventory values(1, '001', 'aa')
insert into Inventory values(2, '002', 'bb')
insert into Inventory values(3, '003', 'cc')
insert into Inventory values(4, '004', 'dd')
create table 仓库分布表(id int,InvID varchar(10),wareID varchar(10))
insert into 仓库分布表 values(1, '001', '01')
insert into 仓库分布表 values(2, '001', '02')
insert into 仓库分布表 values(3, '002', '03')
insert into 仓库分布表 values(4, '003', '04')
insert into 仓库分布表 values(5, '004', '01')
insert into 仓库分布表 values(6, '003', '02')
insert into 仓库分布表 values(7, '002', '03')
insert into 仓库分布表 values(8, '001', '04')
insert into 仓库分布表 values(9, '001', '03')
go--静态SQL
select invname,
sum(case warename when 'A' then 1 else 0 end) 'A',
sum(case warename when 'B' then 1 else 0 end) 'B',
sum(case warename when 'C' then 1 else 0 end) 'C',
sum(case warename when 'D' then 1 else 0 end) 'D'
from
(
select b.invname,a.warename,c.id from wareHourse a,Inventory b,仓库分布表 c where b.invid = c.invid and a.wareid = c.wareid
) t
group by invnamedrop table wareHourse,Inventory, 仓库分布表/*
invname A B C D
---------- ----------- ----------- ----------- -----------
aa 1 1 1 1
bb 0 0 2 0
cc 0 1 0 1
dd 1 0 0 0(所影响的行数为 4 行)
*/
insert into wareHourse values(1, '01', 'A')
insert into wareHourse values(2, '02', 'B')
insert into wareHourse values(3, '03', 'C')
insert into wareHourse values(4, '04', 'D')
create table Inventory(id int,InvID varchar(10),InvName varchar(10))
insert into Inventory values(1, '001', 'aa')
insert into Inventory values(2, '002', 'bb')
insert into Inventory values(3, '003', 'cc')
insert into Inventory values(4, '004', 'dd')
create table 仓库分布表(id int,InvID varchar(10),wareID varchar(10))
insert into 仓库分布表 values(1, '001', '01')
insert into 仓库分布表 values(2, '001', '02')
insert into 仓库分布表 values(3, '002', '03')
insert into 仓库分布表 values(4, '003', '04')
insert into 仓库分布表 values(5, '004', '01')
insert into 仓库分布表 values(6, '003', '02')
insert into 仓库分布表 values(7, '002', '03')
insert into 仓库分布表 values(8, '001', '04')
insert into 仓库分布表 values(9, '001', '03')
go--静态SQL
select invname,
sum(case warename when 'A' then 1 else 0 end) 'A',
sum(case warename when 'B' then 1 else 0 end) 'B',
sum(case warename when 'C' then 1 else 0 end) 'C',
sum(case warename when 'D' then 1 else 0 end) 'D'
from
(
select b.invname,a.warename,c.id from wareHourse a,Inventory b,仓库分布表 c where b.invid = c.invid and a.wareid = c.wareid
) t
group by invnamedrop table wareHourse,Inventory, 仓库分布表/*
invname A B C D
---------- ----------- ----------- ----------- -----------
aa 1 1 1 1
bb 0 0 2 0
cc 0 1 0 1
dd 1 0 0 0(所影响的行数为 4 行)
*/
解决方案 »
- 求数据库的建表方案? 急!
- Select * into T1 from T2 需要在T1里面添加一个自动增加的字段 并且 把自动增加的值放到里面
- SQL 2005/2008字段的修改
- 各位朋友来帮我看看怎么写sql循环
- 小梁大哥,fc写的是2005的改成2000,怎么改呀!谢谢了。
- c程序问题
- 求救:关于ntext数据类型的函数使用问题
- 很急,求一个sql查询语句怎么写法
- 我把doc、jpeg、text等文件写入sql2000的image类型字段中,现在希望把它们从image中读出并生成一个文件,或者直接显示在网页上,用asp.net
- 求助:关于matlab实现无线传感器网络DV-HOP算法中如何计算能量损耗
- 大家好,本人有一个关于SQL Server 2000触发器的问题请教
- sql中的文本字段有什么办法实现自动编号功能吗?
insert into wareHourse values(1, '01', 'A')
insert into wareHourse values(2, '02', 'B')
insert into wareHourse values(3, '03', 'C')
insert into wareHourse values(4, '04', 'D')
create table Inventory(id int,InvID varchar(10),InvName varchar(10))
insert into Inventory values(1, '001', 'aa')
insert into Inventory values(2, '002', 'bb')
insert into Inventory values(3, '003', 'cc')
insert into Inventory values(4, '004', 'dd')
create table 仓库分布表(id int,InvID varchar(10),wareID varchar(10))
insert into 仓库分布表 values(1, '001', '01')
insert into 仓库分布表 values(2, '001', '02')
insert into 仓库分布表 values(3, '002', '03')
insert into 仓库分布表 values(4, '003', '04')
insert into 仓库分布表 values(5, '004', '01')
insert into 仓库分布表 values(6, '003', '02')
insert into 仓库分布表 values(7, '002', '03')
insert into 仓库分布表 values(8, '001', '04')
insert into 仓库分布表 values(9, '001', '03')
go--静态SQL
select invname,
sum(case warename when 'A' then 1 else 0 end) 'A',
sum(case warename when 'B' then 1 else 0 end) 'B',
sum(case warename when 'C' then 1 else 0 end) 'C',
sum(case warename when 'D' then 1 else 0 end) 'D'
from
(
select b.invname,a.warename,c.id from wareHourse a,Inventory b,仓库分布表 c where b.invid = c.invid and a.wareid = c.wareid
) t
group by invname--动态SQL
declare @sql varchar(8000)
set @sql = 'select invname'
select @sql = @sql + ' , sum(case warename when ''' + warename + ''' then 1 else 0 end) [' + warename + ']'
from (select distinct warename from (select b.invname,a.warename,c.id from wareHourse a,Inventory b,仓库分布表 c where b.invid = c.invid and a.wareid = c.wareid)t) as a
set @sql = @sql + ' from (select b.invname,a.warename,c.id from wareHourse a,Inventory b,仓库分布表 c where b.invid = c.invid and a.wareid = c.wareid) t group by invname'
exec(@sql) drop table wareHourse,Inventory, 仓库分布表/*
invname A B C D
---------- ----------- ----------- ----------- -----------
aa 1 1 1 1
bb 0 0 2 0
cc 0 1 0 1
dd 1 0 0 0(所影响的行数为 4 行)
*/