我正在寻找一种更好的方法来生成一个列表,只是使用sql而不是c#。这似乎很简单,我做错了
假设我有一个名为PhoneNumbers的表,其中包含号码/城市/州
示例(但有数百万个数字):
NUMBER CITY STATE
555-555-5555 New York NY
111-111-1111 Los Angeles CA
222-222-2222 Houston TX
333-333-3333 Miami FL
我需要一个查询,将从每个州返回10个电话号码
现在我只是单独运行对所有50个州的查询,然后将它们组合在一个数据集中,但它是一个大数据库,所以它很慢,我希望有一个简单的方法来收回我需要的数据。
下面的查询应该适用于Oracle和SQL Server,但不适用于MySQL:
with cte as
(select number, city, state,
row_number() over (partition by state order by state) rn
from yourtable)
select number,city,state
from cte
where rn <= 10
请注意,如果您需要按特定字段对前10行进行排序,则必须调整order by
以满足您的需求。
不同,但对于MSSQL:
with cteByStateas (
select row_number() over (partition by STATE order by CITY) as RowNo,
number,
city,
state
)
select *
from cteByState
where RowNo <= 10
请注意,您没有指定您想要的前10个数字,或者它们是否应该是随机的
对于mysql,这里的做法有点不同,使用变量
您可以简单地使用UNION ALL
子句。像这样:-
(SELECT NUMBER, CITY, STATE FROM TABLE_NAME WHERE STATE = 'NY' ORDER BY CITY LIMIT 10)
UNION ALL
(SELECT NUMBER, CITY, STATE FROM TABLE_NAME WHERE STATE = 'CA' ORDER BY CITY LIMIT 10)
UNION ALL
(SELECT NUMBER, CITY, STATE FROM TABLE_NAME WHERE STATE = 'TX' ORDER BY CITY LIMIT 10)
UNION ALL
(SELECT NUMBER, CITY, STATE FROM TABLE_NAME WHERE STATE = 'FL' ORDER BY CITY LIMIT 10)