在下卡在这儿了,求各位帮忙
原表:
姓名 日期 售票金额 退票金额
李四 2003-7-20 200 20
王五 2003-6-3 300 10
张三 2003-5-3 500 50
李四 2003-3-5 200 20
王五 2003-4-7 600 62
王五 2003-5-5 300 40
张三 2003-8-9 800 70
如何用sql语句实现查询如下
姓名 一月 二月 三月 四月 五月 六月 七月 八月
王五 (售票金额-退票金额)
张三
李四
原表:
姓名 日期 售票金额 退票金额
李四 2003-7-20 200 20
王五 2003-6-3 300 10
张三 2003-5-3 500 50
李四 2003-3-5 200 20
王五 2003-4-7 600 62
王五 2003-5-5 300 40
张三 2003-8-9 800 70
如何用sql语句实现查询如下
姓名 一月 二月 三月 四月 五月 六月 七月 八月
王五 (售票金额-退票金额)
张三
李四
解决方案 »
- delphi 怎么建立VPN连接,请高手指点.
- 请问怎么查询字段中的其中几位是否等于一个字符串?
- 偶儿子的趣事
- 鼠标右键是激发那个事件 ,比如说当单击右键时弹出PopupMenu菜单,菜单里面的项目是根据条件来控制它的enabled的属性,该怎样做?
- 大侠们,讨论 编程动态设置 控件属性,现行的编译技术有做到么?
- 重分求取Rabin-Miller算法源码及Lehmann算法源码!!!!!!!!!!!!!!
- 如何实现将dbmemo 中内容赋予qrmemo?
- ADOQuery的查询问题!!很简单的,解决马上给分!!在线等了!!!谢谢!!
- paradox真的有很大的bug吗?(请用过paradox的朋友进来探讨一下,来者有分)
- 等级考试
- 如何让文件夹无法访问??
- 如何访问窗体内所有控件?
insert into TempTable values('李四','2003-7-20',200,20)
insert into TempTable values('王五','2003-6-3',300,10)
insert into TempTable values('张三','2003-5-3',500,50)
insert into TempTable values('李四','2003-3-5',200,20)
insert into TempTable values('王五','2003-4-7',600,62)
insert into TempTable values('王五','2003-5-5',300,40)
insert into TempTable values('张三','2003-8-9',800,70)
select A.pname as '姓名',isnull(A1.tsum,0) as '一月',isnull(A2.tsum,0)as '二月',
isnull(A3.tsum,0)as '四月',isnull(A4.tsum,0)as '五月',
isnull(A5.tsum,0)as '六月',isnull(A6.tsum,0)as '七月',
isnull(A7.tsum,0)as '七月',isnull(A8.tsum,0)as '八月'
from (select distinct pname from TempTable) A
left join
(select pname,sum(insum-outsum) as tsum from TempTable
where month(pdate)=1 group by pname) A1
ON A1.pname=A.pname
left join
(select pname,sum(insum-outsum) as tsum from TempTable
where month(pdate)=2 group by pname) A2
ON A2.pname=A.pname
left join
(select pname,sum(insum-outsum) as tsum from TempTable
where month(pdate)=3 group by pname) A3
ON A3.pname=A.pname
left join
(select pname,sum(insum-outsum) as tsum from TempTable
where month(pdate)=4 group by pname) A4
ON A4.pname=A.pname
left join
(select pname,sum(insum-outsum) as tsum from TempTable
where month(pdate)=5 group by pname) A5
ON A5.pname=A.pname
left join
(select pname,sum(insum-outsum) as tsum from TempTable
where month(pdate)=6 group by pname) A6
ON A6.pname=A.pname
left join
(select pname,sum(insum-outsum) as tsum from TempTable
where month(pdate)=7 group by pname) A7
ON A7.pname=A.pname
left join
(select pname,sum(insum-outsum) as tsum from TempTable
where month(pdate)=8 group by pname) A8
ON A8.pname=A.pname结果集略(经过测试,OK)
不是在ms sql server版里回答过了么?select 姓名,
sum(case month(日期) when 1 then 售票金额-退票金额 else 0 end) as 一月,
...
from TEST
group by 姓名
insert into TempTable values('李四','2003-7-20',200,20)
insert into TempTable values('王五','2003-6-3',300,10)
insert into TempTable values('张三','2003-5-3',500,50)
insert into TempTable values('李四','2003-3-5',200,20)
insert into TempTable values('王五','2003-4-7',600,62)
insert into TempTable values('王五','2003-5-5',300,40)
insert into TempTable values('张三','2003-8-9',800,70)
SQL语句:
select pname,
sum(case month(pdate) when 1 then insum-outsum else 0 end) as '一月',
sum(case month(pdate) when 2 then insum-outsum else 0 end) as '二月',
sum(case month(pdate) when 3 then insum-outsum else 0 end) as '三月',
sum(case month(pdate) when 4 then insum-outsum else 0 end) as '四月',
sum(case month(pdate) when 5 then insum-outsum else 0 end) as '五月',
sum(case month(pdate) when 6 then insum-outsum else 0 end) as '六月',
sum(case month(pdate) when 7 then insum-outsum else 0 end) as '七月',
sum(case month(pdate) when 8 then insum-outsum else 0 end) as '八月'
from TempTable
group by pname结果跟上面一样,但简洁多了。
,sum(case when month(日期)=1 then 售票金额-退票金额 else 0 end) as 一月
,sum(case when month(日期)=2 then 售票金额-退票金额 else 0 end) as 二月
,sum(case when month(日期)=3 then 售票金额-退票金额 else 0 end) as 三月
,sum(case when month(日期)=4 then 售票金额-退票金额 else 0 end) as 四月
,sum(case when month(日期)=5 then 售票金额-退票金额 else 0 end) as 五月
,sum(case when month(日期)=6 then 售票金额-退票金额 else 0 end) as 六月
,sum(case ,sum(case when month(日期)=7 then 售票金额-退票金额 else 0 end) as 七月
,sum(case when month(日期)=8 then 售票金额-退票金额 else 0 end) as 八月
,sum(case when month(日期)=9 then 售票金额-退票金额 else 0 end) as 九月
,sum(case when month(日期)=10 then 售票金额-退票金额 else 0 end) as 十月
,sum(case when month(日期)=11 then 售票金额-退票金额 else 0 end) as 十一月
,sum(case when month(日期)=12 then 售票金额-退票金额 else 0 end) as 十二月