求救,帮忙看看这个sql为什么不能用

[winterind]

SELECT count(*) FROM
(SELECT TOP 20 * FROM (SELECT TOP 20 * FROM (SELECT * FROM Pcms_hr_employee,pcms_hr_post WHERE employee_post *= post_code ) AS t0 ORDER BY employee_id ASC) AS t1 ORDER BY employee_id DESC ) AS t2 ORDER BY employee_id

报错是 Query contains an outer-join request that is not permitted.

我要怎么改才可以呢,如果不用那个左连接就是好的

[sdhdy]

SELECT count(*) FROM
(SELECT TOP 20 * FROM (SELECT TOP 20 * FROM (SELECT * FROM Pcms_hr_employee left join pcms_hr_post on Pcms_hr_employee.employee_post = pcms_hr_post.post_code ) AS t0 ORDER BY employee_id ASC) AS t1 ORDER BY employee_id DESC ) AS t2 ORDER BY employee_id

[lsxaa]

直接知道 结果20

不用写语句了吧???

[zjcxc]

--用标准的语法吧

SELECT count(*) FROM (
SELECT TOP 20 * FROM (
SELECT TOP 20 * FROM (
SELECT * FROM
Pcms_hr_employee
LEFT JOIN pcms_hr_post ON employee_post=post_code
) AS t0 ORDER BY employee_id ASC
) AS t1 ORDER BY employee_id DESC
) AS t2 ORDER BY employee_id

[lsxaa]

感觉这样写没什么意义 ...

[zjcxc]

SQL对 *=/=*这类的支持基本上放弃了,而且用这类语法可能会产生错误

这是联机帮助上的说明


在早期的 Microsoft® SQL Server™ 2000 版本中，使用 *= 和 =* 在 WHERE 子句中指定左、右外部联接条件。有时，该语法会导致有多种解释的不明确查询。FROM 子句中指定遵从 SQL-92 的外部联接，不会导致上述不确定性。因为 SQL-92 语法更为精确，所以，本版中未包括有关在 WHERE 子句中使用旧的 Transact-SQL 外部联接语法的详细信息。以后的 SQL Server 版本可能不再支持该语法。任何使用 Transact-SQL 外部联接的语句都应改为使用 SQL-92 语法。

SQL-92 标准支持 FROM 或 WHERE 子句中的内部联接规范。WHERE 子句中指定的内部联接不会出现与 Transact-SQL 外部联接语法相同的不确定性问题。

[winterind]

SELECT count(*) FROM (
SELECT TOP 20 * FROM (
SELECT TOP 20 * FROM (
SELECT * FROM
Pcms_hr_employee
LEFT JOIN pcms_hr_post ON employee_post=post_code
) AS t0 ORDER BY employee_id ASC
) AS t1 ORDER BY employee_id DESC
) AS t2 ORDER BY employee_id

还是不行,:(,报错信息:

Column name 't2.employee_id' is invalid in the ORDER BY clause because it is not contained in an aggregate function and there is no GROUP BY clause.

[WangZWang]

SELECT count(*) FROM
(SELECT TOP 20 * FROM (SELECT TOP 20 * FROM (SELECT * FROM Pcms_hr_employee,pcms_hr_post WHERE employee_post *= post_code ) AS t0 ORDER BY employee_id ASC) AS t1 ORDER BY employee_id DESC ) AS t2

把最后的order by 去掉.

[winterind]

本来不是SELECT count(*),用的 SELECT * ,不过出错是一样的

[zjcxc]

测试清楚,select * from 应该是没有问题的

select count(*) 的话,其他字段必须放在group by 中

[davorsuker39]

SELECT count(*) FROM
(SELECT TOP 20 * FROM (SELECT TOP 20 * FROM (SELECT * FROM Pcms_hr_employee left join pcms_hr_post on Pcms_hr_employee.employee_post = pcms_hr_post.post_code ) AS t0 ORDER BY employee_id ASC) AS t1 ORDER BY employee_id DESC ) AS t2

[winterind]

to WangZWang(阿来)
去掉最后的order by 也没用,结果还是一样的

[zjcxc]

把你现在在执行的完整语句写出来,表结构也写出来做测试(完全没可能嘛)

[zjcxc]

我估计你是用了普通字段,又用了聚合函数,即没有写 group by

[winterind]

完成,谢谢zjcxc(邹建),谢谢各位!!!:)