案例表达时的tsql [英] tsql When Case Expression

问题描述

我正在使用这个sp，根据两个选定的月份（Comparisim）给我信息。我现在需要在此商店程序中使用一种语法，该语法应生成一个新列，然后指示第1个月中的产品是否在第2个月保持（未更改）或第2个月中的产品是否为新等。



我的商店程序：

Hi,
I am using this sp which gives me Information based on two chosen months (Comparisim). I now need a syntax within this store procedure which should generate a new column and then indicate me whether a product in month 1 remained (unchanged) in month 2 or whether a product is new in month 2 etc.

My store procedure:

CREATE PROC [dbo].[spMonthInfo]
 (   
  @Periode1 NVARCHAR (255),
  @Periode2 NVARCHAR (255) 
 )
AS
BEGIN
DECLARE @Dynamictbl nvarchar(MAX) =
 N'SELECT
 [t].[Product],[t].[Description],
 [t].[Finance], --[t].[Product] AS [Entrance],

 CASE 
    // when Products are in both months
  WHEN ....... THEN ''Unchanged''
    // When Product is in Feb but not in March
  WHEN  ....... THEN ''Out''
    // When Product is in March but not in Feb
  WHEN ......... THEN ''New''
END AS Entrance, 

 SUM(CASE WHEN [t].Monat = ' + @Periode1 + ' THEN [t].[Amount1] END) AS     [Amount1Feb],
 SUM(CASE WHEN [t].Monat = ' + @Periode2 + ' THEN [t].[Amount1] END) AS [Amount1March],
 SUM(CASE WHEN [t].Monat = ' + @Periode1 + ' THEN [t].[Amount2] END) AS [Amount2Feb],
 SUM(CASE WHEN [t].Monat = ' + @Periode2 + ' THEN [t].[ Amount2] END) AS [Amount2March],

FROM [dbo].[tblSales] AS t


GROUP BY [t].[Project],[t].[Description],
[t].[Finance], --[t].[Project] AS [Entrance]


 EXECUTE sp_executesql @Dynamictbl

 END
 GO
 EXEC spMonthInfo @Periode1 = '02.2011', @Periode2 = '03.2011'

预期成果： [ ^ ]



我的图像示例，我们可以识别产品'肥皂'和'Cerelac 三月是新的，而产品的布朗尼 仅在二月和产品的其余都在这两个月。我怎样才能在我的sp中实现这一目标？

Expected Result: [^]

In my image example, we can identify that the products 'Soap' and 'Cerelac'are new in March whereas the product 'Brownie' is only in Feb and the rest of the products are in both months. How can I accomplish that within my sp?

推荐答案

我认为这是作业，我将引导您完成我的思考过程，而不仅仅是给您一个解决方案...



首先我重新创建了你的样本数据

As I think this is homework I'm going to walk you through my thought process rather than just give you a solution ...

First of all I recreated your sample data

create table tblSales
(
	monat int,
	jahre int,
	product varchar(5),
	descript varchar(30),
	Finace varchar(2),
	Amount1 float,
	Amount2 float
)

insert into tblSales values
(2,2011,'A.123' ,'Milk', 'AZ', 0, 0),
(2,2011,'A.123' ,'Milk', 'AB', 1200, 1000),
(2,2011,'A.478' ,'Sugar', 'ZE', 1300, 600),
(2,2011,'A.478' ,'Sugar', 'ZB', 1400, 150),
(2,2011,'B.156' ,'Coffee', 'CD', 1000, 2000),
(2,2011,'C.123' ,'Brownie', 'QP', 500, 300),
(3,2011,'A.123' ,'Milk', 'AZ', 0, 0),
(3,2011,'A.123' ,'Milk', 'AB', 1200, 1000),
(3,2011,'A.478' ,'Sugar', 'ZE', 1300, 600),
(3,2011,'A.478' ,'Sugar', 'ZB', 1400, 150),
(3,2011,'B.156' ,'Coffee', 'CD', 1000, 2000),
(3,2011,'D.007' ,'Celeriac', 'JD', 2000, 350),
(3,2011,'E.789' ,'Soap', 'MD', 900, 450)

请注意，我已将月份分开（monat ）和年（jahre） - 这是个人偏好，我根本不喜欢复合列。我本可以选择 Date 而忽略当天的部分。它不应该让这更难以遵循，你必须按照你的感觉。



我不直接写入存储过程，我先在查询窗口中写入它们（直到我认为我做对了），所以我还设置了这些局部变量

Note that I've separated out the month (monat) and year (jahre) - this is a personal preference, I simply don't like composite columns. I could have chosen Date instead and ignored the day part. It shouldn't make this any harder to follow and You must do as you feel.

I don't dive straight into writing stored procedures, I write them in the query window first (and again until I think I've got it right), so I also set up these local variables

DECLARE @P1Monat INT = 2
DECLARE @P1Jahre INT = 2011
DECLARE @P2Monat INT = 3
DECLARE @P2Jahre INT = 2011

在我可以玩的地方有一个环境，然后我想，我们真的尝试过什么在这里做。我们正在尝试比较这两个查询的结果：

Having got an environment together where I can play, I then thought, what are we really trying to do here. We're trying to compare the results of these two queries:

SELECT * FROM tblSales WHERE monat = @P1Monat

和

and

SELECT * FROM tblSales WHERE monat = @P2Monat

好的，我会将这两个查询的结果作为表格加入。 ..但是哪个加入？



这就是有时候潦倒的代价 - 换句话说，就是实验！试试看！走着瞧吧！ （这一点实际上可以很有趣和有趣）。最后，实验所汲取的教训将会坚持下去，你只会知道下一步该做什么，但我会继续讲故事......



所以......我尝试了一个INNER JOIN ......嗯，只能看到两组中的东西

...一个LEFT OUTER JOIN ...不 - 现在可以看到Brownie但是Celeriac或者汤没什么br />
...一个正确的外部加入......不，Celeriac和Soup现在出现了，但Brownie再次出现了。但这给了我一个线索...尝试完全外部加入

（这里有一些关于CodeProject的好文章将有助于这样的决定......例如 SQL联接的可视化表示 [ ^ ]）

Okay, I'll take the results from those two queries as tables and join them... but which join?

This is where sometimes it pays to "get down and dirty" - in other words, experiment! Try it out! See what happens! (this bit can actually be fun and funny). Eventually the lessons learned by the experiment will stick and you will just know what to do next, but I'll keep the story going ...

So ... I tried an INNER JOIN ... hm, only get to see things that in both sets
...a LEFT OUTER JOIN ... nope - can now see Brownie but nothing for Celeriac or soup
...a RIGHT OUTER JOIN...no, Celeriac and Soup now appearing but Brownie has gone again. But that has given me a clue ... try a FULL OUTER JOIN
(There are some good articles here on CodeProject that will help with decisions like this...e.g. Visual Representation of SQL Joins[^])

SELECT  *
FROM
(SELECT * FROM tblSales WHERE monat = @P1Monat) A
full OUTER JOIN
(SELECT * FROM tblSales WHERE monat = @P2Monat) B ON A.product=B.product

给我一个结果集（为了格式清晰，删除了一些列）

gives me a resultset of (some columns removed for formatting clarity)

Month   product Descr   Amnt1   Amnt2   month   product Descr   Amnt1   Amnt2
2	A.123   Milk    0       0       3       A.123   Milk    0       0
2	A.123   Milk    1200    1000    3       A.123   Milk    0       0
2	A.123   Milk    0       0       3       A.123   Milk    1200    1000
2	A.123   Milk    1200    1000    3       A.123   Milk    1200    1000
2	A.478   Sugar   1300    600     3       A.478   Sugar   1300    600
2	A.478   Sugar   1400    150     3       A.478   Sugar   1300    600
2	A.478   Sugar   1300    600     3       A.478   Sugar   1400    150
2	A.478   Sugar   1400    150     3       A.478   Sugar   1400    150
2	B.156   Coffee  1000    2000    3       B.156   Coffee  1000    2000
NULL	NULL    NULL    NULL    NULL    3       D.007   Celerac 2000    350
NULL	NULL    NULL    NULL    NULL    3       E.789   Soap    900     450
2	C.123   Brownie 500     300     NULL    NULL    NULL    NULL     NULL

现在我已经得到了所有东西，但是有些列在它们中有空。它也有点乱，所以我将使用 ISNULL [ ^ ]整理结果（我可以使用 COALESCE [ ^ ]而是）

Now I've got everything but some columns have null in them. It's also all a bit untidy so I'm going to use ISNULL[^] to tidy up the results (I could use COALESCE[^] instead)

SELECT  ISNULL(A.Product, B.Product) As Product,
        ISNULL(A.descript, B.descript) As 'Description',
        ISNULL(A.Finace, B.Finace) As Finace,
        A.Amount1 as P1Amt1, A.Amount2 as P1Amt2,
        B.Amount1 as P2Amt1, B.Amount2 as P2Amt2
FROM
(SELECT * FROM tblSales WHERE monat = @P1Monat) A
FULL OUTER JOIN
(SELECT * FROM tblSales WHERE monat = @P2Monat) B ON A.product=B.product

Product Description     Finace  P1Amt1  P1Amt2  P2Amt1  P2Amt2
A.123	Milk		AZ      0       0       0       0
A.123	Milk		AB      1200    1000    0       0
A.123	Milk		AZ      0       0       1200    1000
A.123	Milk		AB      1200    1000    1200    1000
A.478	Sugar		ZE      1300    600     1300    600
A.478	Sugar		ZB      1400    150     1300    600
A.478	Sugar		ZE      1300    600     1400    150
A.478	Sugar		ZB      1400    150     1400    150
B.156	Coffee		CD      1000    2000    1000    2000
D.007	Celeriac	JD      NULL    NULL    2000    350
E.789	Soap		MD      NULL    NULL    900     450
C.123	Brownie		QP      500	300     NULL    NULL

现在我们可以看到发生了什么......如果 P1 Amt2 （或2）是 NULL 然后它不在表的东西中在第1期，换句话说，它是第2期的新

同样如果 P2 Amt2 是 NULL 那么产品曾经在第1期出现，但在第2期不再存在。

还有其他什么东西列出那里的产品（卖？ ）在这两个时期内。



有了这个记住，我留下入口的实际推导作为练习。



希望你也可以看到现在如何轻松计算Amount1Feb，Amount1Mar等的值，因为现在您需要的所有数字都在一行

Now we can see what is going on ... if P1Amt2 (or 2) is NULL then it wasn't in the table of stuff in period 1, in other words it is "new" to period 2
Similarly if P2Amt2 is NULL then the product used to be there in Period 1 but is no longer there in Period 2.
Anything else is listing products that were there (sold?) in both Periods.

With that in mind, I leave the actual derivation of Entrance as an exercise for you.

Hopefully you can also see now how to easily calculate the values for Amount1Feb, Amount1Mar etc because now all of the figures you need are on a single row

这篇关于案例表达时的tsql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！