sql报表
‘壹’ sql 报表统计 总计小计
哥们儿，你这写的太乱了。。整理一下格式吧。
‘贰’ 用SQL查询报表
select
sales.a,sales.b
-
inbuy.b
-
cost1.b
-
cost2.b
from
(select
date
as
a,sum(销售额)
as
b
from
销售表
where
date
between
开始日期
and
结束日期
group
by
date
)
as
sales,
(select
date
as
a,sum(进货额)
as
b
from
进货表
where
date
between
开始日期
and
结束日期
group
by
date)
as
inbuy,
(select
date
as
a,sum(支出额)
as
b
from
企业支出费用表
where
date
between
开始日期
and
结束日期
group
by
date)
as
cost1,
(select
date
as
a,sum(费用额)
as
b
from
其他费用表
where
date
between
开始日期
and
结束日期
group
by
date)
as
cost2
where
sales.a
=
inbuy.a
and
sales.a
=
cost1.a
and
sales.a
=
cost2.a
‘叁’ 用sql 实现报表查询
select sales.a,sales.b - inbuy.b - cost1.b - cost2.b
from
(select date as a,sum(销售额) as b from 销售表 where date between 开始日期 and 结束日期 group by date ) as sales,
(select date as a,sum(进货额) as b from 进货表 where date between 开始日期 and 结束日期 group by date) as inbuy,
(select date as a,sum(支出额) as b from 企业支出费用表 where date between 开始日期 and 结束日期 group by date) as cost1,
(select date as a,sum(费用额) as b from 其他费用表 where date between 开始日期 and 结束日期 group by date) as cost2
where
sales.a = inbuy.a and
sales.a = cost1.a and
sales.a = cost2.a
‘肆’ SQL 统计报表
-- create sample table
create table tab1 (id int, output_style char(1), amount int, output_date date);
insert into tab1 values(1,'D',1000,'2009-11-12');
insert into tab1 values(2,'C',1000,'2009-11-12');
insert into tab1 values(3,'G',1000,'2009-12-12');
insert into tab1 values(4,'Z',1000,'2010-01-01');
insert into tab1 values(5,'D',1300,'2009-11-12');
insert into tab1 values(6,'C',1400,'2009-11-12');
insert into tab1 values(7,'G',2000,'2009-12-12');
insert into tab1 values(8,'Z',1000,'2010-01-01');
-- query
select concat(year(output_date), month(output_date)) as yearmonth,
sum(case when output_style='C' then amount else 0 end) as C_Amount,
sum(case when output_style='G' then amount else 0 end) as G_Amount,
sum(case when output_style='Z' then amount else 0 end) as Z_Amount,
sum(case when output_style='D' then amount else 0 end) as D_Amount from tab1 group by output_date
‘伍’ 报表的sql语句
日期格式转换错误,
你在控制面板中设置一下日期格式,直接设置成yyyy-mm-dd 格式试试
‘陆’ SQL语句制作报表
t1,t2,t3对应表123
列名用了拼音首字母代替，自己看下吧
SELECT isnull(t1.GG,isnull(t2.gg,t3.gg)) 规格,
isnull(SR,0) 收入,isnull(FC,0) 发出,isnull(jc,0) 结存
FROM
t1 full join t2
on t1.gg=t2.gg
full join t3
on t1.gg = t3.gg
‘柒’ 用SQL统计数据库报表
select 村名,sum(case when 内容分类='家庭矛盾' then 1 end) 家庭矛盾, sum(case when 内容分类='自然灾害、环境保护' then 1 end) 自然灾害、环境保护, sum(case when 内容分类='经济' then 1 end) 经济, sum(case when 处理情况='已办结' then 1 end) 已办结，sum(case when 处理情况='正在办结' then 1 end) 正在办结,sum(case when 处理情况='未办结' then 1 end) 未办结 from table group by 村名
‘捌’ sql 月报表每日数据汇总
看着没人回，我回复一下，这个问题是一个常规的交叉查询法，办法一般有两个
一个是把日期作为自定义字段名，用CASE把结果一个一个字段写入，这个要写31个，比较麻烦。大概样式为SELECT 年 ，月，（case day(日期) when 1 then 金额 else 0 end ）1号 ，……from where group by 样式
另一个是建一个按你每二个表样式的临时表，把符合条件的值INSERT入这张临时表，然后再执行SELECT SUM() FROM GROUP BY样式
‘玖’ 求一条sql语句，显示一个报表，内容如下：
select a.增减员年月 "年月",b.上月人数 ,b.上月金额,a.增员人数,a.增员金额,a.减员人数,a.减员金额,b.上月人数+a.增员人数-a.减员人数 "本月人数",b.上月金额+a.增员金额- a.减员金额 "本月金额"
from am_view a,
(select a.增减员年月+1 "年月",
sum(a.增员人数) over(partition by num order by id)-sum(a.减员人数) over(partition by num order by id)- a.增员金额+ a.减员人数 "上月人数" ,
sum(a.增员金额) over(partition by num order by id)-sum(a.减员金额) over(partition by num order by id)- a.增员金额+ a.减员金额 "上月人数" , "上月金额"
from (select to_number('1') num,rownum id,* from am_view a order by 增减员年月 )
) b
where a.增减员年月=b.年月(+)
凭借你给的信息写出来的，主要就是围绕am_view表的运算
你在am_view表中的增减员年月的类型是什么，如果是date的话还要转换一下
‘拾’ SQL 怎么建立报表
use pubs if exists (select * from sysobjects where name = 'BonusPenalty_info') drop table BonusPenalty_infocreate table BonusPenalty_info([id] nvarchar(20) primary key,[name] nvarchar(20) not null,BonusPenalty_Time nvarchar(50) not null,BonusPenalty_sort nvarchar(50) not null,BonusPenalty_content nvarchar(50) not null,Approve nvarchar(50) not null,RecallCause nvarchar(50) not null,BonusPenalty_Cause nvarchar(50) not null,Remark nvarchar(50) not null,Dept nvarchar(50) not null,)select * from BonusPenalty_info