如何将一个子查询作为查询条件啊.起别名怎么不行?
如:
SELECT DISTINCT s.VehicleID,
s.LargeLibaryNum,
sss=(
SELECT TOP 1 CONVERT(VARCHAR(10), sm2.NextNoteTime, 120) AS
NextNoteTime
FROM SVoltageMan sm2
WHERE sm2.vehicleid = s.VehicleID
ORDER BY
sm2.NextNoteTime DESC
)
FROM SVoltageMan sm,
SVehicle s
WHERE s.VehicleID = sm.vehicleid
and sss>='20090727'
如:
SELECT DISTINCT s.VehicleID,
s.LargeLibaryNum,
sss=(
SELECT TOP 1 CONVERT(VARCHAR(10), sm2.NextNoteTime, 120) AS
NextNoteTime
FROM SVoltageMan sm2
WHERE sm2.vehicleid = s.VehicleID
ORDER BY
sm2.NextNoteTime DESC
)
FROM SVoltageMan sm,
SVehicle s
WHERE s.VehicleID = sm.vehicleid
and sss>='20090727'
(
SELECT DISTINCT s.VehicleID,
s.LargeLibaryNum,
sss=(
SELECT TOP 1 CONVERT(VARCHAR(10), sm2.NextNoteTime, 120) AS
NextNoteTime
FROM SVoltageMan sm2
WHERE sm2.vehicleid = s.VehicleID
ORDER BY
sm2.NextNoteTime DESC
)
FROM SVoltageMan sm,
SVehicle s
WHERE s.VehicleID = sm.vehicleid
) t
WHERE sss>='20090727'
(
SELECT DISTINCT s.VehicleID,
s.LargeLibaryNum,
sss=(
SELECT TOP 1 CONVERT(VARCHAR(10), sm2.NextNoteTime, 120) AS
NextNoteTime
FROM SVoltageMan sm2
WHERE sm2.vehicleid = s.VehicleID
ORDER BY
sm2.NextNoteTime DESC
)
FROM SVoltageMan sm,
SVehicle s
WHERE s.VehicleID = sm.vehicleid
) t
WHERE sss>='20090727'
或
SELECT DISTINCT s.VehicleID,
s.LargeLibaryNum,
sss=(
SELECT TOP 1 CONVERT(VARCHAR(10), sm2.NextNoteTime, 120) AS
NextNoteTime
FROM SVoltageMan sm2
WHERE sm2.vehicleid = s.VehicleID
ORDER BY
sm2.NextNoteTime DESC
)
FROM SVoltageMan sm,
SVehicle s
WHERE s.VehicleID = sm.vehicleid
and (SELECT TOP 1 CONVERT(VARCHAR(10), sm2.NextNoteTime, 120) AS
NextNoteTime
FROM SVoltageMan sm2
WHERE sm2.vehicleid = s.VehicleID
ORDER BY
sm2.NextNoteTime DESC
)
>='20090727'
SELECT DISTINCT s.VehicleID,
s.LargeLibaryNum,
sss=CONVERT(VARCHAR(10), max(sm.NextNoteTime), 120)
FROM SVehicle s, SVoltageMan sm
WHERE s.VehicleID = sm.vehicleid AND sm.NextNoteTime>='20090727'
GROUP BY s.VehicleID, s.LargeLibaryNum