22,209
社区成员
发帖
与我相关
我的任务
分享
ALTER PROCEDURE [dbo].[admin_zy_zyts]
(@kssj DATETIME,@jssj datetime)
AS
BEGIN
SELECT ksmc,DATEDIFF(DAY,rysj,cysj) qnts,COUNT(djxh) qnrc
INTO #temp12
FROM bq_brbqxxk a,jb_ksbmk b
WHERE a.bqdm=b.ksdm AND zt IN (4,5)
and cysj>=dateadd(year,-1,cast(convert(varchar(8),@kssj,23)+'01' as datetime))
AND cysj<dateadd(month,-11,cast(convert(varchar(8),@jssj+31,23)+'01' as datetime))
GROUP BY ksmc,rysj,cysj
SELECT ksmc,CONVERT(REAL,SUM(qnts))/CONVERT(REAL,COUNT(qnrc)) AS jnpjts
INTO #temp3
FROM #temp12
GROUP BY ksmc
SELECT ksmc,DATEDIFF(DAY,rysj,cysj) as wsnts,COUNT(djxh) as wsnrc
INTO #temp1
FROM bq_brbqxxk a,jb_ksbmk b
WHERE a.bqdm=b.ksdm AND zt IN (4,5)
AND cysj>cast(convert(varchar(8),@kssj,23)+'01' as datetime)
AND cysj<cast(convert(varchar(8),@jssj+31,23)+'01' as datetime)
GROUP BY ksmc,rysj,cysj
SELECT * FROM #temp1
--1.先把下面都这些注释,用上面的语句看下#temp1的字段是不是wsnts,wsnrc,如果可以执行出结果,那么就说明#temp1没有问题;
--2.然后把下面这两句去掉注释,再执行,如果也不报错的话那就说明也没问题
--SELECT ksmc,CONVERT(REAL,SUM(wsnts))/CONVERT(REAL,COUNT(wsnrc)) AS jnpjts
--INTO #temp2
--FROM #temp1 GROUP BY ksmc
--SELECT * FROM #temp2
--SELECT *
--FROM #temp3 a,#temp2 b
--WHERE a.ksmc=b.ksmc
DROP TABLE #temp1
--DROP TABLE #temp2
DROP TABLE #temp3
DROP TABLE #temp12
END
ALTER PROCEDURE [dbo].[admin_zy_zyts]
(@kssj DATETIME,@jssj datetime)
AS
BEGIN
SELECT ksmc,DATEDIFF(DAY,rysj,cysj) qnts,COUNT(djxh) qnrc
INTO #temp12
FROM bq_brbqxxk a,jb_ksbmk b
WHERE a.bqdm=b.ksdm AND zt IN (4,5)
and cysj>=dateadd(year,-1,cast(convert(varchar(8),@kssj,23)+'01' as datetime))
AND cysj<dateadd(month,-11,cast(convert(varchar(8),@jssj+31,23)+'01' as datetime))
GROUP BY ksmc,rysj,cysj
SELECT ksmc,CONVERT(REAL,SUM(qnts))/CONVERT(REAL,COUNT(qnrc)) AS jnpjts
INTO #temp3
FROM #temp12
GROUP BY ksmc
SELECT ksmc,DATEDIFF(DAY,rysj,cysj) as wsnts,COUNT(djxh) as wsnrc
INTO #temp1
FROM bq_brbqxxk a,jb_ksbmk b
WHERE a.bqdm=b.ksdm AND zt IN (4,5)
AND cysj>cast(convert(varchar(8),@kssj,23)+'01' as datetime)
AND cysj<cast(convert(varchar(8),@jssj+31,23)+'01' as datetime)
GROUP BY ksmc,rysj,cysj
SELECT * FROM #temp1
--1.先把下面都这些注释,用上面的语句看下#temp1的字段是不是wsnts,wsnrc,如果可以执行出结果,那么就说明#temp1没有问题;
--2.然后把下面这两句去掉注释,再执行,如果也不报错的话那就说明也没问题
--SELECT ksmc,CONVERT(REAL,SUM(wsnts))/CONVERT(REAL,COUNT(wsnrc)) AS jnpjts
--INTO #temp2
--FROM #temp1 GROUP BY ksmc
--SELECT * FROM #temp2
--SELECT *
--FROM #temp3 a,#temp2 b
--WHERE a.ksmc=b.ksmc
DROP TABLE #temp1
--DROP TABLE #temp2
DROP TABLE #temp3
DROP TABLE #temp12
END
SELECT ksmc,DATEDIFF(DAY,rysj,cysj) qnts,COUNT(djxh) qnrc I
NTO #temp12
FROM bq_brbqxxk a,jb_ksbmk b
WHERE a.bqdm=b.ksdm AND zt IN (4,5)
and cysj>=dateadd(year,-1,cast(convert(varchar(8),@kssj,23)+'01' as datetime))
AND cysj<dateadd(month,-11,cast(convert(varchar(8),@jssj+31,23)+'01' as datetime))
GROUP BY ksmc,rysj,cysj
SELECT ksmc,CONVERT(REAL,SUM(qnts))/CONVERT(REAL,COUNT(qnrc)) AS jnpjts
INTO #temp3
FROM #temp12
GROUP BY ksmc
SELECT ksmc,DATEDIFF(DAY,rysj,cysj) as wsnts,COUNT(djxh) as wsnrc
INTO #temp1
FROM bq_brbqxxk a,jb_ksbmk b
WHERE a.bqdm=b.ksdm AND zt IN (4,5)
AND cysj>cast(convert(varchar(8),@kssj,23)+'01' as datetime)
AND cysj<cast(convert(varchar(8),@jssj+31,23)+'01' as datetime)
GROUP BY ksmc,rysj,cysj
SELECT * FROM #temp1
--1.先把下面都这些注释,用上面的语句看下#temp1的字段是不是wsnts,wsnrc,如果可以执行出结果,那么就说明#temp1没有问题;
--2.然后把下面这两句去掉注释,再执行,如果也不报错的话那就说明也没问题
--SELECT ksmc,CONVERT(REAL,SUM(wsnts))/CONVERT(REAL,COUNT(wsnrc)) AS jnpjts
--INTO #temp2
--FROM #temp1 GROUP BY ksmc
--SELECT * FROM #temp2
--SELECT *
--FROM #temp3 a,#temp2 b
--WHERE a.ksmc=b.ksmc
DROP TABLE #temp1
DROP TABLE #temp2
DROP TABLE #temp3
DROP TABLE #temp12
END
[/quote]
---------------
(7427 行受影响)
(19 行受影响)
(560 行受影响)
(560 行受影响)
消息 3701,级别 11,状态 5,过程 admin_zy_zyts,第 44 行
无法对 表 '#temp2' 执行 删除,因为它不存在,或者您没有所需的权限。
ALTER PROCEDURE [dbo].[admin_zy_zyts]
(
@kssj DATETIME ,
@jssj DATETIME
)
AS
BEGIN
SELECT ksmc ,
DATEDIFF(DAY, rysj, cysj) qnts ,
COUNT(djxh) qnrc
INTO #temp12
FROM bq_brbqxxk a ,
jb_ksbmk b
WHERE a.bqdm = b.ksdm
AND zt IN ( 4, 5 )
AND cysj >= DATEADD(YEAR, -1,
CAST(CONVERT(VARCHAR(8), @kssj, 23) + '01' AS DATETIME))
AND cysj < DATEADD(MONTH, -11,
CAST(CONVERT(VARCHAR(8), @jssj + 31, 23)
+ '01' AS DATETIME))
GROUP BY ksmc ,
DATEDIFF(DAY, rysj, cysj)
SELECT ksmc ,
CONVERT(REAL, SUM(qnts)) / CONVERT(REAL, COUNT(qnrc)) AS jnpjts
INTO #temp3
FROM #temp12
GROUP BY ksmc
SELECT ksmc ,
DATEDIFF(DAY, rysj, cysj) wsnts ,
COUNT(djxh) wsnrc
INTO #temp1
FROM bq_brbqxxk a ,
jb_ksbmk b
WHERE a.bqdm = b.ksdm
AND zt IN ( 4, 5 )
AND cysj > CAST(CONVERT(VARCHAR(8), @kssj, 23) + '01' AS DATETIME)
AND cysj < CAST(CONVERT(VARCHAR(8), @jssj + 31, 23) + '01' AS DATETIME)
GROUP BY ksmc ,
DATEDIFF(DAY, rysj, cysj)
--rysj , 这两列没有在select中,所以报错了
--cysj
SELECT * --这里看一下#temp1中的数据列名到底是什么
FROM #temp1
DROP TABLE #temp1
DROP TABLE #temp3
DROP TABLE #temp12
END
SELECT ksmc,DATEDIFF(DAY,rysj,cysj) qnts,COUNT(djxh) qnrc I
NTO #temp12
FROM bq_brbqxxk a,jb_ksbmk b
WHERE a.bqdm=b.ksdm AND zt IN (4,5)
and cysj>=dateadd(year,-1,cast(convert(varchar(8),@kssj,23)+'01' as datetime))
AND cysj<dateadd(month,-11,cast(convert(varchar(8),@jssj+31,23)+'01' as datetime))
GROUP BY ksmc,rysj,cysj
SELECT ksmc,CONVERT(REAL,SUM(qnts))/CONVERT(REAL,COUNT(qnrc)) AS jnpjts
INTO #temp3
FROM #temp12
GROUP BY ksmc
SELECT ksmc,DATEDIFF(DAY,rysj,cysj) as wsnts,COUNT(djxh) as wsnrc
INTO #temp1
FROM bq_brbqxxk a,jb_ksbmk b
WHERE a.bqdm=b.ksdm AND zt IN (4,5)
AND cysj>cast(convert(varchar(8),@kssj,23)+'01' as datetime)
AND cysj<cast(convert(varchar(8),@jssj+31,23)+'01' as datetime)
GROUP BY ksmc,rysj,cysj
SELECT * FROM #temp1
--1.先把下面都这些注释,用上面的语句看下#temp1的字段是不是wsnts,wsnrc,如果可以执行出结果,那么就说明#temp1没有问题;
--2.然后把下面这两句去掉注释,再执行,如果也不报错的话那就说明也没问题
--SELECT ksmc,CONVERT(REAL,SUM(wsnts))/CONVERT(REAL,COUNT(wsnrc)) AS jnpjts
--INTO #temp2
--FROM #temp1 GROUP BY ksmc
--SELECT * FROM #temp2
--SELECT *
--FROM #temp3 a,#temp2 b
--WHERE a.ksmc=b.ksmc
DROP TABLE #temp1
DROP TABLE #temp2
DROP TABLE #temp3
DROP TABLE #temp12
END
ALTER PROCEDURE [dbo].[admin_zy_zyts]
(
@kssj DATETIME ,
@jssj DATETIME
)
AS
BEGIN
SELECT ksmc ,
DATEDIFF(DAY, rysj, cysj) qnts ,
COUNT(djxh) qnrc
INTO #temp12
FROM bq_brbqxxk a ,
jb_ksbmk b
WHERE a.bqdm = b.ksdm
AND zt IN ( 4, 5 )
AND cysj >= DATEADD(YEAR, -1,
CAST(CONVERT(VARCHAR(8), @kssj, 23) + '01' AS DATETIME))
AND cysj < DATEADD(MONTH, -11,
CAST(CONVERT(VARCHAR(8), @jssj + 31, 23)
+ '01' AS DATETIME))
GROUP BY ksmc ,
DATEDIFF(DAY, rysj, cysj)
SELECT ksmc ,
CONVERT(REAL, SUM(qnts)) / CONVERT(REAL, COUNT(qnrc)) AS jnpjts
INTO #temp3
FROM #temp12
GROUP BY ksmc
SELECT ksmc ,
DATEDIFF(DAY, rysj, cysj) wsnts ,
COUNT(djxh) wsnrc
INTO #temp1
FROM bq_brbqxxk a ,
jb_ksbmk b
WHERE a.bqdm = b.ksdm
AND zt IN ( 4, 5 )
AND cysj > CAST(CONVERT(VARCHAR(8), @kssj, 23) + '01' AS DATETIME)
AND cysj < CAST(CONVERT(VARCHAR(8), @jssj + 31, 23) + '01' AS DATETIME)
GROUP BY ksmc ,
DATEDIFF(DAY, rysj, cysj)
--rysj , 这两列没有在select中,所以报错了
--cysj
SELECT * --这里看一下#temp1中的数据列名到底是什么
FROM #temp1
DROP TABLE #temp1
DROP TABLE #temp2
DROP TABLE #temp3
DROP TABLE #temp12
END
ALTER PROCEDURE [dbo].[admin_zy_zyts]
(
@kssj DATETIME ,
@jssj DATETIME
)
AS
BEGIN
SELECT ksmc ,
DATEDIFF(DAY, rysj, cysj) qnts ,
COUNT(djxh) qnrc
INTO #temp12
FROM bq_brbqxxk a ,
jb_ksbmk b
WHERE a.bqdm = b.ksdm
AND zt IN ( 4, 5 )
AND cysj >= DATEADD(YEAR, -1,
CAST(CONVERT(VARCHAR(8), @kssj, 23) + '01' AS DATETIME))
AND cysj < DATEADD(MONTH, -11,
CAST(CONVERT(VARCHAR(8), @jssj + 31, 23)
+ '01' AS DATETIME))
GROUP BY ksmc ,
DATEDIFF(DAY, rysj, cysj)
SELECT ksmc ,
CONVERT(REAL, SUM(qnts)) / CONVERT(REAL, COUNT(qnrc)) AS jnpjts
INTO #temp3
FROM #temp12
GROUP BY ksmc
SELECT ksmc ,
DATEDIFF(DAY, rysj, cysj) wsnts ,
COUNT(djxh) wsnrc
INTO #temp1
FROM bq_brbqxxk a ,
jb_ksbmk b
WHERE a.bqdm = b.ksdm
AND zt IN ( 4, 5 )
AND cysj > CAST(CONVERT(VARCHAR(8), @kssj, 23) + '01' AS DATETIME)
AND cysj < CAST(CONVERT(VARCHAR(8), @jssj + 31, 23) + '01' AS DATETIME)
GROUP BY ksmc ,
DATEDIFF(DAY, rysj, cysj)
--rysj , 这两列没有在select中,所以报错了
--cysj
SELECT * --这里看一下#temp1中的数据列名到底是什么
FROM #temp1
GROUP BY ksmc
DROP TABLE #temp1
DROP TABLE #temp2
DROP TABLE #temp3
DROP TABLE #temp12
END
ALTER PROCEDURE [dbo].[admin_zy_zyts]
(@kssj DATETIME,@jssj datetime)
AS
BEGIN
DECLARE @kssj DATETIME
SET @kssj=convert(DATEtime,'2017-01-01')
DECLARE @jssj DATETIME
SET @jssj=convert(DATEtime,'2017-02-01')
SELECT ksmc,DATEDIFF(DAY,rysj,cysj) qnts,COUNT(djxh) qnrc
INTO #temp12
FROM bq_brbqxxk a,jb_ksbmk b
WHERE a.bqdm=b.ksdm
AND zt IN (4,5)
and cysj>=dateadd(year,-1,cast(convert(varchar(8),@kssj,23)+'01' as datetime))
AND cysj<dateadd(month,-11,cast(convert(varchar(8),@jssj+31,23)+'01' as datetime))
GROUP BY ksmc,rysj,cysj
SELECT ksmc,CONVERT(REAL,SUM(qnts))/CONVERT(REAL,COUNT(qnrc)) AS jnpjts
INTO #temp3
FROM #temp12
GROUP BY ksmc
SELECT ksmc,DATEDIFF(DAY,rysj,cysj) wsnts,COUNT(djxh) wsnrc
INTO #temp1
FROM bq_brbqxxk a,jb_ksbmk b
WHERE a.bqdm=b.ksdm
AND zt IN (4,5)
AND cysj>cast(convert(varchar(8),@kssj,23)+'01' as datetime)
AND cysj<cast(convert(varchar(8),@jssj+31,23)+'01' as datetime)
GROUP BY ksmc,rysj,cysj
SELECT ksmc,CONVERT(REAL,SUM(wsnts))/CONVERT(REAL,COUNT(wsnrc)) AS jnpjts
INTO #temp2
FROM #temp1
GROUP BY ksmc
SELECT *
FROM #temp3 a,#temp2 b
WHERE a.ksmc=b.ksmc
ALTER PROCEDURE [dbo].[admin_zy_zyts]
(
@kssj DATETIME ,
@jssj DATETIME
)
AS
BEGIN
SELECT ksmc ,
DATEDIFF(DAY, rysj, cysj) qnts ,
COUNT(djxh) qnrc
INTO #temp12
FROM bq_brbqxxk a ,
jb_ksbmk b
WHERE a.bqdm = b.ksdm
AND zt IN ( 4, 5 )
AND cysj >= DATEADD(YEAR, -1,
CAST(CONVERT(VARCHAR(8), @kssj, 23) + '01' AS DATETIME))
AND cysj < DATEADD(MONTH, -11,
CAST(CONVERT(VARCHAR(8), @jssj + 31, 23)
+ '01' AS DATETIME))
GROUP BY ksmc ,
rysj ,
cysj
SELECT ksmc ,
CONVERT(REAL, SUM(qnts)) / CONVERT(REAL, COUNT(qnrc)) AS jnpjts
INTO #temp3
FROM #temp12
GROUP BY ksmc
SELECT ksmc ,
DATEDIFF(DAY, rysj, cysj) wsnts ,
COUNT(djxh) wsnrc
INTO #temp1
FROM bq_brbqxxk a ,
jb_ksbmk b
WHERE a.bqdm = b.ksdm
AND zt IN ( 4, 5 )
AND cysj > CAST(CONVERT(VARCHAR(8), @kssj, 23) + '01' AS DATETIME)
AND cysj < CAST(CONVERT(VARCHAR(8), @jssj + 31, 23) + '01' AS DATETIME)
GROUP BY ksmc ,
DATEDIFF(DAY, rysj, cysj)
--rysj , 这两列没有在select中,所以报错了
--cysj
SELECT * --这里看一下#temp1中的数据列名到底是什么
FROM #temp1
GROUP BY ksmc
DROP TABLE #temp1
DROP TABLE #temp2
DROP TABLE #temp3
DROP TABLE #temp12
END
ALTER PROCEDURE [dbo].[admin_zy_zyts]
(
@kssj DATETIME ,
@jssj DATETIME
)
AS
BEGIN
SELECT ksmc ,
DATEDIFF(DAY, rysj, cysj) qnts ,
COUNT(djxh) qnrc
INTO #temp12
FROM bq_brbqxxk a ,
jb_ksbmk b
WHERE a.bqdm = b.ksdm
AND zt IN ( 4, 5 )
AND cysj >= DATEADD(YEAR, -1,
CAST(CONVERT(VARCHAR(8), @kssj, 23) + '01' AS DATETIME))
AND cysj < DATEADD(MONTH, -11,
CAST(CONVERT(VARCHAR(8), @jssj + 31, 23)
+ '01' AS DATETIME))
GROUP BY ksmc ,
rysj ,
cysj
SELECT ksmc ,
CONVERT(REAL, SUM(qnts)) / CONVERT(REAL, COUNT(qnrc)) AS jnpjts
INTO #temp3
FROM #temp12
GROUP BY ksmc
SELECT ksmc ,
DATEDIFF(DAY, rysj, cysj) wsnts ,
COUNT(djxh) wsnrc
INTO #temp1
FROM bq_brbqxxk a ,
jb_ksbmk b
WHERE a.bqdm = b.ksdm
AND zt IN ( 4, 5 )
AND cysj > CAST(CONVERT(VARCHAR(8), @kssj, 23) + '01' AS DATETIME)
AND cysj < CAST(CONVERT(VARCHAR(8), @jssj + 31, 23) + '01' AS DATETIME)
GROUP BY ksmc
--rysj , 这两列没有在select中,所以报错了
--cysj
SELECT * --这里看一下#temp1中的数据列名到底是什么
FROM #temp1
GROUP BY ksmc
DROP TABLE #temp1
DROP TABLE #temp2
DROP TABLE #temp3
DROP TABLE #temp12
END