sql查询，用于在一个查询中返回每个字母的前1列 [英] sql query for returning top 1 column for each alphabet in one query

问题描述

我需要以下要求的SQL查询我有解决方案，但效率不高

我需要的是

如果有对应的
，我需要突出显示该行中的所有字母(A-Z) 数据库中以该字母开头的元素.

目前，我正在使用类似这样的内容

从aspnet_Membership中选择前1个[col]，其中col喜欢"a％"

我重复了26次，然后发现该字母有一个元素，然后突出显示该字母.

如果有一种有效的查询方法，请回复.

Hi ,

i need a sql query for the below req i have a solution but it is not efficient

what i need is

i need to highlight all the alphabets(A-Z) in the row, if there is a corresponding
element starting with that letter in the database.

currently i am using something like this

select top 1 [col] from aspnet_Membership where col like ''a%''

and i am repeating this for 26 times and then finding that there is an element for that letter and then highlighting that alphabet.

If there is an efficient way to do the query ,do reply.

推荐答案

以下是在单个查询中可以解决问题的方法:

Here''s something that should do the trick in a single query:

SELECT Upper(SubString(csUriQuery, 1, 1)) as Letter, COUNT(*) as Number
FROM WebLogs
WHERE  ASCII(Upper(SubString(csUriQuery, 1, 1))) >= 65 AND ASCII(Upper(SubString(csUriQuery, 1, 1))) <= 90
GROUP BY Upper(SubString(csUriQuery, 1, 1))
ORDER BY Letter

如果您不担心过滤掉非字母字符(例如1，

If you''re not worried about filtering out non-letter characters (i.e. 1,

等)，则应该删除WHERE子句.

除了验证之外，您实际上也不需要选择中的计数.

然后，您可以检查结果中是否包含该字母，并且只击中数据库一次.

如果您希望返回数据库中存在"的单个字符字符串，则可以修改它以返回单个字母的串联字符串，并在代码中使用Contains函数.

但这应该可以将您带到想要去的地方.

祝您编码愉快.

, etc.) you should probably remove the WHERE clause.

You also don''t really need the count in the select, except for verification.

Then, you could check if the letter is in the results, and you''ll only hit the DB once.

If you would rather return a single string of characters that "exist" in the DB, You could probably modify it to return a single concatenated string of the letters, and use a Contains function in your code.

But this should get you where you want to go.

Happy coding.

您好，

我不确定这是否是您要找的东西.但是我还是尝试了一下.您可以将a-z中的字母存储在表/临时/变量表中.然后编写SELECT语句以选择字母列表中第一个字母的列.

hello,

I''m not sure if this is what you looking for. But I gave it a try anyway. You can store the letter from a-z in a table/temporary/variable table. Then write a SELECT statement to select the column where the first letter is in the list of letter.

DECLARE @temp table  (
    letter char(1)
)
INSERT INTO @temp SELECT 'a'
INSERT INTO @temp SELECT 'b'
INSERT INTO @temp SELECT 'c'
INSERT INTO @temp SELECT 'd'
INSERT INTO @temp SELECT 'e'
INSERT INTO @temp SELECT 'f'
INSERT INTO @temp SELECT 'g'
INSERT INTO @temp SELECT 'h'
INSERT INTO @temp SELECT 'i'
INSERT INTO @temp SELECT 'j'
INSERT INTO @temp SELECT 'k'
INSERT INTO @temp SELECT 'l'
INSERT INTO @temp SELECT 'm'
INSERT INTO @temp SELECT 'n'
INSERT INTO @temp SELECT 'o'
INSERT INTO @temp SELECT 'p'
INSERT INTO @temp SELECT 'q'
INSERT INTO @temp SELECT 'r'
INSERT INTO @temp SELECT 's'
INSERT INTO @temp SELECT 't'
INSERT INTO @temp SELECT 'u'
INSERT INTO @temp SELECT 'v'
INSERT INTO @temp SELECT 'w'
INSERT INTO @temp SELECT 'x'
INSERT INTO @temp SELECT 'y'
INSERT INTO @temp SELECT 'z'

SELECT TOP 1 [col] FROM aspnet_Membership 
 WHERE SUBSTRING([col], 1, 1) in (SELECT letter FROM @temp)

这篇关于sql查询，用于在一个查询中返回每个字母的前1列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！