有两张表:
tableA (id,typeA,typeB,scode)
-----------------------------
1 服装 上衣 A01
2 服装 裤子 A02
3 家电 手电筒 T17
4 食品 三鹿 S01tableB (starttime,scode)
------------------------
9-13 A01
9-13 A01
9-13 A02
9-13 T17
9-13 A02
9-13 A01
9-13 A01
9-13 A02
9-13 A01
9-13 T17
9-13 T17然后查询的结果是
typeA typeB scode count
---------------------------------------
服装 上衣 A01 5
服装 裤子 A02 3
家电 手电筒 T17 3
食品 三鹿 S01 0
tableA (id,typeA,typeB,scode)
-----------------------------
1 服装 上衣 A01
2 服装 裤子 A02
3 家电 手电筒 T17
4 食品 三鹿 S01tableB (starttime,scode)
------------------------
9-13 A01
9-13 A01
9-13 A02
9-13 T17
9-13 A02
9-13 A01
9-13 A01
9-13 A02
9-13 A01
9-13 T17
9-13 T17然后查询的结果是
typeA typeB scode count
---------------------------------------
服装 上衣 A01 5
服装 裤子 A02 3
家电 手电筒 T17 3
食品 三鹿 S01 0
from tablea a left join tableB b
on a.scode=b.scode
group by a.typeA,a.TypeB,a.scode
from tablea a left join tableB b
on a.scode=b.scode and b.starttime='9-13'
group by a.typeA,a.TypeB,a.scode
from tableA left join tableB
on tableA.scode=tableB.scode
group by typeA,typeB,A.scode
都慢,不能直接a.scode)=b.scode吗?
left join
(select scode , count(*) cnt from B group by scode) m
on a.scode = m.scode
相对来说:rtrim(a.scode)=rtrim(b.scode)快点.其实直接比较就行了.
左边空格会影响比较结果,右边你用varchar就不用去空格了
declare @tableA table (id int ,typeA varchar(10),typeB varchar(10),scode varchar(10))
-----------------------------
insert @tableA select 1, '服装' , '上衣' , 'A01'
insert @tableA select 2, '服装' , '裤子', 'A02'
insert @tableA select 3, '家电' , '手电筒', 'T17'
insert @tableA select 4, '食品' , ' 三鹿' , 'S01'
declare @tableB table (starttime varchar(10),scode varchar(10))
------------------------
insert @tableB select '9-13' , 'A01'
insert @tableB select '9-13' , 'A01'
insert @tableB select '9-13' , 'A02'
insert @tableB select '9-13' , 'T17'
insert @tableB select '9-13' , 'A02'
insert @tableB select '9-13' , 'A01'
insert @tableB select '9-13' , 'A01'
insert @tableB select '9-13' , 'A02'
insert @tableB select '9-13' , 'A01'
insert @tableB select '9-13' , 'T17'
insert @tableB select '9-13' , 'T17'
select a.typeA,a.typeB,a.scode ,isnull(b.[count],0) from @tableA a left join (select scode, count(scode)[Count] from @tableB group by scode) b on a.scode=b.scode
typeA typeB scode
---------- ---------- ---------- -----------
服? 上衣 A01 5
服? ?子 A02 3
家? 手?筒 T17 3
食品 三鹿 S01 0(4 row(s) affected)
from tableA A,tableB B
where A.scode=B.scode
group by typeA,typeB,A.scode