我用的是SAP bo 后台式SQL2008现在我做了一个查询,作为CTE
WITH C AS
(SELECT t1.shipdate as 'Requested ShipDate', T2.ACTDELDATE AS 'Actual ShipDate', diff =
CASE
WHEN datediff( HH, t1.shipdate,t2.actdeldate)>0 THEN '1'
WHEN datediff( HH, t1.shipdate,t2.actdeldate)<0 THEN '-1'
WHEN datediff( HH, t1.shipdate,t2.actdeldate)=0 THEN '0'
END FROM ORDR T0 INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry 我只截取了一些,大家忽略语法其中DIFF 列 就有0,-1,1 三种值我现在的问题是想 计算中三种值的个数,逻辑如下
select count(diff) as late where diff=1
select count(diff) as ontime where diff=0
select count(diff) as earily where diff<0 显示成以下形式,该如何写???
ontime late earily ontime达标率
10 15 20 10/(10+15+20)
SQLSAP
WITH C AS
(SELECT t1.shipdate as 'Requested ShipDate', T2.ACTDELDATE AS 'Actual ShipDate', diff =
CASE
WHEN datediff( HH, t1.shipdate,t2.actdeldate)>0 THEN '1'
WHEN datediff( HH, t1.shipdate,t2.actdeldate)<0 THEN '-1'
WHEN datediff( HH, t1.shipdate,t2.actdeldate)=0 THEN '0'
END FROM ORDR T0 INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry 我只截取了一些,大家忽略语法其中DIFF 列 就有0,-1,1 三种值我现在的问题是想 计算中三种值的个数,逻辑如下
select count(diff) as late where diff=1
select count(diff) as ontime where diff=0
select count(diff) as earily where diff<0 显示成以下形式,该如何写???
ontime late earily ontime达标率
10 15 20 10/(10+15+20)
SQLSAP
--参考一下
create table e
(
diff nvarchar(10)
)
insert into e
select '1' union all
select '1' union all
select '1' union all
select '1' union allselect '0' union all
select '0' union all
select '0' union allselect '-1' union all
select '-1' union all
select '-1'
go
select
count(case when diff = '1' then 1 end) dat1,
count(case when diff = '0' then 1 end) dat2,
count(case when diff = '-1' then 1 end) dat3
from e
想用SELECT 做成下面的结果
DECLARE @MyTableVar table(
id int NOT NULL,
value int
);