久久久久久久av_日韩在线中文_看一级毛片视频_日本精品二区_成人深夜福利视频_武道仙尊动漫在线观看

使用 t(n) 和多個交叉連接進行查詢

Query with t(n) and multiple cross joins(使用 t(n) 和多個交叉連接進行查詢)
本文介紹了使用 t(n) 和多個交叉連接進行查詢的處理方法,對大家解決問題具有一定的參考價值,需要的朋友們下面隨著小編來一起學習吧!

問題描述

我試圖從 Stackoverflow 線程理解這個查詢:

Am trying to understand this query from a Stackoverflow thread:

--create test table
CREATE TABLE dbo.TestTable(
      Col1 nchar(4000) NOT NULL
    , Col2 nvarchar(MAX) NOT NULL
    );

--load 10000 rows (about 2.8GB)
WITH 
    t4 AS (SELECT n FROM (VALUES(0),(0),(0),(0)) t(n))
    ,t256 AS (SELECT 0 AS n FROM t4 AS a CROSS JOIN t4 AS b CROSS JOIN t4 AS c CROSS JOIN t4 AS d)
    ,t16M AS (SELECT ROW_NUMBER() OVER (ORDER BY (a.n)) AS num FROM t256 AS a CROSS JOIN t256 AS b CROSS JOIN t256 AS c)
INSERT INTO dbo.TestTable WITH(TABLOCKX) (Col1, Col2)
SELECT REPLICATE(N'X', 4000), REPLICATE(CAST('X' AS nvarchar(MAX)), 10000)
FROM t16M
WHERE num <= 100000;
GO

--run query in loop (expect parallel execution plan with many read-ahead and LOB page reads)
SET NOCOUNT ON;
DECLARE @RowCount int, @Iteration int = 1;
WHILE @Iteration <= 100
BEGIN
    CHECKPOINT;
    DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
    SELECT @RowCount = COUNT(*) FROM dbo.TestTable WHERE Col2 LIKE 'X%';
    RAISERROR('Iteration %d completed',0,1,@Iteration) WITH NOWAIT; --display progress message
    SET @Iteration += 1;
END;
GO

  1. 我特別無法理解 t-sql 查詢的這一部分:

  1. I especially couldn't understand this portionof the t-sql query :

--load 10000 rows (about 2.8GB)
WITH 
    t4 AS (SELECT n FROM (VALUES(0),(0),(0),(0)) t(n))
    ,t256 AS (SELECT 0 AS n FROM t4 AS a CROSS JOIN t4 AS b CROSS JOIN t4 AS c CROSS JOIN t4 AS d)
    ,t16M AS (SELECT ROW_NUMBER() OVER (ORDER BY (a.n)) AS num FROM t256 AS a CROSS JOIN t256 AS b CROSS JOIN t256 AS c)
INSERT INTO dbo.TestTable WITH(TABLOCKX) (Col1, Col2)
SELECT REPLICATE(N'X', 4000), REPLICATE(CAST('X' AS nvarchar(MAX)), 10000)
FROM t16M
WHERE num <= 100000;
GO

  • 為什么我們在--load 10000rows..."下面有一個With";那個with"是做什么的?它是創建"語句的一部分嗎?

  • Why do we have a "With" below "--load 10000rows..." ; what does that "with" do? is it part of the 'create' statement?

    關于這個插入語句:

    INSERT INTO dbo.TestTable WITH(TABLOCKX) (Col1, Col2)
    SELECT REPLICATE(N'X', 4000), REPLICATE(CAST('X' AS nvarchar(MAX)), 10000)
    FROM t16M
    WHERE num <= 100000;
    

    不會只是

    INSERT INTO dbo.TestTable WITH(TABLOCKX) (Col1, Col2)
    FROM t16M
    WHERE num <= 100000;
    

    做什么?以上不會從 t16M 中取出前 10000 行并插入到 dbo.TestTable 中嗎?我們為什么要進行選擇..."?這意味著什么?我知道 REPLICATE(N'X', 4000) 會將 X 放入 Col1, REPLICATE(CAST('X' AS nvarchar(MAX)), 10000) 會將 X 放在 Col2 中 10000 次.如果我們這樣做,那為什么要從 t16M 中選擇?或者如果我們從 t16M 中選擇,那為什么要復制這個東西?

    do? Wouldn't the above take the first 10000 rows from t16M and insert into dbo.TestTable? Why are we doing that "select..."? What is the implication of that? I understand that REPLICATE(N'X', 4000) would put X 4000 times in Col1, REPLICATE(CAST('X' AS nvarchar(MAX)), 10000) would put X 10000 times in Col2. If we are doing this, then why select from t16M? or if we are selecting from t16M, then why do this replicate thing?

    有什么作用@RowCount = COUNT(*)做?將表中的行數分配給名為RowCount"的變量?它與'@@RowCount' 相同嗎?我認為不可能.

    what does @RowCount = COUNT(*) do? Assign num of rows in the table to a variable called 'RowCount'? Is it the same as '@@RowCount'? I don't think it can be.

    推薦答案

    我將重新格式化代碼以便更好地理解:

    I''ll reformat the code for better understanding:

    WITH t4 AS(
        SELECT n FROM (VALUES(0),(0),(0),(0)) t(n)
    )
    ,t256 AS(
        SELECT 0 AS n 
        FROM t4 AS a 
        CROSS JOIN t4 AS b 
        CROSS JOIN t4 AS c 
        CROSS JOIN t4 AS d
    )
    ,t16M AS(
        SELECT ROW_NUMBER() OVER (ORDER BY (a.n)) AS num 
        FROM t256 AS a 
        CROSS JOIN t256 AS b 
        CROSS JOIN t256 AS c
    )
    INSERT INTO dbo.TestTable WITH(TABLOCKX) (Col1, Col2)
    SELECT REPLICATE(N'X', 4000), REPLICATE(CAST('X' AS nvarchar(MAX)), 10000)
    FROM t16M
    WHERE num <= 100000;
    

    3 個級聯CTE組成一個Tally Table 或其他稱為Numbers Table.這是由從 1 到某個數字的連續數字組成.

    The 3 cascading CTEmade up a Tally Table or others called Numbers Table. This is composed of sequential numbers from 1 up to some number.

    此生成 4 行,值為 0:

    This one generates 4 rows with 0 value:

    WITH t4 AS(
        SELECT n FROM (VALUES(0),(0),(0),(0)) t(n)
    )
    

    然后它是CROSS JOIN 4次,從而生成4 * 4 * 4 * 4 or 4^4 or >256 行,因此別名 t256.再次 t256CROSS JOIN 連接到自身 3 次,產生 16,777,216 行,因此別名 t16M.如果您執行 SELECT * FROM t16M,您可以驗證它返回超過 16M 行.

    Then it is CROSS JOINed to itself 4 times thus generating 4 * 4 * 4 * 4 or 4^4 or 256 rows, thus the alias t256. Again t256 is CROSS JOINed to itself 3 times producing 16,777,216 rows thus the alias t16M. If you do a SELECT * FROM t16M, you can verify that it returns over 16M rows.

    這用于將 100000 行插入 TestTable,如 where 子句所示:

    This is used then to insert 100000 rows into the TestTable, as evidenced by the where clause:

    INSERT INTO dbo.TestTable WITH(TABLOCKX) (Col1, Col2)
    SELECT REPLICATE(N'X', 4000), REPLICATE(CAST('X' AS nvarchar(MAX)), 10000)
    FROM t16M
    WHERE num <= 100000
    

    有些人可能會使用 WHILE 循環來嘗試執行此操作,即將 100000 行插入表中.Tally Table 是一種以基于集合的方式執行此操作的好方法.有關詳細信息,請閱讀:http://www.sqlservercentral.com/articles/T-SQL/62867/

    Some may use a WHILE loop in attempt to do this, that is insert 100000 rows into a table. The Tally Table is a great way to do this in a set-based fashion. For more info, read this: http://www.sqlservercentral.com/articles/T-SQL/62867/

    問題 2CREATE 語句下方的 WITH 關鍵字標記了 公用表表達式.

    Question #2 The WITH keyword below the CREATE statement marks the declaration of a Common Table Expression.

    問題 #3我相信下面的查詢會產生語法錯誤.

    Question #3 I believe the query below will produce a syntax error.

    INSERT INTO dbo.TestTable WITH(TABLOCKX) (Col1, Col2)
    FROM t16M
    WHERE num <= 100000;
    

    另一方面,這不會.

    INSERT INTO dbo.TestTable WITH(TABLOCKX) (Col1, Col2)
    SELECT REPLICATE(N'X', 4000), REPLICATE(CAST('X' AS nvarchar(MAX)), 10000)
    FROM t16M
    WHERE num <= 100000;
    

    這個查詢的作用是INSERT 100000 行,由 2 列組成,其值是連續的 'X' 字符串.請記住,t16M 是我們的 Tally Table,它由從 1 到 16M+ 的數字序列組成.我們不使用Tally Table 的值進行插入,我們只使用其行的存在來限制插入次數.

    What this query does is INSERT 100000 rows, composed of 2 columns, whose values are continuous strings of 'X's. Remember that t16M is our Tally Table which consists of sequence of numbers from 1 up to 16M+. We do not use the values of the Tally Table for the insert, we only use the presence of its rows to limit the number of inserts.

    問題 #4 當您說 @RowCount = COUNT(*) 將行數分配給變量時,您說得對.

    Question #4 You're right when you said @RowCount = COUNT(*) assigns the number of rows to the variable.

    SELECT @RowCount = COUNT(*) FROM dbo.TestTable WHERE Col2 LIKE 'X%';
    SELECT @@ROWCOUNT
    

    然而,上述說法并不相同.@@ROWCOUNT 返回受最后一條語句影響的行數.如果我們將它放在 SELECT @RowCount 之后的 WHILE 循環中,它將返回 1,因為只有 1 行受到影響.但是,如果我們直接把它放在INSERT 語句之后,它會返回與SELECT COUNT(*) FROM dbo.TestTable 一樣的.

    However, the above statements are not the same. @@ROWCOUNT returns the number of rows affected by the last statement. If we put it inside the WHILE loop right after the SELECT @RowCount, it will return 1, as only 1 row is affected. However, if we put it directly after the INSERT statement, it will return the same as SELECT COUNT(*) FROM dbo.TestTable.

    這篇關于使用 t(n) 和多個交叉連接進行查詢的文章就介紹到這了,希望我們推薦的答案對大家有所幫助,也希望大家多多支持html5模板網!

    【網站聲明】本站部分內容來源于互聯網,旨在幫助大家更快的解決問題,如果有圖片或者內容侵犯了您的權益,請聯系我們刪除處理,感謝您的支持!
  • 相關文檔推薦

    Converting Every Child Tags in to a Single Column with multiple Delimiters -SQL Server (3)(將每個子標記轉換為具有多個分隔符的單列-SQL Server (3))
    How can I create a view from more than one table?(如何從多個表創建視圖?)
    Create calculated value based on calculated value inside previous row(根據前一行內的計算值創建計算值)
    How do I stack the first two columns of a table into a single column, but also pair third column with the first column only?(如何將表格的前兩列堆疊成一列,但也僅將第三列與第一列配對?) - IT屋-程序員軟件開發技
    Recursive t-sql query(遞歸 t-sql 查詢)
    Convert Month Name to Date / Month Number (Combinations of Questions amp; Answers)(將月份名稱轉換為日期/月份編號(問題和答案的組合))
    主站蜘蛛池模板: 欧美一区二区三区的 | 久久久.com| 日韩中文字幕一区 | 综合久久99| 在线一级片 | 午夜tv免费观看 | 麻豆久久久久久 | 久久久精品一区二区三区 | 国产精品亚洲综合 | 欧美福利| 成人免费在线视频 | 欧美区在线观看 | 91免费在线播放 | 97精品一区二区 | 日韩字幕一区 | 亚洲天堂免费 | 五月花丁香婷婷 | a毛片 | 日韩第一区| 免费在线成人网 | 一区二区三区影院 | 91丨国产| av香蕉 | 日韩一区二区在线视频 | 日韩福利在线 | 激情网站在线观看 | 在线播放中文字幕 | 色视频免费 | 午夜成人免费视频 | 国产三级大片 | 高清视频一区二区三区 | 国产专区在线 | 久草网在线视频 | 欧美一区2区三区4区公司二百 | 久久久久成人精品免费播放动漫 | 91九色在线观看 | 国产美女视频一区 | 日韩一级精品视频在线观看 | 在线观看国产精品一区二区 | 国产精品一二区 | 人人干人人玩 |