select b.[leader_name],[inspected_person],wrong_type,b.Departure_date,count(*) as miss数量 from ( select * from ( select * ,row_number() over(partition by [check_date],[checker],[inspected_person],[secondary_rework],[type],[project],[project_name],[NID],[wrong_type],[reason],[wrong_description],[complaint_status],[date],[create_time] order by [inspected_person] desc) as row from ( SELECT [check_date] ,[checker] ,[inspected_person] ,[secondary_rework] ,[type] ,[project] ,[project_name] ,[NID] ,[wrong_type] ,[reason] ,[wrong_description] ,[complaint_status] ,[date] ,[create_time] ,[AssigneeId] FROM [DV_report].[dbo].[miss] as a left join [User] as b on a.[AssigneeId]=b.id where date>='${start}' and date<='${end}' and complaint_status='申诉后' and wrong_type not in ('无','建议') and inspected_person not in('DL001','DL002') ) c) d where d.row = '1' UNION select * from ( select * ,row_number() over(partition by [check_date],[checker],[inspected_person],[secondary_rework],[type],[project],[project_name],[NID],[wrong_type],[reason],[wrong_description],[complaint_status],[date],[create_time] order by [inspected_person] desc) as row from ( SELECT [check_date] ,[checker] ,[secondary_rework] as [inspected_person] ,'' as [secondary_rework] ,[type] ,[project] ,[project_name] ,[NID] ,[wrong_type] ,[reason] ,[wrong_description] ,[complaint_status] ,[date] ,[create_time] ,[AssigneeId] FROM [DV_report].[dbo].[miss] as a left join [User] as b on a.[AssigneeId]=b.id where date>='${start}' and date<='${end}' and complaint_status='申诉后' and wrong_type = '网络问题' and [reason] = '状态错误' and [secondary_rework] is not null and [secondary_rework] !='' and inspected_person not in('DL001','DL002') ) c) d where d.row = '1' )as a left join [User] as b on a.[AssigneeId]=b.id WHERE b.leader_name IS NOT NULL and b.IsActive = '1' group by b.[leader_name],[inspected_person],wrong_type,b.Departure_date order by [inspected_person] 帮我将这段代码转换为postgre格式语言
时间: 2023-08-18 17:11:00 浏览: 47
SELECT b."leader_name", "inspected_person", "wrong_type", b."Departure_date", count(*) as miss数量
FROM (
SELECT *
FROM (
SELECT *, row_number() OVER(PARTITION BY "check_date","checker","inspected_person","secondary_rework","type","project","project_name","NID","wrong_type","reason","wrong_description","complaint_status","date","create_time" ORDER BY "inspected_person" DESC) AS row
FROM (
SELECT "check_date"
,"checker"
,"inspected_person"
,"secondary_rework"
,"type"
,"project"
,"project_name"
,"NID"
,"wrong_type"
,"reason"
,"wrong_description"
,"complaint_status"
,"date"
,"create_time"
,"AssigneeId"
FROM "DV_report"."dbo"."miss" AS a
LEFT JOIN "User" AS b ON a."AssigneeId"=b.id
WHERE "date">='${start}'
AND "date"<='${end}'
AND "complaint_status"='申诉后'
AND "wrong_type" NOT IN ('无','建议')
AND "inspected_person" NOT IN('DL001','DL002')
) c
) d
WHERE d.row = '1'
UNION
SELECT *
FROM (
SELECT *, row_number() OVER(PARTITION BY "check_date","checker","inspected_person","secondary_rework","type","project","project_name","NID","wrong_type","reason","wrong_description","complaint_status","date","create_time" ORDER BY "inspected_person" DESC) AS row
FROM (
SELECT "check_date"
,"checker"
,"secondary_rework" AS "inspected_person"
,'' AS "secondary_rework"
,"type"
,"project"
,"project_name"
,"NID"
,"wrong_type"
,"reason"
,"wrong_description"
,"complaint_status"
,"date"
,"create_time"
,"AssigneeId"
FROM "DV_report"."dbo"."miss" AS a
LEFT JOIN "User" AS b ON a."AssigneeId"=b.id
WHERE "date">='${start}'
AND "date"<='${end}'
AND "complaint_status"='申诉后'
AND "wrong_type" = '网络问题'
AND "reason" = '状态错误'
AND "secondary_rework" IS NOT NULL
AND "secondary_rework" !=''
AND inspected_person NOT IN('DL001','DL002')
) c
) d
WHERE d.row = '1'
) AS a
LEFT JOIN "User" AS b ON a."AssigneeId"=b.id
WHERE b."leader_name" IS NOT NULL
AND b."IsActive" = '1'
GROUP BY b."leader_name","inspected_person","wrong_type",b."Departure_date"
ORDER BY "inspected_person"