select * from ( select table1.AutoNum,table1.StartNum,table1.Counter,table1.CreateDate, (select count(*) from table2 where StoreNum=table1.AutoNum and Account=12 and ProvinceAgencyID is null) as count1, (select count(*) from table2 where StoreNum=table1.AutoNum and Account=12 and ProvinceAgencyID is not null) as count2, (select count(*) from table2 where StoreNum=table1.AutoNum and Account=12 and Status=0) as count3 from table1 ) t 最简单的一种做法
?? select a.AutoNum,a.StartNum,a.Counter,a.CreateDate, count1,count2,count3 from table1 a, (select StoreNum, count(*) count1 from table2 where Account=12 and ProvinceAgencyID is null group by StoreNum) b, (select StoreNum, count(*) count2 from table2 where Account=12 and ProvinceAgencyID is not null group by StoreNum) c, (select StoreNum,count(*) count3 from table2 where Account=12 and Status=0 group by StoreNum) d where a.AutoNum=b.StoreNum and a.AutoNum=c.StoreNum and a.AutoNum=d.StoreNum
是这个意思吗? select table1.AutoNum,table1.StartNum,table1.Counter,table1.CreateDate,tb1.count1,tb2.count2,tb3.count3 from table1 , (select count(*)as count1 from table2 where StoreNum=table1.AutoNum and Account=12 and ProvinceAgencyID is null) As tb1, (select count(*)as count2 from table2 where StoreNum=table1.AutoNum and Account=12 and ProvinceAgencyID is not null) as tb2, (select count(*)as count3 from table2 where StoreNum=table1.AutoNum and Account=12 and Status=0) as tb3
这个是可以显示,但是我最需要的是select * 要*为具体的字段名称或别名,有没有办法呢
那就在嵌套下select table1.AutoNum,table1.StartNum,table1.Counter,table1.CreateDate,count1,count2,count3 from ( select table1.AutoNum,table1.StartNum,table1.Counter,table1.CreateDate, (select count(*) from table2 where StoreNum=table1.AutoNum and Account=12 and ProvinceAgencyID is null) as count1, (select count(*) from table2 where StoreNum=table1.AutoNum and Account=12 and ProvinceAgencyID is not null) as count2, (select count(*) from table2 where StoreNum=table1.AutoNum and Account=12 and Status=0) as count3 from table1 ) table1
select a.AutoNum,a.StartNum,a.Counter,a.CreateDate, count1,count2,count3 from table1 a left join (select StoreNum, count(*) count1 from table2 where Account=12 and ProvinceAgencyID is null group by StoreNum) b on a.AutoNum = b.StoreNum left join (select StoreNum, count(*) count2 from table2 where Account=12 and ProvinceAgencyID is not null group by StoreNum) c left join on b.StoreNum = c.StoreNum left join (select StoreNum,count(*) count3 from table2 where Account=12 and Status=0 group by StoreNum) d c.StoreNum = d.StoreNum 你测一下应该就能解决你的问题了
select table1.AutoNum as 名字1,table1.StartNum as 名字2 ,table1.Counter as 名字3,table1.CreateDate as 名字4,count1 as 名字5,count2 as 名字5,count3 as 名字6 from ( select table1.AutoNum,table1.StartNum,table1.Counter,table1.CreateDate, (select count(*) from table2 where StoreNum=table1.AutoNum and Account=12 and ProvinceAgencyID is null) as count1, (select count(*) from table2 where StoreNum=table1.AutoNum and Account=12 and ProvinceAgencyID is not null) as count2, (select count(*) from table2 where StoreNum=table1.AutoNum and Account=12 and Status=0) as count3 from table1 ) table1
select table1.AutoNum,table1.StartNum,table1.Counter,table1.CreateDate, count1=sum(case when ProvinceAgencyID is null then 1 else 0), count2=sum(case when ProvinceAgencyID is not null then 1 else 0), count3=sum(case when Status=0 then 1 else 0) from table1,table2 where table1.autonum=table2.storenum and Account=12 把你的语句简化下
*
from
(
select table1.AutoNum,table1.StartNum,table1.Counter,table1.CreateDate,
(select count(*) from table2 where StoreNum=table1.AutoNum and Account=12 and ProvinceAgencyID is null) as count1,
(select count(*) from table2 where StoreNum=table1.AutoNum and Account=12 and ProvinceAgencyID is not null) as count2,
(select count(*) from table2 where StoreNum=table1.AutoNum and Account=12 and Status=0) as count3
from table1
) t
最简单的一种做法
select a.AutoNum,a.StartNum,a.Counter,a.CreateDate, count1,count2,count3
from table1 a,
(select StoreNum, count(*) count1 from table2 where Account=12 and ProvinceAgencyID is null group by StoreNum) b,
(select StoreNum, count(*) count2 from table2 where Account=12 and ProvinceAgencyID is not null group by StoreNum) c,
(select StoreNum,count(*) count3 from table2 where Account=12 and Status=0 group by StoreNum) d
where a.AutoNum=b.StoreNum and a.AutoNum=c.StoreNum and a.AutoNum=d.StoreNum
是这个意思吗?
select table1.AutoNum,table1.StartNum,table1.Counter,table1.CreateDate,tb1.count1,tb2.count2,tb3.count3
from table1 ,
(select count(*)as count1 from table2 where StoreNum=table1.AutoNum and Account=12 and ProvinceAgencyID is null) As tb1,
(select count(*)as count2 from table2 where StoreNum=table1.AutoNum and Account=12 and ProvinceAgencyID is not null) as tb2,
(select count(*)as count3 from table2 where StoreNum=table1.AutoNum and Account=12 and Status=0) as tb3
from
(
select table1.AutoNum,table1.StartNum,table1.Counter,table1.CreateDate,
(select count(*) from table2 where StoreNum=table1.AutoNum and Account=12 and ProvinceAgencyID is null) as count1,
(select count(*) from table2 where StoreNum=table1.AutoNum and Account=12 and ProvinceAgencyID is not null) as count2,
(select count(*) from table2 where StoreNum=table1.AutoNum and Account=12 and Status=0) as count3
from table1
) table1
from table1 a left join
(select StoreNum, count(*) count1 from table2 where Account=12 and ProvinceAgencyID is null group by StoreNum) b on a.AutoNum = b.StoreNum left join
(select StoreNum, count(*) count2 from table2 where Account=12 and ProvinceAgencyID is not null group by StoreNum) c left join on b.StoreNum = c.StoreNum left join
(select StoreNum,count(*) count3 from table2 where Account=12 and Status=0 group by StoreNum) d
c.StoreNum = d.StoreNum 你测一下应该就能解决你的问题了
,table1.Counter as 名字3,table1.CreateDate as 名字4,count1 as 名字5,count2
as 名字5,count3 as 名字6
from
(
select table1.AutoNum,table1.StartNum,table1.Counter,table1.CreateDate,
(select count(*) from table2 where StoreNum=table1.AutoNum and Account=12 and ProvinceAgencyID is null) as count1,
(select count(*) from table2 where StoreNum=table1.AutoNum and Account=12 and ProvinceAgencyID is not null) as count2,
(select count(*) from table2 where StoreNum=table1.AutoNum and Account=12 and Status=0) as count3
from table1
) table1
count1=sum(case when ProvinceAgencyID is null then 1 else 0),
count2=sum(case when ProvinceAgencyID is not null then 1 else 0),
count3=sum(case when Status=0 then 1 else 0)
from table1,table2
where table1.autonum=table2.storenum and Account=12
把你的语句简化下