请教left outer join 的where语句写法

[marklr]

我只是想显示 t. alarmNum字段的数值大于 t.jiandangkucun + isnull(r.c, 0) as kucun 字段的记录，于是我如此写

select t.id,t.thetypename, t. alarmNum,t.jiandangkucun + isnull(r.c, 0) as kucun from typetable t left outer join (select thetypename, sum(case when operation = '入货' then isnull(goodsnum, 0) else - isnull(goodsnum, 0) end) as c from ruhuo r group by thetypename) r on t.thetypename = r.thetypename where (t.disFlag = 1 and (t.jiandangkucun + isnull(r.c, 0))<t.alarmNum )

但发现 sql server 2000 自动把我的语句改成以下语句，而且出来的结果也不正确，把 alarmNum小于 kucun的记录也同时显示出来，请问正确的sql语句应该如何写

SELECT t.id, t.theTypeName, t.alarmNum, t.jianDangKuCun + ISNULL(r.c, 0) AS kucun
FROM typetable t LEFT OUTER JOIN
(SELECT thetypename,
SUM(CASE WHEN operation = '入货' THEN isnull(goodsnum, 0)
ELSE - isnull(goodsnum, 0) END) AS c
FROM ruhuo r
GROUP BY thetypename) r ON t.alarmNum > t.jianDangKuCun + ISNULL(r.c, 0) AND
t.alarmNum > t.jianDangKuCun + ISNULL(r.c, 0) AND
t.theTypeName = r.thetypename
WHERE (t.disFlag = 1)

[marklr]

没人会吗，原始的语句如下
select t.id,t.thetypename, t. alarmNum,t.jiandangkucun + isnull(r.c, 0) as kucun from typetable t left outer join (select thetypename, sum(case when operation = '入货' then isnull(goodsnum, 0) else - isnull(goodsnum, 0) end) as c from ruhuo r group by thetypename) r on t.thetypename = r.thetypename where t.disFlag = 1

我只是想按照以上语句得到结果之后再筛选出 t. alarmNum 大于 t.kucun 数值 的记录

[v1ctory1216]

那你就在4楼的语句后加条件呀

select t.id,t.thetypename, t. alarmNum,t.jiandangkucun + isnull(r.c, 0) as kucun 
from typetable t left outer join 
(select thetypename, sum(case when operation = '入货' then isnull(goodsnum, 0) else - isnull(goodsnum, 0) end) as c 
from ruhuo r 
group by thetypename) r 
on t.thetypename = r.thetypename 
where t.disFlag = 1 and t.alarmNum > t.jianDangKuCun + ISNULL(r.c, 0)

[marklr]

[Quote=引用 5 楼 v1ctory1216 的回复:]

那你就在4楼的语句后加条件呀
SQL code

select t.id,t.thetypename, t. alarmNum,t.jiandangkucun + isnull(r.c, 0) as kucun
from typetable t left outer join
(select thetypename, sum(case when operation = '入货' then……
[/Quote]

加了之后 ，sql server 2000 自动把语句转化为以下语句，我都有点莫名其妙的，不知道为什么会这样？

SELECT t.id, t.theTypeName, t.alarmNum, t.jianDangKuCun + ISNULL(r.c, 0) AS kucun
FROM typetable t LEFT OUTER JOIN
(SELECT thetypename,
SUM(CASE WHEN operation = '入货' THEN isnull(goodsnum, 0)
ELSE - isnull(goodsnum, 0) END) AS c
FROM ruhuo r
GROUP BY thetypename) r ON t.alarmNum > t.jianDangKuCun + ISNULL(r.c, 0) AND
t.theTypeName = r.thetypename
WHERE (t.disFlag = 1)

[marklr]

where 条件 自动 转为 on 的条件，有点奇怪，sql server 帮我优化语句？

[marklr]

.......... 没人懂？

[老黎]

--实在没办法就试试这个吧
--多一层嵌套
select * 
from 
(
select t.id,t.thetypename, t. alarmNum,t.jiandangkucun + isnull(r.c, 0) as kucun 
from typetable t 
left outer join 
(select 
    thetypename, 
    sum(case when operation = '入货' then isnull(goodsnum, 0) else - isnull(goodsnum, 0) end) as c 
from ruhuo r 
group by thetypename
) r 
on t.thetypename = r.thetypename 
where t.disFlag = 1
) a
where alarmNum > kucun 

[cxmcxm]

对left out join
on 后的条件只是连接条件,左边的表记录一定全部出来, on后的条件只会过渡右边的表
要过滤左边的记录,在where 中指定条件

[老黎]

left join 中，连接字段放在on后
筛选条件放在where 后

[dawugui]

你先看看下面的原理.

/*
标题：SQL中on条件与where条件的区别
作者：爱新觉罗·毓华 
时间：2008-07-14
地点：新疆乌鲁木齐
*/

数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户。 
在使用left jion时，on和where条件的区别如下：

1、on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。

2、where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

假设有两张表：

表1：tab1 
id size 
1  10 
2  20 
3  30 
表2：tab2 
size name 
10   AAA 
20   BBB 
20   CCC 

两条SQL:
1、select * from tab1 left join tab2 on tab1.size = tab2.size where tab2.name='AAA'
2、select * from tab1 left join tab2 on tab1.size = tab2.size and tab2.name='AAA'

第一条SQL的过程：
1、中间表
on条件: 
tab1.size = tab2.size 
tab1.id tab1.size tab2.size tab2.name 
1 10 10 AAA 
2 20 20 BBB 
2 20 20 CCC 
3 30 (null) (null) 
2、再对中间表过滤
where 条件：
tab2.name='AAA'
tab1.id tab1.size tab2.size tab2.name 
1 10 10 AAA 

第二条SQL的过程：
1、中间表
on条件: 
tab1.size = tab2.size and tab2.name='AAA'
(条件不为真也会返回左表中的记录) tab1.id tab1.size tab2.size tab2.name 
1 10 10 AAA 
2 20 (null) (null) 
3 30 (null) (null) 
 
其实以上结果的关键原因就是left join,right join,full join的特殊性，
不管on上的条件是否为真都会返回left或right表中的记录，full则具有left和right的特性的并集。 
而inner jion没这个特殊性，则条件放在on中和where中，返回的结果集是相同的。

[marklr]

那么，现在出来的结果还是不对啊 ，无论放在 where还是 on的后面，出来的结果把 kucun> alarmNum都显示出来了

SELECT t.id, t.theTypeName, t.alarmNum, t.jianDangKuCun + ISNULL(r.c, 0) AS kucun
FROM typetable t LEFT OUTER JOIN
(SELECT thetypename,
SUM(CASE WHEN operation = '入货' THEN isnull(goodsnum, 0)
ELSE - isnull(goodsnum, 0) END) AS c
FROM ruhuo r
GROUP BY thetypename) r ON t.alarmNum > t.jianDangKuCun + ISNULL(r.c, 0) AND
t.theTypeName = r.thetypename
WHERE (t.disFlag = 1)

[marklr]

[Quote=引用 10 楼 cxmcxm 的回复:]

对left out join
on 后的条件只是连接条件,左边的表记录一定全部出来, on后的条件只会过渡右边的表
要过滤左边的记录,在where 中指定条件
[/Quote]

你先看清楚我说的情况，你说的我明白

[sych888]

[Quote=引用 2 楼 dawugui 的回复:]
你先看看下面的原理.

SQL code
/*
标题：SQL中on条件与where条件的区别
作者：爱新觉罗·毓华
时间：2008-07-14
地点：新疆乌鲁木齐
*/

数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户。
在使用left jion时，on和where条件的区别如下：

1、on条件是在生成临时表时使用的……
[/Quote]
学习了.......