Stored Procedure writing experience 3

1.Understand the table connection principle and the key to advanced capabilities

1)Flexibility, left join, right join...

2) query efficiency, write method and sequence of conditional statements, and master the initiative of optimization.

3)Ensure correctness, as shown in figureUpdate fromDuring accumulative Modification

Update tmembers

Set

Isharea= A. isharea + B. inewshare

FromVmembers,(Select * From vmembers) B

Where a. imemberid = B. iupmemberid

And

Update tmembers

Set

Isharea= A. isharea + B. inewshare

FromTmembers,(Select * From vmembers) B

Where a. imemberid = B. iupmemberid

Differences:

When using a viewIshareaAnd Right sideA. ishareaIt is not the same record; it is a table.

Isharea= A. isharea + B. inewshare

2.Some experiences

2.1Use temporary tables

1) To avoid conflicts between multiple users.

2)Fast, memory table.

3)Simplified Logic

4)Data Reuse

2.2 avoidance not in

--MarkBmark = 1 (Next stepBmark = 0Insert,This is much faster)

Update # twiv_total set bmark = 1

From

# Twiv_total A, ttwaccount B

Where

A. cwhsid = B. cwhsidAnd a. citemid = B. citemid and A. cvendorid = B. cvendorid

 

-- 3)SetBmark = 0Insert the keywordTtwaccountMedium

Insert into ttwaccount (cwhsid, citemid, cvendorid, cctrgrpid)

Select

Cwhsid,

Citemid,

Cvendorid,

''-- Because not null

From

# Twiv_total

Where

Bmark = 0

2.3Use floating point with caution

1) chip operation problems

2) rounding error when sum is accumulated, " 1 money splitting problem ".

3) unexpected results are generated during running, for example, Judge = 0 , originally 0 , the possible result is not 0

2.4When the statistical number is used as the denominator, pay attention to the filtering conditions plus<> 0Restrictions, such

Select ilevel, count (*) icount

Into # tchainlevelcount

From tmembers A, tchainlevel B

Where

A. ilevel = B. IID

And a. cstate = '0' and B. ctype = 'A'

Group by ilevel

Having count (*)> 0