如何在SQL中实现count而不是case语句？ [英] How to achieve count instead of case statement in SQL?

问题描述

大家好，

i需要一些关于我的SQL查询的解决方案。

我的xml是：

Hi all,
i need some solution about my sql query.
my xml is:

<main>
    <data>
        <counter>A</counter> 
    </data>
</main>

我的查询是：

my query is :

CREATE TABLE #temp
(
   
    COUNTER VARCHAR(1)
);
 
INSERT #temp
SELECT    
    Data.value('(/COUNTER/text())[1]','VARCHAR(100)') AS COUNTER,

FROM
    @XML.nodes('/main/Data') AS AdviceData (Data);

INSERT INTO SECONDTABLE(COUNTER)
SELECT CASE T.COUNTER WHEN 'A' THEN 1 END AS COUNTER
FROM #temp T

以上查询工作正常。

但我不想使用case语句。为什么因为有时柜台是Z然后我应该显示26.

没有案例陈述我怎么能实现这个。



提前谢谢



我尝试了什么：



如何实现计数而不是案例陈述在sql？

The above query is working fine.
but i don't want to use case statement. why because sometimes counter is Z then i should show 26.
without case statement how can i achieve this.

thanks in advance

What I have tried:

how to achieve count instead of case statement in sql?

推荐答案

你可以使用ASCII功能并将其转换成这种方式。

You can make use of the ASCII function and convert it that way.

declare @counter as nvarchar
set @counter = 'A'

select ascii (@counter) -64 as Counter

大写字母A的acsii值是65，所以很简单击倒64结果。请记住，小写值是不同的，所以你可能想强制它上面，除非你确信xml中的值总是大写。



在你的case

The acsii value for uppercase A is 65, so simply knock 64 off the result. Bear in mind that lowercase values are different so you may want to force it to upper unless you're confident that the value in the xml is always uppercase.

In your case

INSERT INTO SECONDTABLE(COUNTER)
select ascii (t.COUNTER) -64 as Counter
FROM #temp T

这篇关于如何在SQL中实现count而不是case语句？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！