SQL 2000 UNION ALL 子查询增加排序列报错问题

最新推荐文章于 2024-08-05 12:37:12 发布

SQL77

最新推荐文章于 2024-08-05 12:37:12 发布

阅读量2.9k

点赞数

分类专栏： SQL 文章标签： sql 2010 microsoft sql server exception table

本文链接：https://blog.csdn.net/SQL77/article/details/5728793

版权

SQL 专栏收录该内容

14 篇文章 0 订阅

订阅专栏

--问题帖 http://topic.csdn.net/u/20100711/22/0d933e51-d02f-4dac-b9a3-07a2e3bd283a.html
--我的思路是想通过一个函数,
--传入统计的列数@COLNUM,然后根据UNION ALL分解后,增加一个按值从大到小的排序列NUM ,
--利用NUM<=@COLNUM来统计列和,效率其实不怎么好,对表的扫描过多!!!!
--(还有一思路,想利用COL1+','+COL2...,通过传入一个列字符串,来统计列值和,貌似比较难,请高手指点)

--正题

--> 生成测试数据表: [st_rain_s]
IF OBJECT_ID('[st_rain_s]') IS NOT NULL
DROP TABLE [st_rain_s]
GO
create table st_rain_s
(
stcd char(8)
,TM datetime
,DYP DEC(18,2)
,p8 DEC(18,2)
,p9 DEC(18,2)
,p10 DEC(18,2)
,p11 DEC(18,2)
)

insert st_rain_s
select '90800001','2010-7-8 21:02:58',10,4,2,3,4
union all
select '90800001','2010-7-9 21:02:58',20,10,3,3,4

--1 平常写法(2000),利用子查询来增加排序列
SELECT STCD,CONVERT(CHAR(10),TM,120) TM,(RN) NUM,
  (SELECT COUNT(1) FROM
  (
   SELECT STCD,TM,P8 AS RN FROM st_rain_s
   UNION ALL
   SELECT STCD,TM,P9 FROM st_rain_s
   UNION ALL
   SELECT STCD,TM,P10 FROM st_rain_s
   UNION ALL
   SELECT STCD,TM,P11 FROM st_rain_s
  ) T1 WHERE STCD=T.STCD AND RN<=T.RN )
FROM
(
  SELECT STCD,TM,P8 AS RN FROM st_rain_s
  UNION ALL
  SELECT STCD,TM,P9 FROM st_rain_s
  UNION ALL
  SELECT STCD,TM,P10 FROM st_rain_s
  UNION ALL
  SELECT STCD,TM,P11 FROM st_rain_s
) T
/*
ODBC: 消息 0，级别 19，状态 1
SqlDumpExceptionHandler: 进程 60 发生了严重的异常 c0000005 EXCEPTION_ACCESS_VIOLATION。SQL Server 将终止该进程。

连接中断
*/

--结果,竟然报错,严重异常,不明白!!然后想换换临时表随便试试这种写法会不会出错,结果正常
--2
IF OBJECT_ID('TEMPDB..#T') IS NOT NULL
    DROP TABLE [#T]
GO

  SELECT STCD,TM,P8 AS RN,1 AS CNT INTO #T FROM st_rain_s
  UNION ALL
  SELECT STCD,TM,P9,2 FROM st_rain_s
  UNION ALL
  SELECT STCD,TM,P10,3 FROM st_rain_s
  UNION ALL
  SELECT STCD,TM,P11,4 FROM st_rain_s

  SELECT *,
  (SELECT COUNT(1) FROM #T WHERE STCD=T.STCD AND DATEDIFF(DD,TM,T.TM)=0 AND (RN>T.RN OR (RN=T.RN AND CNT>T.CNT)))+1 NUM
   FROM #T T ORDER BY TM
/*

（所影响的行数为 8 行）

STCD     TM                                                     RN                   CNT         NUM
-------- ------------------------------------------------------ -------------------- ----------- -----------
90800001 2010-07-08 21:02:58.000                                4.00                 1           2
90800001 2010-07-08 21:02:58.000                                2.00                 2           4
90800001 2010-07-08 21:02:58.000                                3.00                 3           3
90800001 2010-07-08 21:02:58.000                                4.00                 4           1
90800001 2010-07-09 21:02:58.000                                4.00                 4           2
90800001 2010-07-09 21:02:58.000                                3.00                 3           3
90800001 2010-07-09 21:02:58.000                                3.00                 2           4
90800001 2010-07-09 21:02:58.000                                10.00                1           1

（所影响的行数为 8 行）

--临时表是正常了,但函数里边用不了,为什么那样写报错了呢?
--只能在MSDN搜索了,查了一下,没有具体讲到,不过一般报这种的,解答都是SQL版本和最新补丁的问题

--http://support.microsoft.com/kb/888799/zh-cn
--http://support.microsoft.com/kb/299575/zh-cn
--http://support.microsoft.com/kb/892451/zh-cn
--http://support.microsoft.com/kb/302664/zh-cn

SELECT @@VERSION

--我的SQL版本是无SP4的SQL2000 ,装在XP系统的企业版,没服务的
/*Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
Aug 6 2000 00:57:48
Copyright (c) 1988-2000 Microsoft Corporation
Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)

（所影响的行数为 1 行）*/

--上面的第一条子查询语句发与其他人测试,SQL2005与SQL2000 SP4均无错
--整了这么久时间,没得到个结果,不想放弃,另寻其它解决办法

----------连接查询(写法可通过上面子查询转换)

SELECT T.*,COUNT(T1.CNT)+1 AS COLNUM
   FROM
  (
   SELECT STCD,TM,P8 AS RN ,1 as CNT FROM st_rain_s
   UNION ALL
   SELECT STCD,TM,P9,2 FROM st_rain_s
   UNION ALL
   SELECT STCD,TM,P10,3 FROM st_rain_s
   UNION ALL
   SELECT STCD,TM,P11,4 FROM st_rain_s
  ) T
  LEFT JOIN
  (
   SELECT STCD,TM,P8 AS RN ,1 as CNT FROM st_rain_s
   UNION ALL
   SELECT STCD,TM,P9,2 FROM st_rain_s
   UNION ALL
   SELECT STCD,TM,P10,3 FROM st_rain_s
   UNION ALL
   SELECT STCD,TM,P11,4 FROM st_rain_s
  ) T1

  ON T1.STCD=T.STCD AND DATEDIFF(DD,T.TM,T1.TM)=0 AND (T1.RN>t.RN or (T1.RN=t.RN AND T1.CNT>T.CNT))

  GROUP BY T.STCD,T.TM,T.RN,T.CNT
/*
STCD     TM                                                     RN                   CNT         COLNUM
-------- ------------------------------------------------------ -------------------- ----------- -----------
90800001 2010-07-08 21:02:58.000                                2.00                 2           4
90800001 2010-07-08 21:02:58.000                                3.00                 3           3
90800001 2010-07-08 21:02:58.000                                4.00                 1           2
90800001 2010-07-08 21:02:58.000                                4.00                 4           1
90800001 2010-07-09 21:02:58.000                                3.00                 2           4
90800001 2010-07-09 21:02:58.000                                3.00                 3           3
90800001 2010-07-09 21:02:58.000                                4.00                 4           2
90800001 2010-07-09 21:02:58.000                                10.00                1           1

（所影响的行数为 8 行）

警告: 聚合或其它 SET 操作消除了空值。
*/

--既然这样可以,那么函数里就应该可以了(此方法效率不好,等待高手解决效率问题)

CREATE FUNCTION GET_MAXSUM(@stcd char(8),@TM datetime,@COLNUM INT=1)
RETURNS INT
AS
BEGIN
DECLARE @SUM INT
SET @SUM=
(
SELECT SUM(RN)
FROM(

  SELECT T.*,COUNT(T1.CNT)+1 AS COLNUM
   FROM
  (SELECT STCD,TM,P8 AS RN ,1 as CNT FROM st_rain_s UNION ALL SELECT STCD,TM,P9,2 FROM st_rain_s UNION ALL SELECT STCD,TM,P10,3 FROM st_rain_s UNION ALL SELECT STCD,TM,P11,4 FROM st_rain_s) t
  LEFT JOIN
  (SELECT STCD,TM,P8 AS RN ,1 as CNT FROM st_rain_s UNION ALL SELECT STCD,TM,P9,2 FROM st_rain_s UNION ALL SELECT STCD,TM,P10,3 FROM st_rain_s UNION ALL SELECT STCD,TM,P11,4 FROM st_rain_s) T1

  ON T1.stcd=T.stcd and datediff(DD,T.tm,T1.tm)=0 and (T1.rn>t.rn or (T1.rn=t.rn and T1.cnt>T.cnt))

  GROUP BY T.STCD,T.TM,T.RN,T.CNT
) T WHERE STCD=@STCD AND DATEDIFF(DD,TM,@TM)=0 AND COLNUM<=@COLNUM
)
RETURN @SUM
END

SELECT STCD,TM,DYP,DBO.GET_MAXSUM(STCD,TM,1),DBO.GET_MAXSUM(STCD,TM,2),DBO.GET_MAXSUM(STCD,TM,3),DBO.GET_MAXSUM(STCD,TM,4) FROM st_rain_s

/*
STCD     TM                                                     DYP
-------- ------------------------------------------------------ -------------------- ----------- ----------- ----------- -----------
90800001 2010-07-08 21:02:58.000                                10.00                4           8           11          13
90800001 2010-07-09 21:02:58.000                                20.00                10          14          17          20

（所影响的行数为 2 行）

--结论,安装SQL还是要注意打好补丁,避免出现不必要的问题
--请各位高手抛砖指点子查询为何会报错的具体原因
--转载可不注明作者信息