sql to, split the string, sql to take out each top three, sql multi-line merge one line (summary)

Today, I encountered a demand to calculate the error rate of each order operation error code top3, and I started without saying more. Now I take an order as a case.

 

Knowledge Points:

1. Count the top three error codes for each order

2.sql Merge multiple lines into one line

3. To split the string

4. Keep two decimal places

 

First, I grouped the numbers for an order, which is relatively simple.

What do I do to get the first three orders corresponding to the error code failcode?I tried some of the online Gods'methods before

select *  from Table Name a where (select count(1) from Table Name where OrderID=a.OrderID and codenum>=a.codenum)<=3 

Some of these methods can only take two, others can take three and cannot be compared circularly.Use this next:

select * from 
(select t.*,row_number() over(partition by orderID order by codenum desc) as flag from Table Name t) a where flag<=3 

You get the first three, then insert a temporary table to remove the flag column.

 

Because top3 my idea is to process it on the server and return it to the client so that on the client side it can be converted directly into the corresponding table without cutting itself, so I need to change the format to an order with three errors, three error descriptions, and three error rates as follows

At first my idea was to rotate columns, and it turned out to be difficult to achieve, then I thought about merging them with commas and splitting them into columns:

First merge:

	SELECT OrderID,
		topQty=STUFF((SELECT ','+convert(varchar,codenum) FROM Table Name t WHERE OrderID=t1.OrderID order by OrderID,codenum desc FOR XML PATH('')), 1, 1, ''),
		FailCode=STUFF((SELECT ','+FailCode FROM Table Name t WHERE OrderID=t1.OrderID order by OrderID,codenum desc FOR XML PATH('')), 1, 1, '') ,
		FailCodeDesc=STUFF((SELECT ','+TestRate FROM Table Name t WHERE OrderID=t1.OrderID order by OrderID,codenum desc FOR XML PATH('')), 1, 1, '')
	    FROM Table Name t1
		GROUP BY OrderID,OrderType

You get data like this:

Then we split it up and write a function first

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER   function   [dbo].[f_split](@c   varchar(max),@split   varchar(2))   
returns   @t   table(col   varchar(20))   
as   
    begin     
      while(charindex(@split,@c)<>0)   
        begin   
          insert   @t(col)   values   (substring(@c,1,charindex(@split,@c)-1))   
          set   @c   =   stuff(@c,1,charindex(@split,@c),'')   
        end   
      insert   @t(col)   values   (@c)   
      return   
    end

Execution:

		select 
		dbo.f_splitT(FailCode, ',', 1) as Code1,
		dbo.f_splitT(FailCode, ',', 2) as Code2,
		dbo.f_splitT(FailCode, ',', 3) as Code3,		
		dbo.f_splitT(topRate, ',', 1) as Rate1,
		dbo.f_splitT(topRate, ',', 2) as Rate2,
		dbo.f_splitT(topRate, ',', 3) as Rate3,
		dbo.f_splitT(FailCodeDesc, ',', 1) as FailCode1,
		dbo.f_splitT(FailCodeDesc, ',', 2) as FailCode2,
		dbo.f_splitT(FailCodeDesc, ',', 3) as FailCode3,
	    from Table Name;

Get the results we want:

I see the error rate is too long, I feel very uncomfortable, and client formatting is OK, if you need to keep two decimal places in sql?

Convert(decimal(18,2), field name) as new field name

 

 

Tags: xml SQL

Posted on Fri, 10 Jan 2020 11:20:40 -0800 by Charles256