消除SQL视图的问题

Question

我有一个问题，我已经工作了一个星期了。我在这方面仍然是新手，我很感激到目前为止所得到的帮助。

该查询从3个表中提取数据，然后将其转储到视图中，然后再进行第二个查询，按照老板希望的方式格式化数据。问题是，这必须在SAP B1上运行，它似乎不支持视图。因此，我需要用一种方式来编写它，在一个查询中完成这一切。

下面是第一个查询：

SELECT
t2.cardcode as 'BP_Code',
--t0.Recontact as 'Date',
t2.CardName as 'BP_Name',
SubString(T3.Name,1,2) as 'Salesman',
replace(T0.Street,',',' ') as 'Street_Address',
T0.City, 
T0.State, 
SUM(CASE WHEN YEAR(t0.recontact) = 2011 THEN convert(decimal(10,2),t0.U_sold) ELSE 0 END) AS 'Year2011',
SUM(CASE WHEN YEAR(t0.recontact) = 2012 THEN convert(decimal(10,2),t0.U_sold) ELSE 0 END) AS 'Year2012',
SUM(CASE WHEN YEAR(t0.recontact) = 2013 THEN convert(decimal(10,2),t0.U_sold) ELSE 0 END) AS 'Year2013',
convert(decimal(10,2),SUM(t0.U_sold)) as 'Total_Sold'
From 
OCLG t0 -- OCLG is Activities
inner join OCRD t2 -- OCRD is Customer Definitions
on T0.cardcode like t2.cardcode
inner join OCLS t3 -- OCLS is Activity Definitions
on T0.CntctSbjct=T3.Code 
where 
t0.U_sold > 0
and T0.CntctSbjct=T3.Code 
and T0.CardCode=T2.CardCode
and T0.CntctType='3' 
and t2.CardCode = 'a239' --This was added to simplify output
and T0.Recontact >= Convert(date, '2011-01-01' ) and T0.Recontact <= Convert(date,    '2013-12-31' )
group by t2.cardcode, t0.city, t0.state, t0.street, t2.CardName, T3.Name, t0.Recontact
order by t2.CardCode

现在，它吐出一张桌子，看起来像这样：

BP_Code    BP_Name     Salesman     Street_Address     City     State     Year2011     Year2012     Year 2013     Total_Sold
A239    Buddy's 01  123 WASHINGTON WASHINGTON     MO    8993.84 0.00    0.00    8993.84
A239    Buddy's 01  123 WASHINGTON WASHINGTON     MO    16474.54    0.00    0.00    16474.54
A239    Buddy's 01  123 WASHINGTON WASHINGTON     MO    0.00    7170.79 0.00    7170.79
A239    Buddy's 01  123 WASHINGTON WASHINGTON     MO    0.00    9207.73 0.00    9207.73
A239    Buddy's 01  123 WASHINGTON WASHINGTON     MO    0.00    0.00    6960.20 6960.20
A239    Buddy's 01  123 WASHINGTON WASHINGTON     MO    0.00    0.00    6787.73 6787.73

现在，我将该输出放在一个名为CallReport的视图中，并在其上运行以下查询：

;WITH x AS 
(
  SELECT BP_Code, BP_Name, SalesMan, Street_Address, 
    s = SUM(Total_Sold) OVER (PARTITION BY BP_Code),
    a = SUM(Year2011) OVER (PARTITION BY BP_Code),
    b = SUM(Year2012) OVER (PARTITION BY BP_Code),
    c = SUM(Year2013) OVER (PARTITION BY BP_Code),
    r = ROW_NUMBER() OVER (PARTITION BY BP_Code ORDER BY Date DESC)
  FROM CallReport
)
SELECT BP_Code, BP_Name, Salesman, Street_Address, Year2011 = a, Year2012 = b, Year2013 = c, Total_Sold = s

  FROM x
  WHERE r = 1 and Salesman = 01;

然后我得到了我想要的输出：

 BP_Code    BP_Name Salesman    Street_Address  Year2011    Year2012    Year2013    Total_Sold
 A239   Buddy's 01  123 WASHINGTON 25468.38 16378.52    13747.93    106804.83

看看有什么问题吗？我有数百个BP_Codes，我只是缩小这个例子的A239。我需要每个BP_code只在一条线上，有每年的销售数据和总计。我知道有一种更简单的方法，但我对这场比赛还很陌生。有什么想法吗？这是在MSSQL 2008 R2上的。

Answer 1

如果我正确地理解了这一点，您正在试图修改视图以获得所需的输出，下面是它的代码

SELECT
t2.cardcode as 'BP_Code',
--t0.Recontact as 'Date',
t2.CardName as 'BP_Name',
SubString(T3.Name,1,2) as 'Salesman',
replace(T0.Street,',',' ') as 'Street_Address',
T0.City, 
T0.State, 
SUM(CASE WHEN YEAR(t0.recontact) = 2011 THEN convert(decimal(10,2),t0.U_sold) ELSE 0 END) AS 'Year2011',
SUM(CASE WHEN YEAR(t0.recontact) = 2012 THEN convert(decimal(10,2),t0.U_sold) ELSE 0 END) AS 'Year2012',
SUM(CASE WHEN YEAR(t0.recontact) = 2013 THEN convert(decimal(10,2),t0.U_sold) ELSE 0 END) AS 'Year2013',
convert(decimal(10,2),SUM(t0.U_sold)) as 'Total_Sold'
From 
OCLG t0 -- OCLG is Activities
inner join OCRD t2 -- OCRD is Customer Definitions
on T0.cardcode like t2.cardcode
inner join OCLS t3 -- OCLS is Activity Definitions
on T0.CntctSbjct=T3.Code 
where 
t0.U_sold > 0
and T0.CntctSbjct=T3.Code 
and T0.CardCode=T2.CardCode
and T0.CntctType='3' 
and t2.CardCode = 'a239' --This was added to simplify output
and T0.Recontact >= Convert(date, '2011-01-01' ) and T0.Recontact <= Convert(date,    '2013-12-31' )
group by t2.cardcode, t0.city, t0.state, replace(T0.Street,',',' '), t2.CardName, SubString(T3.Name,1,2)
order by t2.CardCode

我所做的只是更正您的groupby子句，您得到了由于t0而产生的多条记录。

如果在t0.city，t0.state，替换(T0.Street，‘')，t2.CardName，SubString(T3.Name,1,2)列中有多个值，则需要选择这些列的一个值，然后使用下面的代码

SELECT
t2.cardcode as 'BP_Code',
--t0.Recontact as 'Date',
max(t2.CardName) as 'BP_Name',
max(SubString(T3.Name,1,2)) as 'Salesman',
max(replace(T0.Street,',',' ')) as 'Street_Address',
max(T0.City) City, 
max(T0.State) State, 
SUM(CASE WHEN YEAR(t0.recontact) = 2011 THEN convert(decimal(10,2),t0.U_sold) ELSE 0 END) AS 'Year2011',
SUM(CASE WHEN YEAR(t0.recontact) = 2012 THEN convert(decimal(10,2),t0.U_sold) ELSE 0 END) AS 'Year2012',
SUM(CASE WHEN YEAR(t0.recontact) = 2013 THEN convert(decimal(10,2),t0.U_sold) ELSE 0 END) AS 'Year2013',
convert(decimal(10,2),SUM(t0.U_sold)) as 'Total_Sold'
From 
OCLG t0 -- OCLG is Activities
inner join OCRD t2 -- OCRD is Customer Definitions
on T0.cardcode like t2.cardcode
inner join OCLS t3 -- OCLS is Activity Definitions
on T0.CntctSbjct=T3.Code 
where 
t0.U_sold > 0
and T0.CntctSbjct=T3.Code 
and T0.CardCode=T2.CardCode
and T0.CntctType='3' 
and t2.CardCode = 'a239' --This was added to simplify output
and T0.Recontact >= Convert(date, '2011-01-01' ) and T0.Recontact <= Convert(date,    '2013-12-31' )
group by t2.cardcode
order by t2.CardCode