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