SQL Server Compact Edition 中的子查询 [英] Subquery in SQL Server Compact Edition

问题描述

我试图在 SQL Server CE 数据库中执行此操作，但数据库引擎不断报告错误.

I'm trying to do this in a SQL Server CE database, but the database engine keeps reporting errors.

SELECT  C.guid, C.name, C.updated, 
        C.hddsize, C.hddavailable, C.hddfree, 
        C.ramsize, C.profiles, C.cpu, 
        (SELECT COUNT(D.id) AS numprogs 
            FROM ComputerData AS D 
            WHERE D.computer_id = C.id) AS numprograms 
FROM Computers AS C;

有人告诉我 SQL Server CE 支持子查询.我做错了什么吗?

I've been told SQL Server CE supports subqueries. Is there something I'm doing wrong?

推荐答案

我在查询方面的唯一经验是使用 MySQL，但希望它足够相似.

My only experiences in queries are with MySQL, but hopefully it is similar enough.

您的查询在我看来很奇怪，因为您的子查询在 SELECT 子句中.我以前从未见过……但显然 MySQL 支持它.通常子查询出现在 FROM 或 LEFT JOIN 或 JOIN 之后.

Your query looks strange to me because your subquery is in the SELECT clause. I have never seen that before... but apparently it is supported in MySQL. Usually the subquery comes in the after a FROM or LEFT JOIN or JOIN.

您的示例非常简单，您可以使用 LEFT JOIN 来实现它:

Your example is simple enough that you could implement it with a LEFT JOIN:

SELECT C.guid, ..., COUNT(distinct D.id) as numprogs
FROM Computers AS C
LEFT JOIN ComputerData as D ON D.computer_id = C.id

在这种情况下，LEFT JOIN 是要使用的正确连接类型，因为即使 D 表中没有与特定 C 记录匹配的记录，您的结果集仍将包含该 C 记录，而 numprogs 将为零，如您所料.

In this case, LEFT JOIN is the correct type of join to use because even if there is no matching record in the D table for a particular C record, your result set will still contain that C record and numprogs will just be zero, as you would expect.

如果你真的想使用子查询，试试这个:

If you really want to use a subquery, try this:

SELECT C.guid, ..., S.numprogs
FROM Computers AS C
LEFT JOIN
(SELECT computer_id, COUNT(*) as numprogs
 FROM ComputerData GROUP BY computer_id) AS S
ON C.id=S.computer_id

我建议简化您的查询，使其成为应该有效但无效的最简单的查询.然后告诉我们您的数据库引擎返回的具体错误信息.

I suggest simplifying your query to get it to be the simplest possible query that should work, but doesn't work. Then tell us the specific error message that your database engine is returning.

我查看了 关于子查询的 MySQL 章节 似乎您应该尝试在子查询之后删除as numprograms"子句……也许在您已经编写了子查询之后，您对来自子查询的列的命名没有任何选择.

I loooked in the MySQL chapter about subqueries and it seems like you should try removing the "as numprograms" clause after your subquery... maybe you don't get any choice about the naming of the column that comes out of the subquery after you've already composed the subquery.

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