这里的便携式方法不需要 < code> paptition 。 我假设一组中超过一行的您不会有相同的 < code> datetimeIN 值 :

select t.id, t.day, t.month, t.year,
    case when tm.id is null then 0 else t.cost end as cost,
    case when tm.id is null then 0 else t.Count end as Count,
    t.datetimeIN, t.datetimeOUT
from MyTable t
left outer join (
    select id, day, month, year, min(datetimeIN) as minIN
    from MyTable
    group by id, day, month, year
) tm on t.id = tm.id
    and t.day = tm.day
    and t.month = tm.month
    and t.year = tm.year
    and t.datetimeIN = tm.minIN

你可以做这样的事情:

SELECT id, day, month, year,
  CASE WHEN nNum = 1 then cost else 0 end as cost,
  CASE WHEN nNum = 1 then "Count" else 0 end as "Count",
  datetimeIN, datetimeOUT
FROM (
  SELECT id, day, month, year,
    cost, "Count", datetimeIN, datetimeOUT,
    row_number() OVER (PARTITION BY id, day, month, year
                       ORDER BY datetimeIN) as nNum
  FROM TableName
) A

它使用 < code> row_ number () 来编号行,然后使用 < code> CASE 语句来挑出第一个语句,使其行为不同。

http://sqlfiddle.com/#! 3/f6fc8/5" rel="nofollow" > 见这里的 SQL Fiddle 工作。

或,使用共同表格表达式:

    with    commonTableExp ([day], [month], [year], minDate) as (
            select  [day], [month], [year], min(datetimeIn)
            from    #temp
            group by [day], [month], [year])
    select  id,
            dt.[day],
            dt.[month],
            dt.[year],
            case when datetimein = minDate then [cost] else 0 end,
            case when datetimein = minDate then [count] else 0 end,
            dateTimeIn
    from    #temp dt join commonTableExp cte on 
                dt.[day] = cte.[day] and
                dt.[month] = cte.[month] and
                dt.[year] = cte.[year]
    order by dateTimeIn

Query

Select id, [day], [month], [year], Case When K.RowID = 1 Then [cost] Else 0 End as Cost, Case When K.RowID = 1 Then [count] Else 0 End as [count], [DateTimeIN], [DateTimeOut] From
(
    select ROW_NUMBER() Over(Partition by id, [day], [month], [year] Order by ID ) as RowID, * From Testing
)K

Drop table Testing

Click here to see SQL Profiler details for Red Filter s Query

Click here to see SQL Profiler details for my Query

http://sqlfiddle.com/ #! 3/ 6772b/ 1, rel = “ nofollow” > 详情请见 SQL Fiddle