入出库
ID 单据类型 单位名称
1 出库单 联想
2 入库单 IBM
3 出库单 IBM
-----------------明细表
ID 入出库ID 货物名称 数量 金额
1 1 笔记本 1 8000
2 2 鼠标 2 30
3 3 鼠标 3 45
-----------------现在想汇总下单位的入出库的情况,查询出来的结果格式如下单位名称 出库数量 出库金额 入库数量 入库金额
联想 1 8000 0 0
IBM 3 45 2 30请问下这样的SQL语句该如何写?不知道是不是自己一下转不过来弯,怎么也写不出来,谢谢了
ID 单据类型 单位名称
1 出库单 联想
2 入库单 IBM
3 出库单 IBM
-----------------明细表
ID 入出库ID 货物名称 数量 金额
1 1 笔记本 1 8000
2 2 鼠标 2 30
3 3 鼠标 3 45
-----------------现在想汇总下单位的入出库的情况,查询出来的结果格式如下单位名称 出库数量 出库金额 入库数量 入库金额
联想 1 8000 0 0
IBM 3 45 2 30请问下这样的SQL语句该如何写?不知道是不是自己一下转不过来弯,怎么也写不出来,谢谢了
sum(case when a.单据类型='出库单' then b.数量 else 0 end) as 出库数量,
sum(case when a.单据类型='出库单' then b.金额 else 0 end) as 出库金额,
sum(case when a.单据类型='入库单' then b.数量 else 0 end) as 入库数量,
sum(case when a.单据类型='入库单' then b.金额 else 0 end) as 入库金额
from 入出库 a,明细表 b
where a.id=b.入出库ID
group by a.单位名称
sum(case when a.单据类型='出库单' then b.数量 else 0 end) as 出库数量,
sum(case when a.单据类型='出库单' then b.金额 else 0 end) as 出库金额,
sum(case when a.单据类型='入库单' then b.数量 else 0 end) as 入库数量,
sum(case when a.单据类型='入库单' then b.金额 else 0 end) as 入库金额
from 入出库 a,明细表 b
where a.id=b.入出库ID
group by a.单位名称
sum(case 单据类型 when '出库单' then 数量 else 0 end ) 出库数量,
sum(case 单据类型 when '出库单' then 金额 else 0 end ) 出库金额,
sum(case 单据类型 when '入库单' then 数量 else 0 end ) 入库数量,
sum(case 单据类型 when '入库单' then 金额 else 0 end ) 入库金额
from 入出库,明细表
where 入出库.id = 明细表.入出库ID
group by 单位名称
go
insert into 入出库
select 1,'出库单','联想'
union all select 2,'入库单','IBM'
union all select 3,'出库单','IBM'
go
create table 明细表(ID int,入出库ID int,货物名称 varchar(20),数量 int,金额 int)
go
insert into 明细表
select 1,1,'笔记本',1,8000
union all select 2,2,'鼠标 ',2,30
union all select 3,3,'鼠标 ',3,45
go
select 单位名称,sum(case 单据类型 when '出库单' then 数量 else 0 end),sum(case 单据类型 when '出库单' then 金额 else 0 end),sum(case 单据类型 when '入库单' then 数量 else 0 end),sum(case 单据类型 when '入库单' then 金额 else 0 end) from 入出库 t1,明细表 t2 where t1.id=t2.入出库ID group by 单位名称
go
drop table 入出库,明细表
go
(3 行受影响)(3 行受影响)
单位名称
-------------------- ----------- ----------- ----------- -----------
IBM 3 45 2 30
联想 1 8000 0 0(2 行受影响)
insert into tba select 1,'出库单','联想'
insert into tba select 2,'入库单','IBM'
insert into tba select 3,'出库单','IBM'
create table tbb(ID int, 入出库ID int, 货物名称 varchar(20),数量 int,金额 int)
insert into tbb select 1,1,'笔记本',1,8000
insert into tbb select 2,2,'鼠标',2,30
insert into tbb select 3,3,'鼠标',3,45select 单位名称,
sum(case when 单据类型='出库单' then 数量 else 0 end) as '出库数量',
sum(case when 单据类型='出库单' then 金额 else 0 end) as '出库金额',
sum(case when 单据类型='入库单' then 数量 else 0 end) as '出库数量',
sum(case when 单据类型='入库单' then 金额 else 0 end) as '出库金额'
from (
select a.单位名称,a.单据类型,b.数量,b.金额 from tba a,tbb b where a.id=b.入出库ID)tp
group by 单位名称单位名称 出库数量 出库金额 出库数量 出库金额
IBM 3 45 2 30
联想 1 8000 0 0
create table 入出库(ID int,单据类型 varchar(20),单位名称 varchar(20));
go
insert into 入出库
select 1,'出库单','联想'
union all select 2,'入库单','IBM'
union all select 3,'出库单','IBM'
go
create table 明细表(ID int,入出库ID int,货物名称 varchar(20),数量 int,金额 int)
go
insert into 明细表
select 1,1,'笔记本',1,8000
union all select 2,2,'鼠标 ',2,30
union all select 3,3,'鼠标 ',3,45
go
select 单位名称,sum(case 单据类型 when '出库单' then 数量 else 0 end) as 出库数量,
sum(case 单据类型 when '出库单' then 金额 else 0 end) as 出库金额,
sum(case 单据类型 when '入库单' then 数量 else 0 end) as 入库数量,
sum(case 单据类型 when '入库单' then 金额 else 0 end) as 入库金额
from 入出库 t1,明细表 t2
where t1.id=t2.入出库ID
group by 单位名称
go
drop table 入出库,明细表
go
/*
普通行列转换
(爱新觉罗.毓华 2007-11-18于海南三亚)
假设有张学生成绩表(tb)如下:
Name Subject Result
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
*/
-------------------------------------------------------------------------
/*
想变成
姓名 语文 数学 物理
---------- ----------- ----------- -----------
李四 74 84 94
张三 74 83 93
*/
create table tb
(
Name varchar(10) ,
Subject varchar(10) ,
Result int
)
insert into tb(Name , Subject , Result) values('张三' , '语文' , 74)
insert into tb(Name , Subject , Result) values('张三' , '数学' , 83)
insert into tb(Name , Subject , Result) values('张三' , '物理' , 93)
insert into tb(Name , Subject , Result) values('李四' , '语文' , 74)
insert into tb(Name , Subject , Result) values('李四' , '数学' , 84)
insert into tb(Name , Subject , Result) values('李四' , '物理' , 94)
go
--静态SQL,指subject只有语文、数学、物理这三门课程。
select name 姓名,
max(case subject when '语文' then result else 0 end) 语文,
max(case subject when '数学' then result else 0 end) 数学,
max(case subject when '物理' then result else 0 end) 物理
from tb
group by name
/*
姓名 语文 数学 物理
---------- ----------- ----------- -----------
李四 74 84 94
张三 74 83 93
*/
--动态SQL,指subject不止语文、数学、物理这三门课程。
declare @sql varchar(8000)
set @sql = 'select Name as ' + '姓名'
select @sql = @sql + ' , max(case Subject when ''' + Subject + ''' then Result else 0 end) [' + Subject + ']'
from (select distinct Subject from tb) as a
set @sql = @sql + ' from tb group by name'
exec(@sql)
/*
姓名 数学 物理 语文
---------- ----------- ----------- -----------
李四 84 94 74
张三 83 93 74
*/
-------------------------------------------------------------------
/*加个平均分,总分
姓名 语文 数学 物理 平均分 总分
---------- ----------- ----------- ----------- -------------------- -----------
李四 74 84 94 84.00 252
张三 74 83 93 83.33 250
*/
--静态SQL,指subject只有语文、数学、物理这三门课程。
select name 姓名,
max(case subject when '语文' then result else 0 end) 语文,
max(case subject when '数学' then result else 0 end) 数学,
max(case subject when '物理' then result else 0 end) 物理,
cast(avg(result*1.0) as decimal(18,2)) 平均分,
sum(result) 总分
from tb
group by name
/*
姓名 语文 数学 物理 平均分 总分
---------- ----------- ----------- ----------- -------------------- -----------
李四 74 84 94 84.00 252
张三 74 83 93 83.33 250
*/
--动态SQL,指subject不止语文、数学、物理这三门课程。
declare @sql1 varchar(8000)
set @sql1 = 'select Name as ' + '姓名'
select @sql1 = @sql1 + ' , max(case Subject when ''' + Subject + ''' then Result else 0 end) [' + Subject + ']'
from (select distinct Subject from tb) as a
set @sql1 = @sql1 + ' , cast(avg(result*1.0) as decimal(18,2)) 平均分,sum(result) 总分 from tb group by name'
exec(@sql1)
/*
姓名 数学 物理 语文 平均分 总分
---------- ----------- ----------- ----------- -------------------- -----------
李四 84 94 74 84.00 252
张三 83 93 74 83.33 250
*/
drop table tb
---------------------------------------------------------
---------------------------------------------------------
/*
如果上述两表互相换一下:即姓名 语文 数学 物理
张三 74 83 93
李四 74 84 94想变成
Name Subject Result
---------- ------- -----------
李四 语文 74
李四 数学 84
李四 物理 94
张三 语文 74
张三 数学 83
张三 物理 93
*/
create table tb1
(
姓名 varchar(10) ,
语文 int ,
数学 int ,
物理 int
)
insert into tb1(姓名 , 语文 , 数学 , 物理) values('张三',74,83,93)
insert into tb1(姓名 , 语文 , 数学 , 物理) values('李四',74,84,94)select * from
(
select 姓名 as Name , Subject = '语文' , Result = 语文 from tb1
union all
select 姓名 as Name , Subject = '数学' , Result = 数学 from tb1
union all
select 姓名 as Name , Subject = '物理' , Result = 物理 from tb1
) t
order by name , case Subject when '语文' then 1 when '数学' then 2 when '物理' then 3 when '总分' then 4 end
--------------------------------------------------------------------
/*加个平均分,总分
Name Subject Result
---------- ------- --------------------
李四 语文 74.00
李四 数学 84.00
李四 物理 94.00
李四 平均分 84.00
李四 总分 252.00
张三 语文 74.00
张三 数学 83.00
张三 物理 93.00
张三 平均分 83.33
张三 总分 250.00
*/select * from
(
select 姓名 as Name , Subject = '语文' , Result = 语文 from tb1
union all
select 姓名 as Name , Subject = '数学' , Result = 数学 from tb1
union all
select 姓名 as Name , Subject = '物理' , Result = 物理 from tb1
union all
select 姓名 as Name , Subject = '平均分' , Result = cast((语文 + 数学 + 物理)*1.0/3 as decimal(18,2)) from tb1
union all
select 姓名 as Name , Subject = '总分' , Result = 语文 + 数学 + 物理 from tb1
) t
order by name , case Subject when '语文' then 1 when '数学' then 2 when '物理' then 3 when '平均分' then 4 when '总分' then 5 end
drop table tb1
insert into 入出库 values(1 , '出库单' , '联想')
insert into 入出库 values(2 , '入库单' , 'IBM')
insert into 入出库 values(3 , '出库单' , 'IBM')
create table 明细表 (ID int,入出库ID int,货物名称 varchar(10),数量 int,金额 int)
insert into 明细表 values(1, 1, '笔记本', 1, 8000 )
insert into 明细表 values(2, 2, '鼠标 ', 2, 30 )
insert into 明细表 values(3, 3, '鼠标 ', 3, 45 )
goselect 单位名称 ,
sum(case 单据类型 when '出库单' then 数量 else 0 end ) 出库数量,
sum(case 单据类型 when '出库单' then 金额 else 0 end ) 出库金额,
sum(case 单据类型 when '入库单' then 数量 else 0 end ) 入库数量,
sum(case 单据类型 when '入库单' then 金额 else 0 end ) 入库金额
from 入出库,明细表
where 入出库.id = 明细表.入出库ID
group by 单位名称drop table 入出库 , 明细表/*
单位名称 出库数量 出库金额 入库数量 入库金额
---------- ----------- ----------- ----------- -----------
IBM 3 45 2 30
联想 1 8000 0 0(所影响的行数为 2 行)
*/
CREATE TABLE [dbo].[Test2](
[ID] [nvarchar](50) NOT NULL,
[TYPR] [nvarchar](50) NULL,
[NAME] [nvarchar](50) NULL
CONSTRAINT [PK_Test2] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]CREATE TABLE [dbo].[Test2_D](
[ID] [nvarchar](50) NOT NULL,
[HEAD_ID] [nvarchar](50) NOT NULL,
[GOOD_NAME] [nvarchar](50) NULL,
[QTY] [decimal](14,2) NULL,
[PRICE] [decimal](14,2) NULL
CONSTRAINT [PK_Test2_D] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]--插入值--
INSERT INTO TEST2(ID,[TYPE],[NAME]) VALUES('1','出库单','联想')
INSERT INTO TEST2(ID,[TYPE],[NAME]) VALUES('2','入库单','IBM')
INSERT INTO TEST2(ID,[TYPE],[NAME]) VALUES('3','出库单','IBM')INSERT INTO TEST2_D(ID,HEAD_ID,GOOD_NAME,QTY,PRICE) VALUES('1','1','笔记本',1,8000)
INSERT INTO TEST2_D(ID,HEAD_ID,GOOD_NAME,QTY,PRICE) VALUES('2','2','鼠标',2,30)
INSERT INTO TEST2_D(ID,HEAD_ID,GOOD_NAME,QTY,PRICE) VALUES('3','3','鼠标',3,45)--SQL语句--
SELECT
A.单位名称,
(CASE WHEN A.出库数量 IS NULL THEN 0 ELSE A.出库数量 END) 出库数量,
(CASE WHEN A.出库金额 IS NULL THEN 0 ELSE A.出库金额 END) 出库金额,
(CASE WHEN B.入库数量 IS NULL THEN 0 ELSE B.入库数量 END) 入库数量,
(CASE WHEN B.入库金额 IS NULL THEN 0 ELSE B.入库金额 END) 入库金额
FROM
(SELECT HEAD.NAME AS 单位名称, SUM(DETAIL.QTY)AS 出库数量, SUM(DETAIL.PRICE) AS 出库金额
FROM TEST2 AS HEAD
INNER JOIN TEST2_D AS DETAIL ON HEAD.ID = DETAIL.HEAD_ID
WHERE HEAD.TYPE = '出库单'
GROUP BY HEAD.NAME ) AS A
LEFT JOIN
(SELECT HEAD.NAME AS 单位名称, SUM(DETAIL.QTY)AS 入库数量, SUM(DETAIL.PRICE) AS 入库金额
FROM TEST2 AS HEAD
INNER JOIN TEST2_D AS DETAIL ON HEAD.ID = DETAIL.HEAD_ID
WHERE HEAD.TYPE = '入库单'
GROUP BY HEAD.NAME )AS B ON A.单位名称 = B.单位名称
ORDER BY A.单位名称 DESC