Possible titles do not describe clearly, then see detailed description here, thank you,
If A table data is like this:
Name department annual month salary out score
Sname Dname cbYear cbMonth MonthPay Score Score_1
Office 2017 7 4500 50 45
Office 2017 8 4800 50 49
A purchase 46 families and 2017 9 4800 50
B procurement 44 families 2017 7 4500 50
B procurement division 2017 8 4500 50 40
B procurement 49 families and 2017 9 4600 50
Financial room 2017 9 3900 50 48 c
.
And so on,

B table data is like this:
Score points calculation performance coefficient
Lscore Plevel
150 1
140
0.9130
0.7.
And so on,
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
Performance pay 5% according to the monthly salary paid
The sum (Score_1) or sum (MonthPay) is the where (cbMonth=7 or 8 or cbMonth=cbMonth=9)
The problem is this:
If 140 & lt; The sum (Score_1) 150 or less is calculated quarterly performance-based pay sum (MonthPay) * 0.05 * 1
If 130 & lt; The sum (Score_1) 140 or less is calculated quarterly performance-based pay sum (MonthPay) * 0.05 * 0.9
If 120 & lt; The sum (Score_1) 130 or less is calculated quarterly performance-based pay sum (MonthPay) * 0.05 * 0.7
.
And so on,

According to table A sum (Score_1) value to B table matching calculate the coefficient of performance???????

Thank you again,

CodePudding user response:

Or, how to solve in Delphi, the teacher can also give a train of thought,

CodePudding user response:

On a post
http://bbs.csdn.net/topics/392251990
Mention how to sum (Score_1) to find the corresponding calculation Plevel performance coefficient,

 SELECT Plevel FROM B 
WHERE ABS (Lscore - sum (Score_1))=(SELECT MIN (ABS (Lscore - sum (Score_1))) FROM B WHERE Lscore & lt; The sum (Score_1)) 

But the total score of each name sum (Score_1) is different, have no way to distinguish between,,,

CodePudding user response:

. Are you hiring unit not,,,, tested, the algorithm is as follows:
Select sname 'name',
The sum (score_1) as' total scores,
Isnull ((select plevel from B where lscore - sum (score_1) & gt;=0 and lscore - sum (score_1) & lt; 10), 0) as' coefficient ',
The Sum (MonthPay) * 0.05 * isnull ((select plevel from B where lscore - Sum (score_1) & gt;=0 and lscore - sum (score_1) & lt; 10), 0) as' performance pay '
From A ls group by sname

CodePudding user response:

Excuse me, which IS not the final, so

Select sname 'name',
The sum (score_1) as' total scores,
Isnull ((select plevel from a where clause lscore - sum (score_1) & gt;=0 and lscore - sum (score_1) & lt; 10), 0) as' coefficient ',
The Sum (MonthPay) * 0.05 * isnull ((select plevel from a where clause lscore - Sum (score_1) & gt;=0 and lscore - sum (score_1) & lt; 10), 0) as' performance pay '
The from group b by sname

CodePudding user response:

Delphi + SQL data sets, can,

CodePudding user response:

reference 4 floor shadowpj response:

sorry, which IS not the final, so

Select sname 'name',
The sum (score_1) as' total scores,
Isnull ((select plevel from a where clause lscore - sum (score_1) & gt;=0 and lscore - sum (score_1) & lt; 10), 0) as' coefficient ',
The Sum (MonthPay) * 0.05 * isnull ((select plevel from a where clause lscore - Sum (score_1) & gt;=0 and lscore - sum (score_1) & lt; 10), 0) as' performance pay '
The from group b by sname

Thank you very much,,, but, here the "10", we don't have such a constant, a score of 150, 140, 130 is not fixed,

CodePudding user response:

reference 5 floor lyhoo163 reply:

Delphi + SQL data set, and

Data set is A CROSS JOIN B?

Then what should we do with Delphi?

CodePudding user response:

According to the experience, the problem is not the original poster described so simple

With the increase of test data, the algorithm will become inefficient to inefficient - because direct algorithm, are directly to one or several physical list a large number of calculations before finally get the query result set, with increasing the amount of data, many resources will be serious drag on server performance

I need to do is, in short, the p. level JOIN A
So:
1. Find the rules of the Join KEY, the KEY is LScore
2. Question: don't have the KEY in A table LScore
2.1 make A KEY in A table - LScore - through statistics: Select Sname, Sum (Score_1) as the KEY Group By Sname
Production statistics of 2.2 to 2.1 for the view - defined as C, this is the key point
Integration of 2.3 A, C, will Join in A C, D view, this is the key point
3. The formation of the result set: the B.L Score Join the end result set view to D E

Three views above, using the CDE, finally we only need to call the view in the program E,
Too much because of the three views, also let algorithm is straightforward,
The actual practice, when the data changes, the server will be updated dynamically to the view, the efficiency of algorithm is much higher than the actual
And for increasing the amount of data, for execution efficiency effect is not obvious



Perhaps I can predict the subject is just the tip of the iceberg of the host system, so, I'm from up to the end only with the original poster said method, did not say algorithm, hope can be useful to you

CodePudding user response:

Desperate efforts, wait me back, I thought!

CodePudding user response:

refer to the eighth floor doloopcn response:

according to the experience, the problem is not the original poster described so simple

With the increase of test data, the algorithm will become inefficient to inefficient - because direct algorithm, are directly to one or several physical list a large number of calculations before finally get the query result set, with increasing the amount of data, many resources will be serious drag on server performance

nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull