我从sql2000数据库升级到2005后,有的比较多关联关系的查询显示不了数据 如下面这一视图:USE [RMDSS]
GO
/****** 对象: View [dbo].[V_TRANMISSION] 脚本日期: 12/10/2009 15:51:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO/* 作用: EDGE数据检查 显示所有数据 作者:xu
日期 :2009-2-18
*/
--numreqegprsbpc as EDGE信道数,IDLENUM as [64K传输空闲数], YESNUM as [64K传输数],HaltedStatus as Halted状态,station_name as 基站名,RBS_TYPE as 设备类型select * from [V_TRANMISSION] CREATE view [dbo].[V_TRANMISSION]
as select t9.*,t10.HaltedStatus,t11.station_name,t11.RBS_TYPE from (
select distinct t.cell,t.bsc_id,t.mo,t.numreqegprsbpc,t.strk,t2.IDLENUM,t12.YESNUM from ( --T 不用*的原因是 T里面包含了 YESNUM -- distinct 因为现在就用 YESN 了
SELECT *
FROM (SELECT cell, bsc_id, mo, SUM(CONVERT(int, numreqegprsbpc))
AS numreqegprsbpc, '' AS strk, '' AS YESNUM,'NULL' AS YESN --EDGE信道数 小于四的情况
FROM (SELECT t .*, t2.mo
FROM T_CELL_PARAM_RLBDP t LEFT JOIN
T_CELL_PARAM_RXTCP t2 ON t .bsc_id = t2.bsc_id AND
t .cell = t2.cell AND numreqegprsbpc <> '') t3
GROUP BY cell, bsc_id, mo
HAVING SUM(CONVERT(int, numreqegprsbpc)) < 4) t6
UNION
SELECT *
FROM (SELECT t5.cell, t4.bsc_id, t5.mo, '' AS numreqegprsbpc, 'NO YES' AS strk, --一个YES都没有的情况
'' AS YESNUM,'' AS YESN
FROM (SELECT *
FROM (SELECT DISTINCT bsc_id, mo
FROM T_CELL_PARAM_RXAPP) t
WHERE NOT EXISTS
(SELECT *
FROM (SELECT bsc_id, mo
FROM (SELECT bsc_id, mo
FROM T_CELL_PARAM_RXAPP
WHERE strk = 'YES') t3
GROUP BY bsc_id, mo) t2
WHERE t .bsc_id = t2.bsc_id AND t .mo = t2.mo)) t4 LEFT JOIN
T_CELL_PARAM_RXTCP t5 ON t4.bsc_id = t5.bsc_id AND
t5.mo = SUBSTRING(t4.mo, CHARINDEX('-', t4.mo) + 1, len(t4.mo) - 1)) t7 where cell is not null
UNION
SELECT *
FROM (SELECT *
FROM (SELECT t3.cell, t3.bsc_id, t3.mo, t3.numreqegprsbpc, '' AS strk, YESNUM, -- EDGE信道数 不等于 YES数的情况
'否' AS YESN
FROM (SELECT bsc_id, cell, mo, SUM(CONVERT(int, numreqegprsbpc))
AS numreqegprsbpc
FROM (SELECT t .*, t2.mo
FROM T_CELL_PARAM_RLBDP t LEFT JOIN
(SELECT DISTINCT bsc_id, mo, cell
FROM T_CELL_PARAM_RXTCP) t2 ON
t .bsc_id = t2.bsc_id AND t .cell = t2.cell) t9
GROUP BY bsc_id, cell, mo) t3 LEFT JOIN
(SELECT *
FROM (SELECT bsc_id, mo, COUNT(1) AS YESNUM
FROM (SELECT bsc_id, mo
FROM T_CELL_PARAM_RXAPP
WHERE strk = 'YES') t3
GROUP BY bsc_id, mo) t2) t2 ON t2.bsc_id = t3.bsc_id AND
t3.mo = SUBSTRING(t2.mo, CHARINDEX('-', t2.mo) + 1, len(t2.mo) - 1)) -- YES数 是算 T_CELL_PARAM_RXAPP YES 的量
t10
WHERE numreqegprsbpc <> YESNUM) t8
) T left join -- T表里 没有相同的数据
(
select BSC_ID, MO ,count(1) as YESNUM from (
select BSC_ID, MO from T_CELL_PARAM_RXAPP where strK = 'YES') t group by BSC_ID, MO -- t12 64K传输数
)
t12 on t.mo = SUBSTRING(t12.mo, CHARINDEX('-', t12.mo) + 1, len(t12.mo) - 1) and t.bsc_id = t12.bsc_id
left join
(
select BSC_ID, MO ,count(1) as IDLENUM from (
select BSC_ID, MO from T_CELL_PARAM_RXAPP where strK = 'YES' and APSTATE = 'IDLE') t group by BSC_ID, MO
)
t2 on t.mo = SUBSTRING(t2.mo, CHARINDEX('-', t2.mo) + 1, len(t2.mo) - 1) and t.bsc_id = t2.bsc_idwhere IDLENUM is not null
) t9
left join (select CELL_ID,'当前Halted' as HaltedStatus from V_HALTED_CELL) t10 --在当前Halted有的数据
on t10.cell_id = t9.cell
left join V_CELL t11 on t9.cell = t11.cell_id and t9.mo = t11.tg
-- select BSC_ID, MO ,count(1) as IDLENUM from (
-- select BSC_ID, MO from T_CELL_PARAM_RXAPP where strK = 'YES' and APSTATE = 'IDLE') t group by BSC_ID, MO-- select top 4 * from V_CELL
在2000里面 10多秒可出结果的!
GO
/****** 对象: View [dbo].[V_TRANMISSION] 脚本日期: 12/10/2009 15:51:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO/* 作用: EDGE数据检查 显示所有数据 作者:xu
日期 :2009-2-18
*/
--numreqegprsbpc as EDGE信道数,IDLENUM as [64K传输空闲数], YESNUM as [64K传输数],HaltedStatus as Halted状态,station_name as 基站名,RBS_TYPE as 设备类型select * from [V_TRANMISSION] CREATE view [dbo].[V_TRANMISSION]
as select t9.*,t10.HaltedStatus,t11.station_name,t11.RBS_TYPE from (
select distinct t.cell,t.bsc_id,t.mo,t.numreqegprsbpc,t.strk,t2.IDLENUM,t12.YESNUM from ( --T 不用*的原因是 T里面包含了 YESNUM -- distinct 因为现在就用 YESN 了
SELECT *
FROM (SELECT cell, bsc_id, mo, SUM(CONVERT(int, numreqegprsbpc))
AS numreqegprsbpc, '' AS strk, '' AS YESNUM,'NULL' AS YESN --EDGE信道数 小于四的情况
FROM (SELECT t .*, t2.mo
FROM T_CELL_PARAM_RLBDP t LEFT JOIN
T_CELL_PARAM_RXTCP t2 ON t .bsc_id = t2.bsc_id AND
t .cell = t2.cell AND numreqegprsbpc <> '') t3
GROUP BY cell, bsc_id, mo
HAVING SUM(CONVERT(int, numreqegprsbpc)) < 4) t6
UNION
SELECT *
FROM (SELECT t5.cell, t4.bsc_id, t5.mo, '' AS numreqegprsbpc, 'NO YES' AS strk, --一个YES都没有的情况
'' AS YESNUM,'' AS YESN
FROM (SELECT *
FROM (SELECT DISTINCT bsc_id, mo
FROM T_CELL_PARAM_RXAPP) t
WHERE NOT EXISTS
(SELECT *
FROM (SELECT bsc_id, mo
FROM (SELECT bsc_id, mo
FROM T_CELL_PARAM_RXAPP
WHERE strk = 'YES') t3
GROUP BY bsc_id, mo) t2
WHERE t .bsc_id = t2.bsc_id AND t .mo = t2.mo)) t4 LEFT JOIN
T_CELL_PARAM_RXTCP t5 ON t4.bsc_id = t5.bsc_id AND
t5.mo = SUBSTRING(t4.mo, CHARINDEX('-', t4.mo) + 1, len(t4.mo) - 1)) t7 where cell is not null
UNION
SELECT *
FROM (SELECT *
FROM (SELECT t3.cell, t3.bsc_id, t3.mo, t3.numreqegprsbpc, '' AS strk, YESNUM, -- EDGE信道数 不等于 YES数的情况
'否' AS YESN
FROM (SELECT bsc_id, cell, mo, SUM(CONVERT(int, numreqegprsbpc))
AS numreqegprsbpc
FROM (SELECT t .*, t2.mo
FROM T_CELL_PARAM_RLBDP t LEFT JOIN
(SELECT DISTINCT bsc_id, mo, cell
FROM T_CELL_PARAM_RXTCP) t2 ON
t .bsc_id = t2.bsc_id AND t .cell = t2.cell) t9
GROUP BY bsc_id, cell, mo) t3 LEFT JOIN
(SELECT *
FROM (SELECT bsc_id, mo, COUNT(1) AS YESNUM
FROM (SELECT bsc_id, mo
FROM T_CELL_PARAM_RXAPP
WHERE strk = 'YES') t3
GROUP BY bsc_id, mo) t2) t2 ON t2.bsc_id = t3.bsc_id AND
t3.mo = SUBSTRING(t2.mo, CHARINDEX('-', t2.mo) + 1, len(t2.mo) - 1)) -- YES数 是算 T_CELL_PARAM_RXAPP YES 的量
t10
WHERE numreqegprsbpc <> YESNUM) t8
) T left join -- T表里 没有相同的数据
(
select BSC_ID, MO ,count(1) as YESNUM from (
select BSC_ID, MO from T_CELL_PARAM_RXAPP where strK = 'YES') t group by BSC_ID, MO -- t12 64K传输数
)
t12 on t.mo = SUBSTRING(t12.mo, CHARINDEX('-', t12.mo) + 1, len(t12.mo) - 1) and t.bsc_id = t12.bsc_id
left join
(
select BSC_ID, MO ,count(1) as IDLENUM from (
select BSC_ID, MO from T_CELL_PARAM_RXAPP where strK = 'YES' and APSTATE = 'IDLE') t group by BSC_ID, MO
)
t2 on t.mo = SUBSTRING(t2.mo, CHARINDEX('-', t2.mo) + 1, len(t2.mo) - 1) and t.bsc_id = t2.bsc_idwhere IDLENUM is not null
) t9
left join (select CELL_ID,'当前Halted' as HaltedStatus from V_HALTED_CELL) t10 --在当前Halted有的数据
on t10.cell_id = t9.cell
left join V_CELL t11 on t9.cell = t11.cell_id and t9.mo = t11.tg
-- select BSC_ID, MO ,count(1) as IDLENUM from (
-- select BSC_ID, MO from T_CELL_PARAM_RXAPP where strK = 'YES' and APSTATE = 'IDLE') t group by BSC_ID, MO-- select top 4 * from V_CELL
在2000里面 10多秒可出结果的!
http://database.ctocio.com.cn/tips/314/7573314.shtml
问题2. SQL 2000 里的 not exists 要比 sql2005 快,呵呵!