CASE WHEN t.LevelID=6 and Convert(varchar,t.OrderTime,120) like '%-01-%' THEN count(t.LevelID)
ELSE 0 END AS o_lv,
CASE WHEN t.LevelID=7 and Convert(varchar,t.SucceedTime,120) like '%-01-%' THEN count(t.LevelID)
ELSE 0 END AS d_lv,
CASE WHEN t.LevelID=1 and Convert(varchar,t.LoseTime,120) like '%-01-%' THEN count(t.LevelID)
ELSE 0 END AS p_lv 我怎样算它们的总数啊,我试过count(o_lv+d_lv+p_lv)as Amount,结果报错o_lv、d_lv、p_lv字段无效,请问什么算他们的和啊
如果我没有回复,我去吃饭了
ELSE 0 END)+
(CASE WHEN t.LevelID=7 and Convert(varchar,t.SucceedTime,120) like '%-01-%' THEN count(t.LevelID) else 0 end)
SELECT l.SaleName, sum(l.Amount) AS Amount,
sum(l.c_lv) AS c_lv,sum(l.o_lv) AS o_lv,
sum(l.d_lv) AS d_lv,sum(l.p_lv) as p_lv,
cast(cast(sum(l.d_lv)*100 as decimal(8,2))/sum(l.Amount) as decimal(8,1)) as succeed,
cast(cast(sum(l.p_lv)*100 as decimal(8,2))/sum(l.Amount) as decimal(8,1)) as failure,
l.SaleID
FROM ( SELECT t.SaleName,sum(c_lv+o_lv+d_lv+p_lv) as Amount,t.AddTime,t.SaleID,t.SucceedTime,t.LoseTime,t.OrderTime,
CASE WHEN t.LevelID BETWEEN 2 and 5 and Convert(varchar,t.AddTime,120) like '%-00-%' THEN count(t.LevelID)
WHEN t.LevelID BETWEEN 2 and 5 and datediff(week,t.AddTime,getdate())=0 THEN count(t.LevelID)
ELSE 0 END AS c_lv,
CASE WHEN t.LevelID=6 and Convert(varchar,t.OrderTime,120) like '%-01-%' THEN count(t.LevelID)
WHEN t.LevelID BETWEEN 2 and 5 and datediff(week,t.OrderTime,getdate())=0 THEN count(t.LevelID)
ELSE 0 END AS o_lv,
CASE WHEN t.LevelID=7 and Convert(varchar,t.SucceedTime,120) like '%-01-%' THEN count(t.LevelID)
WHEN t.LevelID BETWEEN 2 and 5 and datediff(week,t.SucceedTime,getdate())=0 THEN count(t.LevelID)
ELSE 0 END AS d_lv,
CASE WHEN t.LevelID=1 and Convert(varchar,t.LoseTime,120) like '%-01-%' THEN count(t.LevelID)
WHEN t.LevelID BETWEEN 2 and 5 and datediff(week,t.LoseTime,getdate())=0 THEN count(t.LevelID)
ELSE 0 END AS p_lv
FROM ( SELECT s.SaleID, s.SaleName, s.LevelID, s.AddTime,s.SucceedTime,s.LoseTime,s.OrderTime
FROM SaleClient AS s INNER JOIN Client AS c ON (s.ClientID = c.ID)
WHERE (s.Transfer = 0)
GROUP BY s.SaleName,s.SaleID,s.ClientName,c.MobilePhone,s.LevelID,s.AddTime,s.SucceedTime,s.LoseTime,s.OrderTime) AS t
where t.SaleID=13
group by t.LevelID,t.SaleName,t.SaleID,t.AddTime,t.SucceedTime,t.LoseTime,t.OrderTime ) as l
GROUP BY l.SaleName,l.SaleID
sum(o_lv+d_lv+p_lv) 还是报错
消息 207,级别 16,状态 1,第 1 行
列名 'c_lv' 无效。
消息 207,级别 16,状态 1,第 1 行
列名 'o_lv' 无效。
消息 207,级别 16,状态 1,第 1 行
列名 'd_lv' 无效。
消息 207,级别 16,状态 1,第 1 行
列名 'p_lv' 无效。
sum(l.c_lv) AS c_lv,sum(l.o_lv) AS o_lv,
sum(l.d_lv) AS d_lv,sum(l.p_lv) as p_lv,
cast(cast(sum(l.d_lv)*100 as decimal(8,2))/sum(l.Amount) as decimal(8,1)) as succeed,
cast(cast(sum(l.p_lv)*100 as decimal(8,2))/sum(l.Amount) as decimal(8,1)) as failure,
l.SaleID
FROM ( SELECT t.SaleName,
(c_lv+o_lv+d_lv+p_lv)
as Amount,t.AddTime,t.SaleID,t.SucceedTime,t.LoseTime,t.OrderTime,
CASE WHEN t.LevelID BETWEEN 2 and 5 and Convert(varchar,t.AddTime,120) like '%-00-%' THEN count(t.LevelID)
WHEN t.LevelID BETWEEN 2 and 5 and datediff(week,t.AddTime,getdate())=0 THEN count(t.LevelID)
ELSE 0 END AS c_lv,
CASE WHEN t.LevelID=6 and Convert(varchar,t.OrderTime,120) like '%-01-%' THEN count(t.LevelID)
WHEN t.LevelID BETWEEN 2 and 5 and datediff(week,t.OrderTime,getdate())=0 THEN count(t.LevelID)
ELSE 0 END AS o_lv,
CASE WHEN t.LevelID=7 and Convert(varchar,t.SucceedTime,120) like '%-01-%' THEN count(t.LevelID)
WHEN t.LevelID BETWEEN 2 and 5 and datediff(week,t.SucceedTime,getdate())=0 THEN count(t.LevelID)
ELSE 0 END AS d_lv,
CASE WHEN t.LevelID=1 and Convert(varchar,t.LoseTime,120) like '%-01-%' THEN count(t.LevelID)
WHEN t.LevelID BETWEEN 2 and 5 and datediff(week,t.LoseTime,getdate())=0 THEN count(t.LevelID)
ELSE 0 END AS p_lv
FROM ( SELECT s.SaleID, s.SaleName, s.LevelID, s.AddTime,s.SucceedTime,s.LoseTime,s.OrderTime
FROM SaleClient AS s INNER JOIN Client AS c ON (s.ClientID = c.ID)
WHERE (s.Transfer = 0)
GROUP BY s.SaleName,s.SaleID,s.ClientName,c.MobilePhone,s.LevelID,s.AddTime,s.SucceedTime,s.LoseTime,s.OrderTime) AS t
where t.SaleID=13
group by t.LevelID,t.SaleName,t.SaleID,t.AddTime,t.SucceedTime,t.LoseTime,t.OrderTime ) as l
GROUP BY l.SaleName,l.SaleID (c_lv+o_lv+d_lv+p_lv)也是报错
消息 207,级别 16,状态 1,第 1 行
列名 'c_lv' 无效。
消息 207,级别 16,状态 1,第 1 行
列名 'o_lv' 无效。
消息 207,级别 16,状态 1,第 1 行
列名 'd_lv' 无效。
消息 207,级别 16,状态 1,第 1 行
列名 'p_lv' 无效。
CASE WHEN t.LevelID=6 and Convert(varchar,t.OrderTime,120) like '%-01-%' THEN count(t.LevelID)
ELSE 0 END AS o_lv,
CASE WHEN t.LevelID=7 and Convert(varchar,t.SucceedTime,120) like '%-01-%' THEN count(t.LevelID)
ELSE 0 END AS d_lv,
CASE WHEN t.LevelID=1 and Convert(varchar,t.LoseTime,120) like '%-01-%' THEN count(t.LevelID)
ELSE 0 END AS p_lv from t) TT
COUNT()函数求数量
试过sum(o_lv+d_lv+p_lv)as Amount
试过(o_lv+d_lv+p_lv)as Amount
试过 o_lv+d_lv+p_lv as Amount
但都报错
消息 207,级别 16,状态 1,第 1 行
列名 'c_lv' 无效。
消息 207,级别 16,状态 1,第 1 行
列名 'o_lv' 无效。
消息 207,级别 16,状态 1,第 1 行
列名 'd_lv' 无效。
消息 207,级别 16,状态 1,第 1 行
列名 'p_lv' 无效。
那位高手能告诉我什么算它们的和吗?
CASE WHEN t.LevelID BETWEEN 2 and 5 and Convert(varchar,t.AddTime,120) like '%-00-%' THEN count(t.LevelID)
ELSE 0 END AS c_lv
CASE WHEN t.LevelID=6 and Convert(varchar,t.OrderTime,120) like '%-01-%' THEN count(t.LevelID)
ELSE 0 END AS o_lv,
CASE WHEN t.LevelID=7 and Convert(varchar,t.SucceedTime,120) like '%-01-%' THEN count(t.LevelID)
ELSE 0 END AS d_lv,
CASE WHEN t.LevelID=1 and Convert(varchar,t.LoseTime,120) like '%-01-%' THEN count(t.LevelID)
ELSE 0 END AS p_lv
那位高手高手高高手能告诉我什么算它们的和吗?
我试过count(o_lv+d_lv+p_lv)as Amount,
试过sum(o_lv+d_lv+p_lv)as Amount
试过(o_lv+d_lv+p_lv)as Amount
试过 o_lv+d_lv+p_lv as Amount
但都报错
消息 207,级别 16,状态 1,第 1 行
列名 'c_lv' 无效。
消息 207,级别 16,状态 1,第 1 行
列名 'o_lv' 无效。
消息 207,级别 16,状态 1,第 1 行
列名 'd_lv' 无效。
消息 207,级别 16,状态 1,第 1 行
列名 'p_lv' 无效。
那位高手高手高高手能告诉我什么算它们的和吗?
from
(
select CASE WHEN t.LevelID BETWEEN 2 and 5 and Convert(varchar,t.AddTime,120) like '%-00-%' THEN count(t.LevelID)
ELSE 0 END AS c_lv from tb
union all
select CASE WHEN t.LevelID=6 and Convert(varchar,t.OrderTime,120) like '%-01-%' THEN count(t.LevelID)
ELSE 0 END AS c_lv from tb,
union all
select CASE WHEN t.LevelID=7 and Convert(varchar,t.SucceedTime,120) like '%-01-%' THEN count(t.LevelID)
ELSE 0 END AS c_lv from tb,
union all
select CASE WHEN t.LevelID=1 and Convert(varchar,t.LoseTime,120) like '%-01-%' THEN count(t.LevelID)
ELSE 0 END AS c_lv from tb)t
CASE WHEN t.LevelID=6 and Convert(varchar,t.OrderTime,120) like '%-01-%' THEN 'LevelID=6的情况'
WHEN t.LevelID=7 and Convert(varchar,t.SucceedTime,120) like '%-01-%' THEN 'LevelID=7的情况'
WHEN t.LevelID=1 and Convert(varchar,t.LoseTime,120) like '%-01-%' THEN 'LevelID=1的情况'
ELSE '其他' END, COUNT(0)
FROM [表] as t
GROUP BY
CASE WHEN t.LevelID=6 and Convert(varchar,t.OrderTime,120) like '%-01-%' THEN 'LevelID=6的情况'
WHEN t.LevelID=7 and Convert(varchar,t.SucceedTime,120) like '%-01-%' THEN 'LevelID=7的情况'
WHEN t.LevelID=1 and Convert(varchar,t.LoseTime,120) like '%-01-%' THEN 'LevelID=1的情况'
ELSE '其他' END
--上面是每一种情况的数量
--这个是情况总和
SELECT SUM([总数]) FROM
(
SELECT
CASE WHEN t.LevelID=6 and Convert(varchar,t.OrderTime,120) like '%-01-%' THEN 'LevelID=6的情况'
WHEN t.LevelID=7 and Convert(varchar,t.SucceedTime,120) like '%-01-%' THEN 'LevelID=7的情况'
WHEN t.LevelID=1 and Convert(varchar,t.LoseTime,120) like '%-01-%' THEN 'LevelID=1的情况'
ELSE '其他' END, COUNT(0) AS [总数]
FROM [表] as t
GROUP BY
CASE WHEN t.LevelID=6 and Convert(varchar,t.OrderTime,120) like '%-01-%' THEN 'LevelID=6的情况'
WHEN t.LevelID=7 and Convert(varchar,t.SucceedTime,120) like '%-01-%' THEN 'LevelID=7的情况'
WHEN t.LevelID=1 and Convert(varchar,t.LoseTime,120) like '%-01-%' THEN 'LevelID=1的情况'
ELSE '其他' END
) AS A