Question

关于sql server查询的where不识别列名的问题

我写了如下查询语句：
SELECT [Code],COUNT( [Code]) as 'sl'
FROM [AB].[dbo].[QXZL]
where sl < 1000
group by code
当where中引用了我自定义的列名sl后就会出错，如果是where code = 'AA'就能通过查询，请问我如何才能筛选出数量小于1000的行？

Answer 1

因为sql是在where后order by前加别名，即生成结果集后加别名，where是在生成结果集前的操作，order by是生成结果集后的操作，因为where要生成结果集，而order by是对结果集的操作。如果非要用别名，那么只能用派生表，即先生成别名再where
select * from
(
select 字段1 as A,字段2 as B.... from tablename
) aaa
where A=1
order by A

Answer 2

二楼的做法是对的，或者外层嵌套一层
select * from(
SELECT [Code],COUNT( [Code]) as 'sl'
FROM [AB].[dbo].[QXZL]
where sl < 1000
group by code ) t where t.sl<1000

Answer 3

SELECT [Code],COUNT( [Code]) as 'sl'
FROM [AB].[dbo].[QXZL]
where sl < 1000
group by code
having COUNT( [Code]) <1000

Answer 4

你这语句有问题吧你这个count（【code】）表达式有问题