并且大家可以再试试把having 语句注释再与第一种对比,又有新发现:是否就是因为having语句才产生多一次循环 select left(a.name,1),Isnull(sum(a.value),0) as aValue,b.value as bValue,Isnull(sum(a.value),0) - b.value from #a a left outer join (select name,sum(value) as value from #b group by name) b on a.name = b.name group by left(a.name,1),b.value --having Isnull(sum(a.value),0) > Isnull(b.value,0)
create table #a(id int identity(1,1),name char(1),value int) create table #b(id int identity(1,1),name char(1),value int) set nocount on insert #a select 'a',1 union select 'b',20 union select 'c',3 union select 'a',2 insert #b select 'a',10 union select 'b',2 union select 'd',3 union select 'a',2 select a.name,Isnull(sum(a.value),0) as aValue,b.value as bValue,Isnull(sum(a.value),0) - b.value from (select left(name,1) as name,value from #a) a left outer join (select name,sum(value) as value from #b group by name) b on left(a.name,1) = left(b.name,1) group by a.name,b.value having Isnull(sum(a.value),0) > Isnull(b.value,0) select a.name,Isnull(sum(a.value),0) as aValue,b.value as bValue,Isnull(sum(a.value),0) - b.value from #a a left outer join (select name,sum(value) as value from #b group by name) b on a.name = b.name group by a.name,b.value having Isnull(sum(a.value),0) > Isnull(b.value,0) drop table #a,#b/*name aValue bValue ---- ----------- ----------- ----------- c 3 NULL NULL b 20 2 18name aValue bValue ---- ----------- ----------- ----------- c 3 NULL NULL b 20 2 18*/
我是LZ To 小草: 把上面的代码全面copy试试,结果是一样吗?To 潇洒老乌龟: 久仰大名!这是个例子,在实际就是需要left出了错,才发现这个问题.不知老乌龟有什么好的解释?
楼主看这个就明白了select left(a.name,1) as aname,b.name as bname,Isnull(sum(a.value),0) as aValue,b.value as bValue,Isnull(sum(a.value),0) - b.value from #a a left outer join (select name,sum(value) as value from #b group by name) b on left(a.name,1) = left(b.name,1) group by left(a.name,1),b.name,b.value having Isnull(sum(a.value),0) > Isnull(b.value,0) /* aname bname aValue bValue ----- ----- ----------- ----------- ----------- a b 3 2 1 a NULL 3 NULL NULL b b 20 2 18 b NULL 20 NULL NULL c b 3 2 1 c NULL 3 NULL NULL */
我刚刚执行的结果: aValue bValue ---- ----------- ----------- ----------- c 3 NULL NULL b 20 2 18(2 row(s) affected)name aValue bValue -------------------------------------------------- ----------- ----------- ----------- c 3 NULL NULL b 20 2 18(2 row(s) affected)
To 无枪狙击手: 小弟不才,左看右看上看下看也没看出其中的原因,请指教!To do熊: 我的结果是: 第一种 ------------------------------ c 3 NULL NULL b 20 2 18 a 3 12 -9 c 3 NULL NULL b 20 2 18 a 3 12 -9第二种 ----------------------------- c 3 NULL NULL b 20 2 18
我是LZ To 小草: 把上面的代码全面copy试试,结果是一样吗? To 潇洒老乌龟: 久仰大名!这是个例子,在实际就是需要left出了错,才发现这个问题.不知老乌龟有什么好的解释?----------你把结构,数据,结果拿出来看看.
create table #a(id int identity(1,1),name varchar(50),value int) create table #b(id int identity(1,1),name varchar(50),value int) select * from #a select * from #b insert #a select 'a',1 union select 'b',20 union select 'c',3 union select 'a',2 insert #b select 'a',10 union select 'b',2 union select 'd',3 union select 'a',2 --------------------- 乌龟,这不是有数据了么``` 楼主意思就是从上面的数据可以看出name和left(name,1)是等同的。、、 但按照name分组和left(name,1)分组时,为啥结果不一样? 俺也想知道
to 潇洒老乌龟: 数据真不好抓取,如果有兴趣深入讨论,你可以远程控制我的电脑 咱们就把#a当出货计划,#b当库存表吧。to All: 大家把#a中name = 'b'的value 改为2----------------------------------------- update #a set value = 2 where name = 'b'select left(a.name,1),Isnull(sum(a.value),0) as aValue,b.value as bValue,Isnull(sum(a.value),0) - b.value from #a a left outer join (select name,sum(value) as value from #b group by name) b on a.name = b.name group by left(a.name,1),b.value having Isnull(sum(a.value),0) > Isnull(b.value,0) aValue bValue ---- ----------- ----------- ----------- c 3 NULL NULL b 2 2 0 a 3 12 -9(所影响的行数为 3 行)这时与第一种相比,没有产生多冗余项. 欢迎大家继续讨论!!!
to 无枪狙击手,这是我的运行结果,跟你运行的结果不同:create table #a(id int identity(1,1),name char(1),value int) create table #b(id int identity(1,1),name char(1),value int) set nocount on insert #a select 'a',1 union select 'b',20 union select 'c',3 union select 'a',2 insert #b select 'a',10 union select 'b',2 union select 'd',3 union select 'a',2 select left(a.name,1) as aname,b.name as bname,Isnull(sum(a.value),0) as aValue,b.value as bValue,Isnull(sum(a.value),0) - b.value from #a a left outer join (select name,sum(value) as value from #b group by name) b on left(a.name,1) = left(b.name,1) group by left(a.name,1),b.name,b.value having Isnull(sum(a.value),0) > Isnull(b.value,0) ---------------------------------------------------------------------------- aname bname aValue bValue ----- ----- ----------- ----------- ----------- a a 3 12 -9 b b 20 2 18 c NULL 3 NULL NULL a a 3 12 -9 b b 20 2 18 c NULL 3 NULL NULL
直接执行楼主的代码! create table #a(id int identity(1,1),name varchar(50),value int) create table #b(id int identity(1,1),name varchar(50),value int) select * from #a select * from #b insert #a select 'a',1 union select 'b',20 union select 'c',3 union select 'a',2 insert #b select 'a',10 union select 'b',2 union select 'd',3 union select 'a',2 select left(a.name,1),Isnull(sum(a.value),0) as aValue,b.value as bValue,Isnull(sum(a.value),0) - b.value from #a a left outer join (select name,sum(value) as value from #b group by name) b on a.name = b.name group by left(a.name,1),b.value having Isnull(sum(a.value),0) > Isnull(b.value,0) select a.name,Isnull(sum(a.value),0) as aValue,b.value as bValue,Isnull(sum(a.value),0) - b.value from #a a left outer join (select name,sum(value) as value from #b group by name) b on a.name = b.name group by a.name,b.value having Isnull(sum(a.value),0) > Isnull(b.value,0)结果: c 3 NULL NULL b 20 2 18c 3 NULL NULL b 20 2 18
如果要用left函数,最好在加上字段名前后加上 ltrim(rtrim(字段名))确保正确性
--2005下 create table #a(id int identity(1,1),name varchar(50),value int) create table #b(id int identity(1,1),name varchar(50),value int) insert #a select 'a',1 union select 'b',20 union select 'c',3 union select 'a',2 insert #b select 'a',10 union select 'b',2 union select 'd',3 union select 'a',2 select left(a.name,1),Isnull(sum(a.value),0) as aValue,b.value as bValue,Isnull(sum(a.value),0) - b.value from #a a left outer join (select name,sum(value) as value from #b group by name) b on a.name = b.name group by left(a.name,1),b.value having Isnull(sum(a.value),0) > Isnull(b.value,0) select a.name,Isnull(sum(a.value),0) as aValue,b.value as bValue,Isnull(sum(a.value),0) - b.value from #a a left outer join (select name,sum(value) as value from #b group by name) b on a.name = b.name group by a.name,b.value having Isnull(sum(a.value),0) > Isnull(b.value,0) --执行结果: c 3 NULL NULL b 20 2 18c 3 NULL NULL b 20 2 18 两个就是一样的!
按前者分,2,20,200,...会分在同一组,按后者分则会在不同的组
'a' 是不是 left('a',1)呢?
上面#a,#b中的name中的数据只有一位,也就是说'a' = left('a',1),无论在group by加不加left函数按理应该都是一样的,但事实得出的结果是不一样的.
group by a.name,b.value 如果你的a.name只有一位,就是一样的.不过第一个要慢一些.如果不止一位,就不一样.
--大家可以看一下执行计划,第一种比第二种多一次循环--第一种比第二种多一次对表a(#a)计算标量
--针对以上的数据测试结果一样,LZ怎么说两个不一样呢?
select left(a.name,1),Isnull(sum(a.value),0) as aValue,b.value as bValue,Isnull(sum(a.value),0) - b.value
from #a a left outer join
(select name,sum(value) as value from #b group by name) b on a.name = b.name
group by left(a.name,1),b.value
--having Isnull(sum(a.value),0) > Isnull(b.value,0)
create table #b(id int identity(1,1),name char(1),value int)
set nocount on
insert #a
select 'a',1
union select 'b',20
union select 'c',3
union select 'a',2 insert #b
select 'a',10
union select 'b',2
union select 'd',3
union select 'a',2
select a.name,Isnull(sum(a.value),0) as aValue,b.value as bValue,Isnull(sum(a.value),0) - b.value
from (select left(name,1) as name,value from #a) a left outer join
(select name,sum(value) as value from #b group by name) b on left(a.name,1) = left(b.name,1)
group by a.name,b.value
having Isnull(sum(a.value),0) > Isnull(b.value,0) select a.name,Isnull(sum(a.value),0) as aValue,b.value as bValue,Isnull(sum(a.value),0) - b.value
from #a a left outer join
(select name,sum(value) as value from #b group by name) b on a.name = b.name
group by a.name,b.value
having Isnull(sum(a.value),0) > Isnull(b.value,0)
drop table #a,#b/*name aValue bValue
---- ----------- ----------- -----------
c 3 NULL NULL
b 20 2 18name aValue bValue
---- ----------- ----------- -----------
c 3 NULL NULL
b 20 2 18*/
To 小草:
把上面的代码全面copy试试,结果是一样吗?To 潇洒老乌龟:
久仰大名!这是个例子,在实际就是需要left出了错,才发现这个问题.不知老乌龟有什么好的解释?
from #a a left outer join
(select name,sum(value) as value from #b group by name) b on left(a.name,1) = left(b.name,1)
group by left(a.name,1),b.name,b.value
having Isnull(sum(a.value),0) > Isnull(b.value,0)
/*
aname bname aValue bValue
----- ----- ----------- ----------- -----------
a b 3 2 1
a NULL 3 NULL NULL
b b 20 2 18
b NULL 20 NULL NULL
c b 3 2 1
c NULL 3 NULL NULL
*/
select 'a',1
union select 'b',20 ----------上面#a,#b中的name中的数据只有一位,也就是说'a' = left('a',1),无论在group by加不加left函数按理应该都是一样的,但事实得出的结果是不一样的.-----------------------------
'20'算一位???
那结果应该是一样的,,,
aValue bValue
---- ----------- ----------- -----------
c 3 NULL NULL
b 20 2 18(2 row(s) affected)name aValue bValue
-------------------------------------------------- ----------- ----------- -----------
c 3 NULL NULL
b 20 2 18(2 row(s) affected)
小弟不才,左看右看上看下看也没看出其中的原因,请指教!To do熊:
我的结果是:
第一种
------------------------------
c 3 NULL NULL
b 20 2 18
a 3 12 -9
c 3 NULL NULL
b 20 2 18
a 3 12 -9第二种
-----------------------------
c 3 NULL NULL
b 20 2 18
To 小草:
把上面的代码全面copy试试,结果是一样吗? To 潇洒老乌龟:
久仰大名!这是个例子,在实际就是需要left出了错,才发现这个问题.不知老乌龟有什么好的解释?----------你把结构,数据,结果拿出来看看.
create table #b(id int identity(1,1),name varchar(50),value int)
select * from #a
select * from #b insert #a
select 'a',1
union select 'b',20
union select 'c',3
union select 'a',2 insert #b
select 'a',10
union select 'b',2
union select 'd',3
union select 'a',2
---------------------
乌龟,这不是有数据了么```
楼主意思就是从上面的数据可以看出name和left(name,1)是等同的。、、
但按照name分组和left(name,1)分组时,为啥结果不一样?
俺也想知道
数据真不好抓取,如果有兴趣深入讨论,你可以远程控制我的电脑
咱们就把#a当出货计划,#b当库存表吧。to All:
大家把#a中name = 'b'的value 改为2-----------------------------------------
update #a set value = 2 where name = 'b'select left(a.name,1),Isnull(sum(a.value),0) as aValue,b.value as bValue,Isnull(sum(a.value),0) - b.value
from #a a left outer join
(select name,sum(value) as value from #b group by name) b on a.name = b.name
group by left(a.name,1),b.value
having Isnull(sum(a.value),0) > Isnull(b.value,0) aValue bValue
---- ----------- ----------- -----------
c 3 NULL NULL
b 2 2 0
a 3 12 -9(所影响的行数为 3 行)这时与第一种相比,没有产生多冗余项.
欢迎大家继续讨论!!!
create table #b(id int identity(1,1),name char(1),value int)
set nocount on
insert #a
select 'a',1
union select 'b',20
union select 'c',3
union select 'a',2 insert #b
select 'a',10
union select 'b',2
union select 'd',3
union select 'a',2 select left(a.name,1) as aname,b.name as bname,Isnull(sum(a.value),0) as aValue,b.value as bValue,Isnull(sum(a.value),0) - b.value
from #a a left outer join
(select name,sum(value) as value from #b group by name) b on left(a.name,1) = left(b.name,1)
group by left(a.name,1),b.name,b.value
having Isnull(sum(a.value),0) > Isnull(b.value,0)
----------------------------------------------------------------------------
aname bname aValue bValue
----- ----- ----------- ----------- -----------
a a 3 12 -9
b b 20 2 18
c NULL 3 NULL NULL
a a 3 12 -9
b b 20 2 18
c NULL 3 NULL NULL
直接执行楼主的代码!
create table #a(id int identity(1,1),name varchar(50),value int)
create table #b(id int identity(1,1),name varchar(50),value int)
select * from #a
select * from #b insert #a
select 'a',1
union select 'b',20
union select 'c',3
union select 'a',2 insert #b
select 'a',10
union select 'b',2
union select 'd',3
union select 'a',2
select left(a.name,1),Isnull(sum(a.value),0) as aValue,b.value as bValue,Isnull(sum(a.value),0) - b.value
from #a a left outer join
(select name,sum(value) as value from #b group by name) b on a.name = b.name
group by left(a.name,1),b.value
having Isnull(sum(a.value),0) > Isnull(b.value,0) select a.name,Isnull(sum(a.value),0) as aValue,b.value as bValue,Isnull(sum(a.value),0) - b.value
from #a a left outer join
(select name,sum(value) as value from #b group by name) b on a.name = b.name
group by a.name,b.value
having Isnull(sum(a.value),0) > Isnull(b.value,0)结果:
c 3 NULL NULL
b 20 2 18c 3 NULL NULL
b 20 2 18
--2005下
create table #a(id int identity(1,1),name varchar(50),value int)
create table #b(id int identity(1,1),name varchar(50),value int) insert #a
select 'a',1
union select 'b',20
union select 'c',3
union select 'a',2 insert #b
select 'a',10
union select 'b',2
union select 'd',3
union select 'a',2 select left(a.name,1),Isnull(sum(a.value),0) as aValue,b.value as bValue,Isnull(sum(a.value),0) - b.value
from #a a left outer join
(select name,sum(value) as value from #b group by name) b on a.name = b.name
group by left(a.name,1),b.value
having Isnull(sum(a.value),0) > Isnull(b.value,0) select a.name,Isnull(sum(a.value),0) as aValue,b.value as bValue,Isnull(sum(a.value),0) - b.value
from #a a left outer join
(select name,sum(value) as value from #b group by name) b on a.name = b.name
group by a.name,b.value
having Isnull(sum(a.value),0) > Isnull(b.value,0)
--执行结果:
c 3 NULL NULL
b 20 2 18c 3 NULL NULL
b 20 2 18
两个就是一样的!