forpas

可以按布尔表达式排序 price BETWEEN 10 AND 15 DESC 先是 price :

SELECT *
FROM tablename
ORDER BY price BETWEEN 10 AND 15 DESC, price;

看到了吗 demo .

为此,你需要分组 WorkList 仅限和使用条件聚合:

SELECT WorkList, 
       SUM(RecordStatus = 'Open') AS Open,
       SUM(RecordStatus = 'Closed') AS Closed,
       SUM(RecordStatus = 'Hold') AS Hold
FROM worklist_reports
WHERE WorkList IS NOT NULL
GROUP BY WorkList;

如果使用 execute() 也许这就是你想做的,但是 executemany() 应该使用元组中的一个元组作为第二个参数,因为这是 执行官 ,多次执行同一条语句,每次提供不同的参数列表:

v = [('1360425627', '2.79', '17.33', '486857065.504', '17.88371', '0.08'),]
cur.executemany('UPDATE ...', v)

SELECT * ... 当存在冲突的列名时,可能会导致歧义。
应该为冲突的列名设置别名。
还可以为表名设置短别名,以缩短代码并使其更具可读性,并使用它们来限定所有列名。
多年来,您使用的隐式连接语法已经被显式连接语法所取代。
你的代码应该这样写:

sql = f"""
SELECT c.customer_id, c.customer_name, c.address customer_address,
       p.partner_id, p.partner_name, p.address partner_address
FROM customer c
INNER JOIN customer_partner cp ON c.customer_id = cp.customer_id 
INNER JOIN partner p ON cp.partner_id = p.partner_id
WHERE c.customer_id IN ({','.join(['%s' for _ in range(len(customer_id_list))])})
"""

我漏掉了所有的专栏 customer_partner 从 SELECT 列出,因为它们不需要。

参数的第二个参数的元组中参数的顺序 cursor.execute() 必须与 ? 起搏器:

cursor.execute(updateseller, (Phone, Email, Address, sellername))

如果按产品分组,则在 ORDER BY 条款你只需要:

ORDER BY SUM(stock = 0) > 0

stock = 0 评估为 1 如果没有一种尺码的“S”、“M”或“L”的存货,那么在这种情况下 SUM() 将返回正数并 SUM(stock = 0) > 0 将评估为 在没有行的产品之后对产品进行排序 库存=0 :

SELECT product_id, product_name
FROM product_attributes
WHERE size in ('S', 'M', 'L')
GROUP BY product_id, product_name
ORDER BY SUM(stock = 0) > 0

您想返回表中的所有有效行,对吗?
doc_owner_id 满足表中条件的:

select t.* 
from tablename t inner join (
  select doc_owner_id 
  from tablename
  where 
    str_to_date('4/9/2018', '%m/%d/%Y') between doc_start_date and doc_end_date
    and
    doc_type in ('JANUARY', 'DRIVER', 'LICENCE')
  group by doc_owner_id
  having count(distinct doc_type) = 3
) g on g.doc_owner_id = t.doc_owner_id
where
  str_to_date('4/9/2018', '%m/%d/%Y') between t.doc_start_date and t.doc_end_date
  and
  t.doc_type in ('JANUARY', 'DRIVER', 'LICENCE')

这就是你应该用的方法 group by 汇总:

select users, ref,
  count(`add`) `add`,
  count(sub) sub,
  count(mul) mul,
  sum(marks) total
from tablename
group by users, ref

见 demo
结果:

| users | ref | add | sub | mul | total |
| ----- | --- | --- | --- | --- | ----- |
| u1    | 20  | 3   | 2   | 1   | 270   |
| u2    | 15  | 2   | 1   | 0   | 70    |
| u3    | 20  | 1   | 3   | 1   | 145   |

你需要总结一下 monthlyBudgetAmt 您的问题:

SELECT SUM(t.monthlyBudgetAmt) AS total FROM (
    SELECT 
      MAX(eligible_allowance) AS monthlyBudgetAmt
    FROM vefm_dailybudget
    WHERE
        division=1 AND region=1
        AND unit=1 AND common_status IN('Finance - II Approved','Cash Disbursement')
        AND eligible_allowance != 0
        AND approval_date BETWEEN '2019-02-01'
        AND '2019-02-28' AND budget_type='monthly'
    GROUP BY ecode_id
) t

使用自连接和聚合:

select m.date, m.id_patient, m.value,
  sum(mm.value) somme
from mytable m left join mytable mm
on mm.id_patient = m.id_patient and mm.value is not null and mm.date < m.date 
and (
  select count(*) from mytable
  where id_patient = m.id_patient and value is not null 
  and date >= mm.date and date < m.date 
) <= 4
where m.id_patient = '87321'
group by m.date, m.id_patient, m.value

见 demo .
结果:

| date       | id_patient | value | somme |
| ---------- | ---------- | ----- | ----- |
| 2019-11-17 | 87321      | 6     |       |
| 2019-11-18 | 87321      | 1     | 6     |
| 2019-11-19 | 87321      | 2     | 7     |
| 2019-11-20 | 87321      |       | 9     |
| 2019-11-21 | 87321      | 5     | 9     |
| 2019-11-22 | 87321      | 8     | 14    |
| 2019-11-23 | 87321      |       | 16    |
| 2019-11-24 | 87321      | 3     | 16    |
| 2019-11-25 | 87321      | 4     | 18    |
| 2019-11-26 | 87321      | 6     | 20    |
| 2019-11-27 | 87321      | 1     | 21    |
| 2019-11-28 | 87321      | 10    | 14    |

在GROUPBY之前应该只有一个WHERE子句。
但是由于您使用左连接,因此在 正确的 桌子状 t2.meta_key = '_billing_first_name' 因为拒绝不匹配的行,所以会得到一个内部联接。
因此,请在ON子句中设置所有条件:

cursor.execute("
SELECT t1.ID, t1.post_date, t2.meta_value AS first_name, t3.meta_value AS last_name
FROM test_posts t1 
LEFT JOIN test_postmeta t2 
ON t1.ID = t2.post_id AND t2.meta_key = '_billing_first_name'
LEFT JOIN test_postmeta t3 
ON t1.ID = t3.post_id AND t3.meta_key = '_billing_last_name'
GROUP BY t1.ID 
ORDER BY t1.post_date DESC LIMIT 20")

尽管这个查询在语法上可能对MySql是正确的,但是使用GROUP BY是没有意义的,因为您不做任何聚合。

获取输入表中的和并在 UPDATE 声明:

update output o 
inner join (
  select date_format(date, '%Y%m%d%H') hour, sum(total) total
  from input
  group by date_format(date, '%Y%m%d%H')
) i on i.hour = date_format(o.date, '%Y%m%d%H')
set o.total = i.total

我认为您需要一个UPDATE语句,其中包含两个表的连接,并使用CASE表达式将值设置为列:

UPDATE controller c 
INNER JOIN assignuser au
ON c.idno = au.assignedidno
SET c.access = CASE 
  WHEN (date(au.expiration) < date(now())) THEN '0'
  ELSE '1'
END

可以这样简化:

UPDATE controller c 
INNER JOIN assignuser au
ON c.idno = au.assignedidno
SET c.access = (date(au.expiration) >= date(now())) 

可以通过嵌套select查询来完成此操作:

UPDATE PRODUCTS 
SET PRICE = (
  select PRICE from (select PRICE from PRODUCTS where ID = 1) t
);

见 demo 是的。
另一种方法是使用自交叉连接:

UPDATE PRODUCTS p CROSS JOIN (
  select PRICE from PRODUCTS where ID = 1
) t 
SET p.PRICE = t.PRICE;

见 demo 是的。

将逗号连接到列表的开头和结尾:

SELECT * FROM table WHERE concat(',', list, ',') LIKE '%,0,%';

您需要左连接:

select 
  t.*,
  t.weight - tt.weight diff
from tablename t left join tablename tt
on tt.number = t.number and tt.event = t.event and tt.weight <> t.weight

由于您使用的是准备好的语句,请更改为:

String query="INSERT into user_info (username,password,email_id,`mobile number`) VALUES(?,?,?,?)";

稍后将插入参数,并调用 setString() 方法。

编辑 如果列的名称是 mobile number 然后在select语句中写入
`手机号码` 或 [手机号码]

你需要一个 CASE 中的语句 ORDER BY 先按标题排序结果,再按说明排序结果,然后按 title :

select * from books
where title like '%search%' or description like '%search%'
order by 
  case
    when title like '%search%' then 1
    when description like '%search%' then 2
  end, title

由于只标记了mysql,因此可以编写更简单的查询:

select * from books
where title like '%search%' or description like '%search%'
order by title like '%search%' desc, title

这将在mysql 8.0中工作:

with occup as (
select
  case when o.occupation = 'Doctor' then o.Name end as Doctor, 
  case when o.occupation = 'Engineer' then o.Name end as Engineer,
  case when o.occupation = 'Developer' then o.Name end as Developer,  
  case when o.occupation = 'Musician' then o.Name end as Musician
from occupations o
),

doctors as (
select ROW_NUMBER() OVER (
 ORDER BY case when occup.Doctor is null then 1 else 0 end
 ) as rn, occup.Doctor from occup
),
engineers as (
select ROW_NUMBER() OVER (
 ORDER BY case when occup.Engineer is null then 1 else 0 end
 ) as rn, occup.Engineer from occup
),
developers as (
select ROW_NUMBER() OVER (
 ORDER BY case when occup.Developer is null then 1 else 0 end
 ) as rn, occup.Developer from occup
),
musicians as (
select ROW_NUMBER() OVER (
 ORDER BY case when occup.Musician is null then 1 else 0 end
 ) as rn, occup.Musician from occup
)

select doctors.Doctor, engineers.Engineer, developers.Developer, musicians.Musician 
from doctors
inner join engineers on doctors.rn = engineers.rn  
inner join developers on engineers.rn = developers.rn
inner join musicians on musicians.rn = developers.rn
WHERE coalesce(doctors.Doctor, engineers.Engineer, developers.Developer, musicians.Musician) IS NOT NULL;

见 demo

您必须先按每个作者的最新发布日期排序,然后按发布日期降序排列:

select b.*
from books b
order by (
  select max(publishdate)
  from books
  where author = b.author
) desc, b.publishdate desc

你想数数:
clnt_id 从桌子上 clients_db 和
ac_id 从桌子上 aircon_client_db
对于 usr_sid='a1' 对吧?
我看不出有必要加入这些会议。
您可以在同一查询中使用两个子查询分别计数:

select 
  (select count(ac_id) from aircon_client_db where usr_sid = 'a1') as nAC,
  (select count(clnt_id) from clients_db where usr_sid = 'a1') as nClnt

如果有重复的情况 克朗特 S在 客户端数据库 或复制 阿克西 S在 空调客户数据库 然后使用:
count(distinct clnt_id) 和 count(distinct ac_id)

通过使用交叉联接创建要从中选择的所有可能组合的CTE:

WITH cte AS (
  SELECT 
    n1.id id1, n1.name name1, n1.number number1,
    n2.id id2, n2.name name2, n2.number number2,
    n3.id id3, n3.name name3, n3.number number3
  FROM numbers n1 CROSS JOIN numbers n2 CROSS JOIN numbers n3
  WHERE 
    (n1.number > 3 OR n2.number > 3 OR n3.number > 3)
    AND n1.id <> n2.id AND n1.id <> n3.id AND n3.id <> n2.id
  ORDER BY RAND() LIMIT 1
)

SELECT id1 id, name1 name, number1 number FROM cte
UNION ALL
SELECT id2 id, name2 name, number2 number FROM cte
UNION ALL
SELECT id3 id, name3 name, number3 number FROM cte

见 demo