主页 / dba / 问题 / 103840
Accepted
Jmaurier
Asked: 2015-06-12 13:53:00 +0800 CST

基于 count()、日期范围和字符串匹配进行选择

  • 772

我会在这里尽力而为。

我有一份已写的引文清单以及有关这些引文的一些信息。我需要选择在给定日期范围内已作废的引文，以及撰写它的人在同一日期范围内也写了 5 次以上的引文。

我想要一个结果，显示那个人 'x' 写的所有引文 引文 1 ... 信息，引文 2 ... 信息，引文 3 ... 信息，引文 4 ... 信息，引文 5 ... 信息， ... ETC

DECLARE @BeginDate Date = '3/15/2015'
DECLARE @EndDate Date = '4/1/2015'
DECLARE @MinVoids INT = 5

SELECT d.CreatorUserId

FROM [ECITATION].[dbo].[tblDocument] AS d
    JOIN [ARK].[dbo].[DocStatus] AS ds 
    ON  d.DocStatus = ds.StatusNum AND (ds.StatusName LIKE 'void%')

WHERE DateModified BETWEEN @BeginDate AND @EndDate 
--DateModified is when the document was voided--

GROUP BY d.CreatorUserId
HAVING COUNT(d.CreatorUserId) >= @MinVoids

这给了我正确的指导，但不是全部。我一辈子都弄不明白为什么它没有抓住所有人。（应该是 16，我只得到 11）。我有一种感觉，COUNT 没有按照我想要的方式工作。

只是因为我偏执于在 SQL 专家面前显得无知，我大约两周前才开始学习 SQL。

解决方案 我遗漏了一些可能有帮助的信息，但答案仍然对解决问题有很大帮助。

DECLARE @BeginDate Date = '3/15/2015'
DECLARE @EndDate Date = '4/1/2015'
DECLARE @MinVoids INT = 5

SELECT d.CreatorFirstName + ' ' + d.CreatorLastName as name
    ,ds.StatusName

FROM [ECITATION].[dbo].[tblDocument] AS d
    JOIN [ARK].[dbo].[DocStatus] AS ds 
    ON  d.DocStatus = ds.StatusNum 

WHERE DateModified BETWEEN @BeginDate AND @EndDate
    AND (ds.StatusName LIKE 'void%')
    AND ((ds.StatusNum BETWEEN 1 AND 20) OR (ds.StatusNum BETWEEN 100 AND 120))
    AND ( SELECT COUNT(*)
        FROM [ECITATION].[dbo].[tblDocument] AS dc
            JOIN [ARK].[dbo].[DocStatus] AS dst 
            ON  dc.DocStatus = dst.StatusNum
        WHERE dc.CreatorUserId = d.CreatorUserId
            AND (dst.StatusName LIKE 'void%')
            AND dc.DateCreated BETWEEN @BeginDate AND @EndDate
            AND ((dst.StatusNum BETWEEN 1 AND 20) OR (dst.StatusNum BETWEEN 100 AND 120))
      ) >= @MinVoids
ORDER BY name
sql-server

1 个回答

  1. Best Answer

    这不是一个简单的查询。您还需要一个连接或一个子查询：

    FROM [ECITATION].[dbo].[tblDocument] AS d
    JOIN [ARK].[dbo].[DocStatus] AS ds 
    ON  d.DocStatus = ds.StatusNum AND ds.StatusName LIKE 'void%'

WHERE ds.DateModified BETWEEN @BeginDate AND @EndDate 
--DateModified is when the document was voided--

  AND ( SELECT COUNT(*)
        FROM [ECITATION].[dbo].[tblDocument] AS dc
        WHERE dc.CreatorUserId = d.CreatorUserId
          AND dc.DateCreated BETWEEN @BeginDate AND @EndDate 
      ) >= @MinVoids ;

    不确定DateCreated表中列（使用）的名称[tblDocument]。相应修改

    • 3

相关问题