GROUP BY 同一列中的多个值

GROUP BY multiple values in same column(GROUP BY 同一列中的多个值)
本文介绍了GROUP BY 同一列中的多个值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有这个 SQL 查询:

I have this SQL Query:

select prefix, code, stat, complete, COUNT(*) as Count
from table
group by prefix, code, stat, complete
order by prefix, code, stat, complete

前缀"列是一个字母数字值 (0-9a-zA-z).我想要的是让它如果前缀的值是一个数字,使数字等于0.如果它是一个字母,它会保持它的值.我试图在 group by 子句下面添加以下行:

The column 'prefix' is an alphanumeric value (0-9a-zA-z). What I want is to make it so that if the value of prefix is a number, to make the number equal to 0. If it is a letter, it will keep its value. I have tried to add the following line beneath the group by clause:

(case when prefix like '%[0-9]%' then 0 else prefix end)

但我收到错误消息将 varchar 值 'J' 转换为数据类型 int 时转换失败.".

But I get an error "Conversion failed when converting the varchar value 'J' to data type int.".

是什么导致了这个错误?如何让前缀"列显示 0 或一个字母?

What is causing this error? How can I get the 'prefix' column to display either 0 or a letter?

推荐答案

case when prefix like '%[0-9]%' then '0' else prefix end

你显然也需要这个作为 GROUP BY 中的表达式:

You obviously also need this as the expression in the GROUP BY:

select 
    NewPrefix = case when prefix like '%[0-9]%' then '0' else prefix end, 
    code, 
    stat, 
    complete, 
    COUNT(*) as Count
from table
group by 
    case when prefix like '%[0-9]%' then '0' else prefix end, 
    code, stat, complete
order by 
    case when prefix like '%[0-9]%' then '0' else prefix end, 
    code, stat, complete

这篇关于GROUP BY 同一列中的多个值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!

本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除!

相关文档推荐

Execute complex raw SQL query in EF6(在EF6中执行复杂的原始SQL查询)
Hibernate reactive No Vert.x context active in aws rds(AWS RDS中的休眠反应性非Vert.x上下文处于活动状态)
Bulk insert with mysql2 and NodeJs throws 500(使用mysql2和NodeJS的大容量插入抛出500)
Flask + PyMySQL giving error no attribute #39;settimeout#39;(FlASK+PyMySQL给出错误,没有属性#39;setTimeout#39;)
auto_increment column for a group of rows?(一组行的AUTO_INCREMENT列?)
Sort by ID DESC(按ID代码排序)