SQL Server查询问题 [英] SQL Server Query question

问题描述

我有一个看起来像这样的表（该表也有一个唯一的ID列）：

I have a table that looks something like this (the table also has a unique ID column):

name    value    month    year
John    0.50      11      2014
Mary    0.60      11      2014
Pete    0.60      11      2014
John    0.60      12      2014
Don     0.50      10      2013

如果我将11和2014传递给存储过程，我想为每个名称检索一条记录，如下所示：

If I pass 11 and 2014 to a stored proc, I want to retrieve one record for each of name, like so:

John    0.50      11      2014
Mary    0.60      11      2014
Pete    0.60      11      2014
Don     0.50      10      2013

如果我将12和2014传递给存储过程，我想为每个名称检索一条记录，如下所示：

If I pass 12 and 2014 to a stored proc, I want to retrieve one record for each of name, like so:

Mary    0.60      11      2014
Pete    0.60      11      2014
John    0.60      12      2014
Don     0.50      10      2013

如果我将10和2014传递给存储过程，我想检索每个名称的一条记录，如下所示：

If I pass 10 and 2014 to a stored proc, I want to retrieve one record for each of name, like so:

Mary    0.0       10      2014
Pete    0.0       10      2014
John    0.0       10      2014
Don     0.50      10      2013

如何在sql server中执行此操作？

How would I do that in sql server?

推荐答案

也许这会有所帮助。 LEFT将DISTINCT名称列表加入数据并使用ISNULL：



（需要两个）

Maybe this will help. LEFT JOIN the list of DISTINCT names to the data and use ISNULL:

(take two)

DECLARE @yr INTEGER = 2014
DECLARE @mn INTEGER = 10
DECLARE @ym INTEGER = @yr*100+@mn

;
WITH src AS
(
  SELECT 'John' [name] , 0.50 [value] , 11 [month] , 2014 [year]
UNION ALL
  SELECT 'Mary' 'name' , 0.60 'value' , 11 'month' , 2014 'year'
UNION ALL
  SELECT 'Pete' 'name' , 0.60 'value' , 11 'month' , 2014 'year'
UNION ALL
  SELECT 'John' 'name' , 0.60 'value' , 12 'month' , 2014 'year'
UNION ALL
  SELECT 'Don' 'name' , 0.50 'value' , 10 'month' , 2013 'year'
)
, lst AS
(
  SELECT [name]
  , [value]
  , [month]
  , [year]
  FROM (
    SELECT *
    , ROW_NUMBER() OVER ( PARTITION BY [name] ORDER BY [year] DESC , [month] DESC) RN
    FROM src
    WHERE [year]*100+[month]<=@ym
  ) T
  WHERE RN=1
)
, nam AS
(
  SELECT DISTINCT [name] FROM src
)
SELECT A.Name
, ISNULL(B.value,0) 'value'
, ISNULL(B.[month],@mn) [month]
, ISNULL(B.[year],@yr) [year]
FROM nam A
LEFT OUTER JOIN lst B
ON A.name=B.name

试试这个一：

Try this one:

declare @mytable TABLE (
	[name] [nchar](10) NULL,
	[value] [numeric](10, 2) NULL,
	[month] [numeric](2, 0) NULL,
	[year] [numeric](4, 0) NULL
)

insert into @mytable
SELECT 'John' [name] , 0.50 [value] , 11 [month] , 2014 [year]
UNION ALL
  SELECT 'Mary' 'name' , 0.60 'value' , 11 'month' , 2014 'year'
UNION ALL
  SELECT 'Pete' 'name' , 0.60 'value' , 11 'month' , 2014 'year'
UNION ALL
  SELECT 'John' 'name' , 0.60 'value' , 12 'month' , 2014 'year'
UNION ALL
  SELECT 'Don' 'name' , 0.50 'value' , 10 'month' , 2013 'year'


declare @year numeric(4,0) = 2014
declare @month numeric(2,0) = 11;

WITH myCTE(name, my)
AS
(
	select distinct 
	t1.name,
	(select max(cast(cast(year as varchar) + '-' + cast(month as varchar) + '-01' as DATE))
		from @mytable 
		where cast(cast(@year as varchar) + '-' +cast(@month as varchar) + '-01' as DATE) >= cast(cast(year as varchar) + '-' + cast(month as varchar) + '-01' as DATE)
		and t1.name = name) as my
	from @mytable t1 
)
select myCTE.name, t2.value, DATEPART(MONTH, my) as month, DATEPART(YEAR, my) as year
from myCTE left join @mytable t2 on 
(myCTE.name = t2.name and myCTE.my = cast(cast(year as varchar) + '-' + cast(month as varchar) + '-01' as DATE))

除了解决方案1和2我想推荐一篇关于联接的非常好的文章： SQL连接的可视化表示 [ ^ ]。它可能有助于您了解联接的工作原理。

In addition to solution 1 and 2 i'd like to recommend a very good article about joins: Visual Representation of SQL Joins[^]. It might help you to understand how joins work.

这篇关于SQL Server查询问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！