select sales_mng ,sales,sum(sales_amount)from tbgroup by sales_mng ,sales order by sum(sales_amount)
xuexi dingqi
select sales_mng, sum(sales_amount) as sales_amount, sum(case ordertype when 'new' then 1 end) as NewCnt, sum(case ordertype when 'old' then 1 end) as OldCnt, from tb group by sales_mng order by sales_amount desc
select sales_mng ,sales,sum(sales_amount), sum(case ordertype when '新单' then 1 else 0 end... from tb group by sales_mng ,sales order by sum(sales_amount)
select sales_mng, sum(sales_amount) as sales_amount, sum(case ordertype when 'new' then 1 end) as NewCnt, sum(case ordertype when 'old' then 1 end) as OldCnt, from tb group by sales_mng order by sales_amount desc
select sales_mng, sum(sales_amount) as sales_amount, sum(case ordertype when 'new' then 1 end) as NewCnt, sum(case ordertype when 'old' then 1 end) as OldCnt, from tb group by sales_mng order by sales_amount desc
--检查是否存在Test表 IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id=object_id(N'[dbo].[Test]') And objectproperty(id,N'isUserTable')=1) DROP TABLE Test --创建Test表 CREATE TABLE Test (Sales_Mng varchar(20) null, Order_Type varchar(1) null, Sales varchar(10) null, Sales_Amount decimal(18,2) null) on [primary] GO --插入数据 --注:Order_Type:1为新单;2为旧单 INSERT INTO Test SELECT '张三','1','李四',20000 UNION ALL SELECT '张三','1','李四',22000 UNION ALL SELECT '张三','2','李四',23000 UNION ALL SELECT '张三','1','王五',15000 UNION ALL SELECT '毛小明','1','赵七',20000 UNION ALL SELECT '毛小明','2','赵七',10000 UNION ALL SELECT '毛小明','2','赵七',120000 UNION ALL SELECT '毛小明','1','刘九',20000 GO--查询数据 --注:Order_Mtr1为新单个数;Order_Mtr2为旧单个数 SELECT A.Sales_Mng,A.Sales, SUM(A.Sales_Amount) as Sales_Amount, B.Order_Amt1,C.Order_Amt2 FROM Test A LEFT JOIN ( SELECT Sales_Mng,Sales,COUNT(Order_Type) as Order_Amt1 FROM Test WHERE Order_Type='1' GROUP BY Sales_Mng,Sales ) B ON A.Sales_Mng=B.Sales_Mng AND A.Sales=B.Sales LEFT JOIN ( SELECT Sales_Mng,Sales,COUNT(Order_Type) as Order_Amt2 FROM Test WHERE Order_Type='2' GROUP BY Sales_Mng,Sales ) C ON A.Sales_Mng=C.Sales_Mng AND A.Sales=C.Sales GROUP BY A.Sales_Mng,A.Sales,B.Order_Amt1,C.Order_Amt2 ORDER BY A.Sales_Amount desc
select sales_mng ,sales,sum(sales_amount)from tbgroup by sales_mng ,sales order by sum(sales_amount)
select sales_mng,sales,sum(sales_amount) sales_amount, sum(case ordertype when 'new' then '新单'end) neworder, sum(case ordertype when 'old' then '老单'end) oldorder from tb group by sales_mng,sales order by sales_amount
select sales_mng as 销售经理,sales as 业务员,sum(sales_amount) as 金额, count(case when ordertype='新单' then 1 end) as 新单数量, count(case when ordertype='旧单' then 1 end) as 旧单数量 from tb group by sales_mng,sales order by sum(sales_amount) desc
select sales_mng as 销售经理,sales as 业务员,sum(sales_amount) as 金额, count(case when ordertype='新单' then 1 end) as 新单数量, count(case when ordertype='旧单' then 1 end) as 旧单数量 from tb group by sales_mng,sales order by sum(sales_amount) desc
不行啊..总是报错.sql="select sk_sales, sum(sk_yy) as total_amount,sum(case sk_branch when '旧单' then 1 else 0 end) as OldCnt from wz_result group by sk_sales order by sum(sk_yy) desc"我是这样写的.如果我不加sum(case sk_branch when '旧单' then 1 else 0 end)那就没错.加上这句就报错了.不知道是什么原因.
order by sum(sales_amount)
sum(case ordertype when 'new' then 1 end) as NewCnt,
sum(case ordertype when 'old' then 1 end) as OldCnt,
from tb
group by sales_mng
order by sales_amount desc
sum(case ordertype when '新单' then 1 else 0 end...
from tb
group by sales_mng ,sales
order by sum(sales_amount)
sum(sales_amount) as sales_amount,
sum(case ordertype when 'new' then 1 end) as NewCnt,
sum(case ordertype when 'old' then 1 end) as OldCnt,
from tb
group by sales_mng
order by
sales_amount desc
sum(sales_amount) as sales_amount,
sum(case ordertype when 'new' then 1 end) as NewCnt,
sum(case ordertype when 'old' then 1 end) as OldCnt,
from tb
group by sales_mng
order by
sales_amount desc
IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id=object_id(N'[dbo].[Test]') And objectproperty(id,N'isUserTable')=1)
DROP TABLE Test
--创建Test表
CREATE TABLE Test (Sales_Mng varchar(20) null,
Order_Type varchar(1) null,
Sales varchar(10) null,
Sales_Amount decimal(18,2) null) on [primary]
GO
--插入数据
--注:Order_Type:1为新单;2为旧单
INSERT INTO Test
SELECT '张三','1','李四',20000 UNION ALL
SELECT '张三','1','李四',22000 UNION ALL
SELECT '张三','2','李四',23000 UNION ALL
SELECT '张三','1','王五',15000 UNION ALL
SELECT '毛小明','1','赵七',20000 UNION ALL
SELECT '毛小明','2','赵七',10000 UNION ALL
SELECT '毛小明','2','赵七',120000 UNION ALL
SELECT '毛小明','1','刘九',20000
GO--查询数据
--注:Order_Mtr1为新单个数;Order_Mtr2为旧单个数
SELECT A.Sales_Mng,A.Sales,
SUM(A.Sales_Amount) as Sales_Amount,
B.Order_Amt1,C.Order_Amt2
FROM Test A LEFT JOIN (
SELECT Sales_Mng,Sales,COUNT(Order_Type) as Order_Amt1
FROM Test WHERE Order_Type='1'
GROUP BY Sales_Mng,Sales
) B ON A.Sales_Mng=B.Sales_Mng AND A.Sales=B.Sales LEFT JOIN (
SELECT Sales_Mng,Sales,COUNT(Order_Type) as Order_Amt2
FROM Test WHERE Order_Type='2'
GROUP BY Sales_Mng,Sales
) C ON A.Sales_Mng=C.Sales_Mng AND A.Sales=C.Sales
GROUP BY A.Sales_Mng,A.Sales,B.Order_Amt1,C.Order_Amt2
ORDER BY A.Sales_Amount desc
order by sum(sales_amount)
sum(case ordertype when 'new' then '新单'end) neworder,
sum(case ordertype when 'old' then '老单'end) oldorder
from tb
group by sales_mng,sales
order by sales_amount
count(case when ordertype='新单' then 1 end) as 新单数量,
count(case when ordertype='旧单' then 1 end) as 旧单数量
from tb
group by sales_mng,sales
order by sum(sales_amount) desc
count(case when ordertype='新单' then 1 end) as 新单数量,
count(case when ordertype='旧单' then 1 end) as 旧单数量
from tb
group by sales_mng,sales
order by sum(sales_amount) desc