<sheetData>
<row r="1">
<c r="A1" t="str">
<v>Иванов</v>
</c>
<c r="B1" t="str">
<v>Петров</v>
</c>
<c r="С1" t="str">
<v>Сидоров</v>
</c>
...
</row>
</sheetData>sql_script = """
CREATE TABLE IF NOT EXISTS information
(id INTEGER PRIMARY KEY AUTOINCREMENT,userid INTEGER,money INTEGER,weight INTEGER,sportmode INTEGER);
CREATE TABLE IF NOT EXISTS task
(id INTEGER PRIMARY KEY AUTOINCREMENT,list TEXT, status INTEGER);
CREATE TABLE IF NOT EXISTS what
(mycommands TEXT);
CREATE TABLE IF NOT EXISTS accounts
(login TEXT, password TEXT, token TEXT)
"""
for sql in sql_script.split(";\n"):
cursor.execute(sql)#!/usr/bin/env python3
# -*- coding: utf-8 -*-
import math
def ras (x1, y1, x2, y2, x3, y3):
## Если отрезок вертикальный - меняем местами координаты каждой точки.
if x1==x2:
x1, y1 = y1, x1
x2, y2 = y2, x2
x3, y3 = y3, x3
k=(y1-y2)/(x1-x2) ## Ищем коэффициенты уравнения прямой, которому принадлежит данный отрезок.
d=y1-k*x1
xz=(x3*x2-x3*x1+y2*y3-y1*y3+y1*d-y2*d)/(k*y2-k*y1+x2-x1)
dl=-1
if ( xz<=x2 and xz>=x1 ) or ( xz<=x1 and xz>=x2 ):
dl=math.sqrt((x3-xz)*(x3-xz)+(y3-xz*k-d)*(y3-xz*k-d)) ## Проверим лежит ли основание высоты на отрезке.
return dl
## Вводим параметры отрезков
# xa, ya, xb, yb = [1, 1, 2, 2]
# xc, yc, xd, yd = [2, 1, 3, 0]
xa, ya, xb, yb = [int(s) for s in input().split()]
xc, yc, xd, yd = [int(s) for s in input().split()]
min=-1
t=-2
s=-2
o=(xb-xa)*(-yd+yc)-(yb-ya)*(-xd+xc)
o1=(xb-xa)*(yc-ya)-(yb-ya)*(xc-xa)
o2=(-yd+yc)*(xc-xa)-(-xd+xc)*(yc-ya)
if o!=0:
t=o1/o
s=o2/o
if (t>=0 and s>=0) and (t<=1 and s<=1):
min=0 ## Проверим пересекаются ли отрезки.
else:
## Найдём наименьшую высоту опущенную из конца одного отрезка на другой.
dl1=ras(xa,ya,xb,yb,xc,yc)
min=dl1
dl2=ras(xa,ya,xb,yb,xd,yd)
if ( dl2<min and dl2!=-1 ) or min==-1 :
min=dl2
dl3=ras(xc,yc,xd,yd,xa,ya)
if ( dl3<min and dl3!=-1 ) or min==-1 :
min=dl3
dl4=ras(xc,yc,xd,yd,xb,yb)
if ( dl4<min and dl4!=-1) or min==-1 :
min=dl4
if min==-1 :
## В случае, если невозможно опустить высоту найдём минимальное расстояние между точками.
dl1=math.sqrt((xa-xc)*(xa-xc)+(ya-yc)*(ya-yc))
min=dl1
dl2=math.sqrt((xb-xd)*(xb-xd)+(yb-yd)*(yb-yd))
if dl2<min :
min=dl2
dl3=math.sqrt((xb-xc)*(xb-xc)+(yb-yc)*(yb-yc))
if dl3<min :
min=dl3
dl4=math.sqrt((xa-xd)*(xa-xd)+(ya-yd)*(ya-yd))
if dl4<min :
min=dl4
print (min)SELECT
b.*,
row_number() OVER (PARTITION BY id ORDER BY balance_date DESC) r
FROM balance bПоэтому необходимо просто выбрать с r = 1 или r =2 соответственно !
WITH bal AS (
SELECT
b.*,
row_number() OVER (PARTITION BY id ORDER BY balance_date DESC) r
FROM balance b
)
SELECT id, balance_date, balance FROM bal WHERE r = 1 or r = 2
ORDER BY id, balance_date desc
;WITH
bal AS (
SELECT
b.*,
row_number() OVER (PARTITION BY id ORDER BY balance_date DESC) r
FROM balance b
),
last_bal AS (
SELECT id, balance_date, balance FROM bal WHERE r = 1
),
prev_bal AS (
SELECT id, balance_date, balance FROM bal WHERE r = 2
)
SELECT
lb.id,
lb.balance_date AS last_balance_date, lb.balance AS last_balance,
pb.balance_date AS prev_balance_date, pb.balance AS prev_balance
FROM last_bal lb
LEFT JOIN prev_bal pb ON pb.id = lb.id
;SELECT
id,
balance_date_start,
balance_start,
balance_date_mid,
balance_mid
FROM (
SELECT
cb.*,
row_number() OVER (PARTITION BY id ORDER BY balance_date_start DESC) r
FROM client_balance cb
) t
WHERE r = 1
;select cal.cal_date, count(person)
from some_table st
right join (
-- генерируем календарь - набор дат
select *
from (
select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) cal_date
from
(select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
(select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
(select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
(select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
(select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4
) v
where cal_date between '2021-11-01' and '2021-11-30'
) cal on cal.cal_date = st.date_field
group by cal.cal_dateSELECT '2021-11-01' + INTERVAL seq DAY FROM seq_0_to_29;select cal.cal_date, count(person)
from some_table st
right join (
-- генерируем календарь - набор дат
SELECT '2021-11-01' + INTERVAL seq DAY as cal_date FROM seq_0_to_29
) cal on cal.cal_date = st.date_field
group by cal.cal_date
;-- For MySQL 8.0
select FLOW, OTPERIOD, LOG_DATE, LOG_TIME, ENTITY
from (
select
FLOW, OTPERIOD, LOG_DATE, LOG_TIME, ENTITY,
row_number() over (partition by ENTITY order by LOG_DATE desc, LOG_TIME desc) num
from test
) tt
where num = 1
;
-- For MySQL <=8.0
select t.*
from test t
inner join (
select max(LOG_DATE) LOG_DATE, MAX(LOG_TIME) LOG_TIME, ENTITY
from test
group by ENTITY
having concat(LOG_DATE,' ', LOG_TIME) = max(concat(LOG_DATE,' ', LOG_TIME))
) tt on tt.ENTITY = t.ENTITY and tt.LOG_DATE = t.LOG_DATE and tt.LOG_TIME = t.LOG_TIME
;
-- For MySQL <=8.0
select *
from test t
where concat(LOG_DATE,' ', LOG_TIME) = (
select max(concat(LOG_DATE,' ', LOG_TIME))
from test tt
where tt.ENTITY = t.ENTITY
group by ENTITY
)
;alter session set events '22829 trace name context forever';alter session set events '22829 trace name context forever';
WITH
test AS (
SELECT 'проул.' as code, 'проул.|пр-к|' as str FROM dual UNION
SELECT 'ул.' as code, 'ул.|улица|ул-ца|' as str FROM dual UNION
SELECT 'пер.' as code, 'пер.|переулок|' as str FROM dual UNION
SELECT 'кв-л' as code, 'кв-л|квартал|' as str FROM dual UNION
SELECT 'линия' as code, 'линия|' as str FROM dual UNION
SELECT 'парк' as code, 'парк|' as str FROM dual UNION
SELECT 'рзд' as code, 'рзд|разъезд|' as str FROM dual UNION
SELECT 'сад' as code, 'Сад|' as str FROM dual UNION
SELECT 'тракт' as code, 'тракт|' as str FROM dual UNION
SELECT 'тер' as code, 'тер|территория|' as str FROM dual UNION
SELECT 'ст' as code, 'ст|станция|' as str FROM dual UNION
SELECT 'сл' as code, 'сл|слобода|' as str FROM dual UNION
SELECT 'пр-кт' as code, 'пр.|проспект|пр-кт|' as str FROM dual UNION
SELECT 'шоссе' as code, 'ш.|шоссе|' as str FROM dual UNION
SELECT 'пл' as code, 'пл|площадь|пл-дь|' as str FROM dual UNION
SELECT 'наб.' as code, 'наб.|набережная|наб-ая|' as str FROM dual UNION
SELECT 'тупик' as code, 'тупик|' as str FROM dual UNION
SELECT 'мост' as code, 'мост|' as str FROM dual UNION
SELECT 'пр.' as code, 'проезд|пр.|' as str FROM dual UNION
SELECT 'блв.' as code, 'блв.|бульвар|' as str FROM dual UNION
SELECT 'мкн.' as code, 'мкн.|микрорайон|' as str FROM dual UNION
SELECT 'пос.' as code, 'пос.|посёлок|' as str FROM dual UNION
SELECT 'овраг' as code, 'овраг|' as str FROM dual UNION
SELECT 'городок' as code, 'городок|' as str FROM dual
)
SELECT t1.code, SUBSTR(t1.str,
DECODE(t2.lvl, 1, 1, INSTR(t1.str, '|', 1, t2.lvl - 1) + 1),
INSTR(t1.str, '|', 1, t2.lvl)
- DECODE(t2.lvl, 1, 1, INSTR(t1.str, '|', 1, t2.lvl - 1) + 1))
FROM test t1,
LATERAL (SELECT level AS lvl FROM dual
CONNECT BY NVL(INSTR(t1.str, '|', 1, level), 0) > 0) t2
;WITH
str_csv AS (
SELECT 'проул.' as code, 'проул.|пр-к|' as str FROM dual UNION
SELECT 'ул.' as code, 'ул.|улица|ул-ца|' as str FROM dual UNION
SELECT 'пер.' as code, 'пер.|переулок|' as str FROM dual UNION
SELECT 'кв-л' as code, 'кв-л|квартал|' as str FROM dual UNION
SELECT 'линия' as code, 'линия|' as str FROM dual UNION
SELECT 'парк' as code, 'парк|' as str FROM dual UNION
SELECT 'рзд' as code, 'рзд|разъезд|' as str FROM dual UNION
SELECT 'сад' as code, 'Сад|' as str FROM dual UNION
SELECT 'тракт' as code, 'тракт|' as str FROM dual UNION
SELECT 'тер' as code, 'тер|территория|' as str FROM dual UNION
SELECT 'ст' as code, 'ст|станция|' as str FROM dual UNION
SELECT 'сл' as code, 'сл|слобода|' as str FROM dual UNION
SELECT 'пр-кт' as code, 'пр.|проспект|пр-кт|' as str FROM dual UNION
SELECT 'шоссе' as code, 'ш.|шоссе|' as str FROM dual UNION
SELECT 'пл' as code, 'пл|площадь|пл-дь|' as str FROM dual UNION
SELECT 'наб.' as code, 'наб.|набережная|наб-ая|' as str FROM dual UNION
SELECT 'тупик' as code, 'тупик|' as str FROM dual UNION
SELECT 'мост' as code, 'мост|' as str FROM dual UNION
SELECT 'пр.' as code, 'проезд|пр.|' as str FROM dual UNION
SELECT 'блв.' as code, 'блв.|бульвар|' as str FROM dual UNION
SELECT 'мкн.' as code, 'мкн.|микрорайон|' as str FROM dual UNION
SELECT 'пос.' as code, 'пос.|посёлок|' as str FROM dual UNION
SELECT 'овраг' as code, 'овраг|' as str FROM dual UNION
SELECT 'городок' as code, 'городок|' as str FROM dual
),
str_xml AS (
SELECT code, XMLType('<a><b>'||replace(str, '|','</b><b>')||'</b></a>') xml FROM str_csv
)
SELECT
code, extractValue(value(t),'b') str_type
FROM
str_xml s,
TABLE(XMLSequence(s.xml.extract('a/b'))) t
WHERE extractValue(value(t),'b') is not null
;<td class="local_time0" data-tz="Europe/Saratov">Thu, 29 Apr 2021 09:50:04 +0400</td>function t() {
try {
if (!i)
return !0;
for (var t, e = new Date, a = ".local_time", n = [], r = [], o = 0; o < 2; o++)
n[o] && r[o] || (n[o] = $(a + o).data("tz"),
r[o] = $(a + o).text().substr(25)),
t = Intl.DateTimeFormat("en-GB", {
weekday: "short",
day: "2-digit",
month: "short",
year: "numeric",
hour: "numeric",
minute: "numeric",
second: "numeric",
timeZone: n[o]
}).format(e),
$(a + o).text(t.slice(0, 16) + t.slice(17) + r[o]);
return !0
} catch (t) {
return !1
}
}
t() && window.setInterval(t, 1e3)find . -type f -name "Friends*" -execdir bash -c 'mv "$1" "${1/\w+\s*-\s*(\d)x(\d+).*$/S0\1E\2.srt}"' _ {} \;select name, test from (
select name, test from (select name, test from post where category_id=2 limit 0,4) t2
union
select name, test from (select name, test from post where category_id=3 limit 0,5) t3
union
select name, test from (select name, test from post where category_id=1 limit 0,18) t1
) t
limit 0,18SELECT p.* FROM product p
JOIN ( SELECT rand() * (SELECT max(id) FROM product WHERE p.name LIKE 'Горо%') AS max_id ) AS m
WHERE p.id >= m.max_id
AND p.name LIKE 'Горо%'
ORDER BY p.id ASC LIMIT 1;p.name LIKE 'Горо%'