使用sql在同一列中的2行之间的差异 [英] Difference Between 2 rows in same column by using sql

问题描述

Hi,

month year    Date                    percentage        Total
7   2012    2012-07-31 23:59:59.000 2.28876597367919    90.5969864581347
8   2012    2012-08-31 23:59:59.000 8.25863055502575    98.8556170131604
12  2012    2012-12-31 23:59:59.000 1.1443829868396     100

如果没有 sept，oct，nov 月份的数据，则我们在所有列中都输入0.

如何做到这一点可以帮助我做到这一点，


预先表示感谢....

If there is no data for the month of sept , oct , nov then we put 0''s for all the columns.

How to do this can any one help me to do this,


Thank''s in advance....

推荐答案

您只需创建包含所有月份(仅1月至12月)的表即可.即使该月没有值，也可以使用它来选择整个月.

You simply create table with all the months (simply January to December). You can use that to select all month, even when there is no value for that month.

SELECT m.*, t.* FROM Months m, MyTable t
  WHERE t.year = 2012 
    AND m.month BETWEEN 6 AND 12
    AND t.month = m.month;

您可能想根据需要进行调整，但是希望基本思路很明确.

祝您好运！

You might want to adjust it to your needs but hopefully the basic idea is clear.

Good luck!

A说您首先需要获取/创建需要包含在结果集中的所有行的数据，因为不能从零开始创建行.之后，您将需要进行外部联接，以将月份联接到数据中的现有行.

虽然您可以创建包含月份的表，但另一种方法是即时生成日期.例如，请查看以下内容:在SQL中使用表值函数服务器 [ ^ ]

将TVF用作表格"时，您的查询可能类似于:

A said you first need to get/create the data for all the rows you need to include in the result set since rows cannot be created from nothing. After that you would have an outer join to join the months to existing rows in your data.

While you can create a table containing the months, another way is to generate date on-the-fly. For example have a look at the following: Using Table-Valued Functions in SQL Server[^]

When using TVF as a ''table'', your query could look something like:

SELECT ...
FROM GenerateMonths(date1, date2) a
LEFT OUTER JOIN SomeTable b
ON a.SomeColumn = b.SomeColumn
WHERE ...

创建表，

monthList

create a table,

monthList

month 
1    
2     
3   
.
.
.
12    

现在使用以下查询
@year为varchar(4)

Now use below query
@year as varchar(4)

select a.month, b.year, isnull(b.date,convert(datetime,@year + '-' + a.month + '-01')), isnull(per,0),isnull(total,0) 

from monthlist a
left join yourTbl b on a.month=b.month 

祝您编码愉快！
:)

Happy Coding!
:)

这篇关于使用sql在同一列中的2行之间的差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！