有关SQL Server Compact 3.5使用CTE [英] Using CTE on SQL Server Compact 3.5

问题描述

这是我的计算器上的第一篇文章，我希望其中之一

This is my first post on stackoverflow, I hope one of many!

我的问题是这样的：我使用CTE在查询中检测并删除重复表中的记录。该查询在SQL Server 2005/2008年仅罚款，但在紧凑它抛出一个异常：

My question is this: I'm using CTE in a query to detect and remove duplicate records in a table. This query works just fine in SQL Server 2005 / 2008, but in Compact it throws an exception:

有一个错误解析查询。
[令牌行号= 1，令牌线
偏移量= 1，令牌错误=带]

There was an error parsing the query. [ Token line number = 1,Token line offset = 1,Token in error = WITH ]

这是我的查询：

SqlCeConnection con = new SqlCeConnection(ConfigurationManager.ConnectionStrings["ADSLConnectionString"].ConnectionString);
                SqlCeCommand command = new SqlCeCommand();

                command.Connection = con;
                command.CommandType = CommandType.Text;

                command.CommandText = "WITH Dublicates_CTE(Username, accountid)" +
                                      " AS" +
                                      " (" +
                                      "     SELECT UserName,min(accountid)" +
                                      "     FROM Accounts" +
                                      "     GROUP BY username" +
                                      "     HAVING Count(*) > 1" +
                                      " )" +
                                      "     DELETE FROM Accounts" +
                                      "     WHERE accountid IN (" +
                                      "         SELECT Accounts.accountid" +
                                      "         FROM Accounts" +
                                      "         INNER JOIN Dublicates_CTE" +
                                      "         ON Accounts.Username = Dublicates_CTE.Username" +
                                      "         AND Accounts.accountid <> Dublicates_CTE.accountid" +
                                      " ) ";

                con.Open();

                command.ExecuteNonQuery();

我缺少的东西，还是CTE不能在SQL Server精简工作？

Am I missing something, or does CTE not work on SQL Server Compact?

推荐答案

您可能只窝查询，像这样（可能有一些语法问题）：

You can probably just nest the query, something like this (may have some syntax problems):

DELETE FROM Accounts
WHERE accountid IN (
    SELECT Accounts.accountid
    FROM Accounts
    INNER JOIN (
        SELECT UserName,min(accountid) accountid
        FROM Accounts
        GROUP BY username
        HAVING Count(*) > 1
    ) Dublicates_CTE
    ON Accounts.Username = Dublicates_CTE.Username
    AND Accounts.accountid <> Dublicates_CTE.accountid
)

这篇关于有关SQL Server Compact 3.5使用CTE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！