仰天一笑(Ansonxuyu),专业从事软件定制开发、Web软件开发,网站建设,网络推广,APP开发,微博应用开发,微信应用开发,电子商务开发,物联网开发等技术。
互联网8年风雨,愿在此交朋识友,交流心得,分享技术知识(策划/研发/运营/推广/合作)!QQ:943530498


仰天一笑

昨日不悔,今日勿失,明日莫忧! —徐羽

  博客园 :: 首页 :: 博问 :: 闪存 :: 新随笔 :: 联系 :: 订阅 订阅 :: 管理 ::

协助同事整理sql统计报表。


---建立由avalue的视图,要过滤重复数据
CREATE VIEW vLectAnswerRecord
as
SELECT t2.OpenID,t2.Qguid,t1.AValue av FROM TbLectAnswer t1,TbLectAnswerRecord t2 WHERE t1.AID=t2.AID

---获取用户组合答案
SELECT Qguid,OpenID, data=STUFF((
SELECT ','+[t].[av] FROM vLectAnswerRecord t WHERE t.Qguid=t1.Qguid AND t.openid=t1.OpenID FOR XML PATH('')
), 1, 1, '')
FROM vLectAnswerRecord t1 GROUP BY qguid,OpenID

---将组合答案插入到临时表

INSERT into tbtmp( Qguid,OpenID,avs)

SELECT Qguid,OpenID, data=STUFF((
SELECT ','+[t].[av] FROM vLectAnswerRecord t WHERE t.Qguid=t1.Qguid AND t.openid=t1.OpenID FOR XML PATH('')
), 1, 1, '')
FROM vLectAnswerRecord t1 GROUP BY qguid,OpenID

---将组合的逗号去掉
UPDATE tbtmp SET avs2=REPLACE(avs,',','')

---关联试题正确答案表,得出用户题目是否正确
---SELECT * FROM tbtmp

UPDATE tbtmp SET istrue=1
FROM tbtmp INNER JOIN
TBLectQuestion ON tbtmp.Qguid=TBLectQuestion.Qguid AND tbtmp.avs=TBLectQuestion.QResult

--- SELECT TOP 100 Lguid,QResult,Qguid,QType FROM dbo.TBLectQuestion WHERE qguid='5ff05fb1ff62ddbc31324dc30aae5c31'

posted on 2016-09-02 14:02  仰天一笑  阅读(720)  评论(0编辑  收藏  举报