我现在有一个表t(direction,name,time)
表中数据是这样的
Mo virus 2011-10-10
mt worn 2011-10-20
Mo virus2 2011-10-25
mt virus3 2011-10-12
mt virus 2011-10-10
我想查询的是:
name的总数,mo的总数,mt的总数,time
我用查询语句该怎么写呢?请教高手了,谢谢!
表中数据是这样的
Mo virus 2011-10-10
mt worn 2011-10-20
Mo virus2 2011-10-25
mt virus3 2011-10-12
mt virus 2011-10-10
我想查询的是:
name的总数,mo的总数,mt的总数,time
我用查询语句该怎么写呢?请教高手了,谢谢!
表中数据是这样的
Mo virus 2011-10-10
mt worn 2011-10-20
Mo virus2 2011-10-25
mt virus3 2011-10-12
mt virus 2011-10-10
我想查询的是:
name的总数,mo的总数,mt的总数,time
我用查询语句该怎么写呢?请教高手了,谢谢!
补充一下:是direction=Mo时的name总数,和direction=mt时的name总数
from 表
from t
group by direction
having direction='mt' || direction = 'Mo'
with tb1 as(
select 'Mo' direction,'virus' name,to_date('2011-10-10','yyyy-mm-dd') time from dual union all
select 'mt' direction,'worn' name,to_date('2011-10-20','yyyy-mm-dd') time from dual union all
select 'Mo' direction,'virus2' name,to_date('2011-10-25','yyyy-mm-dd') time from dual union all
select 'mt' direction,'virus3' name,to_date('2011-10-12','yyyy-mm-dd') time from dual union all
select 'mt' direction,'virus' name,to_date('2011-10-10','yyyy-mm-dd') time from dual
)
select distinct name,
(select count(*) from tb1 where name=t.name and direction='Mo') Mo,
(select count(*) from tb1 where name=t.name and direction='mt') mt,
time
from tb1 t;NAME MO MT TIME
------ ---------- ---------- -----------
worn 0 1 2011-10-20
virus2 1 0 2011-10-25
virus 1 1 2011-10-10
virus3 0 1 2011-10-12
select count(distinct name) sum_name,
count(distinct decode(direction,'Mo',name,null)) Mo_sum_name,
count(distinct decode(direction,'mt',name,null)) Mt_sum_name,
max(time) time
from 表t
FROM T1
Group By Direction
Having Direction In('Mo', 'mt');
Direction 计数
1 Mo 2
2 mt 3
--难道lz要的结果是这个???
with tb1 as(
select 'Mo' direction,'virus' name,to_date('2011-10-10','yyyy-mm-dd') time from dual union all
select 'mt' direction,'worn' name,to_date('2011-10-20','yyyy-mm-dd') time from dual union all
select 'Mo' direction,'virus2' name,to_date('2011-10-25','yyyy-mm-dd') time from dual union all
select 'mt' direction,'virus3' name,to_date('2011-10-12','yyyy-mm-dd') time from dual union all
select 'mt' direction,'virus' name,to_date('2011-10-10','yyyy-mm-dd') time from dual
)select tt.name,tt.Mo+tt.mt name总数,tt.Mo Mo总数,tt.mt mt总数,time
from (select distinct name,
(select count(*) from tb1 where name=t.name and direction='Mo') Mo,
(select count(*) from tb1 where name=t.name and direction='mt') mt,
time
from tb1 t) tt;NAME NAME总数 MO总数 MT总数 TIME
------ ---------- ---------- ---------- -----------
worn 1 0 1 2011-10-20
virus2 1 1 0 2011-10-25
virus 2 1 1 2011-10-10
virus3 1 0 1 2011-10-12