如何从一个表中提取数据

Question

嗨，我是一个新的开发人员和一个新的用户在这里，所以让我解释我的问题，我有这个SQL脚本。

我有一个存储

的表

CREATE TABLE test 
(Id int, 
CompanyName nvarchar(200),
BookedDate nvarchar(20), 
BookedTime nvarchar(20), 
Source nvarchar(500), -- is it booked from the enduser website or admin) 
PaymenthMethod  nvarchar(50), 
Cost decimal(10,2))

，所以这是一个存储这些信息的表。

我需要做一个sql server查询，所以我可以得到这样的信息:

终端用户网站

CompanyName|TotalSales|SalesOnSelectedMonth|SalesViaOnline|SalesOffline(cash)

管理>网站

CompanyName|TotalSales|SalesOnSelectedMonth|SalesViaOnline|SalesOffline(cash)

如何将上述表中的数据存储在同一行中，按CompanyName分组(大约有40家公司)?

Answer 1

假设我们在表TEST中有以下数据:

insert into test(ID,COMPANYNAME,BOOKEDDATE,BOOKEDTIME,SOURCE,PAYMENTHMETHOD,COST) values(1, 'company1', '2017-06-22', '21:48:20', 'enduser', 'online',1.2);
insert into test(ID,COMPANYNAME,BOOKEDDATE,BOOKEDTIME,SOURCE,PAYMENTHMETHOD,COST) values(1, 'company2', '2017-05-22', '21:48:20', 'admin', 'cash',100);
insert into test(ID,COMPANYNAME,BOOKEDDATE,BOOKEDTIME,SOURCE,PAYMENTHMETHOD,COST) values(1, 'company4', '2017-04-22', '21:48:20', 'enduser', 'cash',200);
insert into test(ID,COMPANYNAME,BOOKEDDATE,BOOKEDTIME,SOURCE,PAYMENTHMETHOD,COST) values(1, 'company2', '2017-03-22', '21:48:20', 'admin', 'online',500);
insert into test(ID,COMPANYNAME,BOOKEDDATE,BOOKEDTIME,SOURCE,PAYMENTHMETHOD,COST) values(1, 'company3', '2017-02-22', '21:48:20', 'enduser', 'online',700);
insert into test(ID,COMPANYNAME,BOOKEDDATE,BOOKEDTIME,SOURCE,PAYMENTHMETHOD,COST) values(1, 'company5', '2017-01-22', '21:48:20', 'admin', 'cash',800);
insert into test(ID,COMPANYNAME,BOOKEDDATE,BOOKEDTIME,SOURCE,PAYMENTHMETHOD,COST) values(1, 'company6', '2017-12-22', '21:48:20', 'enduser', 'online',800);
insert into test(ID,COMPANYNAME,BOOKEDDATE,BOOKEDTIME,SOURCE,PAYMENTHMETHOD,COST) values(1, 'company8', '2017-11-22', '21:48:20', 'admin', 'online',700);
insert into test(ID,COMPANYNAME,BOOKEDDATE,BOOKEDTIME,SOURCE,PAYMENTHMETHOD,COST) values(1, 'company7', '2017-10-22', '21:48:20', 'admin', 'cash',100);
insert into test(ID,COMPANYNAME,BOOKEDDATE,BOOKEDTIME,SOURCE,PAYMENTHMETHOD,COST) values(1, 'company3', '2017-09-22', '21:48:20', 'enduser', 'cash',200);
insert into test(ID,COMPANYNAME,BOOKEDDATE,BOOKEDTIME,SOURCE,PAYMENTHMETHOD,COST) values(1, 'company1', '2017-08-22', '21:48:20', 'admin', 'online',700);
insert into test(ID,COMPANYNAME,BOOKEDDATE,BOOKEDTIME,SOURCE,PAYMENTHMETHOD,COST) values(1, 'company5', '2017-07-22', '21:48:20', 'enduser', 'cash',100);
insert into test(ID,COMPANYNAME,BOOKEDDATE,BOOKEDTIME,SOURCE,PAYMENTHMETHOD,COST) values(1, 'company1', '2017-06-22', '21:48:20', 'admin', 'online',200);
insert into test(ID,COMPANYNAME,BOOKEDDATE,BOOKEDTIME,SOURCE,PAYMENTHMETHOD,COST) values(1, 'company9', '2017-05-22', '21:48:20', 'admin', 'cash',700);
insert into test(ID,COMPANYNAME,BOOKEDDATE,BOOKEDTIME,SOURCE,PAYMENTHMETHOD,COST) values(1, 'company10', '2017-04-22', '21:48:20', 'admin', 'online',100);
insert into test(ID,COMPANYNAME,BOOKEDDATE,BOOKEDTIME,SOURCE,PAYMENTHMETHOD,COST) values(1, 'company2', '2017-03-22', '21:48:20', 'enduser', 'online',800);
insert into test(ID,COMPANYNAME,BOOKEDDATE,BOOKEDTIME,SOURCE,PAYMENTHMETHOD,COST) values(1, 'company3', '2017-02-22', '21:48:20', 'admin', 'cash',200);
insert into test(ID,COMPANYNAME,BOOKEDDATE,BOOKEDTIME,SOURCE,PAYMENTHMETHOD,COST) values(1, 'company4', '2017-01-22', '21:48:20', 'enduser', 'cash',100);

那么对最终用户和月份june -2017的查询可能是这样的:

WITH TST AS
(
select
ID,
COMPANYNAME,
CONCAT(YEAR(cast(BOOKEDDATE as date)), '-', FORMAT(cast(BOOKEDDATE as date), 'MM', 'en-US')) BOOKEDDATE,
SOURCE,
PAYMENTHMETHOD,
COST  
FROM TEST
)
select
COMPANYNAME,
BOOKEDDATE MONTH_SELECTED,
t.source SOURCE,
ISNULL(sum(cost),0) AS TOTAL_SALES,
(select ISNULL(sum(cost),0) AS SalesViaOnline from TST onl where onl.COMPANYNAME = t.COMPANYNAME and onl.BOOKEDDATE= t.BOOKEDDATE and onl.PAYMENTHMETHOD = 'online' and onl.source = t.source) SALES_VIA_ONLINE,
(select ISNULL(sum(cost),0) AS SalesOffline from TST onl where onl.companyname = t.companyname and onl.BOOKEDDATE= t.BOOKEDDATE and onl.PAYMENTHMETHOD = 'cash' and onl.source = t.source) SALES_OFFLINE
from 
TST T
where 
BOOKEDDATE = '2017-06'
and t.source = 'enduser'
group by 
COMPANYNAME
, BOOKEDDATE
, t.source
order by 1;

同样，对于admin和月份june -2017的查询，可能看起来像这样:

WITH TST AS
(
select
ID,
COMPANYNAME,
CONCAT(YEAR(cast(BOOKEDDATE as date)), '-', FORMAT(cast(BOOKEDDATE as date), 'MM', 'en-US')) BOOKEDDATE,
SOURCE,
PAYMENTHMETHOD,
COST  
FROM TEST
)
select
COMPANYNAME,
BOOKEDDATE MONTH_SELECTED,
t.source SOURCE,
ISNULL(sum(cost),0) AS TOTAL_SALES,
(select ISNULL(sum(cost),0) AS SalesViaOnline from TST onl where onl.COMPANYNAME = t.COMPANYNAME and onl.BOOKEDDATE= t.BOOKEDDATE and onl.PAYMENTHMETHOD = 'online' and onl.source = t.source) SALES_VIA_ONLINE,
(select ISNULL(sum(cost),0) AS SalesOffline from TST onl where onl.companyname = t.companyname and onl.BOOKEDDATE= t.BOOKEDDATE and onl.PAYMENTHMETHOD = 'cash' and onl.source = t.source) SALES_OFFLINE
from 
TST T
where 
BOOKEDDATE = '2017-06'
and t.source = 'admin'
group by 
COMPANYNAME
, BOOKEDDATE
, t.source
order by 1;