Can I use the sub-query in the FROM in T-SQL?

Can I 开发者_如何学运维write the T-SQL like below

select *
 FROM (select * 
        from TableA 
       where FieldA = 1
       )
where FieldB > 10

which means I want to query from the results of another query.

---

Yes you can

select * 
FROM  ( select * from TableA where FieldA=1 ) sub
where FieldB > 10

Just remember to give the sub select an alias.

---

Yes, you can do that.

---

If you want to separate out your sub-queries you can also use Common Table Expressions (CTE's) which help make your code more readable:

WITH Foo (FieldA, FieldB, FieldC) AS
(
    SELECT FieldA, FieldB, FieldC
    FROM TableA
    WHERE FieldA=1
)

SELECT *
FROM Foo
WHERE FieldB > 10

The downside is you will have to explicitly name your columns. However, this actually makes your code faster so it's not always a bad thing.