select 帐号ZH AS 账号,SUM(交易金额JYJE) AS 交易总金额, SUM( 交易代码JYDM ) AS 交易次数 FROM 你的表 GROUP BY 帐号ZH
declare @sql varchar(8000) set @sql = 'select 帐号 select @sql = @sql + ',sum(case 交易代码JYDM when '''+cast(交易代码JYDM as varchar(10))+''' then 1 else 0 end) ['+cast(交易代码JYDM as varchar(10))+'交易次数]', + ',sum(case 交易代码JYDM when '''+cast(交易代码JYDM as varchar(10))+''' then 交易金额JYJE else 0 end) ['+cast(交易代码JYDM as varchar(10))+'交易金额]' from (select distinct 交易代码JYDM from testdata) as a select @sql = @sql+',sum(交易金额JYJE) 合计 from testdata group by 表名'exec(@sql) go
select 帐号ZH,交易代码JYDM,SUM(交易金额JYJE),COUNT(交易代码JYDM) group by 交易代码JYDM不知道是不是这意思“group by 交易代码JYDM”就是按 交易代码JYDM 分组聚合,然后用SUM求总金额,COUNT(交易代码JYDM)来数出交易次数
目前的处理。 select zh,sum(case jydm when 9999 then jyje else 0 end),sum(case jydm when 9999 then 1 else 0),sum(case jydm when 9998 then jyje else 0 end),sum(case jydm when 9998 then 1 else 0),sum(case jydm when 9995 then jyje else 0 end),sum(case jydm when 9995 then 1 else 0),sum(case jydm when 9990 then jyje else 0 end),sum(case jydm when 9990 then 1 else 0) from yourtable 如果你的每个帐号都有这四个交易代码。而求和操作又是必须做的,而且数据量和客户访问次数很多的话,你不如建立另外一个表,用来存放统计数据。即每次添加现在的表的时候,根据添加内容,在新建立的表里找到相应记录,更改它的内容或者添加。可以用触发器实现。
不好意思 忘了点东西 select 帐号ZH,交易代码JYDM,SUM(交易金额JYJE) AS 交易总金额,COUNT(交易代码JYDM) AS 交易次数 FROM TABLENAME group by 交易代码JYDM
不好意思,忘记加了一个处理 select zh,sum(case jydm when 9999 then jyje else 0 end),sum(case jydm when 9999 then 1 else 0),sum(case jydm when 9998 then jyje else 0 end),sum(case jydm when 9998 then 1 else 0),sum(case jydm when 9995 then jyje else 0 end),sum(case jydm when 9995 then 1 else 0),sum(case jydm when 9990 then jyje else 0 end),sum(case jydm when 9990 then 1 else 0) from yourtable group by zh
declare @s varchar(3000) set @s = 'select ZH [帐号]' select @s = @s + ',sum(case when JYDM ='''+ JYDM + ''' then JYJE else 0 end) as [' +JYDM+'交易总金额]' + ',sum(case when JYDM ='''+ JYDM + ''' then 1 else 0 end) as [交易次数]' from (select distinct ZH from table1) A order by JYDM desc set @s = @s + ' from talbe1 group by ZH' exec (@s)
这个就有交易次数和交易金额。 declare @sql varchar(8000) set @sql = 'select 帐号 select @sql = @sql + ',sum(case 交易代码JYDM when '''+cast(交易代码JYDM as varchar(10))+''' then 1 else 0 end) ['+cast(交易代码JYDM as varchar(10))+'交易次数]', + ',sum(case 交易代码JYDM when '''+cast(交易代码JYDM as varchar(10))+''' then 交易金额JYJE else 0 end) ['+cast(交易代码JYDM as varchar(10))+'交易金额]' from (select distinct 交易代码JYDM from testdata) as a select @sql = @sql+',sum(交易金额JYJE) 合计 from testdata group by 表名'exec(@sql) go
sum(case jydm when 9999 then 1 else 0)这句就统计次数。
declare @n table (帐号ZH nvarchar(100), 交易代码JYDM int,交易金额JYJE smallmoney) insert into @n select '00001', 9999, 2536 insert into @n select '00002', 9998, 3659 insert into @n select '00003', 9999, 65499 insert into @n select '00001', 9997, 85 insert into @n select '00001' , 9995, 8596 insert into @n select '00003' , 9995, 45 insert into @n select '00002' , 9990 , 1 insert into @n select '00001' , 9999 , 12112 insert into @n select '00001', 9995, 85748select 帐号ZH as 帐号, sum(case 交易代码JYDM when 9999 then 交易金额JYJE else 0 end) as '9999交易总金额', count(*) as 交易次数, sum(case 交易代码JYDM when 9998 then 交易金额JYJE else 0 end) as '9998交易总金额', count(*) as 交易次数, sum(case 交易代码JYDM when 9995 then 交易金额JYJE else 0 end) as '9995交易总金额', count(*) as 交易次数, sum(case 交易代码JYDM when 9990 then 交易金额JYJE else 0 end) as '9990交易总金额', count(*) as 交易次数 from @n group by 帐号ZH
declare @n table (帐号ZH nvarchar(100), 交易代码JYDM int,交易金额JYJE smallmoney) insert into @n select '00001', 9999, 2536 insert into @n select '00002', 9998, 3659 insert into @n select '00003', 9999, 65499 insert into @n select '00001', 9997, 85 insert into @n select '00001' , 9995, 8596 insert into @n select '00003' , 9995, 45 insert into @n select '00002' , 9990 , 1 insert into @n select '00001' , 9999 , 12112 insert into @n select '00001', 9995, 85748select 帐号ZH as 帐号, sum(case 交易代码JYDM when 9999 then 交易金额JYJE else 0 end) as '9999交易总金额', count(*) as 交易次数, sum(case 交易代码JYDM when 9998 then 交易金额JYJE else 0 end) as '9998交易总金额', count(*) as 交易次数, sum(case 交易代码JYDM when 9995 then 交易金额JYJE else 0 end) as '9995交易总金额', count(*) as 交易次数, sum(case 交易代码JYDM when 9990 then 交易金额JYJE else 0 end) as '9990交易总金额', count(*) as 交易次数 from @n group by 帐号ZH
declare @sql varchar(8000) set @sql = 'select 帐号 select @sql = @sql + ',sum(case 交易代码JYDM when '''+cast(交易代码JYDM as varchar(10))+''' then 1 else 0 end) ['+cast(交易代码JYDM as varchar(10))+'交易次数]', + ',sum(case 交易代码JYDM when '''+cast(交易代码JYDM as varchar(10))+''' then 交易金额JYJE else 0 end) ['+cast(交易代码JYDM as varchar(10))+'交易金额]' from (select distinct 交易代码JYDM from testdata) as a select @sql = @sql+',sum(交易金额JYJE) 合计 from testdata group by 表名'exec(@sql)
to all 一条查询可以解决的问题,不用搞得那么复杂吧
select DISTINCT 交易代码JYDM,(select sum(交易金额JYJE) from 你的表 WHERE 交易代码JYDM=a.交易代码JYDM) AS 交易总金额, (select count(交易代码JYDM) FROM WHERE 交易代码JYDM=a.交易代码JYDM) AS 交易次数 FROM 你的表 AS A
SUM( 交易代码JYDM ) AS 交易次数
FROM 你的表
GROUP BY 帐号ZH
set @sql = 'select 帐号
select @sql = @sql + ',sum(case 交易代码JYDM when '''+cast(交易代码JYDM as varchar(10))+''' then 1 else 0 end) ['+cast(交易代码JYDM as varchar(10))+'交易次数]',
+ ',sum(case 交易代码JYDM when '''+cast(交易代码JYDM as varchar(10))+''' then 交易金额JYJE else 0 end) ['+cast(交易代码JYDM as varchar(10))+'交易金额]'
from (select distinct 交易代码JYDM from testdata) as a
select @sql = @sql+',sum(交易金额JYJE) 合计 from testdata group by 表名'exec(@sql)
go
select zh,sum(case jydm when 9999 then jyje else 0 end),sum(case jydm when 9999 then 1 else 0),sum(case jydm when 9998 then jyje else 0 end),sum(case jydm when 9998 then 1 else 0),sum(case jydm when 9995 then jyje else 0 end),sum(case jydm when 9995 then 1 else 0),sum(case jydm when 9990 then jyje else 0 end),sum(case jydm when 9990 then 1 else 0) from yourtable
如果你的每个帐号都有这四个交易代码。而求和操作又是必须做的,而且数据量和客户访问次数很多的话,你不如建立另外一个表,用来存放统计数据。即每次添加现在的表的时候,根据添加内容,在新建立的表里找到相应记录,更改它的内容或者添加。可以用触发器实现。
忘了点东西
select 帐号ZH,交易代码JYDM,SUM(交易金额JYJE) AS 交易总金额,COUNT(交易代码JYDM) AS 交易次数
FROM TABLENAME
group by 交易代码JYDM
select zh,sum(case jydm when 9999 then jyje else 0 end),sum(case jydm when 9999 then 1 else 0),sum(case jydm when 9998 then jyje else 0 end),sum(case jydm when 9998 then 1 else 0),sum(case jydm when 9995 then jyje else 0 end),sum(case jydm when 9995 then 1 else 0),sum(case jydm when 9990 then jyje else 0 end),sum(case jydm when 9990 then 1 else 0) from yourtable group by zh
第一:建个临时表。表字段如下:交易代码、交易总金额、交易次数
这些东西在存储过程中执行:
在执行的时候。就先清除临时表中的数据了
如下:
CREATE PROC sum_test
//删除临时表中的数据
//在里面添充临时表
第一:建个临时表。表字段如下:交易代码、交易总金额、交易次数
这些东西在存储过程中执行:
在执行的时候。就先清除临时表中的数据了
如下:
CREATE PROC sum_test
//删除临时表中的数据
//在里面添充临时表
set @s = 'select ZH [帐号]'
select @s = @s + ',sum(case when JYDM ='''+ JYDM + ''' then JYJE else 0 end) as [' +JYDM+'交易总金额]'
+ ',sum(case when JYDM ='''+ JYDM + ''' then 1 else 0 end) as [交易次数]'
from (select distinct ZH from table1) A order by JYDM desc
set @s = @s + ' from talbe1 group by ZH'
exec (@s)
请大家看看,有什么好的SQL语句,
其实需求里面还有一个当只要求查询一个交易代码的情况时候,按交易金额,交易次数排序
当所有的交易都需要统计的时,只按帐号排序,
declare @sql varchar(8000)
set @sql = 'select 帐号
select @sql = @sql + ',sum(case 交易代码JYDM when '''+cast(交易代码JYDM as varchar(10))+''' then 1 else 0 end) ['+cast(交易代码JYDM as varchar(10))+'交易次数]',
+ ',sum(case 交易代码JYDM when '''+cast(交易代码JYDM as varchar(10))+''' then 交易金额JYJE else 0 end) ['+cast(交易代码JYDM as varchar(10))+'交易金额]'
from (select distinct 交易代码JYDM from testdata) as a
select @sql = @sql+',sum(交易金额JYJE) 合计 from testdata group by 表名'exec(@sql)
go
declare @n table (帐号ZH nvarchar(100), 交易代码JYDM int,交易金额JYJE smallmoney)
insert into @n select '00001', 9999, 2536
insert into @n select '00002', 9998, 3659
insert into @n select '00003', 9999, 65499
insert into @n select '00001', 9997, 85
insert into @n select '00001' , 9995, 8596
insert into @n select '00003' , 9995, 45
insert into @n select '00002' , 9990 , 1
insert into @n select '00001' , 9999 , 12112
insert into @n select '00001', 9995, 85748select 帐号ZH as 帐号,
sum(case 交易代码JYDM when 9999 then 交易金额JYJE else 0 end) as '9999交易总金额',
count(*) as 交易次数,
sum(case 交易代码JYDM when 9998 then 交易金额JYJE else 0 end) as '9998交易总金额',
count(*) as 交易次数,
sum(case 交易代码JYDM when 9995 then 交易金额JYJE else 0 end) as '9995交易总金额',
count(*) as 交易次数,
sum(case 交易代码JYDM when 9990 then 交易金额JYJE else 0 end) as '9990交易总金额',
count(*) as 交易次数
from @n
group by 帐号ZH
insert into @n select '00001', 9999, 2536
insert into @n select '00002', 9998, 3659
insert into @n select '00003', 9999, 65499
insert into @n select '00001', 9997, 85
insert into @n select '00001' , 9995, 8596
insert into @n select '00003' , 9995, 45
insert into @n select '00002' , 9990 , 1
insert into @n select '00001' , 9999 , 12112
insert into @n select '00001', 9995, 85748select 帐号ZH as 帐号,
sum(case 交易代码JYDM when 9999 then 交易金额JYJE else 0 end) as '9999交易总金额',
count(*) as 交易次数,
sum(case 交易代码JYDM when 9998 then 交易金额JYJE else 0 end) as '9998交易总金额',
count(*) as 交易次数,
sum(case 交易代码JYDM when 9995 then 交易金额JYJE else 0 end) as '9995交易总金额',
count(*) as 交易次数,
sum(case 交易代码JYDM when 9990 then 交易金额JYJE else 0 end) as '9990交易总金额',
count(*) as 交易次数
from @n
group by 帐号ZH
set @sql = 'select 帐号
select @sql = @sql + ',sum(case 交易代码JYDM when '''+cast(交易代码JYDM as varchar(10))+''' then 1 else 0 end) ['+cast(交易代码JYDM as varchar(10))+'交易次数]',
+ ',sum(case 交易代码JYDM when '''+cast(交易代码JYDM as varchar(10))+''' then 交易金额JYJE else 0 end) ['+cast(交易代码JYDM as varchar(10))+'交易金额]'
from (select distinct 交易代码JYDM from testdata) as a
select @sql = @sql+',sum(交易金额JYJE) 合计 from testdata group by 表名'exec(@sql)
一条查询可以解决的问题,不用搞得那么复杂吧
(select count(交易代码JYDM) FROM WHERE 交易代码JYDM=a.交易代码JYDM) AS 交易次数
FROM 你的表 AS A
大开眼界,真心感谢。
你的方法不能试,数据库太大了,80W
搜索的时候会出现“过时或过期”,
需要做一个“非唯一索引”我也不会!
不知道怎么办?