一个很基础的查询时的别名问题，求老鸟帮忙了！

[aojunpeng313001]

SELECT (CASE Code WHEN 1 THEN 'A' ELSE 'B' END) AS Name, (SELECT Count(*) FROM A2 WHERE ID = A1.ID) AS Code FROM A1

如上所示，我要做一个类似上面形式的查询，前面的字段要用到后面字段的内容,，而字段的顺序不能改变，上面的东西怎么能实现呢？上面的写法会提示Code错误的，小鸟很菜，求老鸟们帮忙了，先拜大家一个了。

[churchatp1]

SELECT case ( SELECT Count(*) FROM A2 WHERE ID = A1.ID) WHEN 1 THEN 'A' ELSE 'B' END) AS Name, (SELECT Count(*) FROM A2 WHERE ID = A1.ID) AS Code FROM A1

[churchatp1]

SELECT (case ( SELECT Count(*) FROM A2 WHERE ID = A1.ID) WHEN 1 THEN 'A' ELSE 'B' END) AS Name, (SELECT Count(*) FROM A2 WHERE ID = A1.ID) AS Code FROM A1

[aojunpeng313001]

。。。楼上的方法我知道，不过我的表里有40多的字段，并且查询表做字段的有30多个，如果用上面的方法有点让SQL文太长了，没有别的好方法吗?

[vivianfdlpw]

SELECT (CASE Code WHEN 1 THEN 'A' ELSE 'B' END) AS Name
,Code
FROM (
SELECT ID
,(SELECT Count(*) FROM A2 WHERE ID = A1.ID) 'Code'
FROM A1
)t

[子陌红尘]

select
Name = (CASE t.Code WHEN 1 THEN 'A' ELSE 'B' END)
from
A1
inner join
(SELECT Code=Count(*) FROM A2 group by ID) t
on
A1.ID = t.ID

[churchatp1]

长一点没有关系啊
你首先要看你的需求啊

[wgsasd311]

select (CASE count(*) WHEN 1 THEN 'A' ELSE 'B' END) AS Name,count(*) as code
from A1 a left join A2 b on a.id=b.id
group by a.id

[lisiyong]

SELECT (CASE Code WHEN 1 THEN 'A' ELSE 'B' END) AS Name,Code
FROM (
SELECT ID,(SELECT Count(*) FROM A2 WHERE ID = A1.ID) 'Code'

FROM A1
) t