c# - SQL查询中的If语句？

Question

我有一个查询给我多个结果，如下所示:

|DATE|------|DESCRIPTION|---|PDV|----------|ID|-------|NAME|--------|Value|-|Quantity|
2016-01-25    DESAYUNOS     REST            1   DEL PATRON COMBO    162.00    1
2016-01-25    COMIDAS       REST            1   DEL PATRON COMBO    162.00    1
2016-01-25    CENAS         REST            1   DEL PATRON COMBO    162.00    1
2016-01-25    DESAYUNOS     REST            1   DEL PATRON COMBO    162.00    1

我的查询:

SELECT
    [dbo].[CHEQUES].Fecha,  
    [dbo].[TURNOS].Descripcion, 
    [dbo].[CAPMO].Clave_PDV, 
    [dbo].[CAPMO].Pla AS Platillo_Id, 
    [dbo].[CAPMO].Descripcion, 
    [dbo].[CAPMO].Pre AS PrecioPlatillo, 
    [dbo].[CAPMO].Can AS CantidadPlatillo
FROM         
    [dbo].[CAPMO] 
INNER JOIN 
    [dbo].[CHEQUES] ON [dbo].[CAPMO].Clave_PDV = [dbo].[CHEQUES].Cla_PDV 
                    AND [dbo].[CAPMO].Che = [dbo].[CHEQUES].Che 
INNER JOIN 
    [dbo].[PLATILLOS] ON [dbo].[CAPMO].Pla = [dbo].PLATILLOS].Pla 
INNER JOIN 
    [dbo].[TURNOS] ON [dbo].[CAPMO].Clave_PDV = [dbo].[TURNOS].Clave_PDV
WHERE     
    ([dbo].[CHEQUES].St = 'P') AND ([dbo].[CAPMO].Stpl = 'A') 

第 1 行和第 4 行完全相同，是否有一个查询可以检测相同的行并增加 |Quantity|每个等于 1 列？喜欢和 If 有一段时间我不知道这在 SQL 中是否可行

我正在寻找这样的输出:

|DATE|------|DESCRIPTION|---|PDV|----------|ID|-------|NAME|--------|Value|-|Quantity|

2016-01-25    COMIDAS       REST            1   DEL PATRON COMBO    162.00    1
2016-01-25    CENAS         REST            1   DEL PATRON COMBO    162.00    1
2016-01-25    DESAYUNOS     REST            1   DEL PATRON COMBO    162.00    2

Answer 1

在 SQL-Server 中，您可以在下面使用 ROW_NUMBER:

查询

select [date], [description], pdv, id, name, value, 
       case when rn > 1 then quantity+rn-1 else quantity end as quantity
from( 
    select *, 
           row_number() over(partition by [date], [description], pdv, id, name, value, quantity order by [date]) rn 
    from #t
)x

示例数据

create table #t
(
    [date] date,
    [description] nvarchar(60),
    pdv nvarchar(60),
    id int,
    name nvarchar(60),
    value nvarchar(60),
    quantity int
)
insert into #t values
 ('2016-01-25','DESAYUNOS','REST',1,'DEL PATRON COMBO','162.00',1)
,('2016-01-25','COMIDAS'  ,'REST',1,'DEL PATRON COMBO','162.00',1)
,('2016-01-25','CENAS'    ,'REST',1,'DEL PATRON COMBO','162.00',1)
,('2016-01-25','DESAYUNOS','REST',1,'DEL PATRON COMBO','162.00',1)
,('2016-01-25','DESAYUNOS','REST',1,'DEL PATRON COMBO','162.00',1)
,('2016-01-25','DESAYUNOS','REST',1,'DEL PATRON COMBO','162.00',1)

输出

date        description pdv     id  name                value   quantity
2016-01-25  CENAS       REST    1   DEL PATRON COMBO    162.00  1
2016-01-25  COMIDAS     REST    1   DEL PATRON COMBO    162.00  1
2016-01-25  DESAYUNOS   REST    1   DEL PATRON COMBO    162.00  1
2016-01-25  DESAYUNOS   REST    1   DEL PATRON COMBO    162.00  2
2016-01-25  DESAYUNOS   REST    1   DEL PATRON COMBO    162.00  3
2016-01-25  DESAYUNOS   REST    1   DEL PATRON COMBO    162.00  4

看看这部分 quantity+rn-1 它会将每个重复行增加 1，即使重复行数多于 2 也是如此。

如果您只想增加 1 而不是取决于重复项的数量，请使用 quantity-1 而不是 quantity+rn-1。

更新

如果您只想获得最高数量的结果而不重复，您可以在下面使用 MAX 和 GROUP BY 子句:

查询

select [date], [description], pdv, id, name, value, 
       max(case when rn > 1 then quantity +rn-1 else quantity end) as quantity
from( 
    select *, 
           row_number() over(partition by [date], [description], pdv, id, name, value, quantity order by [date]) rn 
    from #t
)x
group by [date], [description], pdv, id, name, value

输出

date        description pdv     id  name                value   quantity
2016-01-25  CENAS       REST    1   DEL PATRON COMBO    162.00  1
2016-01-25  COMIDAS     REST    1   DEL PATRON COMBO    162.00  1
2016-01-25  DESAYUNOS   REST    1   DEL PATRON COMBO    162.00  4