以下代码如何在T-SQL中读取和查看? [英] How does the following code read and look in T-SQL?
问题描述
The code originates from the following post:
我正在处理大量数据，并且此代码(在MS-ACCESS JET SQL中)当前需要大约10-15分钟的查询时间(不崩溃时).我想提高查询的运行速度，这就是为什么我希望利用直通查询方法.当前，我使用的后端数据库是SQL Server(2012)，我使用ODBC连接来链接到该数据库.
I am working with a large amount of data, and this code (in MS-ACCESS JET SQL) currently takes about 10-15 minutes to query (when it doesn't crash). I want to increase the speed in which my query runs, which is why I am looking to utilize the pass through query approach. Currently, the back end database I use is SQL Server (2012), which I use an ODBC connection to link to.
我的印象是，使用后端数据库的语言编写SQL可以使直通查询的执行效率更高.我相信T-SQL是这种语言.我已经完成了研究，并且确实没有"JET-SQL到T-SQL转换器".因此，请问是否有人对以下代码在T-SQL中的读取和显示方式有任何建议.
I am under the impression that writing your SQL in the back end database's language allows the pass through query to perform more efficiently. I believe that T-SQL would be the language. I have done the research, and there really is no "JET-SQL to T-SQL converter". Therefore, I kindly ask if anyone has any suggestions on how the following code reads and looks in T-SQL.
TRANSFORM Nz(First(CountOfStudents),0) AS n
SELECT
YearNumber,
MonthNumber,
School
FROM
(
SELECT
YearNumber,
MonthNumber,
School,
[School Service Type],
COUNT(*) AS CountOfStudents
FROM
(
SELECT DISTINCT
mtr.YearNumber,
mtr.MonthNumber,
pym.[Student ID],
pym.School,
pym.[School Service Type]
FROM
MonthsToReport AS mtr
INNER JOIN
PaymentsYearMonth AS pym
ON mtr.YYYYMM>=pym.StartYYYYMM
AND mtr.YYYYMM<=pym.EndYYYYMM
) AS distinctQuery
GROUP BY
YearNumber,
MonthNumber,
School,
[School Service Type]
) AS countQuery
GROUP BY
YearNumber,
MonthNumber,
School
PIVOT [School Service Type]
一如既往，感谢您的宝贵时间.
As always, thank you for your time.
推荐答案
将Access SQL交叉表查询转换为T-SQL只是过程的一部分.为了将所有处理推到SQL Server上，我们还需要确保所有依赖项也都在SQL Server上.(例如，在SQL Server上运行的T-SQL查询将无法从Access中保存的查询中提取数据.)
Converting the Access SQL crosstab query to T-SQL is only part of the process. In order to push all of the processing onto the SQL Server we also need to make sure that all of the dependencies are on the SQL Server, too. (For example, a T-SQL query running on the SQL Server won't be able to pull data from a saved query in Access.)
按照上一个问题中的步骤进行操作，我们在SQL Server上有一个名为[Payments]的表:
Following the steps from the previous question, we have our table named [Payments] on the SQL Server:
Payment Row Student ID School School Service Type PaymentStartDate PaymentEndDate
----------- ---------- ------ ------------------- ---------------- --------------
1 001 ABC ED 2010-01-02 2012-02-04
2 001 ABC ED 2010-01-02 2010-01-05
3 001 ABC ED 2010-04-02 2010-05-05
4 001 DEF EZ 2010-01-02 2012-02-04
5 001 RR 2012-02-02 2012-02-03
6 002 ABC ED 2010-02-02 2011-02-03
7 002 ABC EZ 2010-02-02 2010-06-03
8 002 GHI ED 2011-02-04 2012-02-04
9 003 ABC ED 2011-02-02 2012-02-03
10 003 DEF ED 2010-01-02 2010-08-03
11 003 RR 2011-02-02 2011-02-03
12 004 RR 2011-02-02 2011-02-03
13 005 GHI ED 2010-08-02 2011-02-04
14 006 GHI ED 2010-08-02 2010-08-02
我们在SQL Server中创建[PaymentsYearMonth]视图
We create the [PaymentsYearMonth] view in SQL Server
CREATE VIEW PaymentsYearMonth AS
SELECT
[Student ID],
School,
[School Service Type],
(Year(PaymentStartDate) * 100) + Month(PaymentStartDate) AS StartYYYYMM,
(Year(PaymentEndDate) * 100) + Month(PaymentEndDate) AS EndYYYYMM
FROM Payments
SQL Server还需要具有我们的[MonthNumbers]表的副本
The SQL Server also needs to have copies of our [MonthNumbers] table
MonthNumber
-----------
1
2
3
4
5
6
7
8
9
10
11
12
和我们的[YearNumbers]表
and our [YearNumbers] table
YearNumber
----------
2009
2010
2011
2012
2013
因此，我们现在可以创建[MonthsToReport]视图.T-SQL没有 DMin()和 DMax()函数，因此我们需要稍微更改查询
So now we can create the [MonthsToReport] view. T-SQL doesn't have DMin() and DMax() functions, so we need to change the query slightly
CREATE VIEW MonthsToReport AS
SELECT
yn.YearNumber,
mn.MonthNumber,
(yn.YearNumber * 100) + mn.MonthNumber AS YYYYMM
FROM
YearNumbers AS yn,
MonthNumbers AS mn
WHERE ((yn.YearNumber * 100) + mn.MonthNumber)>=(SELECT MIN(StartYYYYMM) FROM PaymentsYearMonth)
AND ((yn.YearNumber * 100) + mn.MonthNumber)<=(SELECT MAX(EndYYYYMM) FROM PaymentsYearMonth)
用于计算不同行的SQL查询是完全相同的，因此让我们为其创建一个视图，以便在下一步执行PIVOT时更容易了解正在发生的事情
The SQL query to count the distinct rows is exactly the same, so let's create a view for that so when we do the PIVOT in the next step it will be easier to see what's going on
CREATE VIEW DistinctCountsByMonth AS
SELECT
YearNumber,
MonthNumber,
School,
[School Service Type],
COUNT(*) AS CountOfStudents
FROM
(
SELECT DISTINCT
mtr.YearNumber,
mtr.MonthNumber,
pym.[Student ID],
pym.School,
pym.[School Service Type]
FROM
MonthsToReport AS mtr
INNER JOIN
PaymentsYearMonth AS pym
ON mtr.YYYYMM>=pym.StartYYYYMM
AND mtr.YYYYMM<=pym.EndYYYYMM
) AS distinctQuery
GROUP BY
YearNumber,
MonthNumber,
School,
[School Service Type]
现在，如果我们在Access中进行交叉表查询，那就简单了
Now, if we were doing a crosstab query in Access it would simply be
TRANSFORM First(CountOfStudents) AS n
SELECT YearNumber, MonthNumber, School
FROM DistinctCountsByMonth
GROUP BY YearNumber, MonthNumber, School
PIVOT [School Service Type]
，但是T-SQL中的PIVOT子句要求我们为其提供列名的实际列表(与Access相对，后者可以自动生成列名).因此，我们将在SQL Server上创建一个存储过程，该存储过程建立列名列表，构造SQL语句并执行它:
but the PIVOT clause in T-SQL requires that we give it the actual list of column names (as opposed to Access, which can generate the column names automatically). So we'll create a stored procedure on the SQL Server that builds the list of column names, constructs the SQL statement, and executes it:
CREATE PROCEDURE DistinctPaymentsCrosstab
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@ColumnList AS NVARCHAR(MAX),
@SQL AS NVARCHAR(MAX)
-- build the list of column names based on the current contents of the table
-- e.g., '[ED],[EZ],[RR]'
-- required by PIVOT ... IN below
-- ref: https://stackoverflow.com/a/14797796/2144390
SET @ColumnList =
STUFF(
(
SELECT DISTINCT ',' + QUOTENAME([School Service Type])
FROM [DistinctCountsByMonth]
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'),
1,
1,
'')
SET @SQL = '
WITH rollup
AS
(
SELECT
[School Service Type],
YearNumber,
MonthNumber,
School,
SUM(CountOfStudents) AS n
FROM [DistinctCountsByMonth]
GROUP BY
[School Service Type],
YearNumber,
MonthNumber,
School
)
SELECT * FROM rollup
PIVOT (SUM([n]) FOR [School Service Type] IN (' + @ColumnList + ')) AS Results'
EXECUTE(@SQL)
END
现在，我们可以使用传递查询从Access中调用该存储过程
Now we can call that stored procedure from Access by using a pass-through query
返回
这篇关于以下代码如何在T-SQL中读取和查看?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
