SQL Server:检查组中是否存在行

Question

使用SQL Server提取一些数据，我想得到存在或不做进一步分析的行。使用以下代码，我无法获得预期的输出。假设我想添加一个列，如果价格19.17000存在一周，则给出一个标志。以下代码将所有内容标记为存在，但我只想将价格存在的周标记为存在

代码:

SELECT 
(CASE WHEN EXISTS (SELECT 1 
FROM table1 WITH (NOLOCK)
WHERE Price = 19.170000
GROUP BY companyId, YEAR([calendarDate]), datepart(week, [calendarDate])) 
THEN 'Record exists'
ELSE 'Record doesn''t exist' 
END) AS [Transaction],
YEAR([calendarDate]) AS year_,  
DATEPART (week, [calendarDate]) AS week_,
calendarDate,
Price
FROM 
table1
WHERE 
companyId = 18527

输出:

Transaction     year_   week_   calendarDate    stockPrice
----------------------------------------------------------
Record Exists   2010    7   2010-02-12  19.170000
Record Exists   2010    7   2010-02-13  19.170000
Record Exists   2010    8   2010-02-14  19.170000
Record Exists   2010    8   2010-02-15  19.220000
Record Exists   2010    8   2010-02-16  19.400000
Record Exists   2010    8   2010-02-17  19.770000
Record Exists   2010    8   2010-02-18  21.280000
Record Exists   2010    8   2010-02-19  21.340000
Record Exists   2010    8   2010-02-20  21.340000
Record Exists   2010    9   2010-02-21  21.340000
Record Exists   2010    9   2010-02-22  21.590000
Record Exists   2010    9   2010-02-23  21.370000
Record Exists   2010    9   2010-02-24  21.510000
Record Exists   2010    9   2010-02-25  21.520000
Record Exists   2010    9   2010-02-26  21.730000
Record Exists   2010    9   2010-02-27  21.730000

预期输出:

Transaction             year_ week_ calendarDate    stockPrice
--------------------------------------------------------------
Record Exists           2010    7   2010-02-12  19.170000
Record Exists           2010    7   2010-02-13  19.170000
Record Exists           2010    8   2010-02-14  19.170000
Record doesn''t Exists  2010    8   2010-02-15  19.220000
Record doesn''t Exists  2010    8   2010-02-16  19.400000
Record doesn''t Exists  2010    8   2010-02-17  19.770000
Record doesn''t Exists  2010    8   2010-02-18  21.280000
Record doesn''t Exists  2010    8   2010-02-19  21.340000
Record doesn''t Exists  2010    8   2010-02-20  21.340000
Record doesn''t Exists  2010    9   2010-02-21  21.340000
Record doesn''t Exists  2010    9   2010-02-22  21.590000
Record doesn''t Exists  2010    9   2010-02-23  21.370000

Answer 1

您没有获得特定周的记录，您的内部查询返回具有Price = 19.170000的随机前1记录，您需要在case语句中添加以下条件以获取特定周的数据。

t2.companyId = 18527 AND T1.YEAR([calendarDate])=T2.YEAR([calendarDate]) 
AND T1.datepart(week, [calendarDate]) = T2.datepart(week, [calendarDate])

完整的查询应该如下所示:

SELECT (
CASE 
WHEN EXISTS (
SELECT 1
FROM table1 t2 WITH (NOLOCK)
WHERE Price = 19.170000 AND t2.companyId = 18527
AND T1.YEAR([calendarDate]) = T2.YEAR([calendarDate])
AND T1.datepart(week, [calendarDate]) = T2.datepart(week, [calendarDate])
GROUP BY companyId
,YEAR([calendarDate])
,datepart(week, [calendarDate])
)
THEN 'Record Exists'
ELSE 'Record doesn''t Exists'
END
) AS [Transaction]
,YEAR([calendarDate]) AS year_
,datepart(week, [calendarDate]) AS week_
,calendarDate
,Price
FROM table1 t1
WHERE t1.companyId = 18527

Answer 2

可以使用一个条件窗口计数

SELECT 
CASE WHEN COUNT(CASE WHEN Price = 19.170000 THEN 1 END)
OVER (PARTITION BY companyId, YEAR([calendarDate]), datepart(week, [calendarDate])) > 0
THEN 'Record exists'
ELSE 'Record doesn''t exist' 
END AS [Transaction],
YEAR([calendarDate]) AS year_,  
DATEPART (week, [calendarDate]) AS week_,
calendarDate,
Price
FROM 
table1
WHERE 
companyId = 18527

如果您想包含其他companyid值的数据(如您的原始查询)，然后将计数放入派生表

SELECT 
[Transaction],
YEAR([calendarDate]) AS year_,  
DATEPART (week, [calendarDate]) AS week_,
calendarDate,
Price
FROM (
SELECT *,
CASE WHEN COUNT(CASE WHEN Price = 19.170000 THEN 1 END)
OVER (PARTITION BY companyId, YEAR([calendarDate]), datepart(week, [calendarDate])) > 0
THEN 'Record exists'
ELSE 'Record doesn''t exist' 
END AS [Transaction]
FROM table1
) table1
WHERE 
companyId = 18527

旁注:做不使用NOLOCK除非你真的知道你在做什么，它可以导致完全不正确的结果