Sql语句

1.不连续的天数

#coding:utf-8

import pymssql
import re

list_num = []
cursor1 = s16.cursor()
sql1 = "select orderNum from oprHotShop where shopId = 73 and businessDate = '2018-06-06'order by orderNum"
# LLDP
cursor1.execute(sql1)

res = cursor1.fetchall()
a = str(res)
num = re.findall("(\d+)",a)

for i in num:
    list_num.append(int(i))

order = set(range(list_num[0],list_num[-1]+1))-set(list_num)
print(order)

2.删除重复数据，前提是id自增

delete from [MTGoods].[dbo].[oprComSourceDetail] where id in (select min (id) from [MTGoods].[dbo].[oprComSourceDetail] where inTime > '2018-10-09' group by shopTitle,sourceGroupName having count(1)>1);

3.根据区间范围更新字段

 update [MTGoods].[dbo].[oprComSourceRank] set uv=(

            select
                case when (temp.tradeIndex- uvIndex) > (uvIndex-t.tradeIndex) then t.salesPrice else temp.salesPrice end
            from(
                select top 1 *,1 a from oprIndustryTradeIndex where tradeIndex>uvIndex order by tradeIndex asc
            ) as temp
            left join (
                select top 1 *,1 a from oprIndustryTradeIndex where tradeIndex<uvIndex order by tradeIndex desc
            ) t on t.a=temp.a

) where inTime = '2018-11-19'