创建按字母顺序索引的列表（ColdFusion + Microsoft SQL Server） [英] Creating an Alphabetical Indexed List (ColdFusion + Microsoft SQL Server)

问题描述

我目前正在努力寻找任何知道如何做到这一点的人？我尝试了几种不同的方法，结果中途结果，但不是我想要的。基本上，我试图创建一个列表显示所有的乐队A-Z，但乐队名称正在从数据库中调用，所以我不得不在嵌套列表中使用＃band_name＃。如果我重写代码并发布，有人可能会看到我错了。

I'm currently struggling to find anyone who knows how this can be done? I've tried a few different methods and ended up with halfway results but not quite what i wanted. Basically i'm trying to create a list showing all the bands A-Z, but the band names are being called from a database, so i'm having to use #band_name# within a nested list. If i re-write the code and post it, someone might be able to see where i'm going wrong.

<cfoutput query="bandNameList">
  <cfloop from="65" to="90" index="i">
    <UL>
     <LI> #chr(i)#
       <UL>
         <LI> #band_name# </LI>
       </UL>
     </LI>
    </UL>
  </cfloop>
</cfoutput>

推荐答案

我认为你只是输出以该字母开头的第一乐队的Letter。一个方法是实现这一点是稍微改变你的查询（注意，我使用的是我认为是SQL-92语法在这里，但可能是一个更好的方式来获取您的特定数据库中的第一个字母）：

What I think you're after is to only output the Letter for the first band that begins with that letter. One way to achieve this is to change your query slightly (note I'm using what I think is SQL-92 syntax here, but there's probably a nicer way to get the first letter in your particular database):

select 
  band_name, 
  SUBSTRING(band_name from 1 for 1) AS first_letter
from 
  bands 
order by 
  band_name

这会给你第一个字母在查询中。

Which will get you the first letter in the query.

如果你想把所有带有数字第一个字母的乐队组合在一起，那么你可以使用SQL的CASE语句来做到这一点（你可能需要找到ascii ）在你的DBMS）。你也可以反转逻辑和匹配正常字母，并将其他一切变成一个0-9和标点符号类，如果这更容易。我认为这是一些音乐系统做的事情（我认为在iPhone上的iTunes，但我相信有其他人）

If you want to group all the bands with numeric first letters together, then you can use SQL's CASE statement to do that (you may need to find the equivalent to ascii() in your DBMS). You could also invert the logic and match against 'normal' letters and lump everything else into a '0-9 and punctuation' category if that's easier. I think that's what a number of music systems do (I'm thinking iTunes on the iPhone, but I'm sure there are others)

select 
  band_name, 
  CASE  
    WHEN ascii(left(band_name, 1)) BETWEEN 48 AND 57 THEN '0-9'
    ELSE left(band_name, 1)
  END AS first_letter   
from 
  bands 
order by 
  band_name

现在，您可以使用该额外的列以及cfoutput的组属性来帮助您获得所需的输出。

Now you can use that extra column along with cfoutput's group attribute to help get the output as you want it.

<UL>
<cfoutput query="bandNameList" group="first_letter">
     <LI> #first_letter#
     <UL>
       <cfoutput>             
           <LI> #band_name# </LI>             
       </cfoutput>
       </UL>
     </LI>        
</cfoutput>
</UL>

这篇关于创建按字母顺序索引的列表（ColdFusion + Microsoft SQL Server）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！