如何计算SQL中表格中以字母开头的列数 [英] How to get count of the columns beginning with the alphabet in a table in SQL

问题描述

大家好



我正在开展一个项目，我必须在表格中以字母表开头的列数。



例如

在桌子下面



名称

apple

banana

椰子

杏子

熊

ant



结果应该是



姓名数
apple 3

banana 2

椰子1

杏子3

熊2
ant 3



我尝试了以下查询，但我得到了所有行的记录总数

 选择名称，（选择计数（ *）来自 tablename 其中 name < span class =code-keyword> like   LEFT （name， 1 ）+ < span class =code-string>' ％'）来自 tablename 

请帮助



谢谢

解决方案

问题在于您的子查询 - 它不知道您所指的是哪个名称列。



你基本上问过有多少名字以他们自己的第一个字母开头？，答案是all all them。



您需要在表格中添加别名，以便您可以参考正确表格中的列：

< pre lang =sql> 选择名称，（选择 count（*） from tablename As t2 其中​​ t2.name 喜欢 LEFT （t1.name， 1 ）+ ' ％'）来自 tablename 作为 t1

Hi Everyone

I am working on a project where I have to get the count of the columns beginning with the alphabet in a table.

For example
Below the table

Name
apple
banana
coconut
apricot
bear
ant

The result should be

Name count
apple 3
banana 2
coconut 1
apricot 3
bear 2
ant 3

I tried the following query but I am getting the total count of the records for all rows

select name,(select count(*) from tablename where name like LEFT(name,1)+ '%') from tablename

Please help

Thank You

解决方案

The problem is in your sub-query - it doesn't know which name column you're referring to.

You've essentially asked "how many names start with their own first letter?", to which the answer is "all of them".

You need to add an alias to the tables, so that you can refer to the column from the correct table:

select name, (select count(*) from tablename As t2 where t2.name like LEFT(t1.name, 1) + '%') from tablename As t1

这篇关于如何计算SQL中表格中以字母开头的列数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！