select count(DISTINCT A.ArticleID) as info_1,sum(A.Hits) as info_2,count(DISTINCT B.TopicID) as info_3,count(DISTINCT C.MessageID) as info_4 from A,B,C想用一条语句实现多表统计的效果,但上述语句中“sum(A.Hits) as info_2”是错误的。请问有什么实现A表Hits的SUM办法? 谢谢
Hits的类型是数值型的吗?如果是,用SUM,没错.如果不是,改为count()select count(DISTINCT A.ArticleID) as info_1, count(A.Hits) as info_2, count(DISTINCT B.TopicID) as info_3, count(DISTINCT C.MessageID) as info_4 from A,B,C select count(DISTINCT A.ArticleID) as info_1, count(A.Hits) as info_2, count(DISTINCT B.TopicID) as info_3, count(DISTINCT C.MessageID) as info_4 from A,B,C where A.关键字 = B.关键字 and A.关键字 = C.关键字
那就是你三表之间没有进行关联.造成出现了迪卡尔乘积,改为如下:select count(DISTINCT A.ArticleID) as info_1, sum(A.Hits) as info_2, count(DISTINCT B.TopicID) as info_3, count(DISTINCT C.MessageID) as info_4 from A,B,C where A.关键字 = B.关键字 and B.关键字 = C.关键字orselect count(DISTINCT A.ArticleID) as info_1, sum(A.Hits) as info_2, count(DISTINCT B.TopicID) as info_3, count(DISTINCT C.MessageID) as info_4 from A,B,C where A.关键字 = B.关键字 and A.关键字 = C.关键字不知道你三表具体的连接关系,自己更改一下就可以了.
这三个表之间是独立的,相互之间没有任何联系,用多个SQL语句分别进行统计可以实现目的。如: select count(*) as info_1 from a select sum(Hits) as info_2 from a select count(*) as info_3 from bselect count(*) as info_4 from c 现有是想将这些语句合并成一个SQL语句。(也许把一个简单的问题给搞复杂了)
select (select count(*) from a)as info_1, (select sum(Hits) from a) as info_2, (select count(*) from b) as info_3, (select count(*) from c) as info_4
首先感谢CCTV,同时感谢LZ的DISTINCT 提醒了我,再感谢四楼的dawugui 兄的迪卡尔乘积我的问题与LZ类似,不过三表是有关联的,现在解决了,感谢你们,GOD BLESS YOU ALL!
count(A.Hits) as info_2,
count(DISTINCT B.TopicID) as info_3,
count(DISTINCT C.MessageID) as info_4
from A,B,C select count(DISTINCT A.ArticleID) as info_1,
count(A.Hits) as info_2,
count(DISTINCT B.TopicID) as info_3,
count(DISTINCT C.MessageID) as info_4
from A,B,C
where A.关键字 = B.关键字 and A.关键字 = C.关键字
sum(A.Hits) as info_2,
count(DISTINCT B.TopicID) as info_3,
count(DISTINCT C.MessageID) as info_4
from A,B,C
where A.关键字 = B.关键字 and B.关键字 = C.关键字orselect count(DISTINCT A.ArticleID) as info_1,
sum(A.Hits) as info_2,
count(DISTINCT B.TopicID) as info_3,
count(DISTINCT C.MessageID) as info_4
from A,B,C
where A.关键字 = B.关键字 and A.关键字 = C.关键字不知道你三表具体的连接关系,自己更改一下就可以了.
select count(*) as info_1 from a
select sum(Hits) as info_2 from a select count(*) as info_3 from bselect count(*) as info_4 from c
现有是想将这些语句合并成一个SQL语句。(也许把一个简单的问题给搞复杂了)
select
(select count(*) from a)as info_1,
(select sum(Hits) from a) as info_2,
(select count(*) from b) as info_3,
(select count(*) from c) as info_4