当前位置: 动力学知识库 > 问答 > 编程问答 >

sql server - SQL to get X number of accounts from DB, which could be variable number of rows

问题描述:

I have a SQL Server table AccountAction which is denormalised. It is a flattened version of the Account and Action tables, which I'm hoping should be a lot quicker for reporting queries over millions of rows. One Account can have many Actions, so the table looks similar to:

Account Action

account1 action1

account1 action2

account1 action10

account2 action5

However I'm having some trouble getting the information back for a restricted subset in a simple stored procedure.

select Account, Action

from AccountAction

where ???

What I'm looking for is to get the first X accounts, with all their actions. So this will be a dynamic number of rows. So using the example table above if I passed in 1, I would get 3 rows (i.e. give me all rows for the first account).

(I don't mind that the account name will be in each row - it is pivoted elsewhere)

Do I need to use a ROWNUM or similar to restrict the rows? I'm sure this must be a simpler issue than I've found so far.

EDIT

The answers using TOP won't work, in the example I'd be wanting 3 rows returned if I said 'give me one (the first) account'. But how do I know there will be 3? Its dynamic. Also they may not be sequential, what if account1's action99 was at position 55 million in the results.

网友答案:
WITH
  SequencedData
AS
(
  SELECT
    DENSE_RANK() OVER (ORDER BY Account) AS account_sequence_id,
    *
  FROM
    AccountAction
)
SELECT
  *
FROM
  SequenceData
WHERE
  account_sequence_id = ???

Or, for multiples...

WHERE
  account_sequence_id BETWEEN 3 AND 5    -- For the 3rd, 4th and 5th accounts.
网友答案:
SELECT *
FROM AccountAction
WHERE account IN  (SELECT account
    FROM AccountAction
    GROUP BY account HAVING account BETWEEN *start-account* AND *end-account*
    ORDER BY account
)

Explanation: The subquery groups by the distinct accounts (and allows for more fine-grained selection criteria than a simple DISTINCT) and returns only those accounts. The outer SELECT gets you a variable number of rows depending on the distinct accounts fetched by the subquery.

EDIT: The above assumes that one can filter by the account field in AccountAction table; this is usually the case in tables that join a M:N relationship at DB level.

网友答案:

If I got the question right then if u want to select initial 10 rows then use

SELECT TOP 10 Account, Action
FROM AccountAction

or if u want some initial 20 percent records then use-

SELECT TOP 10 PERCENT Account, Action
FROM AccountAction
网友答案:

did you try TOP?

 declare @hoW_many int
 set @hoW_many = 10

 select top (@hoW_many)  * 
 from AccountAction
网友答案:

A simple sub-select with top keyword (and distinct), would give you all actions for first X accounts

select * from AccountAction
where Account in 
(select distinct top (@NumberOfAccounts) Account 
from AccountAction order by Account)
分享给朋友:
您可能感兴趣的文章:
随机阅读: