lqxue

mysql 行變列的例句

select distinct item.reportoid, item.lineoid,  item.accountoid, sun.amount, mon.amount, tue.amount, wes.amount, thur.amount, fri.amount, sat.amount
from expenseitem item
left join (select reportoid, accountoid, lineoid, amount, itemDate from expenseitem
 where itemDate = '2007-11-04' ) sun
 on item.reportoid = sun.reportoid and
  item.accountoid = sun.accountoid and
  item.lineoid = sun.lineoid and
  item.itemDate = sun.itemDate
left join (select reportoid, accountoid,  lineoid, amount, itemDate from expenseitem
 where itemDate = adddate('2007-11-04', 1) ) mon
 on item.reportoid = mon.reportoid and
  item.accountoid = mon.accountoid and
  item.lineoid = mon.lineoid and
  item.itemDate = mon.itemDate
left join (select reportoid, accountoid,  lineoid,amount, itemDate from expenseitem
 where itemDate = adddate('2007-11-04', 2)) tue
 on item.reportoid = tue.reportoid and
  item.accountoid = tue.accountoid and
  item.lineoid = tue.lineoid and
  item.itemDate = tue.itemDate
left join (select reportoid, accountoid,  lineoid,amount, itemDate from expenseitem
 where itemDate = adddate('2007-11-04', 3)  ) wes
 on item.reportoid = wes.reportoid and
  item.accountoid = wes.accountoid and
  item.lineoid = wes.lineoid and
  item.itemDate = wes.itemDate
left join (select reportoid, accountoid,  lineoid,amount, itemDate from expenseitem
 where itemDate = adddate('2007-11-04', 4)  ) thur
 on item.reportoid = thur.reportoid and
  item.accountoid = thur.accountoid and
  item.lineoid = thur.lineoid and
  item.itemDate = thur.itemDate
left join (select reportoid, accountoid,  lineoid,amount, itemDate from expenseitem
 where itemDate = adddate('2007-11-04', 5)  ) fri
 on item.reportoid = fri.reportoid and
  item.accountoid = fri.accountoid and
  item.lineoid = fri.lineoid and
  item.itemDate = fri.itemDate
left join (select reportoid, accountoid,  lineoid,amount, itemDate from expenseitem
 where itemDate = adddate('2007-11-04', 6) ) sat
 on item.reportoid = sat.reportoid and
  item.accountoid = sat.accountoid and
  item.lineoid = sat.lineoid and
  item.itemDate = sat.itemDate
where item.reportoid = 3712
order by reportoid, accountoid;

posted on 2008-07-08 10:52 lqx 閱讀(646) 評論(0)  編輯  收藏 所屬分類: database