blocks|key|2001576|text|select+*+from+TABLE_NAME
where+$partition.fpartfunction(date)+=+1|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|2001577|这将返回分区1中的所有数据。|unstyled|2001578|如果你想从你可以这样做的日期检索它，|2001579|CREATE+PARTITION+FUNCTION+PF_Date(date)+AS+RANGE+LEFT+FOR+VALUES('2017-01-01','2018-01-01');|2001580|--first+partition
SELECT+*
FROM+schema.table_name
WHERE+key_column+<=+'2017-01-01';

--second+partition
SELECT+*
FROM+schema.table_name
WHERE+key_column+>+'2017-01-01'+AND+key_column+<=+'2018-01-01';

--last+partition
SELECT+*
FROM+schema.table_name
WHERE+key_column+>+'2018-01-01';|2001581|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|P|8|@]|9|@]|A|$]]|$1|G|3|H|5|F|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$B|C]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$B|C]]|$1|M|3|-4|5|F|7|T|8|@]|9|@]|A|$]]]|N|$]]

<pre><code>select * from TABLE_NAME
where $partition.fpartfunction(date) = 1
</code></pre>

This returns all the data in partition 1.

If you want to retrieve it from the date you can do as below,

<pre><code>CREATE PARTITION FUNCTION PF_Date(date) AS RANGE LEFT FOR VALUES('2017-01-01','2018-01-01');
</code></pre>

<pre><code>--first partition
SELECT *
FROM schema.table_name
WHERE key_column &lt;= '2017-01-01';

--second partition
SELECT *
FROM schema.table_name
WHERE key_column &gt; '2017-01-01' AND key_column &lt;= '2018-01-01';

--last partition
SELECT *
FROM schema.table_name
WHERE key_column &gt; '2018-01-01';
</code></pre>

blocks|key|1790927|text|我希望这能满足你的需要：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1790928|CREATE+PROCEDURE+USP_GetRowsWithinPartition+(@InputDate+DATE)
AS
BEGIN
DECLARE+@TV+TABLE+
(StartDate+DATE,+
EndDate+DATE
)
INSERT+INTO+@TV+(StartDate,+EndDate)
SELECT+TOP+1
CAST((CASE
++++WHEN+(DATEPART(MONTH,+@InputDate))+=+7+AND+(DATEPART(DAY,+@InputDate))+>+2
++++THEN+DATEADD(YEAR,+-1,+CONVERT(DATE,+prv.Value,+116))
++++++++WHEN+(DATEPART(MONTH,+@InputDate))+>+7+AND+(DATEPART(DAY,+@InputDate))+>=+1
++++++++THEN+DATEADD(YEAR,+-1,+CONVERT(DATE,+prv.Value,+116))
++++ELSE+prv.Value
++++END)+AS+DATE)+AS+StartDate,
CAST((CASE
++++WHEN+(DATEPART(MONTH,+@InputDate))+=+7+AND+(DATEPART(DAY,+@InputDate))+>+2
++++THEN+prv.Value
++++++++WHEN+(DATEPART(MONTH,+@InputDate))+>+7+AND+(DATEPART(DAY,+@InputDate))+>=+1
++++++++THEN+prv.Value
++++ELSE+DATEADD(YEAR,+1,+CONVERT(DATE,+prv.Value,+116))
++++END+)+AS+DATE)+AS+EndDate
FROM+sys.partition_functions+pf
INNER+JOIN+sys.partition_schemes+AS+ps+ON+ps.function_id+=+pf.function_id
INNER+JOIN+sys.destination_data_spaces+AS+dds+ON+dds.partition_scheme_id+=+ps.data_space_id
INNER+JOIN+sys.dm_db_partition_stats+pstats+ON+pstats.partition_number+=+dds.destination_id
INNER+JOIN+sys.partitions+p+ON+p.partition_id+=+pstats.partition_id
INNER+JOIN+sys.data_spaces+AS+ds+ON+dds.data_space_id+=+ds.data_space_id
INNER+JOIN+sys.partition_range_values+AS+prv+ON+pf.function_id+=+prv.function_id+
WHERE+pstats.object_id+=+OBJECT_ID('YourTable')
GROUP+BY+prv.boundary_id,+prv.value
ORDER+BY+ABS(DATEDIFF(DAY,+@InputDate,+Convert(DATE,+value,+116)))

SELECT+yt.*+FROM+YourTable+yt
INNER+JOIN+@TV+t+ON+yt.DateColumn>=t.StartDate+AND+yt.DateColumn+<+t.EndDate
END

EXEC+USP_GetRowsWithinPartition+'2015-08-01'++++|code-block|syntax|javascript|1790929|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I hope this may work for your need:

<pre><code>CREATE PROCEDURE USP_GetRowsWithinPartition (@InputDate DATE)
AS
BEGIN
DECLARE @TV TABLE 
(StartDate DATE, 
EndDate DATE
)
INSERT INTO @TV (StartDate, EndDate)
SELECT TOP 1
CAST((CASE
 WHEN (DATEPART(MONTH, @InputDate)) = 7 AND (DATEPART(DAY, @InputDate)) &gt; 2
 THEN DATEADD(YEAR, -1, CONVERT(DATE, prv.Value, 116))
 WHEN (DATEPART(MONTH, @InputDate)) &gt; 7 AND (DATEPART(DAY, @InputDate)) &gt;= 1
 THEN DATEADD(YEAR, -1, CONVERT(DATE, prv.Value, 116))
 ELSE prv.Value
 END) AS DATE) AS StartDate,
CAST((CASE
 WHEN (DATEPART(MONTH, @InputDate)) = 7 AND (DATEPART(DAY, @InputDate)) &gt; 2
 THEN prv.Value
 WHEN (DATEPART(MONTH, @InputDate)) &gt; 7 AND (DATEPART(DAY, @InputDate)) &gt;= 1
 THEN prv.Value
 ELSE DATEADD(YEAR, 1, CONVERT(DATE, prv.Value, 116))
 END ) AS DATE) AS EndDate
FROM sys.partition_functions pf
INNER JOIN sys.partition_schemes AS ps ON ps.function_id = pf.function_id
INNER JOIN sys.destination_data_spaces AS dds ON dds.partition_scheme_id = ps.data_space_id
INNER JOIN sys.dm_db_partition_stats pstats ON pstats.partition_number = dds.destination_id
INNER JOIN sys.partitions p ON p.partition_id = pstats.partition_id
INNER JOIN sys.data_spaces AS ds ON dds.data_space_id = ds.data_space_id
INNER JOIN sys.partition_range_values AS prv ON pf.function_id = prv.function_id 
WHERE pstats.object_id = OBJECT_ID('YourTable')
GROUP BY prv.boundary_id, prv.value
ORDER BY ABS(DATEDIFF(DAY, @InputDate, Convert(DATE, value, 116)))

SELECT yt.* FROM YourTable yt
INNER JOIN @TV t ON yt.DateColumn&gt;=t.StartDate AND yt.DateColumn &lt; t.EndDate
END

EXEC USP_GetRowsWithinPartition '2015-08-01' 
</code></pre>

blocks|key|1790931|text|(MySQL)|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1790932|访问特定的PARTITION几乎没有任何用处。您应该让WHERE子句选择使用哪个分区。|offset|length|style|CODE|1790933|此外，分区的用途很少。我建议您忽略该功能，直到您有了大量的经验在您的腰带。|1790934|任何回答你的问题..。是的，这应该是可行的：|1790935|SELECT+*+FROM+TABLE_NAME++PARTITION(partitionId);|code-block|syntax|javascript|1790936|但直到5.6才能得到这一数字。|1790937|可能需要引用分区is，特别是如果它是一个数字。如需进一步研究，请提供SHOW+CREATE+TABLE。|1790938|entityMap^0|0|5|9|R|5|0|0|0|0|0|Y|H|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|X|8|@$D|Y|E|Z|F|G]|$D|10|E|11|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|12|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|13|8|@]|9|@]|A|$]]|$1|L|3|M|5|N|7|14|8|@]|9|@]|A|$O|P]]|$1|Q|3|R|5|6|7|15|8|@]|9|@]|A|$]]|$1|S|3|T|5|6|7|16|8|@$D|17|E|18|F|G]]|9|@]|A|$]]|$1|U|3|-4|5|6|7|19|8|@]|9|@]|A|$]]]|V|$]]

(MySQL)

There is virtually no use for accessing a specific <code>PARTITION</code>. You should let the <code>WHERE</code> clause pick which partition(s) to use.

Furthermore, there are very few uses for partitioning. I recommend that you ignore that feature until you have a non-trivial amount of experience under your belt.

Any, to answer your question... Yes, this should work:

<pre><code>SELECT * FROM TABLE_NAME PARTITION(partitionId);
</code></pre>

But that was not available until 5.6.

The partitionid may need to be quoted, especially if it is a number. For further research, please provide <code>SHOW CREATE TABLE</code>.

I'm new to <code>SQL Server partitioning</code>. I have tried a some of the tutorials and finally I got <a href="https://www.tutorialgateway.org/table-partitioning-in-sql-server/" rel="nofollow noreferrer">this</a> based on that created a partition but the problem is that I can't retrieve the data's based on partition like in <code>MYSQL</code> query 

<pre><code>SELECT * FROM TABLE_NAME PARTITION(partitionId)
</code></pre>

and also tried this query in <code>SQL Server</code> 

<pre><code>SELECT $PARTITION.partition_function (partition_function_id)
</code></pre>

<blockquote>
 EDIT:
</blockquote>

<a href="https://i.stack.imgur.com/nLadS.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/nLadS.png" alt="enter image description here"></a>

<blockquote>
 The picture shows the data in table I have created partition based on Date, I like to retrieve data based on partition name, which is if partition name is 20170203 I am excepting results on that date.
</blockquote>

Retrieve a data from a partition in SQL Servertable

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我是SQL Server partitioning的新手。我尝试了一些教程，最后我得到了基于它的，创建了一个分区，但问题是，我不能像在MYSQL查询中那样根据分区检索数据SELECT * FROM TABLE_NAME  PARTITION(partitionId)并在SQL Server中尝试了这个查询。SELECT...

问从SQL Servertable表中的分区检索数据
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问从SQL Servertable表中的分区检索数据EN