34,590
社区成员
发帖
与我相关
我的任务
分享
SELECT cniId,
(SELECT TOP 1 cnvcLeadOrganCname FROM tbApproval AS tbA WHERE tbCommonPart.cniId = tbA.cniCommonPartId) AS cnvcLeadOrganCname,
(SELECT TOP 1 cnvcSpeedName FROM tbApproval AS tbA WHERE tbCommonPart.cniId = tbA.cniCommonPartId ORDER BY tbA.cniId DESC) AS cnvcSpeedName,
(SELECT TOP 1 cnvcSpeed FROM tbApproval AS tbA WHERE tbCommonPart.cniId = tbA.cniCommonPartId ORDER BY tbA.cniId DESC) AS cnvcSpeed,
(SELECT TOP 1 cndSpeedTime FROM tbApproval AS tbA WHERE tbCommonPart.cniId = tbA.cniCommonPartId ORDER BY tbA.cniId DESC) AS
FROM tbCommonPart
SELECT a.cniId, a.cndSpeedTime, a.OweStatus,a.cnvcFileName,
b.cnvcLeadOrganCname,b.cnvcSpeedName,b.cnvcSpeed,b.cndSpeedTime
FROM tbCommonPart a left join tbApproval b on a.cniId=b.cniCommonPartId
and not exists(select 1 from tbApproval
where cniCommonPartId=b.cniCommonPartId and cniId>b.cniId)
SELECT cniId, cndSpeedTime, OweStatus,cnvcFileName,
(SELECT TOP 1 cnvcLeadOrganCname FROM tbApproval AS tbA WHERE tbCommonPart.cniId = tbA.cniCommonPartId) AS cnvcLeadOrganCname,
(SELECT TOP 1 cnvcSpeedName FROM tbApproval AS tbA WHERE tbCommonPart.cniId = tbA.cniCommonPartId ORDER BY tbA.cniId DESC) AS cnvcSpeedName,
(SELECT TOP 1 cnvcSpeed FROM tbApproval AS tbA WHERE tbCommonPart.cniId = tbA.cniCommonPartId ORDER BY tbA.cniId DESC) AS cnvcSpeed,
(SELECT TOP 1 cndSpeedTime FROM tbApproval AS tbA WHERE tbCommonPart.cniId = tbA.cniCommonPartId ORDER BY tbA.cniId DESC) AS
FROM tbCommonPart
Select Top 1 TA.cniId, TB.cnvcLeadOrganCname, TB.cnvcSpeedName, TB.cnvcSpeed, TB.cndSpeedTime
From tbCommonPart TA, tbApproval TB
Where TA.cniId = TB.cniCommonPartId
Order By TB.cniId
---TRY
select
a.cniid,
max(b.cnvcLeadOrganCname),
min(b.cnvcSpeedName),
min(cnvcSpeed),
min(cndSpeedTime )
from
tbCommonPart a,tbApproval b
where
a.cniId = b.cniCommonPartId
group by
a.cniid
select b.cniId,a.cnvcLeadOrganCname,a.cnvcSpeedName,a.cnvcSpeed,a.cndSpeedTime
FROM tbApproval a, tbCommonPart b
where a.cniCommonPartId= b.cniId
and not exists
(select 1 from tbApproval c where c.cniId>a.cniId)
select top 1 cniCommonPartId,cnvcLeadOrganCname,cnvcSpeedName,cnvcSpeed,cndSpeedTime
from tbApproval order by cniCommonPartId