select id=identity(int,1,1),table2.mc,[count]=count(table2.mc)
into #t1
from table1,table2
where table1.szfj=table2.id
group by table2.mcselect id=identity(int,1,1),table3.mc,[count]=count(table3.mc)
into #t2
from table1,table2
where table1.szfj=table2.id and table2.szdd=table3.id
group by table3.mcselect [table2.mc]=a.mc,[table2.count]=a.[count]
,[table3.mc]=b.mc,[table3.count]=b.[count]
from #t1 a,#t2 b where a.id=b.id
drop table #t1,#t2
into #t1
from table1,table2
where table1.szfj=table2.id
group by table2.mcselect id=identity(int,1,1),table3.mc,[count]=count(table3.mc)
into #t2
from table1,table2
where table1.szfj=table2.id and table2.szdd=table3.id
group by table3.mcselect [table2.mc]=a.mc,[table2.count]=a.[count]
,[table3.mc]=b.mc,[table3.count]=b.[count]
from #t1 a,#t2 b where a.id=b.id
drop table #t1,#t2
,[table3.mc]=b.mc,[table3.count]=b.[count]
from(
select table2.mc,[count]=count(table2.mc)
from table1,table2
where table1.szfj=table2.id
group by table2.mc
)a full join(
select table3.mc,[count]=count(table3.mc)
from table1,table2
where table1.szfj=table2.id and table2.szdd=table3.id
group by table3.mc
)b on a.mc=b.mc
union all (select table3.mc as mc1,mcount1 from table1,table2 where
table1.szfj=table2.id and table2.szdd=table3.id group by table3.mc) b
小弟有几个地方不是很明白,full join 是什么意思?它和union all有什么区别,on和where是一个意思吗?不过不是,它们有什么区别?这个identity(int,1,1)我也不懂,呵呵!!!
union all 是不是将字段数相同,字段的类型也相同联合成一个新的表,形成多个记录啊?
SELECT a.au_fname, a.au_lname, p.pub_name
FROM authors a FULL OUTER JOIN publishers p
ON a.city = p.city
ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC下面是结果集:au_fname au_lname pub_name
-------------------- ---------------------------- --------------------
Reginald Blotchet-Halls NULL
Michel DeFrance NULL
Innes del Castillo NULL
Ann Dull NULL
Marjorie Green NULL
Morningstar Greene NULL
Burt Gringlesby NULL
Sheryl Hunter NULL
Livia Karsen NULL
Charlene Locksley NULL
Stearns MacFeather NULL
Heather McBadden NULL
Michael O'Leary NULL
Sylvia Panteley NULL
Albert Ringer NULL
Anne Ringer NULL
Meander Smith NULL
Dean Straight NULL
Dirk Stringer NULL
Johnson White NULL
Akiko Yokomoto NULL
Abraham Bennet Algodata Infosystems
Cheryl Carson Algodata Infosystems
NULL NULL Binnet & Hardley
NULL NULL Five Lakes Publishing
NULL NULL GGG&G
NULL NULL Lucerne Publishing
NULL NULL New Moon Books
NULL NULL Ramona Publishers
NULL NULL Scootney Books
identity(int,1,1) 是建立一个标识,它将从1开始以1的基数开始自增
,[table3.mc]=b.mc,[table3.count]=b.[count]
from(
select table2.mc,[count]=count(table2.mc)
from table1,table2
where table1.szfj=table2.id
group by table2.mc
)a full join(
select table3.mc,[count]=count(table3.mc)
from table1,table2
where table1.szfj=table2.id and table2.szdd=table3.id
group by table3.mc
)b on a.mc=b.mc
我的查询确实是按照MC关联,我用了几种方式按照你给的句子往里套,可是总是有错误,
我的查询实际上包括6个SELECT。
[table2.mc]=a.mc是说把table2.mc从新命名成a.mc是吗?我的SQL语句直接写[table2.mc]=a.mc,还是只写个a.mc就可以了?
[count]=count(table2.mc)是说把count(table2.mc)从新命名成[COUNT]个吗?写时就这么写吗?
where gcsshebei.szsbj=gcsshebeijia.id and gcsshebeijia.szsbz=shebeizuo.id and
shebeizuo.szjf=jifang.id and jifang.szjd=jiedian.id and
jiedian.szfj=fenju.id group by fenju.mc */语句1
/* select fenju.mc,count(fenju.mc) from gcssbduankouban,gcsshebei,gcsshebeijia,shebeizuo,jifang,jiedian,fenju
where dkblx='1' and gcssbduankouban.szsb=gcsshebei.id and
gcsshebei.szsbj=gcsshebeijia.id and gcsshebeijia.szsbz=shebeizuo.id and
shebeizuo.szjf=jifang.id and jifang.szjd=jiedian.id and
jiedian.szfj=fenju.id group by fenju.mc;
*/ 语句2
上边是我的两个查询语句,他们的fenju.mc都是一样的,但是count是不一样的,
我要按照fenju.mc关联,查询的结果集包括以下三个字段:fenju.mc,count(fenju.mc),count(fenju.mc)
其中fenju.mc两个语句的结果是一样的,但是它们各自的count(fenju.mc)值不一样,
第一个count(fenju.mc)是语句1中的,第二个是count(fenju.mc)语句2中的,请问该如何写?
或者有别的办法也可以!!!不用我的方式!
2 (
3 select fenju.mc,count(fenju.mc) from gcsshebei,gcsshebeijia,shebeizuo,jifang,jiedian,fenju
4 where gcsshebei.szsbj=gcsshebeijia.id and gcsshebeijia.szsbz=shebeizuo.id and
5 shebeizuo.szjf=jifang.id and jifang.szjd=jiedian.id and
6 jiedian.szfj=fenju.id group by fenju.mc
7 ) a full join (select fenju.mc,count(fenju.mc) from gcssbduankouban,gcsshebei,gcsshebeijia,sheb
eizuo,jifang,jiedian,fenju
8 where dkblx='1' and gcssbduankouban.szsb=gcsshebei.id and
9 gcsshebei.szsbj=gcsshebeijia.id and gcsshebeijia.szsbz=shebeizuo.id and
10 shebeizuo.szjf=jifang.id and jifang.szjd=jiedian.id and
11 jiedian.szfj=fenju.id group by fenju.mc
12 ) b on a.mc=b.mc;
) a full join (select fenju.mc,count(fenju.mc) from
*
gcssbduankouban,gcsshebei,gcsshebeijia,shebeizuo
ERROR 位于第 7 行:
ORA-00933: SQL 命令未正确结束
我自己写了一个,但是有错误,该怎么改呢?