10 rows
Id number
001
002
003
004 35
005 41
006 2
15 007
008 10
009

How to remove the line 10 remember any line of total number of 50? Any way,

CodePudding user response:

Can take A number first, then 1-25 for A group A, 49 for A group of 26 - B, the number will directly take 50,
Group B data due to greater than 25, so there is no question of two Numbers together,
Group A number then do loop, to try and group B Numbers together to see if 50, equal to 50 will take corresponding record,
Existence of group A after two Numbers together, to go and group B Numbers together equal to 50,

A + B=50?
A + A + B=50?
.

CodePudding user response:

This I also want to know, every time they go to a pharmacy, deducting the amount of social security is the system automatically gather together with other medicine

CodePudding user response:

 

If OBJECT_ID (' tempdb for.. # ') is not A null 
The begin 
Drop table # A 
End 

Select v, ROW_NUMBER () over (order by v) id # into A the from (
Union all select id 1, 5 v 
Select 2, 8 union all 
Select 3, 20 union all 
35 union all select 4, 
41 union all select 5, 
Select 6, 2 union all 
Select 7, 15 union all 
Select 8, 10 union all 
Select 9, 9 
)t; 

- six digits 
With cte as (
Select a t1. Id t1_id, t1. V t1_v, 
T2. Id t2_id, t2. V t2_v, 
T3. Id t3_id, t3. V t3_v, 
T4. Id t4_id, t4. V t4_v, 
T5. Id t5_id, t5. V t5_v, 
T6. Id t6_id, t6. V t6_v, 
ROW_NUMBER () over (order by t1. Id) word 

The from # A t1 
Left the join # A t2 on t1. IdLeft the join # A t3 on t2. IdLeft the join # A t4 on t3. IdLeft the join # A t5 on t4. IdLeft the join # A t6 on t5. IdC), as (
Select *, 
The SUM (isnull (cte) t1_v, 0) + isnull (cte) t2_v, 0)) over (partition by word) Sum_2, 
The SUM (isnull (cte) t1_v, 0) + isnull (cte) t2_v, 0) + isnull (cte) t3_v, 0)) over (partition by word) Sum_3, 
The SUM (isnull (cte) t1_v, 0) + isnull (cte) t2_v, 0) + isnull (cte) t3_v, 0) + isnull (cte) t4_v, 0)) over (partition by word) Sum_4, 
The SUM (isnull (cte) t1_v, 0) + isnull (cte) t2_v, 0) + isnull (cte) t3_v, 0) + isnull (cte) t4_v, 0) + isnull (cte) t5_v, 0)) over (partition by word) Sum_5, 
The SUM (isnull (cte) t1_v, 0) + isnull (cte) t2_v, 0) + isnull (cte) t3_v, 0) + isnull (cte) t4_v, 0) + isnull (cte) t5_v, 0) + isnull (cte) t6_v, 0)) over (partition by word) Sum_6 

The from cte 
) 

Select distinct c.t 1 _v, 
The case when c.s. um_2 & lt;=50 then c.t 2 _v else 0 end t2_v, 
The case when c.s. um_3 & lt;=50 then c.t 3 _v else 0 end t3_v, 
The case when c.s. um_4 & lt;=50 then c.t 4 _v else 0 end t4_v, 
The case when c.s. um_5 & lt;=50 then c.t 5 _v else 0 end t5_v, 
The case when c.s. um_6 & lt;=50 then c.t 6 _v else 0 end t6_v 
The from c 
Where c.s. um_2 c.s. um_3==50 or 50 or c.s. um_4 c.s. um_5==50 or 50 or c.s. um_6=50 

Pretty good, literally write, accounts for the answer to a position such as the great god,

CodePudding user response:

In SQL is not recommended to implement the new algorithm, can look back to the principle of the new algorithm