复杂的sql 一对多查询

SELECT distinct
      u.Name as UserName,
      a.OrderId,
      (
      select  d.SignCity from T_OperaterInfo o left join T_DepartmentInfo d on o.DepId=d.DepId where o.OptId=a.CreateUserId
      ) jinjianchengshi,
      o.Name as lurukefu,
      p.ProductName,
      VerifyName = v.VerifyName ,
      DepName=
      (
      select  D6.DepName from  T_OperaterInfo O5
      left join  T_DepartmentInfo D5 on D5.DepId = O5.DepId
      left join  T_DepartmentInfo D6 on D6.DepId = D5.ParentDepId
      where  a.FirstVerifyOptId = O5.OptId
      ) ,
      a.NetAmount,
      a.Amount,
      a.Term,
      a.RepayPerMonth,
      (select count(1) from T_VerifySituation v1 where v1.VerifyStatus=1005 and v1.OrderId=a.OrderId) tuihuicishu,
      vst3.RefuseWayName as  jujueyuanyin,
      vst10.VerifyDate   as  lixiangshijian,
      'jinjianshijian' =
      case
      when  ( CONVERT(varchar(100), vs2.VerifyDate, 20)) is not null then ( CONVERT(varchar(100), vs2.VerifyDate, 20))
      else
      CONVERT(varchar(100),  vst4.VerifyDate , 20)
      end,
      vst5.VerifyDate  as  zongpingshijian,
      CONVERT(varchar(100), a.ContractStartDate, 20) jihuaqianyueshijian ,
      CONVERT(varchar(100), a.ContractEndDate, 20) hetongjieshushijian,
      (select O1.Name from T_OperaterInfo O1 where O1.OptId=  a.FirstVerifyOptId ) chushen,
      (select O2.Name from T_OperaterInfo O2 where O2.OptId=  a.LastVerifyOptId ) zhongshen,
      (select O3.Name from T_OperaterInfo O3 where O3.OptId=  a.TotalReviewOptId ) zongping,
      (select O4.Name from T_OperaterInfo O4 where O4.OptId=  a.ReconsiderOptId ) fuyi,
      ''''+(  select cast(u.IDCard as varchar(25)))  as  IDCard,
      vst6.AuditOpinion as  chushenyijian,
      vst7.AuditOpinion as   zhongshenyijian,
      vst8.AuditOpinion as   zongpingyijian,
      vst9.AuditOpinion as  fuyiyijian,
      shifougenggaichanpin = case when a.IsAccordanceProduct=1 then '是' else '否' end ,
      'ChangeProductName'=
      case
      when  (a.ChangeProductName is not null and a.ChangeProductName != '')   then substring(a.ChangeProductName,0,charindex(',', ChangeProductName))
      end,
      shifoulianmingjiekuan =case when a.IsCoborrowLoan = 1 then '是' else '否' end,
      'CoborrowName'=
      case
      when  (a.CoborrowName is not null and a.CoborrowName !=  '')   then substring(a.CoborrowName,0,charindex(',', CoborrowName))
      end
      FROM  T_LoanProductOrder as a
      inner join T_UserInfo as u on a.UserId=u.UserId
      inner join T_VerfyStatus as v on a.VerifyStatus=v.VerifyId
      inner join T_Product as p on a.ProductTypeId=p.ProductId
      left join T_OperaterInfo as o on o.OptId=a.CreateUserId
      left join T_DepartmentInfo as d on o.DepId=d.DepId
      left join T_UserWorkInfo as w on u.UserId=w.UserId
      left join (select     OrderId, VerifyDate = max (VerifyDate)  ,flag=count(*) from T_VerifySituation where  VerifyStatus=3023 group by OrderId  ) vs2 on a.OrderId=vs2.OrderId
      left join (select    OrderId, RefuseWayName ,VerifyDate = max (VerifyDate) ,flag=count(*) from T_VerifySituation where  VerifyStatus in (1015,1016) group by OrderId ,RefuseWayName) vst3 on a.OrderId=vst3.OrderId
      left join (select    OrderId ,VerifyDate = max (VerifyDate)  ,flag=count(*) from T_VerifySituation where  VerifyStatus in (1002) group by OrderId ) vst4 on a.OrderId=vst4.OrderId
      left join (select    OrderId ,VerifyDate = max (VerifyDate) ,flag=count(*)  from T_VerifySituation where  VerifyStatus in (1008 ,1015 )   group by OrderId ) vst5 on a.OrderId=vst5.OrderId
      left join (select OrderId ,VerifyDate,AuditOpinion from (
select ROW_NUMBER() over(partition by OrderId order by VerifyDate desc) ROWNO,OrderId ,VerifyDate = max (VerifyDate),AuditOpinion 
from T_VerifySituation where  VerifyStatus in (1004,1005,3013,3016,1028 )  group by OrderId,VerifyDate,AuditOpinion
) as t   where ROWNO=1 ) vst6 on a.OrderId=vst6.OrderId
      left join (
 select OrderId ,VerifyDate,AuditOpinion from (
select ROW_NUMBER() over(partition by OrderId order by VerifyDate desc) ROWNO,OrderId ,VerifyDate = max (VerifyDate),AuditOpinion 
from T_VerifySituation where  VerifyStatus in (1006,1007,3018,3025,1029 )  group by OrderId,VerifyDate,AuditOpinion
) as t   where ROWNO=1 ) vst7 on a.OrderId=vst7.OrderId
      left join (
 select OrderId ,VerifyDate,AuditOpinion from (
select ROW_NUMBER() over(partition by OrderId order by VerifyDate desc) ROWNO,OrderId ,VerifyDate = max (VerifyDate),AuditOpinion 
from T_VerifySituation where  VerifyStatus in (1008, 1009 ,1015)  group by OrderId,VerifyDate,AuditOpinion
) as t   where ROWNO=1
 ) vst8 on a.OrderId=vst8.OrderId
      left join (
 select OrderId ,VerifyDate,AuditOpinion from (
select ROW_NUMBER() over(partition by OrderId order by VerifyDate desc) ROWNO,OrderId ,VerifyDate = max (VerifyDate),AuditOpinion 
from T_VerifySituation where  VerifyStatus in (1010,1011)  group by OrderId,VerifyDate,AuditOpinion
) as t  where ROWNO=1 ) vst9 on a.OrderId=vst9.OrderId
      left join (select    OrderId ,VerifyDate = min (VerifyDate),AuditOpinion ,flag=count(*)  from T_VerifySituation where  VerifyStatus in (1002) group by OrderId ,AuditOpinion ) vst10 on a.OrderId=vst10.OrderId
      where 1=1
  and a.ProductTypeId !=30013 
  and a.OrderId=11376
上一篇:jq版本的checkbox有radio的单选效果(可得到value值)


下一篇:jsp作用域