1. oracle sql報表統計，怎麼按訂單類別，分組顯示訂單總數和交易成功訂單數。
SELECT
ord.ORDER_TYPE as 訂單類型,
COUNT(ord.ORDER_ID) as 訂單總數,
orde.succCount as 交易成功訂單總數
FROM
ORDER ord
LEFT JOIN
(
SELECT
ORDER_TYPE,
COUNT(ORDER_ID) AS succCount
FROM
ORDER
WHERE
STATUS_ID = 'ORDER_SUCCESS'
GROUP BY
ORDER_TYPE
)
orde
ON
orde.ORDER_TYPE = ord.ORDER_TYPE
GROUP BY
ord.ORDER_TYPE;
還有一種方法是，
SELECT
ORDER_TYPE AS 訂單類型,
STATUS_ID AS 訂單狀態,
COUNT(ORDER_ID) AS 訂單數
FROM
ORDER
GROUP BY
ORDER_TYPE,
STATUS_ID;
查出以訂單類型的不同狀態為分組的訂單數，然後在代碼中求訂單總數
2. java統計報表sql查出來一個欄位的不同的統計
你同步更新數據就行了啊，每次打接，總次數+1更新資料庫，取數據就根據電話號取
3. 要出報表，怎麼用sql循環出12個月的數據來： 1,0,1 2,0,0 3,0,0 4,0,0 5,0,0 6,0,0 . . 12,0,0
用group by 時間進行分組數據
4. 關於SQL查詢報表，按月份顯示出每月各個業務的辦理量
select月份,業務名,sum(辦理量)as總辦理量from業務表A
groupby月份,業務名
5. SQL銷售日報表分組排序問題，請高手指導,謝謝。。。
你要怎麼分組？ 能把需求說的明白點嗎？
給你寫一個類似的例子 你自己還得動動腦筋 需要的知識我幫你解決了
Select CASE WHEN
GROUPING(FItemNumber)=1
THEN 'Total:' ELSE
FItemNumber END as FItemNumber
,CASE WHEN GROUPING(Fdate)=1 THEN 'State Total:' ELSE Fdate END as Fdate
,Sum(FSaleQty) AS FSaleQty ,Sum(FAmount) AS FAmount ,Sum(FGain) AS FGain
From T_ItemSale
GROUP BY
ROLLUP(Fdate,FItemNumber)
6. 要查詢每天早晨七點到前一天早晨7點的數據，生成日報表SQL怎麼寫啊
這得看你生成的日報表需要從什麼地方取什麼樣的數據，
給你提供個思路，先查詢所有的數據，再where選擇日期范圍，如果涉及匯總數據等，可以考慮用分組子查詢去處理
7. 請問用SQL如何編制以下報表
SQL Server 2008及以上的話可以這樣：
--測試數據
with表A(倉庫,物料代碼,物料名稱,規格型號,單位,單價,收入數量,收入金額)as(
select'原料倉','01.01','原料1','注塑','PCS',2,100,200unionall
select'原料倉','01.02','原料2','','PCS',3,100,300unionall
select'原料倉','01.03','原料3','噴油','PCS',4,100,400unionall
select'原料倉','01.15','原料6','','PCS',1.2,1000,1200unionall
select'成品倉','02.00001','等級品測試A','','PCS',23.37,2,46.74unionall
select'成品倉','02.00004','等級品測試B','','PCS',46.34,2,92.67unionall
select'成品倉','02.03','成品3','','PCS',16.54,30,496.16unionall
select'成品倉','02.04','成品4','','PCS',2.13,200,426.15unionall
select'成品倉','02.06','替代測試0911','','PCS',0.4,100,40
)
--按倉庫分類匯總，並與原始數據一起展示
select倉庫,物料代碼,物料名稱,規格型號,單位,單價,收入數量,收入金額from(
select0asordercode,*from表A
unionall
selectgrouping_id(倉庫),isnull(倉庫+'(小計)','合計'),null,null,null,null,null,sum(收入數量),sum(收入金額)from表A
groupbycube(倉庫)
)t
orderbyordercode,倉庫
結果：
8. 如何運用SQL進行雙向表頭報表查詢
可以先將原始數據按照存款的區間和類別進行一次分組：SELECT TYPE, CASE WHEN BAL < 50000 THEN '5萬元以下'WHEN BAL BETWEEN 50001 AND 100000 THEN '5-10萬'WHEN BAL BETWEEN 100001 AND 200000 THEN '10-20萬'WHEN BAL BETWEEN 200001 AND 300000 THEN '20-30萬'WHEN BAL BETWEEN 300001 AND 400000 THEN '30-40萬'WHEN BAL BETWEEN 400001 AND 500000 THEN '40-50萬'WHEN BAL > 500000 THEN '50萬以上'END AS BAL_RANGE,BAL, AGE, COUNT(*) COUNT_B FROM C_CUST_AGE WHERE DEPT_ID = ？ AND TYPE = ？ GROUP BY TYPE, AGE, BAL
9. sql 按照小時分組查詢並統計
--模擬銷售的表.
CREATETABLE#temp(
[銷售時間]datetime,
[銷量]int,
[實銷]int
)
go
--由於成本與毛利，我估計應該是在另外一個表裡面的，這里就不模擬了
INSERTINTO#temp
--9點的
SELECT'2013-07-1609:55:15',9,900UNIONALL
--10點的
SELECT'2013-07-1610:15:15',1,100UNIONALL
SELECT'2013-07-1610:25:15',15,1500UNIONALL
SELECT'2013-07-1610:35:15',10,1000UNIONALL
--11點的
SELECT'2013-07-1611:15:15',1,100UNIONALL
SELECT'2013-07-1611:25:15',2,200UNIONALL
SELECT'2013-07-1611:35:15',3,300UNIONALL
SELECT'2013-07-1611:45:15',4,400UNIONALL
SELECT'2013-07-1611:55:15',5,500UNIONALL
--其他的.
SELECT'2013-07-1612:15:15',12,1200UNIONALL
SELECT'2013-07-1613:15:15',13,1300UNIONALL
SELECT'2013-07-1614:15:15',14,1400UNIONALL
--中間缺少15點與16點的
SELECT'2013-07-1617:15:15',17,1700UNIONALL
SELECT'2013-07-1618:15:15',18,1800;
goSELECT
ISNULL(Convert(varchar(2),sv.number)+'-'+Convert(varchar(2),sv.number+1),'合計')AS[銷售時段],
SUM([銷量])AS[銷量],
SUM([實銷])AS[實銷],
(SELECTSUM([實銷])FROM#tempt2WHEREDATEPART(hh,t2.[銷售時間])<=ISNULL(sv.number,23))AS[累計實銷]
FROM
master..spt_valuessv
JOIN#tempt
ON(sv.number=DATEPART(hh,t.[銷售時間]))
WHERE
sv.type='P'
ANDsv.number>=9
ANDsv.number<20
GROUPBY
sv.numberWITHrollup
GO
銷售時段銷量實銷累計實銷
--------------------------------------
9-109900900
10-112626003500
11-121515005000
12-131212006200
13-141313007500
14-151414008900
17-1817170010600
18-1918180012400
合計1241240012400
(9行受影響)
這樣行么？
10. SQL報表顯示分組小計和合計是怎麼實現的
客戶提出需求，針對某一列分組加上小計，合計匯總。網上找了一些有關SQL加合計的語句。都不是很理想。決定自己動手寫。
思路有三個：
1.很多用GROUPPING和ROLLUP來實現。
優點：實現代碼簡潔，要求對GROUPPING和ROLLUP很深的理解。
缺點：低版本的Sql Server不支持。
2.游標實現。
優點：思路邏輯簡潔。
缺點：復雜和低效。
3.利用臨時表。
優點：思路邏輯簡潔，執行效率高。SQL實現簡單。
缺點：數據量大時耗用內存.
綜合三種情況，決定「利用臨時表」實現。
實現效果
原始表TB
SQL語句
復制代碼 代碼如下:
select * into #TB from TB
select * into #TB1 from #TB where 1<>1
select distinct zcxt into #TBype from #TB order by zcxt
select identity(int,1,1) fid,zcxt into #TBype1 from #TBype
DECLARE @i int
DECLARE @k int
select @i=COUNT(*) from #TBype
set @k=0
DECLARE @strfname varchar(50)
WHILE @k < @i
BEGIN
Set @k =@k +1
select @strfname=zcxt from #TBype1 where fid =@k
set IDENTITY_INSERT #TB1 ON
insert into #TB1(fid,qldid,fa_cardid,ztbz,fa_name,model,i_number,gzrq,zcyz,ljzj,jz,sybm,zcxt,fa_ljjzzb)
select fid,qldid,fa_cardid,ztbz,fa_name,model,i_number,gzrq,zcyz,ljzj,jz,sybm,zcxt,fa_ljjzzb from
(
select * from #TB where zcxt=@strfname
union all
select 0 fid,'' qldid,'' fa_cardid,'' ztbz,'小計' fa_name,'' model,sum(i_number) as i_number,'' gzrq,sum(CAST(zcyz as money)) as zcyz,sum(CAST(ljzj as money)) as ljzj,sum(CAST(jz as money)) as jz,'' sybm,'' zcxt,Sum(fa_ljjzzb) as fa_ljjzzb
from #TB where zcxt=@strfname
group by ztbz
) as B
set IDENTITY_INSERT #TB1 off
END
select qldid,fa_cardid,zcxt,fa_name,model,i_number,gzrq,zcyz,ljzj,jz,sybm,ztbz,fa_ljjzzb from #TB1
union all
select '' qldid,'' fa_cardid,'' ztbz,'合計' fa_name,'' model,sum(i_number) as i_number,'' gzrq,sum(CAST(zcyz as money)) as zcyz,sum(CAST(ljzj as money)) as ljzj,sum(CAST(jz as money)) as jz,'' sybm,'' zcxt,Sum(fa_ljjzzb) as fa_ljjzzb
from #TB
drop table #TB1
drop table #TBype1
drop table #TBype
drop table #TB
擴展改進
可以改寫成一個通用的添加合計小計的存儲過程。