获取行取决于列数和最大值 [英] get row depending on the count of column and max value
问题描述
我有3个表：文件，用户和回复
文件表
docid docName sendTo
1 休假 1
2 private 1
用户表
userid userName
1 john
2 结婚
回复表
replyid docid userid replyDate
1 1 1 14-06 -2015
2 1 2 15-60-2015
如何创建查询获取最后回复日期的回复计数
当我选择的时候文件= 1（john）的se senderid如下所示：
docName numOfReplies lastReply
vacation 2 15-06-2015
private 0 null
我该怎么做才能得到这个结果？
编辑：AL）来自评论
这是我到目前为止所尝试的：
SELECT distinct
dbo.Documents.DocName，
COUNT（ *） over （） AS [numOfReplies]，
max（dbo.Replies，replyDate） as [lastreply]
FROM
dbo.Documents
INNER JOIN dbo.Replies ON dbo.Documents.DocumentID = dbo.Replies.DocumentID
INNER JOIN dbo.Users ON dbo.Documents.SendTo = dbo.Users.UserID
WHERE
dbo.Users.UserID = 1
GROUP BY
dbo.Documents.DocumentID
尝试这样的事情：
WITH cteCounts As
（
SELECT
DocumentID，
Count（*） As numOfReplies，
Max（ReplyDate） As lastReply
FROM
dbo.Replies
GRO UP BY
DocumentID
）
SELECT
D.DocName，
C.numOfReplies，
C.lastReply
FROM
dbo.Documents As D
LEFT JOIN cteCounts < span class =code-keyword> As C
ON C.DocumentID = D.DocumentID
WHERE
D.SendTo = 1
;
我先指出一些问题：
- 样本数据有误 -15 -60-2015不是有效日期
- 在您的尝试中，MAX 不正确 - 您使用的是逗号而不是句点。它应该是max（dbo.Replies.replyDate）为[lastreply]
- 在你的示例SQL中你使用了列DocumentID，但该表有docId
- 您的预期结果完全不依赖于UserId - 它们是每种文档类型的总计/最大日期。
关键似乎是您想看到的所有文档类型，无论它们是否出现在回复表中。你可以得到如下; WITH CTE AS
（
选择 docid，count（replyid） as num，max（replyDate） as maxDate
来自回复
GROUP BY docid
）
SELECT docName，isnull（num， 0 ），maxDate
FROM 文件D
LEFT OUTER JOIN CTE ON D.docid = CTE.docid上面的查询给出了您的预期结果，如图所示。注意，没有引用
userId
但是，如果您想要的是特定用户的详细信息然后在; WITH CTE AS
（
选择用户ID，docid，count（replyid） as num，max（replyDate） as maxDate
来自回复
GROUP BY docid，userid
）
SELECT docName，isnull（num， 0 ），maxDate
FROM 文件D
LEFT OUTER JOIN CTE ON D.docid = CTE.docid
< span class =code-keyword> WHERE userid = 1 或 userid null其中包含
假期1 2015的结果 - 06-14
private 0 NULL请注意不同的日期。
要以您建议的格式获取日期差异非常简单，但可以使SQL看起来有点混乱。我把这个凌乱的位置拉到了第二个公用表表达式中（希望）清楚地说明发生了什么：
; WITH CTE AS
（
选择userid，docid，count（replyid）为num，max（replyDate）为maxDate
来自回复
GROUP BY docid，userid
），
CTE2 AS
（
SELECT CTE.userid，docName，isnull（num，0）as numOfReplies，maxDate
，DateDiff（dd，maxDate，getdate（））as days
，DateDiff（hh，maxDate） ，getdate（））％24当小时
，DateDiff（mi，maxDate，getdate（））％60作为分钟
来自文件D
LEFT OUTER加入CTE对D.docid = CTE。 docid
）
SELECT CTE2.docName，CTE2.numOfReplies，
Res ='（'+ RTRIM（（CASE WHEN CTE2.days> 0 THEN CAST（CTE2.days as varchar）+ 'd'结束）
+（当CTE2.hours&0;那么CAST（CTE2.hours as varchar）+'h'END）
+（例如CTE2.mins> 0那么CAST （CTE2.mins as varchar）+'m'END））
+'）'
来自CTE2
WHERE userid = 1 OR userid为null
作为解释 - 使用DATEDIFF函数计算自MaxDate和Now以来的天数。使用相同的函数模24来获得超过该天数的剩余（整个）小时数，并使用模数为60的日期来获得超过天数和小时数的分钟数。请注意，sql的那部分时间正在滴答作响 - 每次调用getdate（）都会返回一个微妙的不同值，所以不要达到毫秒级的比较！
查询的最后一部分只是尝试将输出格式化。在正常情况下，我会在我的通话程序中这样做（即输出格式化），例如可能类似于var res = new StringBuilder（ （）;
res.Append（（days！= 0 ）？ string .Format（ {0} d，days）： ）;
res .Append（（小时！= 0 ）？ string .Format（ {0} h，小时）： ）;
res.Append（（mins！= 0 ）？ string .Format（ {0} d，mins）： ）;
res.Append（ ））;
Console.WriteLine（res.ToString（）。Replace（ ）， ）））;
i have 3 tables : Documents, Users and Replies
Documents table
docid docName sendTo
1 vacation 1
2 private 1
Users table
userid userName
1 john
2 marry
Replies table
replyid docid userid replyDate
1 1 1 14-06-2015
2 1 2 15-60-2015
how can i create query get the count of replies with last reply date
when i choose senderid of document=1 (john) appear like this :
docName numOfReplies lastReply
vacation 2 15-06-2015
private 0 null
how can i do that to get this result?
EDIT: AL) from comments
This is what I have tried so far:
SELECT distinct
dbo.Documents.DocName,
COUNT(*) over () AS [numOfReplies],
max(dbo.Replies,replyDate) as [lastreply]
FROM
dbo.Documents
INNER JOIN dbo.Replies ON dbo.Documents.DocumentID = dbo.Replies.DocumentID
INNER JOIN dbo.Users ON dbo.Documents.SendTo = dbo.Users.UserID
WHERE
dbo.Users.UserID = 1
GROUP BY
dbo.Documents.DocumentIDTry something like this:
WITH cteCounts As ( SELECT DocumentID, Count(*) As numOfReplies, Max(ReplyDate) As lastReply FROM dbo.Replies GROUP BY DocumentID ) SELECT D.DocName, C.numOfReplies, C.lastReply FROM dbo.Documents As D LEFT JOIN cteCounts As C ON C.DocumentID = D.DocumentID WHERE D.SendTo = 1 ;
I'll point out some of the problems first:
- There is a mistake in your sample data -15-60-2015is not a valid date
- In your attempt the syntax forMAXis incorrect - you've used a comma instead of a period. It should bemax(dbo.Replies.replyDate) as [lastreply]
- In your example SQL you have used columnDocumentIDbut the table hasdocId
- Your expected results do not depend on UserId at all - they are the totals / max date per document type.
The key seems to be that you want to see all document types regardless of whether they appear in theRepliestable. You can get those as follows;WITH CTE AS ( select docid, count(replyid) as num, max(replyDate) as maxDate from Replies GROUP BY docid ) SELECT docName, isnull(num,0), maxDate FROM Documents D LEFT OUTER JOIN CTE ON D.docid = CTE.docidThe query above gives your expected results as shown. Note there is no reference to
userId
If, however, what you want is the details for a particular user then introduceUserIdinto the mix as follows;WITH CTE AS ( select userid, docid, count(replyid) as num, max(replyDate) as maxDate from Replies GROUP BY docid, userid ) SELECT docName, isnull(num,0), maxDate FROM Documents D LEFT OUTER JOIN CTE ON D.docid = CTE.docid WHERE userid = 1 OR userid is nullWhich gives results of
vacation 1 2015-06-14 private 0 NULLNote the different date.
[EDIT - after OP request for further information]
To get the difference in dates in the format you've suggested is quite simple but can make the SQL look a little messy. I've pulled the messy bit into a 2nd common table expression to (hopefully) make it clear what is going on :
;WITH CTE AS ( select userid, docid, count(replyid) as num, max(replyDate) as maxDate from Replies GROUP BY docid, userid ), CTE2 AS ( SELECT CTE.userid, docName, isnull(num,0) as numOfReplies, maxDate ,DateDiff(dd, maxDate, getdate()) As days , DateDiff(hh, maxDate, getdate()) % 24 As hours , DateDiff(mi, maxDate, getdate()) % 60 As mins FROM Documents D LEFT OUTER JOIN CTE ON D.docid = CTE.docid ) SELECT CTE2.docName, CTE2.numOfReplies, Res = '(' + RTRIM((CASE WHEN CTE2.days > 0 THEN CAST(CTE2.days as varchar) + 'd ' END) + (CASE WHEN CTE2.hours > 0 THEN CAST(CTE2.hours as varchar) + 'h ' END) + (CASE WHEN CTE2.mins > 0 THEN CAST(CTE2.mins as varchar) + 'm' END)) + ')' from CTE2 WHERE userid = 1 OR userid is null
By way of explanation - use the DATEDIFF function to work out the number of days since MaxDate and Now. Use the same function modulo 24 to get the remaining number of (whole) hours over and above that number of days, and datediff modulo 60 to get the minutes over and above the number of days and hours. Be aware that time is ticking in that part of the sql - each call to getdate() will return a subtly different value, so don't go down to the millisecond level of comparison!
The final part of the query is just trying to get the output formatted. Under normal circumstances I would do that (i.e. output formatting) in my calling program e.g. probably something likevar res = new StringBuilder("("); res.Append((days != 0) ? string.Format("{0}d ", days) : ""); res.Append((hours != 0) ? string.Format("{0}h ", hours) : ""); res.Append((mins != 0) ? string.Format("{0}d ", mins) : ""); res.Append(")"); Console.WriteLine(res.ToString().Replace(" )", ")"));
这篇关于获取行取决于列数和最大值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
