在存储过程中多选择语句，以获得期望的结果(Multiple select statement in

大家好我有两个表，即tblTechnology和tblQuestions其中tblTechnology保留有关类似技术的信息Asp.net, c# and all与tblQuestions持有的信息QuestionID] ,[QuestionTitle] ,[QuestionDesc] ,[DatePosted] ,[UserName] ,[TechID] ,[viewCount] ,[ReplyCount]

最初，我有一个书面的存储过程，以得到期望的结果，现在我想获得从每个技术的最新插入的记录tblQuestions ，这是我在数据tblquestion

由此我想从每个技术得到最后插入的问题ORDER BY Dateposted

这是我最初写

 SELECT TechName,TechDesc, tblTechnology.TechID as ID, COUNT(QuestionDesc) AS 
  'Totalposts',sum(ReplyCount) as ReplyCount FROM tblTechnology LEFT JOIN 
  tblQuestions ON tblQuestions.TechID = tblTechnology.TechID 
     GROUP BY tblTechnology.TechID, TechName,TechDesc

这给出结果如下

我想有我QuestionTitle ， DatePosted和Username包括在结果等都可以有一个人帮助我

Answer 1:

http://www.sqlfiddle.com/#!3/f5fe6/5

WITH A AS (
    SELECT top(1) WITH ties Q.TechID
        ,QuestionID
        ,QuestionTitle
        ,DatePosted
        ,Username
    FROM tblTechnology T LEFT JOIN tblQuestions Q ON Q.TechID = T.TechID
    ORDER BY row_number() over(partition BY Q.TechID ORDER BY Dateposted DESC)
)
SELECT * FROM A
OUTER apply (SELECT count(QuestionDesc) Totalposts, sum(ReplyCount) ReplyCount
    FROM tblQuestions WHERE A.TechID=tblQuestions.TechID) D

文章来源: Multiple select statement in stored procedure to get the desired result