select k.*
from
(select c.acId,c.subject,c.createDate,type,d.status,e.emName,d.totalSignCount from action c
join (select a.acId,sum(a.status) as status, count(a.acId) as totalSignCount from actionEmployee a
join action b on a.acId=b.acId group by a.acId) as d on c.acId=d.acId
join employee e on e.emId=c.emId where c.emId=103
union select c.acId,c.subject,c.createDate,type,d.status,e.emName,d.totalSignCount from action c
join (select a.acId,sum(a.status) as status, count(a.acId) as totalSignCount from actionEmployee a
join action b on a.acId=b.acId group by a.acId) as d on c.acId=d.acId
join employee e on e.emId=c.emId join actionEmployee f on c.acId=f.acId and f.signEmId=103 ) as k
order by k.acId desc产生了3个tablescan,有没有办法优化一下这个语句?里面有两段都重复了
from
(select c.acId,c.subject,c.createDate,type,d.status,e.emName,d.totalSignCount from action c
join (select a.acId,sum(a.status) as status, count(a.acId) as totalSignCount from actionEmployee a
join action b on a.acId=b.acId group by a.acId) as d on c.acId=d.acId
join employee e on e.emId=c.emId where c.emId=103
union select c.acId,c.subject,c.createDate,type,d.status,e.emName,d.totalSignCount from action c
join (select a.acId,sum(a.status) as status, count(a.acId) as totalSignCount from actionEmployee a
join action b on a.acId=b.acId group by a.acId) as d on c.acId=d.acId
join employee e on e.emId=c.emId join actionEmployee f on c.acId=f.acId and f.signEmId=103 ) as k
order by k.acId desc产生了3个tablescan,有没有办法优化一下这个语句?里面有两段都重复了
解决方案 »
- 查詢數據的速度很快,但是將這些數據插入到一個表速度則相當慢?請教高手!
- 内连续上班等于或大于4天的员工
- 一个函数问题,在线等,工作的似乎不太正常
- 如何用SQL来删除自增长属性。谢谢
- Conversion failed when converting the varchar value 'Bern' to data type int.
- 求助一个高性能SQL语句!
- 如何用别人给我的文件产生数据库?
- 问,怎样用存储过程返回数据库中以某些字符串开始的全部表名?
- 我要疯了,怎么我们这这么多人看不懂!!!
- 弱实体,4范式与多值依赖的问题
- 使用bcp 报错:命令行中的参数 'and' 未知。
- 问一个数据整编的问题
join (select a.acId,sum(a.status) as status, count(a.acId) as totalSignCount from actionEmployee a
join action b on a.acId=b.acId group by a.acId) as d on c.acId=d.acId
join employee e on e.emId=c.emId
where c.emId=103
union all
select c.acId,c.subject,c.createDate,type,d.status,e.emName,d.totalSignCount from action c --怎么没有条件呢?
join (select a.acId,sum(a.status) as status, count(a.acId) as totalSignCount from actionEmployee a
join action b on a.acId=b.acId group by a.acId) as d on c.acId=d.acId
join employee e on e.emId=c.emId join actionEmployee f on c.acId=f.acId and f.signEmId=103
order by f.acId desc
重新贴一下,可以执行的select k.*
from
(select c.acId,c.subject,c.createDate,type,d.status,e.emName,d.totalSignCount from action c join (select a.acId,sum(a.status) as status, count(a.acId) as totalSignCount from actionEmployee a join action b on a.acId=b.acId group by a.acId) as d on c.acId=d.acId join employee e on e.emId=c.emId where c.emId=103 union select c.acId,c.subject,c.createDate,type,d.status,e.emName,d.totalSignCount from action c join (select a.acId,sum(a.status) as status, count(a.acId) as totalSignCount from actionEmployee a join action b on a.acId=b.acId group by a.acId) as d on c.acId=d.acId join employee e on e.emId=c.emId join actionEmployee f on c.acId=f.acId and f.signEmId=103 ) as k
order by k.acId desc
select --distinct
c.acId,c.subject,c.createDate,type,d.status,e.emName,d.totalSignCount
from action c
join
(select a.acId,sum(a.status) as status, count(a.acId) as totalSignCount
from actionEmployee a
join action b
on a.acId=b.acId
group by a.acId
) as d
on c.acId=d.acId
join employee e
on e.emId=c.emId
where c.emId=103
or exists (select 1 from actionEmployee f where c.acId = f.acId and f.signEmId=103)
order by c.acId desc
select
c.acId,c.subject,c.createDate,type,d.status,e.emName,d.totalSignCount
from
action c
inner join
(select
a.acId,sum(a.status) as status, count(a.acId) as totalSignCount
from
actionEmployee a
inner join
action b
on
a.acId=b.acId
group by
a.acId) as d
on
c.acId=d.acId
inner join
employee e
on
e.emId=c.emId
left join
actionEmployee f
on
c.acId=f.acId
where
c.emId=103 or (c.emId!=103 and f.signEmId=103)
order by
c.acId desc
from
(select c.*,d.status,e.emName,d.totalSignCount from action c join (select a.acId,sum(a.status) as status, count(a.acId) as totalSignCount from actionEmployee a join action b on a.acId=b.acId and b.emId=103 group by a.acId) as d on c.acId=d.acId join employee e on e.emId=c.emId
union select c.*,d.status,e.emName,d.totalSignCount from action c join (select a.acId,sum(a.status) as status, count(a.acId) as totalSignCount from actionEmployee a join action b on a.acId=b.acId and a.signEmId=103 group by a.acId) as d on c.acId=d.acId join employee e on e.emId=c.emId ) as k
order by k.acId desc最后优化成这个样子,去掉了两个内连接,加了个索引,正常了,再看看大家的回复,对比一下
你这个语句查询结果和我的是一直的,至于性能,我看不明白,大家看看是用哪个好些
duration cpu reads writes
15 15 68 0 你的
16 16 152 0 我的两个脚本一起执行,则执行计划你的是51.39%,我的是48.61%到底哪个效率高些?
SELECT
status, emName, totalSignCount
FROM
action AS c
INNER JOIN employee AS e
ON e.emId = c.emId
INNER JOIN
(SELECT
a.acId, SUM(a.status) AS status, COUNT(a.acId) AS totalSignCount
FROM actionEmployee AS a
INNER JOIN action AS b
ON a.acId = b.acId
AND b.emId = 103
GROUP BY a.acId
UNION
SELECT
a.acId, SUM(a.status), COUNT(a.acId)
FROM actionEmployee AS a
INNER JOIN action AS b
ON a.acId = b.acId
AND a.signEmId = 103
GROUP BY a.acId
) AS k
ON c.acId = k.acId
ORDER BY c.acId DESC
SQL:StmtCompleted
16 16 68 0 SQL:StmtCompleted A
31 31 152 0 SQL:StmtCompleted 我的
0 0 93 0 C