表A,存放分类信息,有列ID,Name
表B,存放对象信息,有列ID,CatalogID,NameSQL语句需要取得表A的ID列,Name列,'表B中CatalogID列值=表A的ID列值'的行的数量,'表B中CatalogID列值=表A的ID列值'的最后一行数据的Name列值(ORDER BY [ID] DESC)
表B,存放对象信息,有列ID,CatalogID,NameSQL语句需要取得表A的ID列,Name列,'表B中CatalogID列值=表A的ID列值'的行的数量,'表B中CatalogID列值=表A的ID列值'的最后一行数据的Name列值(ORDER BY [ID] DESC)
c.[ID],c.[Name],(SELECT Count(*) FROM [B] a WHERE a.[CatalogID]=c.[ID]) AS [CatalogSum],(SELECT TOP(1) a.[Name] FROM [B] a WHERE a.[CatalogID]=c.[ID] ORDER BY a.[ID] DESC) AS [LastUpdate]
FROM
[A] c
ORDER BY
[ID]
DESC我试了下,这样子的SQL貌试能取到数据
c.[ID],c.[Name],(SELECT Count(*) FROM [B] a WHERE a.[CatalogID]=c.[ID]) AS [CatalogSum],
(SELECT TOP 1 a.[Name] FROM [B] a WHERE a.[CatalogID]=c.[ID] ORDER BY a.[ID] DESC) AS [LastUpdate]
FROM
[A] c
ORDER BY
[ID]
DESC
其他没问题