SQL statement Dual Loop, SQL statement

Source: Internet
Author: User

SQL statement Dual Loop, SQL statement



The shunxu field in Table j_wenzhang_aps201503 is null. Now you want to update the shunxu Field Based on lanmu_id and qishiye.

1. If the shunxu field is auto-incremented, there are no duplicates, And the lanmu_id is small, the shunxu is also small; the lanmu_id is the same, the qishiye is small, and the shunxu is small.

declare @maxid int declare @minid intdeclare @shunxu intdeclare @maxqishiye intdeclare @minqishiye intselect @maxid= max(lanmu_id) from j_wenzhang_aps201503 where shunxu is null select @minid= min(lanmu_id) from j_wenzhang_aps201503 where shunxu is null select @shunxu=0if ((@maxid is not null) and (@minid is not null))beginwhile (@maxid>=@minid) beginselect @maxqishiye=max(qishiye) from j_wenzhang_aps201503 where shunxu is null and lanmu_id=@minidselect @minqishiye=min(qishiye) from j_wenzhang_aps201503 where shunxu is null and lanmu_id=@minidif ((@maxqishiye is not null) and (@minqishiye is not null))beginwhile (@maxqishiye>=@minqishiye) beginselect @shunxu=@shunxu+1update j_wenzhang_aps201503  set shunxu=@shunxu where lanmu_id=@minid and qishiye=@minqishiye and shunxu is nullselect @minqishiye=min(qishiye) from j_wenzhang_aps201503 where shunxu is null and lanmu_id=@minidendendselect @minid=min(lanmu_id) from j_wenzhang_aps201503 where shunxu is null endendgo


Result After execution:



2. If the shunxu field is auto-incrementing according to the column, and the lanmu_id is small, the shunxu is also small; the lanmu_id is the same, the qishiye is small, and the shunxu is small.

That is, (shunxu increases from 1 in each lanm_id)


declare @maxid int declare @minid intdeclare @shunxu intdeclare @maxqishiye intdeclare @minqishiye intselect @maxid= max(lanmu_id) from j_wenzhang_aps201503 where shunxu is null select @minid= min(lanmu_id) from j_wenzhang_aps201503 where shunxu is null if ((@maxid is not null) and (@minid is not null))beginwhile (@maxid>=@minid) beginselect @maxqishiye=max(qishiye) from j_wenzhang_aps201503 where shunxu is null and lanmu_id=@minidselect @minqishiye=min(qishiye) from j_wenzhang_aps201503 where shunxu is null and lanmu_id=@minidselect @shunxu=0if ((@maxqishiye is not null) and (@minqishiye is not null))beginwhile (@maxqishiye>=@minqishiye) beginselect @shunxu=@shunxu+1update j_wenzhang_aps201503  set shunxu=@shunxu where lanmu_id=@minid and qishiye=@minqishiye and shunxu is nullselect @minqishiye=min(qishiye) from j_wenzhang_aps201503 where shunxu is null and lanmu_id=@minidendendselect @minid=min(lanmu_id) from j_wenzhang_aps201503 where shunxu is null endendgo




Result After execution:







Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.