假设你真的想保留匹配/不匹配的结果,你可以在这里使用列表:

holid = ['2021-01-26', '2021-03-11', '2021-03-29', '2021-04-02', '2021-04-14', '2021-04-21', '2021-05-13', '2021-07-21', '2021-08-19', '2021-09-10', '2021-10-15', '2021-11-05','2021-11-19']
date = datetime.today().strftime('%Y-%m-%d')
output = ["Matched" if x == date else "Not Matched" for x in holid]
print(output)

子查询当前查询并基于滞后值进行筛选:

WITH cte AS (
    SELECT emp_no, LAG(title) OVER (PARTITION BY emp_no ORDER BY <col>) lag_title
    FROM titles
)

SELECT emp_no, lag_title
FROM cte
WHERE lag_title IS NOT NULL;

注意,我添加了一个 ORDER BY 条款至 LAG ,没有它就没有什么意义。

我们可以尝试使用 str.extract 在这里:

df[["LONG", "LAT"]] = df["GEO"].str.extract(r'POINT \((-?\d+(?:\.\d+)?) (-?\d+(?:\.\d+)?)\)')

对于那些 不要 匹配 POINT 格式,则上面将指定一个缺少的值。你可以用任何你想要的占位符来替换它。

你一定没有用Python的引擎测试过正则表达式,因为你的语法是关闭的。这个 re.sub 函数不使用分隔符。使用此版本:

str = 'bla / bla / bla'
str = re.sub(r'\s+/\s+', ' - ', str)
print(str)  # bla - bla - bla

你可以用 re.search 这里有一个regex替换:

str1 = "cat, dog, rat"
regex = r'\b(?:' + r'|'.join(re.split(r',\s*', str1)) + r')\b'
if not re.search(regex, str1):
    print("MATCH")
else:
    print("NO MATCH")

您可以使用以下逻辑:

DELETE
FROM tbl_sqs t1
WHERE uniqueid NOT IN (SELECT uniqueid
                       FROM (
                           SELECT t2.uniqueid
                           FROM tbl_sqs t2
                           WHERE t2.asin = t1.asin AND
                                 t2.timeOfOfferChange > t1.timeOfOfferChange
                       ) t);

假设我们将目标词定义为在第一个字符之外同时包含小写和大写字母,我们可以尝试使用 re.sub 对于正则表达式选项:

def case_insensitivity(sentence):
    return re.sub(r'\w+(?:[A-Z]\w*[a-z]|[a-z]\w*[A-Z])\w*', 'XXX_', sentence)

print(case_insensitivity('My team is the BeST team at the tournament'))

这张照片是:

My team is the XXX_ team at the tournament

这里是一种正则表达式拆分方法。我们可以试着分开 \s+and\s+ ,目标是 and 两边都是空白。请注意,制表符和换行符是空白字符,包含在 \s .

inp = "good \nand bad and\n\t not great and awesome"
parts = re.split(r'\s+and\s+', inp)
print(parts)  # ['good', 'bad', 'not great', 'awesome']

在基本Python中,我们可以尝试使用lambda表达式进行排序:

para_planilha.sort(key=lambda row: row[0])

但请注意,这里有一些1D列表,其第一个元素是 不 日期时间字符串,例如 [2.36, '-'] ,它在排序中首先出现。

您需要在此处进行条件聚合:

SELECT
    u.id_user,
    COUNT(CASE WHEN c.status = 'ABAN'  THEN 1 END) AS ABAN,
    COUNT(CASE WHEN c.status = 'DES'   THEN 1 END) AS DES,
    COUNT(CASE WHEN c.status = 'LET'   THEN 1 END) AS LET,
    COUNT(CASE WHEN c.status = 'NOANS' THEN 1 END) AS NOANS,
    COUNT(CASE WHEN c.status NOT IN ('ABAN', 'DES', 'LET', 'NOAN')
               THEN 1 END) AS OTH
FROM Users u
LEFT JOIN Get_cal c
    ON c.id_user = u.id_user
GROUP BY
    u.id_user
ORDER BY
    u.id_user;

更换 \b\d+\b :

inp = "dslkf 234 dkf23 12asd 2 23 4"
output = re.sub(r'\b\d+\b', r'<NUM>', inp)
print(output)  # dslkf <NUM> dkf23 12asd <NUM> <NUM> <NUM>

对单次传递查询使用条件聚合:

SELECT SUM(cu.Gender = 'M') AS Males,
       SUM(cu.Gender = 'F') AS Females
FROM Customers cu
INNER JOIN Rents r ON r.Customers_Cid = cu.Cid
INNER JOIN Cars ON c.CarId = r.Cars_CarId 
WHERE c.Make = 'Dodge';

为了让你的工会方法发挥作用,你只需为性别选择一个计算列,例如。

SELECT COUNT(Gender) AS Num, 'M' AS Gender
...
UNION ALL
SELECT COUNT(Gender), 'F'

我建议简化你的模式,只使用点所有模式:

def tryRegexData(area):

  try: 
      return re.search(r'\bLegal Description:\s+(.*?)\s+Property Use:', area.text, flags=re.S).group(1)
  except Exception as e:
      return ""

我们可以尝试使用 re.findall 在多行模式下:

inp = """uint256 private constant _tTotal = 100000000 * 10**9;
uint256 private _rTotal = (MAX - (MAX % _tTotal));
_maxTxAmount = 300000000 * 10**9;"""

lines = re.findall(r'^.*\d+\s*\*\s*\d+\*\*\d+.*$', inp, flags=re.M)
print(lines)

这张照片是:

['uint256 private constant _tTotal = 100000000 * 10**9;',
 '_maxTxAmount = 300000000 * 10**9;']

这里使用的正则表达式模式试图找到任何包含整数乘以10的整数幂的行。

我们可以使用 str.extract 在这里:

df["X"] = df["X"].str.extract(r'(\d{4}-\d{1,2}-\d{1,2})_(\d{2}-\d{2}-\d{2}).*\.(\d+)', r'\1 \2 \3')

这是一个正则表达式 demo .

使用 re.findall 这是你的朋友:

stringA = "values-are-10,20,30,40,50"
stringB = "values-are-10"
strings = [stringA, stringB]
output = [re.findall(r'\d+(?:,\d+)*', s)[0] for s in strings]
print(output)  # ['10,20,30,40,50', '10']

我想你只需要更新一下:

def addBooks(title, quantity):
    c.execute('UPDATE books SET quantity = quantity + ? WHERE title = ?', (quantity, title,))
    conn.commit()

您当前的代码有多个问题。首先,做什么 SELECT * 在第一个示例中(不必要的),select在语义上是不正确的,因为您应该明确列出所需的列。此外,不需要迭代此选择的结果集来更新每个记录。 UPDATE 已经是一个基于集合的操作,它将针对表中的每个记录。也没有必要做一个 INSERT 如果我能理解你的要求,就给你。

你应该先创建一个 新的 用于存储正确日期结果的日期列:

ALTER TABLE Table2 ADD COLUMN gun_date DATE;

然后,用 STR_TO_DATE :

UPDATE Table2
SET gun_date = STR_TO_DATE(gun, '%d %M %Y');

如果你愿意,你可以放弃原版 gun 如果你认为你不再需要它了。

你想把这两张桌子连接起来吗?

SELECT *
FROM field2 t1
INNER JOIN wides2 t2
    ON t2.horseid = t1.horseid AND
       t2.raceid = t1.raceid;

MySQL中的日期文字应该用单引号括起来:

SET @trimRetaineddate = '2017-01-11';
SET @delete_text = CONCAT('DELETE FROM Test WHERE id = ''', @trimRetaineddate, '''');
SELECT @delete_text;  -- DELETE FROM Test WHERE id = '2017-01-11'
PREPARE delete_stmt FROM @delete_text;

使用您当前的方法,但将大写字母和数字放在积极的展望中:

[a-z](?=[A-Z]{2,}[0-9]{3,})

这种模式意味着:

[a-z]          match a lowercase letter
(?=            then lookahead (but do NOT consume)
    [A-Z]{2,}  2 or more uppercase letters
    [0-9]{3,}  followed by 3 or more digits
)

看啊 明确肯定 ,但不是 消费 ,以正则表达式模式。

在MySQL 8+上,我们可以使用 REGEXP_REPLACE :

SELECT PrizeMoneyBreakDown,
       REGEXP_REPLACE(PrizeMoneyBreakDown,
                      '(total_value|welfare_fund|trophy_total_value),\\d+;',
                      '') AS NewPrizeMoneyBreakDown
FROM race2;

如果你想 使现代化 然后,实际列使用:

UPDATE race2
SET PrizeMoneyBreakDown = REGEXP_REPLACE(
    PrizeMoneyBreakDown,
    '(total_value|welfare_fund|trophy_total_value),\\d+;',
    '')
WHERE PrizeMoneyBreakDown REGEXP '(total_value|welfare_fund|trophy_total_value),\\d+;';

在MySQL 8+上,我们可以使用 RANK 分析函数:

WITH cte AS (
    SELECT COUNT(*) in_count, employee_id, employee,
           RANK() OVER (ORDER BY COUNT(*)) rnk
    FROM active_attendance
    WHERE today BETWEEN  DATE_FORMAT(NOW() ,'%Y-%m-01') AND NOW()
    GROUP BY employee_id
)

SELECT in_count, employee_id, employee
FROM cte
WHERE rnk = 1;

如果要从较大的文本中找出这些值,则使用 re.findall

inp = "R15.49 or ZAR15.49"
matches = re.findall(r'\b([A-Z]+)(\d+(?:\.\d+)?)\b', inp)
print(matches)

打印:

[('R', '15.49'), ('ZAR', '15.49')]

实际上你现在的角色类 [^aeiouAEIOU] 除了 元音。请尝试此版本:

s = "This website is for losers LOL!"
out = re.sub(r'[aeiou]', '', s, flags=re.IGNORECASE)
print(s + "\n" + out)

这张照片:

This website is for losers LOL!
Ths wbst s fr lsrs LL!

听起来像是在做 二 COALESCE 选择所需地址:

SELECT
    u.name,
    COALESCE(a1.address, a2.address) AS address
FROM user u
LEFT JOIN address a1
    ON u.id = a1.user_id AND
       a1.address_type = 'Mailing'
LEFT JOIN address a2
    ON u.id = a2.user_id AND
       a2.address_type = 'Home';

对聚合使用联接:

SELECT
    prod.id,
    prod.title,
    prod.price,
    prod.status,
    COALESCE(t.sales, 0) AS sales,
    COALESCE(t.revenue, 0) AS revenue
FROM products prod
LEFT JOIN
(
    SELECT
        product,
        COUNT(*) AS sales,
        SUM(price) AS revenue
    FROM payments p
    WHERE vendor = '1' AND credited = 'Vendor' AND
          status IN ('Completed', 'Pending', 'Canceled_Reversal')
    GROUP BY product
) t
    ON t.product = prod.id;

注意,我们使用 左边 products 表中甚至可能没有任何匹配的数据 payments COALESCE 报告销售和收入的零值。

子查询别名为 t 以上可能可以使用以下索引进行优化:

payments (product, vendor, credit, status, price)

我只需要在这里使用一个查询:

sql = """UPDATE players
         SET update_date = datetime("now")
         WHERE player_tag IN (SELECT player_tag FROM players
                              ORDER BY update_date DESC LIMIT 2)"""
cursor.execute(sql)

以下是处理您的需求的规范方法:

SELECT recipe_id
FROM recipe_categories
WHERE sub_category_id IN (3, 5)
GROUP BY recipe_id
HAVING COUNT(DISTINCT sub_category_id) = 2;

您可能需要做一些额外的工作来使用准备好的语句从PHP实现这一点。

您确实希望这里有一个self(inner)连接,但您缺少将每个表限制为特定年份的逻辑:

SELECT
    p1.country_code,
    p1.size AS size2010,
    p2.size AS size2015
FROM populations AS p1
INNER JOIN populations AS p2
    ON p1.country_code = p2.country_code
WHERE
    p1.year = 2010 AND
    p2.year = 2015;