mysql能用select x = 1 as x1, count(*) as c from (values 0, 1, 2, cast(null as integer)) as t(x) group by x = 1;

是的，这条MySQL语句是可以执行的。它使用了一个类似于临时表的方式，创建了一个包含了四个值的表，然后使用了GROUP BY和COUNT函数来统计x等于1和x不等于1的行数。由于NULL在MySQL中被视为未知值，它不会被计入任何一组中。这条语句的结果将是一个包含两列的表，其中第一列x1表示x是否等于1，第二列c表示该组中的行数。

相关问题

with tmp(a, b) as ( values (1, 1), (1, 0), (1, cast(null as int)) , (0, 1), (0, 0), (0, cast(null as int)) , (cast(null as int), 1), (cast(null as int), 0), (cast(null as int), cast(null as int))) select * from tmp where a = 1 AND b = 1 order by 1, 2;mysql支持吗

这是一个使用 WITH 子句和 VALUES 子句生成一个临时表并从中查询数据的 SQL 语句。MySQL 并不支持 WITH 子句，但可以通过创建临时表或者使用子查询来实现相同的功能。以下是一个使用子查询的示例：

SELECT *
FROM (SELECT 1 AS a, 1 AS b UNION ALL
      SELECT 1, 0 UNION ALL
      SELECT 1, NULL UNION ALL
      SELECT 0, 1 UNION ALL
      SELECT 0, 0 UNION ALL
      SELECT 0, NULL UNION ALL
      SELECT NULL, 1 UNION ALL
      SELECT NULL, 0 UNION ALL
      SELECT NULL, NULL) AS tmp
WHERE a = 1 AND b = 1
ORDER BY 1, 2;

注意，MySQL 中的子查询必须包含在括号内，并且必须为其指定一个别名。

检查这段mysql语法错误set @n1= ceiling(0.25*(select count(*) from customers)); select @n1; set @n2=ceiling(0.8*(select count(*) from customers)); select @n2; with tmp as( select customerid,sum(amount) amt from customers join orders using(customerid) join orderitems using(orderid) group by customerid),tmp2 as( select *,rank() over(order by amt desc) num from tmp),tmp3 as( select *, case when num<@n1 then'A' when num>@n2 then'B' else 'C' end as'category' from tmp2) select *,row_number()over( partition by category order by amt desc) num1 from tmp3;

这段 MySQL 语法的确存在语法错误，是因为在 `tmp3` 子查询中给 `case` 语句的第二个 `when` 子句加了引号，导致 MySQL 无法正确解析该语句。正确的语法如下所示：

set @n1= ceiling(0.25*(select count(*) from customers)); 
select @n1; 
set @n2=ceiling(0.8*(select count(*) from customers)); 
select @n2; 
with tmp as( 
    select customerid,sum(amount) amt 
    from customers join orders using(customerid) join orderitems using(orderid) 
    group by customerid
),
tmp2 as( 
    select *,rank() over(order by amt desc) num 
    from tmp
),
tmp3 as( 
    select *, 
        case  
            when num<@n1 then 'A' 
            when num>@n2 then 'B' 
            else 'C'  
        end as category 
    from tmp2
) 
select *,row_number()over( partition by category order by amt desc) num1 
from tmp3;

注意，上面的 SQL 语句可能并不适用于所有版本的 MySQL 数据库，具体实现上可能会有所不同。