SQL列名无效

[PAUSlE]

select * from ( select *, ROW_NUMBER() OVER(Order by ChildId ) AS RowNumber from ( SELECT TOP 100 C.Id as
ChildId, F.Relationship,C.EName, C.CName, C.Sex,
C.Birthday, C.RegNo, C.CaseNo, C.SocialWorkerId, C.CurrentStatus, U.DisplayName AS SocialWorker, ISNULL
((SELECT TOP (1) GroupName
FROM dbo.GroupRecord
WHERE (ChildId = C.Id)
ORDER BY CreatedOnDate DESC), '') AS CurrentGroup,isnull(F.FCName,F.FEName) as FCName
,
(select COUNT(*) from ContactRecord
where ChildId = C.Id and Situation in('3','6') and Relationship in('父','父,母')) as FCount
,
(select COUNT(*) from ContactRecord
where ChildId = C.Id and Situation in('3','6') and Relationship in('母','父,母')) as MCount

FROM Children AS C
inner join dbo.Family AS F on F.ChildId = C.Id and F.Relationship in('母親','父親')
LEFT OUTER JOIN dbo.Users AS U ON U.UserID = C.SocialWorkerId
where C.id NOT IN(SELECT TOP 0 C.id FROM Children ORDER BY ID DESC) ORDER BY C.ID DESC) AS A
where A.RowNumber BETWEEN 0 AND 3

[xiaoxiangqing]

把表结构也发出来，不然运行不行

[二月十六]

这个嵌套有点乱啊……试试这样对吗？

SELECT *
FROM
(
    SELECT *,
           ROW_NUMBER() OVER (ORDER BY ChildId) AS RowNumber
    FROM
    (
        SELECT TOP 100
            C.Id AS ChildId,
            F.Relationship,
            C.EName,
            C.CName,
            C.Sex,
            C.Birthday,
            C.RegNo,
            C.CaseNo,
            C.SocialWorkerId,
            C.CurrentStatus,
            U.DisplayName AS SocialWorker,
            ISNULL(
            (
                SELECT TOP (1)
                    GroupName
                FROM dbo.GroupRecord
                WHERE (ChildId = C.Id)
                ORDER BY CreatedOnDate DESC
            ),
            ''
                  ) AS CurrentGroup,
            ISNULL(F.FCName, F.FEName) AS FCName,
            (
                SELECT COUNT(*)
                FROM ContactRecord
                WHERE ChildId = C.Id
                      AND Situation IN ( '3', '6' )
                      AND Relationship IN ( '父', '父,母' )
            ) AS FCount,
            (
                SELECT COUNT(*)
                FROM ContactRecord
                WHERE ChildId = C.Id
                      AND Situation IN ( '3', '6' )
                      AND Relationship IN ( '母', '父,母' )
            ) AS MCount
        FROM Children AS C
            INNER JOIN dbo.Family AS F
                ON F.ChildId = C.Id
                   AND F.Relationship IN ( '母親', '父親' )
            LEFT OUTER JOIN dbo.Users AS U
                ON U.UserID = C.SocialWorkerId
        WHERE C.id NOT IN (
                              SELECT TOP 0 C.id FROM Children ORDER BY ID DESC
                          )
        ORDER BY C.ID DESC
    ) t
) AS A
WHERE A.RowNumber
BETWEEN 0 AND 3;

[卖水果的net]

ORDER BY C.ID DESC)) AS A -- 这里，应该写两个 )，你只写了一个。 where A.RowNumber BETWEEN 0 AND 3