日期:2014-05-18  浏览次数:20613 次

请教一个查询的SQL,弄了半天弄不出来。来请教高手
请教一个查询的SQL,弄了半天弄不出来。来请教高手
SQL code

tba
id Savepath
1,  xxx  
2,  xxx  
3,  xxx  
4,  xxx  
5,  xxx  
tbb
id,aid
11,1
12,4
13,4
14,5
--想要的结果
id, Savepath,count(指tbb的记录条数)
1,  xxx    ,1   
2,  xxx    ,0   
3,  xxx    ,0   
4,  xxx    ,2  
5,  xxx    ,1  

(下面的总出错因为Savepath是TEXT型,总是出错:
不能比较或排序 text、ntext 和 image 数据类型,除非使用 IS NULL 或 LIKE 运算符错误)
select a.id,a.savepath,count(b.aid) from tba a
inner join tbb b on b.aid=a.id
group by a.id,a.savepath,b.count



------解决方案--------------------
SQL code
create table tba(id int,Savepath text)
insert into tba select 1,  'xxx'  
insert into tba select 2,  'xxx'  
insert into tba select 3,  'xxx'  
insert into tba select 4,  'xxx'  
insert into tba select 5,  'xxx'  
create table tbb(id int,aid int)
insert into tbb select 11,1
insert into tbb select 12,4
insert into tbb select 13,4
insert into tbb select 14,5
go
select *,(select count(*) from tbb where aid=a.id)ct
from tba a
id          Savepath             ct
----------- ----------------- -----------
1           xxx                  1
2           xxx                  0
3           xxx                  0
4           xxx                  2
5           xxx                  1

(5 行受影响)


go
drop table tba,tbb

------解决方案--------------------
探讨
5楼那样会和我同样出错的,Savepath是TEXT型不能那样GROUP BY

------解决方案--------------------
探讨

我想用5楼这样的,主要是解决TEXT型GROUP BY出错就可以,谢谢
SQL code
select a.ID,a.Savepath,count(b.ID) as con from tba as a left join tbb as b on a.ID=b.aid
group by a.ID,a.Savepath