22,210
社区成员
发帖
与我相关
我的任务
分享
现在我写的代码只能查询出来每个人的合计拜访量,但是不能剔除3天内重复拜访的客户信息
SELECT a.[id],a.[xingming],isnull(count(b.[kehuID]),0) as [合计]
FROM [Sys_Userinfo] a
left join [Ribaobiao2] r
on (a.[id]=r.[luruname_userid] AND r.bfdate>='2014-11-01' AND r.bfdate<='2014-11-31')
left join [Ribaobiao_Record] b
on r.ribaoNO=b.ribaoNO
WHERE a.UserDept='1014' OR a.UserDept='1015' OR a.UserDept='1016' OR a.UserDept='1017' OR a.UserDept='1019' OR a.UserDept='1020'
group by a.[id],a.[xingming]
把结果插入到一个YuebaiFangLiang 表中,然后程序查询这个表的数据 每次执行存储过程的时候先删除这个表
DROP TABLE YuebaiFangLiang
--查询结果
SELECT T1.id,T1.username,COUNT(T2.id)[heji] into YuebaiFangLiang
[/quote]我刚在想,用临时表代替表变量CREATE proc sp_YuebaiFangLiang
(
@Kqyue VARCHAR(10)
)
as
DECLARE @DateLevel INT
SET @DateLevel=1
SELECT T1.luruname_userID id,T1.bfdate,T2.kehuName INTO #TEMPTB
FROM Ribaobiao2 T1
JOIN Ribaobiao_Record T2 ON T1.ribaoNO=T2.ribaoNO
WHERE T1.bfdate>='2014-11-01'AND T1.bfdate<'2014-11-30'
--INSERT INTO #RESULT
SELECT id,MIN(bfdate)bfdate,kehuName,@DateLevel DateLevel INTO #RESULT
FROM #TEMPTB
GROUP BY id,kehuName
--2000没有递归,采用迭代
WHILE @@ROWCOUNT>0
BEGIN
SET @DateLevel=@DateLevel+1
INSERT INTO #RESULT
SELECT T1.id,MIN(T1.bfdate),T1.kehuName,@DateLevel
FROM #TEMPTB T1 JOIN #RESULT T2 ON T1.id=T2.id AND T1.kehuName=T2.kehuName AND T2.DateLevel=@DateLevel-1
AND T1.bfdate>=DATEADD(DAY,2,CONVERT(VARCHAR(10),T2.bfdate,120))
GROUP BY T1.id,T1.kehuName
END
--查询结果
SELECT T1.id,T1.username,COUNT(T2.id)[月拜访合计]
FROM Sys_Userinfo T1
LEFT JOIN #RESULT T2 ON T1.id=T2.id
where
UserDept='1014' OR UserDept='1015' OR UserDept='1016' OR UserDept='1017' OR UserDept='1019' OR UserDept='1020'
GROUP BY T1.id,T1.username
DROP TABLE #TEMPTB
DROP TABLE #RESULT
GO
不确定可不可以[/quote]
还是不行,不弄了 用刚才说的笨办法 实现!
把结果插入到一个YuebaiFangLiang 表中,然后程序查询这个表的数据 每次执行存储过程的时候先清空这个表
把结果插入到一个YuebaiFangLiang 表中,然后程序查询这个表的数据 每次执行存储过程的时候先删除这个表
DROP TABLE YuebaiFangLiang
--查询结果
SELECT T1.id,T1.username,COUNT(T2.id)[heji] into YuebaiFangLiang
[/quote]我刚在想,用临时表代替表变量CREATE proc sp_YuebaiFangLiang
(
@Kqyue VARCHAR(10)
)
as
DECLARE @DateLevel INT
SET @DateLevel=1
SELECT T1.luruname_userID id,T1.bfdate,T2.kehuName INTO #TEMPTB
FROM Ribaobiao2 T1
JOIN Ribaobiao_Record T2 ON T1.ribaoNO=T2.ribaoNO
WHERE T1.bfdate>='2014-11-01'AND T1.bfdate<'2014-11-30'
--INSERT INTO #RESULT
SELECT id,MIN(bfdate)bfdate,kehuName,@DateLevel DateLevel INTO #RESULT
FROM #TEMPTB
GROUP BY id,kehuName
--2000没有递归,采用迭代
WHILE @@ROWCOUNT>0
BEGIN
SET @DateLevel=@DateLevel+1
INSERT INTO #RESULT
SELECT T1.id,MIN(T1.bfdate),T1.kehuName,@DateLevel
FROM #TEMPTB T1 JOIN #RESULT T2 ON T1.id=T2.id AND T1.kehuName=T2.kehuName AND T2.DateLevel=@DateLevel-1
AND T1.bfdate>=DATEADD(DAY,2,CONVERT(VARCHAR(10),T2.bfdate,120))
GROUP BY T1.id,T1.kehuName
END
--查询结果
SELECT T1.id,T1.username,COUNT(T2.id)[月拜访合计]
FROM Sys_Userinfo T1
LEFT JOIN #RESULT T2 ON T1.id=T2.id
where
UserDept='1014' OR UserDept='1015' OR UserDept='1016' OR UserDept='1017' OR UserDept='1019' OR UserDept='1020'
GROUP BY T1.id,T1.username
DROP TABLE #TEMPTB
DROP TABLE #RESULT
GO
不确定可不可以
把结果插入到一个YuebaiFangLiang 表中,然后程序查询这个表的数据 每次执行存储过程的时候先删除这个表
DROP TABLE YuebaiFangLiang
--查询结果
SELECT T1.id,T1.username,COUNT(T2.id)[heji] into YuebaiFangLiang
declare @P1 int
set @P1=0
declare @P2 int
set @P2=98305
declare @P3 int
set @P3=98305
declare @P4 int
set @P4=0
exec sp_cursoropen @P1 output, N'exec sp_YuebaiFangLiang ''2014-11-01''', @P2 output, @P3 output, @P4 output
select @P1, @P2, @P3, @P4
exec sp_YuebaiFangLiang '2014-11-01'
set rs=server.createobject("adodb.recordset")
sql="exec sp_YuebaiFangLiang '2014-11-01'"
rs.open sql,conn,1,1
create proc sp_RibaobiaoKq
(
@Kqyue VARCHAR(10)
)
as
DECLARE @MONTH DATETIME
SET @MONTH=@Kqyue
DECLARE @SQL VARCHAR(8000)
SET @SQL='SELECT T1.Username,T2.*,'+CAST(DAY(DATEADD(DAY,-1,DATEADD(MONTH,1,@MONTH))) AS VARCHAR(10))+'-[hejiCq] [hejiQq] FROM Sys_Userinfo T1 LEFT JOIN(SELECT luruname_userID'
SELECT @SQL=@SQL+',ISNULL(MAX(CASE WHEN DAY(bfdate)='+CAST(number AS VARCHAR(2))+' THEN''1''END),''0'')['+CAST(number AS VARCHAR(2))+']'
FROM master..spt_values WHERE type='P'AND number>0 AND number<=DAY(DATEADD(DAY,-1,DATEADD(MONTH,1,@MONTH)))--列数随月份变化
SET @SQL=@SQL+',ISNULL(COUNT(DISTINCT bfdate),0)[hejiCq]'
SET @SQL=@SQL+'FROM Ribaobiao2'
--月份条件在这过滤最好
--查询列不进行计算
+' WHERE bfdate>='''+CONVERT(VARCHAR(10),@MONTH,120)+'''AND bfdate<'''+CONVERT(VARCHAR(10),DATEADD(MONTH,1,@MONTH),120)
+''' GROUP BY luruname_userID)T2 ON T1.Id=T2.luruname_userID'
SET @SQL=@SQL+' WHERE T1.UserDept IN(''1014'',''1015'',''1016'',''1017'',''1019'',''1020'')'
--UserDept可以加到上面,T1表的条件都可以加到上面
--都是常量,可以直接用IN
EXEC(@SQL)
GO
这段代码 我通过程序就调用不到值,通过查询分析器就可以 显示出来呢,都是存储过程!
CREATE proc sp_YuebaiFangLiang
(
@Kqyue VARCHAR(10)
)
as
DECLARE @RESULT TABLE(id INT,bfdate DATETIME,kehuName NVARCHAR(100),DateLevel INT)
DECLARE @DateLevel INT
SET @DateLevel=1
DECLARE @TABLE TABLE(id INT,bfdate DATETIME,kehuName NVARCHAR(100))
INSERT INTO @TABLE
SELECT T1.luruname_userID,T1.bfdate,T2.kehuName
FROM Ribaobiao2 T1
JOIN Ribaobiao_Record T2 ON T1.ribaoNO=T2.ribaoNO
WHERE T1.bfdate>='2014-11-01'AND T1.bfdate<'2014-11-30'
INSERT INTO @RESULT
SELECT id,MIN(bfdate),kehuName,@DateLevel
FROM @TABLE
GROUP BY id,kehuName
--2000没有递归,采用迭代
WHILE @@ROWCOUNT>0
BEGIN
SET @DateLevel=@DateLevel+1
INSERT INTO @RESULT
SELECT T1.id,MIN(T1.bfdate),T1.kehuName,@DateLevel
FROM @TABLE T1 JOIN @RESULT T2 ON T1.id=T2.id AND T1.kehuName=T2.kehuName AND T2.DateLevel=@DateLevel-1
AND T1.bfdate>=DATEADD(DAY,2,CONVERT(VARCHAR(10),T2.bfdate,120))
GROUP BY T1.id,T1.kehuName
END
--查询结果
SELECT T1.id,T1.username,COUNT(T2.id)[月拜访合计]
FROM Sys_Userinfo T1
LEFT JOIN @RESULT T2 ON T1.id=T2.id
where
UserDept='1014' OR UserDept='1015' OR UserDept='1016' OR UserDept='1017' OR UserDept='1019' OR UserDept='1020'
GROUP BY T1.id,T1.username
GO
RIGHT JOIN Ribaobiao_Record T2 ON T1.ribaoNO=T2.ribaoNO
SELECT T1.luruname_userID,T1.bfdate,T2.kehuName
FROM Ribaobiao2 T1
LEFT JOIN Ribaobiao_Record T2 ON T1.ribaoNO=T2.ribaoNO
WHERE T1.bfdate>='2014-10-01'AND T1.bfdate<'2014-11-01'
AND T1.luruname_userID=45
ORDER BY T2.kehuName
SELECT T1.luruname_userID,T1.bfdate,T2.kehuName
FROM Ribaobiao2 T1
LEFT JOIN Ribaobiao_Record T2 ON T1.ribaoNO=T2.ribaoNO
WHERE T1.bfdate>='2014-10-01'AND T1.bfdate<'2014-11-01'
这个ID为45的人,在10月26号有拜访记录,却没有记录他拜访了谁DECLARE @RESULT TABLE(id INT,bfdate DATETIME,kehuName NVARCHAR(100),DateLevel INT)
DECLARE @DateLevel INT
SET @DateLevel=1
DECLARE @TABLE TABLE(id INT,bfdate DATETIME,kehuName NVARCHAR(100))
INSERT INTO @TABLE
SELECT T1.luruname_userID,T1.bfdate,T2.kehuName
FROM Ribaobiao2 T1
LEFT JOIN Ribaobiao_Record T2 ON T1.ribaoNO=T2.ribaoNO
WHERE T1.bfdate>='2014-10-01'AND T1.bfdate<'2014-11-01'
INSERT INTO @RESULT
SELECT id,MIN(bfdate),kehuName,@DateLevel
FROM @TABLE
GROUP BY id,kehuName
--2000没有递归,采用迭代
WHILE @@ROWCOUNT>0
BEGIN
SET @DateLevel=@DateLevel+1
INSERT INTO @RESULT
SELECT T1.id,MIN(T1.bfdate),T1.kehuName,@DateLevel
FROM @TABLE T1 JOIN @RESULT T2 ON T1.id=T2.id AND T1.kehuName=T2.kehuName AND T2.DateLevel=@DateLevel-1
AND T1.bfdate>=DATEADD(DAY,3,CONVERT(VARCHAR(10),T2.bfdate,120))
GROUP BY T1.id,T1.kehuName
END
--查询结果
SELECT T1.id,T1.username,COUNT(T2.id)[月拜访合计]
FROM Sys_Userinfo T1
LEFT JOIN @RESULT T2 ON T1.id=T2.id
GROUP BY T1.id,T1.username
看了你的数据,我发现有一个地方,就是
Ribaobiao2表记录某个人有去拜访的记录
而对应的Ribaobiao_Record表,却没有记录去哪个地方的记录
这种怎么算呢,算不算一次
WITH Sys_Userinfo(id,username) AS (
SELECT 1,'张三' UNION ALL
SELECT 2,'李四' UNION ALL
SELECT 3,'王二' UNION ALL
SELECT 4,'王二麻子'
)
,Ribaobiao2(id,ribaoNO,bfdate,userid) AS (
SELECT 1,11,'2014-10-21',1 UNION ALL
SELECT 2,12,'2014-10-21',2 UNION ALL
SELECT 3,13,'2014-10-22',1 UNION ALL
SELECT 4,14,'2014-10-22',3
)
,Ribaobiao_Record(id,ribaoNO,kehuName) AS (
SELECT 1,11,'永盛汽修' UNION ALL
SELECT 2,11,'玉松汽修' UNION ALL
SELECT 3,11,'奥迪修理厂' UNION ALL
SELECT 4,12,'大众修理厂' UNION ALL
SELECT 5,14,'大众修理厂' UNION ALL
SELECT 6,13,'永盛汽修'
)
,r AS (
SELECT r2.userid, r3.kehuName, r2.bfdate
FROM Ribaobiao2 r2
JOIN Ribaobiao_Record r3
ON r3.ribaoNO = r2.ribaoNO
)
,f AS (
SELECT userid, kehuName, min(bfdate) AS bfdate
FROM r
GROUP BY userid, kehuName
UNION ALL
SELECT r.userid,
r.kehuName,
r.bfdate
FROM f
JOIN r
ON r.userid = f.userid
AND r.kehuName = f.kehuName
AND DateDiff(day,f.bfdate,r.bfdate) >= 3
WHERE NOT EXISTS ( -- 递归不能求 MIN,用 NOT EXISTS 实现
SELECT *
FROM r r2
WHERE r2.userid = r.userid
AND r2.kehuName = r.kehuName
AND r2.bfdate > r.bfdate)
)
,s AS (
SELECT userid, COUNT(*) c
FROM f
GROUP BY userid
)
SELECT u.id,
u.username,
s.c 月拜访合计
FROM Sys_Userinfo u
LEFT JOIN s
ON u.id = s.userid
id username 月拜访合计
----------- -------- -----------
1 张三 3
2 李四 1
3 王二 1
4 王二麻子 NULL
表一员工信息表、Sys_Userinfo
id username
1 张三
2 李四
3 王二
4 王二麻子
表二客户拜访表、Ribaobiao2
id ribaoNO bfdate userid
1 11 2014-10-21 1
2 12 2014-10-21 2
3 13 2014-10-22 1
4 14 2014-10-22 3
表三客户拜访记录表、Ribaobiao_Record
id ribaoNO kehuName
1 11 永盛汽修
2 11 玉松汽修
3 11 奥迪修理厂
4 12 大众修理厂
5 14 大众修理厂
6 13 永盛汽修
就目前数据看来,张三 10-21日拜访了“永盛汽修”3天之内 10-22 又拜访了 “永盛汽修”,那只能算张三拜访了1次该客户。
期待的结果是
查询所有人员的月拜访量合计, 对3天内重复拜访的客户只能算一次拜访
id username 月拜访合计
1 张三 3
2 李四 1
3 王二 1
4 王二麻子 0
/*id username (无列名)
1 张三 1
2 李四 0
3 王二 1
4 王二麻子 1*/
对吗?
所以如果还不对的话,期待你更多能体现你要的效果的数据,不必传文件,
直接贴些代表性的即可
测试数据+期待结果