视图如下,其中A,B为两个子试图:
SELECT DISTINCT T.Time,T.TransID,T.Status,T.TransType
FROM
(SELECT Time,TransID,Status,TransType
FROM A
UNION
SELECT Time,TransID,Status,TransType
FROM B
) AS T
上面试图检索出来的结果有TransID重复的记录,对于TransID相同的记录,只取Time字段大的那一条。改进的视图如下:
SELECT DISTINCT T1.Time,T1.TransID,T1.Status,T1.TransType
FROM
(SELECT Time,TransID,Status,TransType
FROM A
UNION
SELECT Time,TransID,Status,TransType
FROM B
) AS T1 INNER JOIN(SELECT MAX(Time) AS MaxTime, TransID
FROM
(SELECT Time,TransID
FROM A
UNION
SELECT Time,TransID
FROM B
) AS Temp GROUP BY TransID
) AS T2
ON T1.Time=T2.MaxTime AND T1.TransID=T2.TransID
问题:
执行效率很低。由于业务量太大,库里至少有30万条数据,在这种情况下查询一次花费的时间太长。界面上其实每次最多能显示前1000条数据。各位帮忙看一下,如何优化。
SELECT DISTINCT T.Time,T.TransID,T.Status,T.TransType
FROM
(SELECT Time,TransID,Status,TransType
FROM A
UNION
SELECT Time,TransID,Status,TransType
FROM B
) AS T
上面试图检索出来的结果有TransID重复的记录,对于TransID相同的记录,只取Time字段大的那一条。改进的视图如下:
SELECT DISTINCT T1.Time,T1.TransID,T1.Status,T1.TransType
FROM
(SELECT Time,TransID,Status,TransType
FROM A
UNION
SELECT Time,TransID,Status,TransType
FROM B
) AS T1 INNER JOIN(SELECT MAX(Time) AS MaxTime, TransID
FROM
(SELECT Time,TransID
FROM A
UNION
SELECT Time,TransID
FROM B
) AS Temp GROUP BY TransID
) AS T2
ON T1.Time=T2.MaxTime AND T1.TransID=T2.TransID
问题:
执行效率很低。由于业务量太大,库里至少有30万条数据,在这种情况下查询一次花费的时间太长。界面上其实每次最多能显示前1000条数据。各位帮忙看一下,如何优化。
解决方案 »
- SQL query运行速度奇慢,如何提高
- SQL两表查询
- 一个月进行统计一次的SQL 怎么写
- 【求助】 如何利用Store Procedure(存储过程)读取本地硬盘的日志文件。
- SQLServer 工具箱(脚本排序+清除日志+更多实用功能)
- 请问如何获得@@ERROR的id对应错误的内容,请zjcxc大哥等帮个忙
- 未能找到服务器 'ITSV'?
- 从数据库里取数据的问题???
- 我是一名工业自动化专业的毕业生,现在在一家国企搞系统网络维护,开发!工作一个多月来,倍感压力,请问,我现在应先从哪方面着手学习!
- 关于数据库数据传输问题~
- 好心人帮帮忙,帮我看一下这段存储过程老是报错。
- 关于通过udl连接文件完成的数据导入问题(需要用代码)
(
SELECT Time,TransID,Status,TransType FROM A
UNION
SELECT Time,TransID,Status,TransType FROM B
) t1,
(
select TransID , max(time) time from
(
SELECT Time,TransID,Status,TransType FROM A
UNION
SELECT Time,TransID,Status,TransType FROM B
) m
group by Transid
) t2
where t1.Transid = t2.transid and t1.time = t2.time
(
SELECT Time,TransID,Status,TransType FROM A
UNION
SELECT Time,TransID,Status,TransType FROM B
) t1 where Time in
(
select max(time) time from
(
SELECT Time,TransID,Status,TransType FROM A
UNION
SELECT Time,TransID,Status,TransType FROM B
) m
group by Transid
)
SELECT DISTINCT T1.Time,T1.TransID,T1.Status,T1.TransType
FROM
(SELECT Time,TransID,Status,TransType
FROM A
UNION
SELECT Time,TransID,Status,TransType
FROM B
) AS T1 INNER JOIN(SELECT MAX(Time) AS MaxTime, TransID
FROM
(SELECT Time,TransID
FROM A
UNION
SELECT Time,TransID
FROM B
) AS Temp GROUP BY TransID
) AS T2
ON T1.Time=T2.MaxTime AND T1.TransID=T2.TransID
---改成这样试试看
Select X.* From (
SELECT T.Time,T.TransID,T.Status,T.TransType
FROM
(SELECT Time,TransID,Status,TransType
FROM A
UNION ALL
SELECT Time,TransID,Status,TransType
FROM B) AS T
) AS X WHERE Not Exists
(SELECT 1 FROM (
SELECT T.Time,T.TransID,T.Status,T.TransType
FROM
(SELECT Time,TransID,Status,TransType
FROM A
UNION ALL
SELECT Time,TransID,Status,TransType
FROM B) AS T
) AS Y WHERE Y.TransID=X.TransID AND Y.Time>X.Time)