select count(*),to_char(A.time,'yyyy-mm-dd'),MAX(B.companyName) --count(*)是统计和, from A inner join B -- to_char(A.time,'yyyy-mm-dd')是时间 On A.channelID=B.companyID --B.companyName 公司名称 group by to_char(A.time,'yyyy-mm-dd') order by to_char(A.time,'yyyy-mm-dd')公司名称都一样,但是有7条?不太懂你表结构,但是给你个建议 把GROUP BY中的COMPANYNAME去掉, 把选择的COMPANYNAME加上MAX() 改成以上,试试
select count(*),to_char(A.time,'yyyy-mm-dd'),MAX(B.companyName) from A inner join B On A.channelID=B.companyID group by to_char(A.time,'yyyy-mm-dd') order by to_char(A.time,'yyyy-mm-dd')这个就可以的,试试,
select count(*),to_char(A.time,'yyyy-mm-dd'),B.companyName from A ,B where B.companyID=A.channelID(+) group by to_char(A.time,'yyyy-mm-dd'),B.companyName order by to_char(A.time,'yyyy-mm-dd')
select count(*),to_char(A.time,'yyyy-mm-dd'), (select B.companyName from B where A.channelID=B.companyID and rownum=1) companyName from A group by to_char(A.time,'yyyy-mm-dd') order by to_char(A.time,'yyyy-mm-dd')
select count(*),to_char(A.time,'yyyy-mm-dd'),B.companyName from A , (select distinct(c.companyID),c.companyName from C) B where B.companyID=A.channelID group by to_char(A.time,'yyyy-mm-dd'),B.companyName order by to_char(A.time,'yyyy-mm-dd') desc
from A inner join B
On A.channelID=B.companyID
这个结果是什么?贴出来看看
显示出了7条一样的数据,例如:
A.channelID B.companyID to_char(A.time,'yyyy-mm-dd') B.companyName
1 1 2010-06-21 ECC因为A表统计今天有7条,所以现在显示出的是7条数据
from A inner join B -- to_char(A.time,'yyyy-mm-dd')是时间
On A.channelID=B.companyID --B.companyName 公司名称
group by to_char(A.time,'yyyy-mm-dd')
order by to_char(A.time,'yyyy-mm-dd')公司名称都一样,但是有7条?不太懂你表结构,但是给你个建议
把GROUP BY中的COMPANYNAME去掉, 把选择的COMPANYNAME加上MAX()
改成以上,试试
情况是这样的,我想对A表做一个按每天的总量统计,需要加上B表的一个公司名称。但是B表有这样一个情况,假设ID是1,B表中可能有7个ID为1的数据(这公司有7个员工)。结果“A.channelID=B.companyID ”判断是就会有7条满足,所以用上面的语句就会出现7条数据。如果做统计聚会就会出现3*7=21的情况(A表统计只有3条,但由于满足条件 的B表有7条数据,结果就统计了7次变成了21)
就想问问如何限制这种情况的发生
假设A表数据如下:
channelID:1,time:2010-06-10。(其余字段省略)
一共有三条。B表:
companyName:卓信 ,channelID:1。(其余字段省略)
一共有7条。----------------------------------
我希望查询出来的结果是:
count(*):3,to_char(A.time,'yyyy-mm-dd'):2010-06-10, companyName:卓信
-------------------------------------------------------------------------------
我实际上需要的就是B表的一个companyName,conut(*)和to_char(A.time,'yyyy-mm-dd')都是A表的。
但是由于B表中存在7条channelID=1的数据,
导致:"On A.channelID=B.companyID"有7条数据满足,结果
conut(*)在group by后就变成了3*7=21了。
from A inner join B
On A.channelID=B.companyID
group by to_char(A.time,'yyyy-mm-dd')
order by to_char(A.time,'yyyy-mm-dd')这个就可以的,试试,
例如:以前是
ECC 21 2010-06-21
AAA 10 2010-06-21
用了MAX就变成了:
AAA 31 2010-06-21----
公司名成了AAA,统计变成了两家公司的总和21+10=31了。
from A ,B
where B.companyID=A.channelID(+)
group by to_char(A.time,'yyyy-mm-dd'),B.companyName
order by to_char(A.time,'yyyy-mm-dd')
(select B.companyName from B where A.channelID=B.companyID and rownum=1) companyName
from A
group by to_char(A.time,'yyyy-mm-dd')
order by to_char(A.time,'yyyy-mm-dd')
select count(*),to_char(A.time,'yyyy-mm-dd'),B.companyName
from A ,
(select distinct(c.companyID),c.companyName from C) B
where B.companyID=A.channelID
group by to_char(A.time,'yyyy-mm-dd'),B.companyName
order by to_char(A.time,'yyyy-mm-dd') desc