Tim Biegeleisen

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

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;

您可以尝试删除所有非单词字符:

string_value = "hello ' how ' are - you ? and/ nice to % meet # you"
output = re.sub(r'\s+', ' ', re.sub(r'[^\w\s]+', '', string_value))
print(string_value)
print(output)

hello ' how ' are - you ? and/ nice to % meet # you
hello how are you and nice to meet you

我首先使用的解决方案针对使用模式的所有非单词字符(除了空白) [^\w\s]+ . 但是,有可能留下两个或更多空间的集群。所以,我们再打电话给 re.sub 删除多余的空格。

问题是一旦你消费了 converted 中的CTE offending converting 两次:

WITH converted1 AS (
    SELECT CONVERT(v using utf8mb4) COLLATE utf8mb4_0900_ai_ci AS v FROM t
),
converted2 AS (
    SELECT CONVERT(v using utf8mb4) COLLATE utf8mb4_0900_ai_ci AS v FROM t
),
offending AS (
    SELECT v FROM converted1 GROUP BY v HAVING COUNT(*) > 1
),
duplicates AS (
    SELECT c.v FROM converted2 c
    INNER JOIN offending o ON c.v = o.v
)
SELECT *
FROM duplicates a
INNER JOIN duplicates b
    ON a.v = b.v AND
       a.v COLLATE utf8mb4_bin > b.v COLLATE utf8mb4_bin;

你应该先联合,然后聚合:

SELECT
    eventid,
    sentiment,
    COUNT(*) AS cnt
FROM
(
    SELECT eventid, sentiment FROM tablea
    UNION ALL
    SELECT eventid, sentiment FROM tableb
) t
-- WHERE eventid = 10     -- optional
GROUP BY
    eventid,
    sentiment;

使用非贪婪模式:

\b[AH]\[.*?\]

Python脚本:

inp = "UNCKEV\nPumpkins 10/1/20-2030\nRunners\nha\nH[ 12 ]\nA[ O ]\nKNOWLEDGI\nPLA\nDISTRIBUTION\nHOME TEAM\nPINK VISITING TEAM\nBLANCHE BUREAU NATIONAL\nJAUNE \u00c9C\nALE\nPR\u00c9CISER LES DE\nSEULEMENT\nOFF\nSORTIE\nSTART\nD\u00c9BUT\nON\nRETOUR\nPER\nP\u00c9R.\nMIN\nSERV\nPURG\nOFFENCE\nINFRACTION\nDUR\u00c9E\nNo.\nDU\nNeinterferCACE =\n188 Cross clicak 3\n1010hgh shicle\n"
matches = re.findall(r'\b[AH]\[.*?\]', inp)
print(matches)

这张照片:

['H[ 12 ]', 'A[ O ]']

我会使用这个版本:

SELECT STR_TO_DATE('1/1/2016 12:00:00.000 PM', '%d/%m/%Y %h:%i:%s.%f %p')

这里有一种方法,使用 re.search 有五个捕捉组按预期方式将输出拼凑在一起:

inp = '<NOV 20, 2017 5:20:17, 20PM IST> <Summary> <USER-1000-1 2017-11-20 08:150:80, 2000'
match = re.search(r'^<(\w{3}) (\d+), (\d{4}).*\bUSER-(\d+).*\b\d{4}-\d{2}-\d{2} (.*?),.*$', inp)
if match:
    print("Date: " + match.group(2) + "-" + match.group(1) + "-" + match.group(3))
    print("USER: " + match.group(4))
    print("Time: " + match.group(5))

这张照片:

Date: 20-NOV-2017
USER: 1000
Time: 08:150:80

请注意,即使使用regex,拆分也可能不是最好的方法,因为没有明确的分隔符可以在任何地方工作。

对此我没有文档参考,但从我的观察测试结果来看,MySQL将把等式表达式的两边都转换为RHS上字符串文本的长度。也就是说,MySQL将执行以下查询:

SELECT *
FROM yourTable
WHERE CAST(name AS CHAR(100)) = CAST('longgg... ...string' AS CHAR(100));
-- assuming it has a length of 100

这种比较通常会失败,因为MySQL不会在LHS上填充少于100个字符的字符串,这意味着长度通常不会匹配。

你可以试着把你的电流求和 LIKE 表达。我建议使用 REGEXP 但是:

SELECT
    PlaceID,
    (PlaceDescription REGEXP '[[:<:]]tokyo[[:>:]]') +
    (PlaceDescription REGEXP '[[:<:]]restaurant[[:>:]]') AS Count
FROM Place
WHERE
    PlaceDescription REGEXP '[[:<:]]tokyo|restaurant[[:>:]]';

注意,我正在进行regex搜索 \btokyo\b 或 \brestaurant\b ,意思是 正则表达式 只会找到那些确切的词,两边都有词的界限。还要注意 正则表达式 不区分大小写,因此 tokyo 和 Tokyo 会被捡起来数数。

您可以为 name 两个不同表中的列。

select
    ga.id,
    ga.name as action_name,
    ga.action_owner,
    gu.user_id,
    gu.name as user_name
from grc_action ga
left join grc_users gu
    on ga.action_owner = gu.user_id;

请注意,我还使用了使查询更易于阅读的表别名。一般来说,做 SELECT * 不需要,通常最好显式列出所需的列。

您可以在此处尝试使用更新联接:

UPDATE songs s
LEFT JOIN artists a
    ON a.name = s.artist
SET artist_id = a.artist_id;