含有多重含义字段的纵横表切换,增加难度
TA表如下：商品销售和退货在一起，用vtype区别：30表示退货，31表示销售 ,optime标示操作时间

HTML code

proname     qty     vtype       optimeAA          -10      30        2012-01-09AA          21       31        2012-02-14AA          12       31        2012-02-11AA          -2       30        2012-03-11BB          -1       30        2012-02-12BB          90       31        2012-02-11BB          90       31        2012-03-17BB          -6       30        2012-02-19

要求列出商品最近一次的销售数量，退货数量极其最近一次销售、退货时间

HTML code

proname   xssl    xtsl  lastxstime   lastxttime AA       33      -12   2012-02-14   2012-03-11 BB       180     -7    2012-03-17   2012-02-19... 

[解决办法]

SQL code

--> 测试数据：[tbl]if object_id('[tbl]') is not null drop table [tbl]create table [tbl]([proname] varchar(2),[qty] int,[vtype] int,[optime] datetime)insert [tbl]select 'AA',-10,30,'2012-01-09' union allselect 'AA',21,31,'2012-02-14' union allselect 'AA',12,31,'2012-02-11' union allselect 'AA',-2,30,'2012-03-11' union allselect 'BB',-1,30,'2012-02-12' union allselect 'BB',90,31,'2012-02-11' union allselect 'BB',90,31,'2012-03-17' union allselect 'BB',-6,30,'2012-02-19'select [proname],SUM(case when  [vtype] = 31 THEN [qty] ELSE 0 END) as xsqty,SUM(CASE WHEN [vtype] = 30 THEN [qty] ELSE 0 END) AS xtqty,(select convert(varchar(10),max([optime]),120) from tbl b where a.proname=b.proname and [vtype] = 31) as lastxstime ,(select convert(varchar(10),max([optime]),120) from tbl b where a.proname=b.proname and [vtype] = 30) as lastxstime  from tbl agroup by [proname]/*proname    xsqty    xtqty    lastxstime    lastxstimeAA    33    -12    2012-02-14    2012-03-11BB    180    -7    2012-03-17    2012-02-19*/