如何获得特定的行 [英] how to get particular row at first

查看:83
本文介绍了如何获得特定的行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

如何以相同的顺序获得第一个剩余记录的特定行



ex:



table



abc

------

2种子10

3 kadd 12

1质量32

4 ssds 55

---------



i希望第一行是b = mass

how to get particular row at first remaining records in same order

ex:

table

a b c
------
2 seed 10
3 kadd 12
1 mass 32
4 ssds 55
---------

i want first row is b=mass

推荐答案

已经发布了另一个已发布的解决方案,但我认为可能需要替代方案。



乍一看,您似乎只想按 [a] 列的顺序排列表格你需要哪种情况(向@hypermellow点头)

Posting this solution as another has already been posted but I think an alternative might be required.

At first sight it looks as if you just want the table to be ordered by column [a] in which case you need (nod to @hypermellow)
select a, b, c from [table] order by a

将生成

1 mass 32
2 seed 10
3 kadd 12
4 ssds 55

然而你的措辞

Quote:

如何以相同的顺序获得第一个剩余记录的特定行

how to get particular row at first remaining records in same order

表示您可能需要类似

suggests that you might want something like

with CTE AS (
    select a, b, c, 1 as rownum from [table] where b = 'mass'
    UNION ALL
    select a, b, c, 1 + ROW_NUMBER() OVER (ORDER BY b)
    FROM [table] where b <> 'mass'
)
SELECT a, b, c FROM CTE order by rownum

将产生结果

1	mass	32
3	kadd	12
2	seed	10
4	ssds	55

这里的兴趣点是我手动将 rownum 1分配给我想要的记录,然后允许sql生成行号我基于所需的任何订单(我使用列 [b] 只是为了突出显示差异) - 请注意我添加 1 生成的行号,以确保子查询不会以rownum = 1结束与我的第一个查询冲突。

然后我只是UNION这两个查询。



如果您只想要返回一条记录(在任何一种情况下),那么按照建议使用 TOP 1 Amaan23(但你的措辞意味着这不是你想要的)

The point of interest here is that I have manually assigned a rownum of 1 to the record I want first, then allowed sql to generate row numbers for me based on whichever order is required (I used column [b] just to highlight the difference) - note that I add 1 to the generated row number to ensure the sub-query doesn't end up with a rownum=1 to clash with my first query.
I then just UNION the two queries.

If you did only want a single record returned (in either case) then use TOP 1 as suggested by Amaan23 (but your wording implies that this is not what you want)


CHill60答案的一个更简单的替代方法是使用 CASE 语句在 ORDER BY 子句中:

A simpler alternative to CHill60's answer is to use a CASE statement in the ORDER BY clause:
SELECT 
    a, 
    b, 
    c 
FROM 
    [table] 
ORDER BY 
    CASE 
        WHEN b = 'mass' THEN 0 
        ELSE 1 
    END, 
    b
;


这篇关于如何获得特定的行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆