语句如下:SELECT a.xxx,
a.P001_005,
a.p001_024,
1 AS yz_type
FROM CLI.p001 a,
HOS.p012 d
WHERE LEFT(a.xxx, 1) <> '9' AND p001_007 IS NOT NULL AND p001_026 IN ('1', '4') AND a.xxx = d.xxx AND rtrim(p012_042) = 'TEAM1' AND datediff('day', p001_003, to_date('2011.07.07', 'YY.MM.DD')) >= 0 AND p001_005 NOT BETWEEN 13001 AND 14000
GROUP BY a.xxx,
a.P001_005,
a.p001_024
UNION ALL
SELECT a.xxx,
a.P003_005,
a.p003_024,
2 AS yz_type
FROM CLI.p003 a,
HOS.p012 d
WHERE LEFT(a.xxx, 1) <> '9' AND p003_007 IS NOT NULL AND p003_026 IN ('1', '4', '0', '2') AND datediff('day', p003_003, to_date('2011.07.07', 'YY.MM.DD')) >= 0 AND a.xxx = d.xxx AND rtrim(p012_042) = 'TEAM1' AND p003_005 NOT BETWEEN 16001 AND 17000
GROUP BY a.xxx,
a.P003_005,
a.p003_024想问问有没有办法优化这段语句,其中 CLI.P001(XXX,P003), CLI.P003(XXX,P003) , HOS.P012(XXX) 都已经建有索引。
或者指点一下查询语句效率的办法,最好详细点的,谢谢!
a.P001_005,
a.p001_024,
1 AS yz_type
FROM CLI.p001 a,
HOS.p012 d
WHERE LEFT(a.xxx, 1) <> '9' AND p001_007 IS NOT NULL AND p001_026 IN ('1', '4') AND a.xxx = d.xxx AND rtrim(p012_042) = 'TEAM1' AND datediff('day', p001_003, to_date('2011.07.07', 'YY.MM.DD')) >= 0 AND p001_005 NOT BETWEEN 13001 AND 14000
GROUP BY a.xxx,
a.P001_005,
a.p001_024
UNION ALL
SELECT a.xxx,
a.P003_005,
a.p003_024,
2 AS yz_type
FROM CLI.p003 a,
HOS.p012 d
WHERE LEFT(a.xxx, 1) <> '9' AND p003_007 IS NOT NULL AND p003_026 IN ('1', '4', '0', '2') AND datediff('day', p003_003, to_date('2011.07.07', 'YY.MM.DD')) >= 0 AND a.xxx = d.xxx AND rtrim(p012_042) = 'TEAM1' AND p003_005 NOT BETWEEN 16001 AND 17000
GROUP BY a.xxx,
a.P003_005,
a.p003_024想问问有没有办法优化这段语句,其中 CLI.P001(XXX,P003), CLI.P003(XXX,P003) , HOS.P012(XXX) 都已经建有索引。
或者指点一下查询语句效率的办法,最好详细点的,谢谢!
那我想问问这句能不能用其他的表达式代替呢?
例如 p001_003<=to_date('2011.07.07', 'YY.MM.DD')这样可以吗?拿数据和结构挺麻烦的,不好意思
to_date 是oracle的,莫非你要用oracle的优化方法来优化sqlserver的sql?
oracle也有datediff的,程序是用得正常的,就是有点慢而已,貌似用了datediff就使用不了索引,其实也不是什么大问题。