导航
  • 主页
  • 基础类
  • 应用实例
  • 新技术前沿

在线等,比较复杂的问题,得出结果马上结贴

zuoxingyu 2008-01-08 11:15:44
我的表如下
date1 dealFlag stimes smoneys
1 1 1 2.5
1 2 1 3
1 3 2 5
2 2 1 2.5
2 3 3 10
数据基本如上,其中date1是日期,每个月的哪天,dealfalg是早,中,晚餐,stimes是消费次数,smoneys是消费总金额
我现在想得到这样一个记录集
日期 早餐 次数 金额 中餐 次数 金额 晚餐 次数 金额
1 1 1 2.5 2 1 3 3 2 5
2 1 0 0 2 1 2.5 3 3 10

各位大侠帮忙,得出结果马上结贴
...全文
57 点赞 收藏 9
写回复
9 条回复
切换为时间正序
请发表友善的回复…
发表回复
wzy_love_sly 2008-01-09
create table a (date int,dealflag int,stimes int,smoneys decimal(10,1))
insert into a select 1,1,1,2.5
insert into a select 1,2,1,3
insert into a select 1,3,2,5
insert into a select 2,2,1,2.5
insert into a select 2,3,3,1
--其中date1是日期,每个月的哪天,dealfalg是早,中,晚餐,stimes是消费次数,smoneys是消费总金额
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+','+case when dealflag=1 then '早餐' when dealFlag=2 then '中餐' when dealFlag=3 then '晚餐' end +'='''+cast(dealFlag as varchar(10))+''''+
',sum(case when dealflag='''+cast(dealflag as varchar(10))+''' then 1 else 0 end) as ''次数'',
sum(case when dealflag='''+cast(dealflag as varchar(10))+''' then smoneys else 0 end) as ''金额'''
from a group by dealflag
print @sql
exec('select date'+@sql+' from a group by date')

[code=SQL]date 早餐 次数 金额 中餐 次数 金额 晚餐 次数 金额
1 1 1 2.5 2 1 3.0 3 1 5.0
2 1 0 0.0 2 1 2.5 3 1 1.0


回复
中国风 2008-01-08
--动态生成方法;

create table T(date1 int, dealFlag int, stimes int, smoneys numeric(18,2))
insert T select 1, 1, 1, 2.5
insert T select 1, 2, 1, 3
insert T select 1, 3, 2, 5
insert T select 2, 2, 1, 2.5
insert T select 2, 3, 3, 10

go

declare @s nvarchar(4000)
set @s='select date1'
select @s=@s+',['+case when dealFlag=1 then '早餐' when dealFlag=2 then '中餐' else '晚餐' end+']='+rtrim(dealFlag)+',[次数]='+'sum(case when dealFlag='+rtrim(dealFlag)+' then stimes else 0 end),'+
'[金额]='+'sum(case when dealFlag='+rtrim(dealFlag)+' then smoneys else 0 end)'
from T
group by dealFlag

exec(@s+' from T group by date1')

date1 早餐 次数 金额 中餐 次数 金额 晚餐 次数 金额
----------- ----------- ----------- ---------------------------------------- ----------- ----------- ---------------------------------------- ----------- ----------- ----------------------------------------
1 1 1 2.50 2 1 3.00 3 2 5.00
2 1 0 .00 2 1 2.50 3 3 10.00

回复
-狙击手- 2008-01-08
create table ta(date1 int,dealFlag int,stimes int,smoneys  numeric(12,2))
insert ta select 1,1, 1,2.5
insert ta select 1,2, 1,3
insert ta select 1,3, 2,5
insert ta select 2,2, 1,2.5
insert ta select 2,3, 3,10

go

declare @s varchar(8000)
set @s = 'select date1'
select @s = @s + ',['+case dealflag when 1 then '早' when 2 then '中' when 3 then '晚' end+']= max(case when dealflag = '+ltrim(dealflag)+ ' then dealflag else Null end)'+
',[次数]= sum(case when dealflag = '+ltrim(dealflag)+ ' then stimes else 0 end)'+
',[金额]= sum(case when dealflag = '+ltrim(dealflag)+ ' then smoneys else 0 end)'
from (select distinct dealflag from ta) a
exec(@s+ ' from ta group by date1')


drop table ta
/*
date1 早 次数 金额 中 次数 金额 晚 次数 金额
----------- ----------- ----------- ---------------------------------------- ----------- ----------- ---------------------------------------- ----------- ----------- ----------------------------------------
1 1 1 2.50 2 1 3.00 3 2 5.00
2 NULL 0 .00 2 1 2.50 3 3 10.00

警告: 聚合或其它 SET 操作消除了空值。


*/
回复
-狙击手- 2008-01-08
create table ta(date1 int,dealFlag int,stimes int,smoneys  numeric(12,2))
insert ta select 1,1, 1,2.5
insert ta select 1,2, 1,3
insert ta select 1,3, 2,5
insert ta select 2,2, 1,2.5
insert ta select 2,3, 3,10

go

declare @s varchar(8000)
set @s = 'select date1'
select @s = @s + ',['+case dealflag when 1 then '早' when 2 then '中' when 3 then '晚' end+']= max(case when dealflag = '+ltrim(dealflag)+ ' then dealflag else Null end)'+
',['+case dealflag when 1 then '早' when 2 then '中' when 3 then '晚' end+'次数]= sum(case when dealflag = '+ltrim(dealflag)+ ' then stimes else 0 end)'+
',['+case dealflag when 1 then '早' when 2 then '中' when 3 then '晚' end+'金额 ]= sum(case when dealflag = '+ltrim(dealflag)+ ' then smoneys else 0 end)'
from (select distinct dealflag from ta) a
exec(@s+ ' from ta group by date1')


drop table ta
/*
date1 早 早次数 早金额 中 中次数 中金额 晚 晚次数 晚金额
----------- ----------- ----------- ---------------------------------------- ----------- ----------- ---------------------------------------- ----------- ----------- ----------------------------------------
1 1 1 2.50 2 1 3.00 3 2 5.00
2 NULL 0 .00 2 1 2.50 3 3 10.00

警告: 聚合或其它 SET 操作消除了空值。

*/
回复
OracleRoob 2008-01-08
--或:

select
date1 as 日期,
1 as 早餐,
sum(case when dealFlag = 1 then stimes else 0 end) as 早餐次数,
sum(case when dealFlag = 1 then smoneys else 0 end) as 早餐金额,
2 as 午餐,
sum(case when dealFlag = 2 then stimes else 0 end) as 午餐次数,
sum(case when dealFlag = 2 then smoneys else 0 end) as 午餐金额,
3 as 晚餐,
sum(case when dealFlag = 3 then stimes else 0 end) as 晚餐次数,
sum(case when dealFlag = 3 then smoneys else 0 end) as 晚餐金额

from 表名
group by date1
回复
中国风 2008-01-08
create table T(date1 int,    dealFlag int,      stimes int,      smoneys numeric(18,2))
insert T select 1, 1, 1, 2.5
insert T select 1, 2, 1, 3
insert T select 1, 3, 2, 5
insert T select 2, 2, 1, 2.5
insert T select 2, 3, 3, 10

go


select
date1,
[早餐]=1,
[次数]=sum(case when dealFlag=1 then stimes else 0 end),
[金额]=sum(case when dealFlag=1 then smoneys else 0 end),
[中餐]=2,
[次数]=sum(case when dealFlag=2 then stimes else 0 end),
[金额]=sum(case when dealFlag=2 then smoneys else 0 end),
[晚餐]=3,
[次数]=sum(case when dealFlag=3 then stimes else 0 end),
[金额]=sum(case when dealFlag=3 then smoneys else 0 end)
from
T
group by date1

date1 早餐 次数 金额 中餐 次数 金额 晚餐 次数 金额
----------- ----------- ----------- ---------------------------------------- ----------- ----------- ---------------------------------------- ----------- ----------- ----------------------------------------
1 1 1 2.50 2 1 3.00 3 2 5.00
2 1 0 .00 2 1 2.50 3 3 10.00

(所影响的行数为 2 行)

回复
OracleRoob 2008-01-08
--改一点:

select
date1 as 日期,
1 as 早餐,
sum(case when dealFlag = 1 then stimes else 0 end) 早餐次数,
sum(case when dealFlag = 1 then smoneys else 0 end) 早餐金额,
2 as 午餐,
sum(case when dealFlag = 2 then stimes else 0 end) 午餐次数,
sum(case when dealFlag = 2 then smoneys else 0 end) 午餐金额,
3 as 晚餐,
sum(case when dealFlag = 3 then stimes else 0 end) 晚餐次数,
sum(case when dealFlag = 3 then smoneys else 0 end) 晚餐金额

from 表名
group by date1
回复
OracleRoob 2008-01-08
select
date1 as 日期,
1 as 早餐,
sum(case when dealFlag = 1 then stimes else 0 end) a 早餐次数,
sum(case when dealFlag = 1 then smoneys else 0 end) a 早餐金额,
2 as 午餐,
sum(case when dealFlag = 2 then stimes else 0 end) a 午餐次数,
sum(case when dealFlag = 2 then smoneys else 0 end) a 午餐金额,
3 as 晚餐,
sum(case when dealFlag = 3 then stimes else 0 end) a 晚餐次数,
sum(case when dealFlag = 3 then smoneys else 0 end) a 晚餐金额

from 表名
group by date1
回复
wzy_love_sly 2008-01-08
都几点了 还等
回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告