如何改进这条SQL语句？

Question

我现在发现我的语句存在以下问题，首先是原始语句：select A.thetypename, isnull(B.total,0) as inventory from typeTable  A  left join(select thetypename, sum(case when operation = '入货' then goodsnum else - goodsnum end) + isnull((select jiandangkucun from typeTable where thetypename=t.thetypename),0)  as total from ruhuo t  group by thetypename ) B on A.thetypename=B.thetypename   里面的子句：
select thetypename, sum(case when operation = '入货' then goodsnum else - goodsnum end) + isnull((select jiandangkucun from typeTable where thetypename=t.thetypename),0)  as total from ruhuo t  group by thetypename  
如果在ruhuo这个表里面没有相应thetypename的记录，那么即使 select jiandangkucun from typeTable where thetypename=t.thetypename
存在大于0的数值，也是直接返回0，因此不符合我统计的要求，应该如何写才能达到在ruhuo表里即使不存在记录，那么也应该把jiandangkucun这个数字加上，而不是直接返回 0

Answer 1

如果在ruhuo这个表里面没有相应thetypename的记录，那么即使 select jiandangkucun from typeTable where thetypename=t.thetypename 
存在大于0的数值，也是直接返回0，因此不符合我统计的要求，应该如何写才能达到在ruhuo表里即使不存在记录，那么也应该把jiandangkucun这个数字加上，而不是直接返回 0存在的话还返回0?
要不你改连接查询吧,

Answer 2

select T.thetypename,
 sum(case when T.operation = '入货' then T.goodsnum else - T.goodsnum end) + ISNULL(T1.jiandangkucun,0)
FROM ruhuo T 
LEFT JOIN typeTable T1 ON  thetypename=t.thetypename 
group by T.thetypename  

Answer 3

例如查询'轴承' ，在ruhuo表里面没有‘轴承’对应的记录，虽然轴承在typeTable表里面的 jiandangkucun 数值是 10，但是最终返回的结果是库存 0

Answer 4

提示 t1.jiandangkucun 无效

Answer 5

在我原始的sql语句上面不能更改吗？

Answer 6

select thetypename, sum(case when operation = '入货' then goodsnum else - goodsnum end) + isnull((select jiandangkucun from typeTable where thetypename=t.thetypename),0)  as total from ruhuo t  group by thetypename  ------try
select thetypename, sum(case when operation = '入货' then goodsnum else - goodsnum end) + coalesce((select jiandangkucun from typeTable where thetypename=t.thetypename),(select max(jiandangkucun) from typetable),0)  as total from ruhuo t  group by thetypename  

Answer 7

------try
select thetypename, sum(case when operation = '入货' then goodsnum else - goodsnum end) + coalesce((select jiandangkucun from typeTable where thetypename=t.thetypename),(select max(jiandangkucun) from typetable),0)  as total 
from ruhuo t  group by thetypename  

Answer 8

select T.thetypename,
 sum(case when T.operation = '入货' then T.goodsnum else - T.goodsnum end) 
+ ISNULL(T.jiandangkucun,0)
FROM ruhuo T 
LEFT JOIN typeTable T1 ON  T1.thetypename=t.thetypename 
group by T.thetypename  

Answer 9

select T.thetypename, sum(case when T.operation = '入货' then T.goodsnum else - T.goodsnum end) + ISNULL(T1.jiandangkucun,0) FROM ruhuo T LEFT JOIN typeTable T1 ON  thetypename=t.thetypename group by T.thetypename  主要是提示 该列既不包含在聚合函数中也不包含在group by 语句中，jiandangkucun是属于 typeTable 表的

Answer 10

select T.thetypename,
 sum(case when T.operation = '入货' then T.goodsnum + ISNULL(T1.jiandangkucun,0) else - T.goodsnum + ISNULL(T1.jiandangkucun,0) end) FROM ruhuo T 
LEFT JOIN typeTable T1 ON  T1.thetypename=t.thetypename 
group by T.thetypename 

Answer 11

你的方法我试过了，对于在typetable存在的名称，但是由于在ruhuo表里面还没有记录，最终结果也是返回0，而未能取得jiandangkucun 这个建档库存值作为库存返回

Answer 12

我的表结构 
CREATE TABLE [dbo].[typetable] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[theTypeName] [nvarchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[ruhuo] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
 
[theTypeName] [nvarchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
[compnyName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[theUnit] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[goodsNum] [numeric](18, 4) NULL ,
 

Answer 13

CREATE TABLE [dbo].[ruhuo] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
 
[theTypeName] [nvarchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
[compnyName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[theUnit] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[goodsNum] [numeric](18, 4) NULL ,
[operation] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,

) ON [PRIMARY]
GO

Answer 14

ruhuo 表
theTypeName    compnyName   theUnit  goodsNum  operation
黄瓜               日盛           条         23          进仓
冬瓜                               条          2          出仓
酱油               好又多          瓶          15         进仓     typetable表id  thetypename
1     黄瓜
2     笔记本
3      橘子
4      酱油
4      

Answer 15

ruhuo 表 theTypeName    compnyName  theUnit  goodsNum  operation 
黄瓜              日盛          条      23          入货 
冬瓜                           条       2          出货 
酱油              好又多        瓶      15          入货    
------------------------------------------------------------
typetable表 id  thetypename   jiandangkucun(建档库存)
1    黄瓜              50
2    笔记本             95
3    橘子               0 
4    酱油               30  
     

Answer 16

----------------------------------------------------------------
-- Author  :SQL77(只为思齐老)
-- Date    :2010-01-19 21:07:25
-- Version:
--      Microsoft SQL Server  2000 - 8.00.194 (Intel X86) 
-- Aug  6 2000 00:57:48 
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：# ruhuo
if object_id('tempdb.dbo.#ruhuo') is not null drop table #ruhuo
go 
create table #ruhuo([theTypeName] varchar(4),[compnyName] varchar(6),[theUnit] varchar(2),[goodsNum] int,[operation] varchar(4))
insert #ruhuo
select '黄瓜','日盛','条',23,'入货' union all
select '冬瓜',null,'条',2,'出货' union all
select '酱油','好又多','瓶',15,'入货'
--> 测试数据：#typetable
if object_id('tempdb.dbo.#typetable') is not null drop table #typetable
go 
create table #typetable([id] int,[thetypename] varchar(6),[jiandangkucun] int)
insert #typetable
select 1,'黄瓜',50 union all
select 2,'笔记本',95 union all
select 3,'橘子',0 union all
select 4,'酱油',30
--------------开始查询--------------------------
select 
t.id,t.thetypename,
sum(case when operation='入货'
 then jiandangkucun+ ISNULL(goodsNum,0)  else jiandangkucun-ISNULL(goodsNum,0) end )as 库存 
FROM #typetable T LEFT JOIN #ruhuo R ON T.thetypename=R.thetypenameGROUP BY t.id,t.thetypename
----------------结果----------------------------
/* （所影响的行数为 3 行）
（所影响的行数为 4 行）id          thetypename 库存          
----------- ----------- ----------- 
2           笔记本         95
1           黄瓜          73
4           酱油          45
3           橘子          0（所影响的行数为 4 行）
*/

Answer 17

to  SQL77:你这样写，其实还是有点问题，如果对于同一名字，例如：黄瓜，存在多条进出记录，那么这样就把建档库存多次累加，请参考我按照你写法的测试
--------------------------------------------------------------------------------
if object_id('tempdb.dbo.#ruhuo') is not null drop table #ruhuo
go 
create table #ruhuo([theTypeName] varchar(4),[compnyName] varchar(6),[theUnit] varchar(2),[goodsNum] int,[operation] varchar(4))
insert #ruhuo
select '黄瓜','日盛','条',23,'入货' union all
select '黄瓜','日盛','条',6,'入货' union all
select '黄瓜','日盛','条',6,'出货' union all
select '冬瓜',null,'条',2,'出货' union all
select '酱油','好又多','瓶',15,'入货'
--> 测试数据：#typetable
if object_id('tempdb.dbo.#typetable') is not null drop table #typetable
go 
create table #typetable([id] int,[thetypename] varchar(6),[jiandangkucun] int)
insert #typetable
select 1,'黄瓜',50 union all
select 2,'笔记本',95 union all
select 3,'橘子',0 union all
select 4,'酱油',30select 
t.id,t.thetypename,
sum(case when operation='入货'
 then jiandangkucun+ ISNULL(goodsNum,0)  else jiandangkucun-ISNULL(goodsNum,0) end )as 库存 
FROM #typetable T LEFT JOIN #ruhuo R ON T.thetypename=R.thetypenameGROUP BY t.id,t.thetypename
--------------------------------------------------------------
2 笔记本 95
1 黄瓜 173
4 酱油 45
3 橘子 0

Answer 18

to  SQL77:你这样写，其实还是有点问题，如果对于同一名字，例如：黄瓜，存在多条进出记录，那么这样就把建档库存多次累加，请参考我按照你写法的测试 
if object_id('tempdb.dbo.#ruhuo') is not null drop table #ruhuo
go 
create table #ruhuo([theTypeName] varchar(4),[compnyName] varchar(6),[theUnit] varchar(2),[goodsNum] int,[operation] varchar(4))
insert #ruhuo
select '黄瓜','日盛','条',23,'入货' union all
select '黄瓜','日盛','条',6,'入货' union all
select '黄瓜','日盛','条',6,'出货' union all
select '冬瓜',null,'条',2,'出货' union all
select '酱油','好又多','瓶',15,'入货'
--> 测试数据：#typetable
if object_id('tempdb.dbo.#typetable') is not null drop table #typetable
go 
create table #typetable([id] int,[thetypename] varchar(6),[jiandangkucun] int)
insert #typetable
select 1,'黄瓜',50 union all
select 2,'笔记本',95 union all
select 3,'橘子',0 union all
select 4,'酱油',30select 
t.id,t.thetypename,
sum(case when operation='入货'
 then jiandangkucun+ ISNULL(goodsNum,0)  else jiandangkucun-ISNULL(goodsNum,0) end )as 库存 
FROM #typetable T LEFT JOIN #ruhuo R ON T.thetypename=R.thetypenameGROUP BY t.id,t.thetypename
-------------------------------------------------------------
 
2 笔记本 95 
1 黄瓜 173 
4 酱油 45 
3 橘子 0 

Answer 19

if object_id('tempdb.dbo.#ruhuo') is not null drop table #ruhuo 
go 
create table #ruhuo([theTypeName] varchar(4),[compnyName] varchar(6),[theUnit] varchar(2),[goodsNum] int,[operation] varchar(4)) 
insert #ruhuo 
select '黄瓜','日盛','条',23,'入货' union all 
select '黄瓜','日盛','条',6,'入货' union all 
select '黄瓜','日盛','条',6,'出货' union all 
select '冬瓜',null,'条',2,'出货' union all 
select '酱油','好又多','瓶',15,'入货' 
--> 测试数据：#typetable 
if object_id('tempdb.dbo.#typetable') is not null drop table #typetable 
go 
create table #typetable([id] int,[thetypename] varchar(6),[jiandangkucun] int) 
insert #typetable 
select 1,'黄瓜',50 union all 
select 2,'笔记本',95 union all 
select 3,'橘子',0 union all 
select 4,'酱油',30 select 
t.id,t.thetypename, 
SUM(DISTINCT jiandangkucun)+
SUM( case when operation='入货' then ISNULL(goodsNum,0)  else -ISNULL(goodsNum,0) end )AS 库存 
FROM #typetable T LEFT JOIN #ruhuo R ON T.thetypename=R.thetypename GROUP BY t.id,t.thetypename ORDER BY ID/*（所影响的行数为 5 行）
（所影响的行数为 4 行）id          thetypename 库存          
----------- ----------- ----------- 
1           黄瓜          73
2           笔记本         95
3           橘子          0
4           酱油          45（所影响的行数为 4 行）*/

Answer 20

if object_id('tempdb.dbo.#ruhuo') is not null drop table #ruhuo 
go 
create table #ruhuo([theTypeName] varchar(4),[compnyName] varchar(6),[theUnit] varchar(2),[goodsNum] int,[operation] varchar(4)) 
insert #ruhuo 
select '黄瓜','日盛','条',23,'入货' union all 
select '黄瓜','日盛','条',6,'入货' union all 
select '黄瓜','日盛','条',6,'出货' union all 
select '冬瓜',null,'条',2,'出货' union all 
select '酱油','好又多','瓶',15,'入货' 
--> 测试数据：#typetable 
if object_id('tempdb.dbo.#typetable') is not null drop table #typetable 
go 
create table #typetable([id] int,[thetypename] varchar(6),[jiandangkucun] int) 
insert #typetable 
select 1,'黄瓜',50 union all 
select 2,'笔记本',95 union all 
select 3,'橘子',0 union all 
select 4,'酱油',30 select 
t.id,t.thetypename, 
 jiandangkucun+ISNULL(C,0) AS 库存
--SUM( case when operation='入货' then ISNULL(goodsNum,0)  else -ISNULL(goodsNum,0) end )AS 库存 
FROM #typetable T LEFT JOIN 
(SELECT theTypeName,
SUM( case when operation='入货' 
then ISNULL(goodsNum,0)  else -ISNULL(goodsNum,0) end )AS C
FROM #ruhuo R GROUP BY  theTypeName) AS R ON T.thetypename=R.thetypename --GROUP BY t.id,t.thetypename 
ORDER BY ID/*（所影响的行数为 5 行）
（所影响的行数为 4 行）id          thetypename 库存          
----------- ----------- ----------- 
1           黄瓜          73
2           笔记本         95
3           橘子          0
4           酱油          45（所影响的行数为 4 行）
*/上面两种办法都行