帮忙写个查询语句

现有： 
TABLE A 
ID PCODE DN PSD        ISD 
---------------------------------- 
1  01    AA 2008-12-20 2008-12-21 
2  02    AA 2008-12-18 2008-12-20 
3  03    AA 2008-12-21 2008-12-19 
4  04    AA 2008-12-22 2008-12-20 
5  01    BB 2008-12-18 2008-12-19 
6  02    BB 2008-12-17 2008-12-18 
7  03    BB 2008-12-19 2008-12-20 
8  04    BB 2008-12-20 2008-12-20 
9  01    CC 2008-12-22 <NULL> 
10 02    CC <NULL>    2008-12-19 TABLE B 
ID PCODE PN 
------------ 
1  01    MM 
2  02    NN 
3  03    PP 
4  04    QQ 要想得到以下查询结果： 
RESULT 
ID DN MMPSD      MMISD      NNPSD      NNISD      PPPSD      PPISD      QQPSD      QQISD 
1  AA 2008-12-20 2008-12-21 2008-12-18 2008-12-20 2008-12-21 2008-12-19 2008-12-22 2008-12-20 
2  BB 2008-12-18 2008-12-19 2008-12-17 2008-12-18 2008-12-19 2008-12-20 2008-12-22 2008-12-20 
3  CC 2008-12-22 <NULL>    <NULL>    2008-12-19 <NULL>    <NULL>    <NULL>    <NULL>

解决方案 »

  1.   

    create table A(ID int,PCODE varchar(10),DN varchar(10),PSD datetime,       ISD datetime)
    insert into A values(1 , '01' ,   'AA', '2008-12-20', '2008-12-21') 
    insert into A values(2 , '02' ,   'AA', '2008-12-18', '2008-12-20') 
    insert into A values(3 , '03' ,   'AA', '2008-12-21', '2008-12-19') 
    insert into A values(4 , '04' ,   'AA', '2008-12-22', '2008-12-20') 
    insert into A values(5 , '01' ,   'BB', '2008-12-18', '2008-12-19') 
    insert into A values(6 , '02' ,   'BB', '2008-12-17', '2008-12-18') 
    insert into A values(7 , '03' ,   'BB', '2008-12-19', '2008-12-20') 
    insert into A values(8 , '04' ,   'BB', '2008-12-20', '2008-12-20') 
    insert into A values(9 , '01' ,   'CC', '2008-12-22', NULL) 
    insert into A values(10, '02' ,   'CC', NULL        , '2008-12-19') 
    create table B(ID int, PCODE varchar(10) , PN varchar(10)) 
    insert into b values(1 , '01' ,   'MM') 
    insert into b values(2 , '02' ,   'NN') 
    insert into b values(3 , '03' ,   'PP') 
    insert into b values(4 , '04' ,   'QQ') 
    go
    --静态SQL
    select
      dn ,
      max(case b.pn when 'mm' then psd end) mmpsd,
      max(case b.pn when 'mm' then isd end) mmisd,
      max(case b.pn when 'nn' then psd end) nnpsd,
      max(case b.pn when 'nn' then isd end) nnisd,
      max(case b.pn when 'pp' then psd end) pppsd,
      max(case b.pn when 'pp' then isd end) ppisd,
      max(case b.pn when 'qq' then psd end) qqpsd,
      max(case b.pn when 'qq' then isd end) qqisd
    from a , b
    where a.pcode = b.pcode
    group by dn
    /*
    dn         mmpsd                                                  mmisd                                                  nnpsd                                                  nnisd                                                  pppsd                                                  ppisd                                                  qqpsd                                                  qqisd                                                  
    ---------- ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ 
    AA         2008-12-20 00:00:00.000                                2008-12-21 00:00:00.000                                2008-12-18 00:00:00.000                                2008-12-20 00:00:00.000                                2008-12-21 00:00:00.000                                2008-12-19 00:00:00.000                                2008-12-22 00:00:00.000                                2008-12-20 00:00:00.000
    BB         2008-12-18 00:00:00.000                                2008-12-19 00:00:00.000                                2008-12-17 00:00:00.000                                2008-12-18 00:00:00.000                                2008-12-19 00:00:00.000                                2008-12-20 00:00:00.000                                2008-12-20 00:00:00.000                                2008-12-20 00:00:00.000
    CC         2008-12-22 00:00:00.000                                NULL                                                   NULL                                                   2008-12-19 00:00:00.000                                NULL                                                   NULL                                                   NULL                                                   NULL（所影响的行数为 3 行）
    */--动态SQL
    declare @sql varchar(8000)
    set @sql = 'select dn '
    select @sql = @sql + ' , max(case b.pn when ''' + pn + ''' then psd end) [' + pn + 'psd]'
                       + ' , max(case b.pn when ''' + pn + ''' then isd end) [' + pn + 'isd]'
    from (select distinct pn from b) as t
    set @sql = @sql + ' from a , b
    where a.pcode = b.pcode
    group by dn'
    exec(@sql) 
    /*
    dn         MMpsd                                                  MMisd                                                  NNpsd                                                  NNisd                                                  PPpsd                                                  PPisd                                                  QQpsd                                                  QQisd                                                  
    ---------- ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ 
    AA         2008-12-20 00:00:00.000                                2008-12-21 00:00:00.000                                2008-12-18 00:00:00.000                                2008-12-20 00:00:00.000                                2008-12-21 00:00:00.000                                2008-12-19 00:00:00.000                                2008-12-22 00:00:00.000                                2008-12-20 00:00:00.000
    BB         2008-12-18 00:00:00.000                                2008-12-19 00:00:00.000                                2008-12-17 00:00:00.000                                2008-12-18 00:00:00.000                                2008-12-19 00:00:00.000                                2008-12-20 00:00:00.000                                2008-12-20 00:00:00.000                                2008-12-20 00:00:00.000
    CC         2008-12-22 00:00:00.000                                NULL                                                   NULL                                                   2008-12-19 00:00:00.000                                NULL                                                   NULL                                                   NULL                                                   NULL
    */drop table a , b