3张表A,B,C,
3张表得字段结构如下:
A:a,b,c
B:a,d,e
C:a,f,g
其中A.a=B.a and A.a=C.a and B.a=C.a
我分别对三张表做了三个查询,都还有Group by 子句
得出得结果分别是
select count(b),a,c from A group by a
select count(d),a,e from B group by a
select count(f),a,g from C group by a
现在我希望结果返回得字段中
a,count(b),count(d),count(f),c,e,g
不知道如何合并我尝试:
select A.a,count(b),count(d),count(f),c,e,g
from A,B,C
where A.a=B.a and A.a=C.a and B.a=C.a
group by A.a但是返回得结果不对。谢谢各位
3张表得字段结构如下:
A:a,b,c
B:a,d,e
C:a,f,g
其中A.a=B.a and A.a=C.a and B.a=C.a
我分别对三张表做了三个查询,都还有Group by 子句
得出得结果分别是
select count(b),a,c from A group by a
select count(d),a,e from B group by a
select count(f),a,g from C group by a
现在我希望结果返回得字段中
a,count(b),count(d),count(f),c,e,g
不知道如何合并我尝试:
select A.a,count(b),count(d),count(f),c,e,g
from A,B,C
where A.a=B.a and A.a=C.a and B.a=C.a
group by A.a但是返回得结果不对。谢谢各位
select count(d),a,e from B group by a
select count(f),a,g from C group by a楼主这种语法有问题。
select count(b),a,c from A group by a如果仅对a分组,那么c也要做聚合处理。其它类似。
都是没有得
select count(b),a from A group by a
select count(d),a from B group by a
select count(f),a from C group by a
最后显示
a,count(b),count(d),count(f)
(select count(b) from A where a=tp.a) as cnt_A,
(select count(d) from B where a=tp.a) as cnt_B,
(select count(f) from C where a=tp.a) as cnt_C
from
(
select a from A group by a
union
select a from B group by a
union
select a from C group by a
) tp
服务器: 消息 116,级别 16,状态 1,行 1
当没有用 EXISTS 引入子查询时,在选择列表中只能指定一个表达式。
create table A(a int,b int)
create table B(a int,D int)
create table C(a int,F int)
insert into A select 1,1
insert into A select 1,2
insert into B select 2,1
insert into B select 2,2
insert into C select 3,1
insert into C select 3,2select a,
(select count(b) from A where a=tp.a) as cnt_A,
(select count(d) from B where a=tp.a) as cnt_B,
(select count(f) from C where a=tp.a) as cnt_C
from
(
select a from A group by a
union
select a from B group by a
union
select a from C group by a
) tp
drop table A,B,C
create table #B(a int,D int)
create table #C(a int,F int)
insert into #A select 1,1
insert into #A select 1,2
insert into #B select 2,1
insert into #B select 2,2
insert into #C select 3,1
insert into #C select 3,2select a,
(select count(b) from #A where a=tp.a) as cnt_A,
(select count(d) from #B where a=tp.a) as cnt_B,
(select count(f) from #C where a=tp.a) as cnt_C
from
(
select a from #A group by a
union
select a from #B group by a
union
select a from #C group by a
) tp
drop table #A,#B,#C
这种方式,该如何处理,谢谢
create table #B(a int,D int,e int)
create table #C(a int,F int,g int)
insert into #A select 1,1,1
insert into #A select 1,2,3
insert into #B select 2,1,1
insert into #B select 2,2,3
insert into #C select 3,1,1
insert into #C select 3,2,3select a,
(select count(b) from #A where a=tp.a) as cnt_A,
(select count(d) from #B where a=tp.a) as cnt_B,
(select count(f) from #C where a=tp.a) as cnt_C,
(select top 1 c from #A where a=tp.a) as c,
(select top 1 e from #B where a=tp.a) as e,
(select top 1 g from #C where a=tp.a) as g
from
(
select a from #A group by a
union
select a from #B group by a
union
select a from #C group by a
) tp
drop table #A,#B,#C
(select top 1 e from #B where a=tp.a) as e,
(select top 1 g from #C where a=tp.a) as g,中得c,e,g设置一下条件
比如设置c都是大于5得,如何弄呢?