在MS SQL中分组

Salman 发表于 Dev

萨尔曼

请帮助我获得如下结果表中所示的结果。

数据表

CASH_ID    FK_TOUR_ID    CASH_COLLECTED    CASH_TYPE    COLLECTION_DATE    FK_COLLECTED_BY_ID
616        14500         966.6             0            5/14/2014          433
617        14500         470               1            5/14/2014          433
618        14602         546               0            5/15/2014          891

结果表：

FK_TOUR_ID    By_Check    By_Cash    COLLECTION_DATE    FK_COLLECTED_BY_ID
14500         470         966.6      5/14/2014          433
14602                     546        5/15/2014          891

安多玛

您可以按分组fk_tour_id，然后使用汇总功能显示支票和现金号码：

select  fk_tour_id
,       sum(case when cash_type = 1 then cash_collected end) as [By Check]
,       sum(case when cash_type = 0 then cash_collected end) as [By Cash]
,       sum(cash_collected) as [Both Check and Cash]
,       min(collection_date)
,       min(fk_collected_by_id)
from    YourTable
group by
        fk_tour_id

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。