如何将数据组恢复10年 [英] how to retreieve data group by 10 years
问题描述
例如我有数据
年
1920
1931
1932
1921
1933 >
我想要的结果如
1920's
1920
1921
1930's
1931
1932
1933
基本上我想显示记录组10年差距,在sql查询中
这里1920年代和1930年代只是为了理解
如果我理解你的问题,你想拥有结果显示为
1920
1921
1931
1932
1933
而不是
1920
1921
1931
1932
1933
如果这是正确的,你绝不应该使用SQL格式化结果。而是使用调用端的功能,您可以在其中显示数据。例如,如果这是一个报表,请使用报表设计器的格式(分组)功能等。
SQL仅用于获取和修改数据。它是一种糟糕的语言,因为它不是为格式化而设计的。
正如Mika Wendelius在解决方案1中提到的那样,你应该使用报告工具,但是有一些小技巧可以得到类似的东西你想要。
创建 表 A
(
MyYear INT
)
INSERT INTO A(MyYear)
VALUES ( 1920 ),( 1931 ),( 1932 ),( 1921 ),( 1933 )
SELECT CONVERT ( VARCHAR ( 10 ),MyYear - (MyYear% 10 ))+ ' 's' < span class =code-keyword> AS TenYears,MyYear
FROM A
ORDER BY MyYear
结果你'得到:
TenYears MyYear
1920's 1920
1920's 1921
1930's 1931
1930年代1932年
1930年代1933年
For example I have data
Year
1920
1931
1932
1921
1933
I want result like
1920's
1920
1921
1930's
1931
1932
1933
So basically I want to display record group by 10 years gap, in sql query
here 1920's and 1930's just for understanding
If I understand your question correctly, you would like to have the result shown as
1920 1921 1931 1932 1933
instead of
1920 1921 1931 1932 1933
If that is correct, you should never do the formatting of the results using SQL. Instead use the capabilities of the calling side, where you show the data. For example if this is a report, use the formatting (grouping) capabilities of the report designer and so on.
SQL is designed solely to fetch and modify data. It's a poor language what comes to formatting since it's not designed for formatting.
As Mika Wendelius had mentioned in solution 1, you should use reporting tools, but there's small trick to get something similar you want to.
CREATE TABLE A ( MyYear INT ) INSERT INTO A (MyYear) VALUES (1920), (1931), (1932), (1921), (1933) SELECT CONVERT(VARCHAR(10), MyYear - (MyYear % 10)) + '''s' AS TenYears, MyYear FROM A ORDER BY MyYear
As a result you'll get:
TenYears MyYear 1920's 1920 1920's 1921 1930's 1931 1930's 1932 1930's 1933
这篇关于如何将数据组恢复10年的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!