如何将访问查询转换为MS SQL

Question

我正在尝试在MS SQL中运行访问查询，但我不知道该怎么做，但我已经研究了，但找不到任何解决方案。

这是访问查询(在访问中工作正常(：

更新的代码：

SELECT DISTINCT b.Category, b.SubCategory1, b.SubCategory2, b.SubCategory3, 
b.SubCategory4, 'Total '+ b.SubCategory3+' FY 2018' AS Title, 
Sum(b.TotalAmount) AS Budget,
0 AS [201801],
0 AS [201802],
0 AS [201803],
0 AS [201804],
0 AS [201805],
0 AS [201806],
0 AS [201807],
0 AS [201808],
0 AS [201809],
0 AS [201810],
0 AS [201811],
0 AS [201812],
0 AS TotalSpent,
0 AS TotalCommited,
0 AS UncommitedBudget
FROM tblBudget AS b
GROUP BY b.Category, b.SubCategory1, b.SubCategory2, b.SubCategory3, 
b.SubCategory4
UNION ALL SELECT DISTINCT b.Category, b.SubCategory1, b.SubCategory2, 
b.SubCategory3, b.SubCategory4, IIF((po.PONumber), 'Forecast - ' + po.Title, 
po.PONumber + ' - ' + po.Title + ' ('+ po.ProjectStatus +')' ), 0 AS Budget, 
(select sum(tb.InvoiceAmount) from tblInvoices AS tb WHERE 
Month(tb.AcctPeriod)=1 AND tb.PONumber=po.PONumber) AS [201801], 
(select sum(tb.InvoiceAmount) from tblInvoices AS tb WHERE 
Month(tb.AcctPeriod)=2 AND tb.PONumber=po.PONumber) AS [201802], 
(select sum(tb.InvoiceAmount) from tblInvoices AS tb WHERE 
Month(tb.AcctPeriod)=3 AND tb.PONumber=po.PONumber) AS [201803],
(select sum(tb.InvoiceAmount) from tblInvoices AS tb WHERE 
Month(tb.AcctPeriod)=4 AND tb.PONumber=po.PONumber) AS [201804],
(select sum(tb.InvoiceAmount) from tblInvoices AS tb WHERE 
Month(tb.AcctPeriod)=5 AND tb.PONumber=po.PONumber) AS [201805], 
(select sum(tb.InvoiceAmount) from tblInvoices AS tb WHERE 
Month(tb.AcctPeriod)=6 AND tb.PONumber=po.PONumber) AS [201806],
(select sum(tb.InvoiceAmount) from tblInvoices AS tb WHERE 
Month(tb.AcctPeriod)=7 AND tb.PONumber=po.PONumber) AS [201807],
(select sum(tb.InvoiceAmount) from tblInvoices AS tb WHERE 
Month(tb.AcctPeriod)=8 AND tb.PONumber=po.PONumber) AS [201808],
(select sum(tb.InvoiceAmount) from tblInvoices AS tb WHERE 
Month(tb.AcctPeriod)=9 AND tb.PONumber=po.PONumber) AS [201809],
(select sum(tb.InvoiceAmount) from tblInvoices AS tb WHERE 
Month(tb.AcctPeriod)=10 AND tb.PONumber=po.PONumber) AS [201810],
(select sum(tb.InvoiceAmount) from tblInvoices AS tb WHERE 
Month(tb.AcctPeriod)=11 AND tb.PONumber=po.PONumber) AS [201811],
(select sum(tb.InvoiceAmount) from tblInvoices AS tb WHERE 
Month(tb.AcctPeriod)=12 AND tb.PONumber=po.PONumber) AS [201812], 
(select sum(tb.InvoiceAmount) from tblInvoices AS tb WHERE 
year(tb.AcctPeriod)=2018 AND tb.PONumber=po.PONumber) AS TotalSpent,
SUM(TotalOrdered) - IIF(((SELECT Sum(tb.InvoiceAmount) FROM tblInvoices AS 
tb WHERE year(tb.AcctPeriod)=2018 AND tb.PONumber=po.PONumber)),0,(SELECT 
Sum(tb.InvoiceAmount) FROM tblInvoices AS tb WHERE year(tb.AcctPeriod)=2018 
AND tb.PONumber=po.PONumber))-
IIF(po.ProjectStatus='Closed',
SUM(TotalOrdered) - IIF(((SELECT Sum(tb.InvoiceAmount) FROM tblInvoices AS 
tb WHERE year(tb.AcctPeriod)=2018 AND tb.PONumber=po.PONumber)),0,(SELECT 
Sum(tb.InvoiceAmount) FROM tblInvoices AS tb WHERE year(tb.AcctPeriod)=2018 
AND tb.PONumber=po.PONumber))
,0)  AS TotalCommited,
0+
IIF(po.ProjectStatus='Closed',
SUM(TotalOrdered) - IIF(((SELECT Sum(tb.InvoiceAmount) FROM tblInvoices AS 
tb WHERE year(tb.AcctPeriod)=2018 AND tb.PONumber=po.PONumber)),0,(SELECT 
Sum(tb.InvoiceAmount) FROM tblInvoices AS tb WHERE year(tb.AcctPeriod)=2018 
AND tb.PONumber=po.PONumber))
,0) AS UncommitedBudget
FROM (tblBudget AS b LEFT JOIN tblPO AS po ON b.id = po.BudgetCategory) 
GROUP BY b.Category, b.SubCategory1, b.SubCategory2, b.SubCategory3, 
b.SubCategory4, po.Title, po.PONumber, po.ProjectStatus
UNION ALL SELECT DISTINCT b.Category, b.SubCategory1, b.SubCategory2, 
b.SubCategory3, b.SubCategory4, 'Total '+b.SubCategory3+' FY 2018' AS Title,
0 AS Budget,
0 AS [201801],
0 AS [201802],
0 AS [201803],
0 AS [201804],
0 AS [201805],
0 AS [201806],
0 AS [201807],
0 AS [201808],
0 AS [201809],
0 AS [201810],
0 AS [201811],
0 AS [201812],
0 AS TotalSp,
0 AS TotalCommited,
Sum(b.TotalAmount) - IIF((SELECT Sum(p.TotalOrdered) FROM tblPO AS p WHERE 
p.BudgetCategory=b.id),0,(SELECT Sum(p.TotalOrdered) FROM tblPO AS p WHERE 
p.BudgetCategory=b.id)) AS UncommitedBudget
FROM tblBudget AS b
GROUP BY b.Category, b.SubCategory1, b.SubCategory2, b.SubCategory3, 
b.SubCategory4,b.id;

MS SQL Server中的错误：

更新错误：

Msg 4145, Level 15, State 1, Line 21
An expression of non-boolean type specified in a context where a condition 
is expected, near '('.
Msg 156, Level 15, State 1, Line 22
Incorrect syntax near the keyword 'AS'.
Msg 156, Level 15, State 1, Line 23
Incorrect syntax near the keyword 'AS'.
Msg 156, Level 15, State 1, Line 24
Incorrect syntax near the keyword 'AS'.
Msg 156, Level 15, State 1, Line 25
Incorrect syntax near the keyword 'AS'.
Msg 156, Level 15, State 1, Line 26
Incorrect syntax near the keyword 'AS'.
Msg 156, Level 15, State 1, Line 27
Incorrect syntax near the keyword 'AS'.
Msg 156, Level 15, State 1, Line 28
Incorrect syntax near the keyword 'AS'.
Msg 156, Level 15, State 1, Line 29
Incorrect syntax near the keyword 'AS'.
Msg 156, Level 15, State 1, Line 30
Incorrect syntax near the keyword 'AS'.
Msg 156, Level 15, State 1, Line 31
Incorrect syntax near the keyword 'AS'.
Msg 156, Level 15, State 1, Line 32
Incorrect syntax near the keyword 'AS'.
Msg 156, Level 15, State 1, Line 33
Incorrect syntax near the keyword 'AS'.
Msg 156, Level 15, State 1, Line 34
Incorrect syntax near the keyword 'AS'.
Msg 102, Level 15, State 1, Line 35
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Line 35
Incorrect syntax near ')'.
Msg 102, Level 15, State 1, Line 37
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Line 37
Incorrect syntax near ')'.
Msg 102, Level 15, State 1, Line 41
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Line 41
Incorrect syntax near ')'.
Msg 4145, Level 15, State 1, Line 62
An expression of non-boolean type specified in a context where a condition 
is expected, near '('.

我在SQL Server中运行相同的查询，但我无法弄清楚这个问题，请帮助我。

任何帮助对我来说都是很好的。

Answer 1

1。

" 201801"附近的不正确语法。

列名不能以数字开头：https：//learn.microsoft.com/en-us/sql/sql/relational-databases/databases/database-nendifiers/database-indentifiers将其放在 [2018]

之类的方括号中

2。

isnull函数需要2个参数。

ISNULL IIF内部被打破。您没有完成迁移到他们中的任何一个。应该是这个(服务器的额外努力，不受欢迎(：

IIF(
  (SELECT Sum(p.TotalOrdered) FROM tblPO AS p WHERE p.BudgetCategory=b.id),
  0,
  (SELECT Sum(p.TotalOrdered) FROM tblPO AS p WHERE p.BudgetCategory=b.id)
) AS UncommitedBudget

或以下：

ISNULL((SELECT Sum(p.TotalOrdered) FROM tblPO AS p WHERE p.BudgetCategory=b.id), 0)

3。 DISTINCT之后的CC_4是荒谬的。它对输出没有影响，但仍需要服务器进行分类。或者您的逻辑上错误的GROUP BY子句。

4。在语句的中间，您不能拥有半 ;。请参阅每个UNION ALL。

5。看看通常如何进行枢纽：

SELECT p.[201801], p.[201802], p.[201803]
FROM 
( 
    SELECT CONVERT(VARCHAR(6), i.period, 112) period_name, i.amount
    FROM tblInvoices i
    WHERE i.period >= '20180101' and i.period < '20190101'
) i
pivot (
  sum(i.amount) 
  for i.period_name in (
    [201801], [201802], [201803]
  )
)p

http://sqlfiddle.com/#！18/ea5c5/6

避免这种情况：

...
(select sum(tb.InvoiceAmount) from tblInvoices AS tb WHERE 
Month(tb.AcctPeriod)=1 AND tb.PONumber=po.PONumber) AS 201801, 
(select sum(tb.InvoiceAmount) from tblInvoices AS tb WHERE 
Month(tb.AcctPeriod)=2 AND tb.PONumber=po.PONumber) AS 201802,
...

您的方法使服务器工作得更多。