select deparet_name,
(select count(t.id) from T t where t.tag like '%001003103%' and substr(t.code,0,10) = d.code) as tysb,
(select count(t.id) from T t where t.tag like '%001003103%' and sysdate-DAILY_TIME>30 and substr(t.code,0,10) = d.code) as wwtysb from D d where
d.deparet_id=(select e.deparet_id from D e where e.code='001_001')
求帮助
select deparet_name,
(select count(t.id) from T t where t.tag like '%001003103%' and substr(t.code,0,10) = d.code) as tysb,
(select count(t.id) from T t where t.tag like '%001003103%' and sysdate-DAILY_TIME>30 and substr(t.code,0,10) = d.code) as wwtysb
from D d where d.deparet_id=(select e.deparet_id from D e where e.code='001_001')
select deparet_name,
(select count(t.id) from T t where t.tag like '%001003103%' and substr(t.code,0,10) = d.code) as tysb,
(select count(t.id) from T t where t.tag like '%001003103%' and sysdate-DAILY_TIME>30 and substr(t.code,0,10) = d.code) as wwtysb
from D d
-- where d.deparet_id=(select e.deparet_id from D e where e.code='001_001') --这里是不是要 in哦?
where d.code='001-001'-- 这里
SELECT d.deparet_name,SUM(case when t.sysdate-daily_time>30 then 0 else 1 end) as wwtysb,SUM(case when t.sysdate-daily_time>30 then 1 else 0 end) as wwtysb, FROM d JOIN t on d.code=SUBSTRING(t.code,0,10)
where t.tag like '%001003103%' and -- 上面后面的条件,不知道楼主是要
group by d.deparet_name
count(t.id) as tysb,
sum(case when sysdate-DAILY_TIME>30 then 1 else 0 end) as wwtysb
from D d
left join T t on t.tag like '%001003103%' and t.code like d.code+'%'
where d.code='001_001'
group by d.deparet_name
测测这是不是你要的效果
select d.deparet_name,count(t.id) as tysb, sum(case when sysdate-t.DAILY_TIME>30 then 1 else 0 end) as wwtysb
from D d left join T t on t.tag like '%001003103%' and t.code like '%'||d.code||'%'
where d.parent_deparet_id=(select e.deparet_id from D e where e.code='001_001')
group by d.deparet_name
这样可以比我先那个要快2秒