MYSQL

1、求出连续三天有销售记录的店铺

create table sales_record( shopid varchar(11),dt date,sale int);

insert into sales_record values
('A','2017-10-11',300),
('A','2017-10-12',200),
('A','2017-10-13',100),
('A','2017-10-15',100),
('A','2017-10-16',300),
('A','2017-10-17',150),
('A','2017-10-18',340),
('A','2017-10-19',360),
('B','2017-10-11',400),
('B','2017-10-12',200),
('B','2017-10-15',600),
('C','2017-10-11',350),
('C','2017-10-13',250),
('C','2017-10-14',300),
('C','2017-10-15',400),
('C','2017-10-16',200),
('D','2017-10-13',500),
('E','2017-10-14',600),
('E','2017-10-15',500),
('D','2017-10-14',600);

select distinct t2.shopid1
from
(
    select t1.shopid as shopid1,t1.dt as dt1,t1.sale as sale1
    ,lead(t1.shopid,1,null) over (partition by t1.shopid order by dt) as shopid2 
    ,lead(t1.dt,1,null) over (partition by t1.shopid order by dt) as dt2
    ,lead(t1.sale,1,null) over (partition by t1.shopid order by dt) as sale2
    ,lead(t1.shopid,2,null) over (partition by t1.shopid order by dt) as shopid3 
    ,lead(t1.dt,2,null) over (partition by t1.shopid order by dt) as dt3
    ,lead(t1.sale,2,null) over (partition by t1.shopid order by dt) as sale3                                          
    from sales_record t1
)t2
where t2.sale2>0 and t2.sale3>0

image.png

PYTHON

1、下面这段代码的输出结果是什么？请解释

def extendList(val,list=[]):
list.append(val)
return list
list1 = extendList(10)
list2 = extendList(123,[])
list3 = extendList('a')
print("list1 = %s" %list1)
print("list1 = %s" %list2)
print("list1 = %s" %list3)

image.png

函数中第二个参数有默认值，这个默认值里的列表，只有在定义函数时会创建一次。所以第一次添加10到这个列表时，10就被存在这个列表当中了。当第三次添加a到列表时，还是添加在之前已经存在10的列表里，所以列表里有10和a两个值。执行完上面三步以后，再去打印的话，list里就有10和a两个值。

2、下面这段代码的输出结果是什么？请解释

num = 9
def f1():
num = 20
def f2():
print(num)
f2()
f1()
f2()

image.png

函数f1无返回值，且函数体内仅有一个局部变量，调用f1时不输出任何值，f2输出全局变量的值。

3、以下两段代码运行之后结果是否相同？为什么？

#第一段
l=[]
for i in range(10):
l.append({'num':i})
print(l)
#第二段
l=[]
a={'num':0}
for i in range(10):
a['num']=i
l.append(a)
print(l)

image.png

因为Python传的参数是传地址。第一个每次都是新建一个字典，再将每次的字典append到list中，每个字典的地址是不同的。而第二个是先创建了一个字典，再修改字典中的值，再添加到list中。这样的话，每次引用的都是同一个地址的字典，这个字典的值被改变的话，引用这个字典的内容都会改变。

4、从两个列表取出相同和不同的元素

list1=[1,2,3,4,5]
list2=[2,3,5,7,8,9]

A=[1,2,3,4,5]
B=[2,3,5,7,8,9]
C = []  # 不同的元素
D = []  # 相同的元素
AB = A + B
for i in AB:
    if i in A and i in B:
        if i not in D:
            D.append(i)
        else:
            continue
    else:
        C.append(i)

print(D)
print(list(set(C)))  # 使用集合去重再转为列表

image.png