上面的写错了一些
DECLARE @a varchar(20)
DECLARE @b varchar(10)Set @a=cast(getdate() as varchar(20))
Set @b=substring(@a,1,2) + '%' + substring(@a,7,4)
SELECT usr_info_view.bumen_name 所在部门,anti.name 姓名,COUNT(*) 次数
FROM anti INNER JOIN tiku
ON
anti.tiid=tiku.id
AND Cast(anti.andate as varchar(10)) LIKE @b
AND anti.good1=tiku.good1
AND anti.good2=tiku.good2
AND anti.good3=tiku.good3
AND anti.good4=tiku.good4
AND anti.good5=tiku.good5
AND anti.good6=tiku.good6
INNER JOIN usr_info_view
ON
anti.name=usr_info_view.name AND anti.bumen_id=usr_info_view.bumen_id
GROUP BY anti.name,usr_info_view.bumen_name
ORDER BY 次数 DESC
如果来做,也就是说上面出来的只是中间结果,那么如何在不保存的情况下继续查询
DECLARE @a varchar(20)
DECLARE @b varchar(10)Set @a=cast(getdate() as varchar(20))
Set @b=substring(@a,1,2) + '%' + substring(@a,7,4)
SELECT usr_info_view.bumen_name 所在部门,anti.name 姓名,COUNT(*) 次数
FROM anti INNER JOIN tiku
ON
anti.tiid=tiku.id
AND Cast(anti.andate as varchar(10)) LIKE @b
AND anti.good1=tiku.good1
AND anti.good2=tiku.good2
AND anti.good3=tiku.good3
AND anti.good4=tiku.good4
AND anti.good5=tiku.good5
AND anti.good6=tiku.good6
INNER JOIN usr_info_view
ON
anti.name=usr_info_view.name AND anti.bumen_id=usr_info_view.bumen_id
GROUP BY anti.name,usr_info_view.bumen_name
ORDER BY 次数 DESC
如果来做,也就是说上面出来的只是中间结果,那么如何在不保存的情况下继续查询
但是你的这段SQL中有一个GetDATE()函数,他在函数中不支持!我帮你改写了一下!CREATE FUNCTION fn_MyName(
@a VARCHAR(20)
)
RETURNS @MyTable TABLE (bumen_name VARCHAR (30),
anti_name VARCHAR (30),
iCount INT)
AS
BEGIN
DECLARE @b varchar(10)
Set @b=substring(@a,1,2) + '%' + substring(@a,7,4) INSERT INTO @MyTable
SELECT usr_info_view.bumen_name 所在部门,anti.name 姓名,COUNT(*) 次数
FROM anti INNER JOIN tiku
ON
anti.tiid=tiku.id
AND Cast(anti.andate as varchar(10)) LIKE @b
AND anti.good1=tiku.good1
AND anti.good2=tiku.good2
AND anti.good3=tiku.good3
AND anti.good4=tiku.good4
AND anti.good5=tiku.good5
AND anti.good6=tiku.good6
INNER JOIN usr_info_view
ON
anti.name=usr_info_view.name AND anti.bumen_id=usr_info_view.bumen_id
GROUP BY anti.name,usr_info_view.bumen_name
ORDER BY 次数 DESC
RETURN
END
-------------------------------------
--调用方法:DECLARE @dDate varchar(20)
Set @dDate =cast(getdate() as varchar(20))
--在这里写你的二次查询语句
select * from fn_MyName(@dDate)
DECLARE @b varchar(10)Set @a=cast(getdate() as varchar(20))
Set @b=substring(@a,1,2) + '%' + substring(@a,7,4) select * from
(
SELECT usr_info_view.bumen_name 所在部门,anti.name 姓名,COUNT(*) 次数
FROM anti INNER JOIN tiku
ON
anti.tiid=tiku.id
AND Cast(anti.andate as varchar(10)) LIKE @b
AND anti.good1=tiku.good1
AND anti.good2=tiku.good2
AND anti.good3=tiku.good3
AND anti.good4=tiku.good4
AND anti.good5=tiku.good5
AND anti.good6=tiku.good6
INNER JOIN usr_info_view
ON
anti.name=usr_info_view.name AND anti.bumen_id=usr_info_view.bumen_id
GROUP BY anti.name,usr_info_view.bumen_name
--ORDER BY 次数 DESC 这一句要去掉
) as TT