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 同一列中的多个值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

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

相关文档推荐

Creating table with T-SQL - can#39;t see created tables in Object explorer(使用 T-SQL 创建表 - 在对象资源管理器中看不到创建的表)
How to check if VARCHAR strings are (not) hexadecimal?(如何检查 VARCHAR 字符串是否为(非)十六进制?)
Arithmetic overflow error converting IDENTITY to data type int(将 IDENTITY 转换为数据类型 int 的算术溢出错误)
Where clause if there are multiple of the same ID(如果有多个相同的 ID,Where 子句)
Azure SQL: Invalid Object Name using Powershell#39;s quot;Invoke-sqlcmdquot; on Adventureworks(Azure SQL:使用 Powershell 的“Invoke-sqlcmd的无效对象名称在 Adventureworks 上)
How to Convert Table Data into xml format using sql with multiple sub nodes(如何使用具有多个子节点的sql将表数据转换为xml格式)