Для нахождения разницы между двумя значениями в SQL можно использовать различные функции и операторы, в зависимости от конкретной задачи.
Например, если необходимо найти разницу между двумя датами, можно использовать функцию DATEDIFF(). Эта функция возвращает количество временных единиц (например, дней, месяцев, лет) между двумя датами. Синтаксис функции выглядит следующим образом:
DATEDIFF(interval, startdate, enddate)
где interval – это временная единица, которую необходимо использовать для измерения разницы, startdate – это начальная дата, а enddate – это конечная дата.
Например, чтобы найти разницу между двумя датами в днях, можно использовать следующий запрос:
SELECT DATEDIFF(day, '2022-01-01', '2022-01-15') AS days_diff;
Этот запрос вернет значение 14, что означает, что между 1 января 2022 года и 15 января 2022 года прошло 14 дней.
Если же необходимо найти разницу между двумя числовыми значениями, можно использовать оператор вычитания -. Например, чтобы найти разницу между двумя числами в таблице orders, можно использовать следующий запрос:
SELECT price_paid - price_listed AS price_diff
FROM orders;
Этот запрос вернет столбец price_diff, содержащий разницу между значениями в столбцах price_paid и price_listed.
I have a table like this:
rowInt Value
2 23
3 45
17 10
9 0
....
The column rowInt values are integer but not in a sequence with same increament. I can use the following sql to list values by rowInt:
SELECT * FROM myTable ORDER BY rowInt;
This will list values by rowInt. How can get get the difference of Value between two rows with the result like this:
rowInt Value Diff
2 23 22 --45-23
3 45 -35 --10-45
9 0 -45 --0-45
17 10 10 -- 10-0
....
The table is in SQL 2005 (Miscrosoft)
asked Mar 11, 2009 at 13:43
David.Chu.caDavid.Chu.ca
37k63 gold badges148 silver badges190 bronze badges
4
SELECT
[current].rowInt,
[current].Value,
ISNULL([next].Value, 0) - [current].Value
FROM
sourceTable AS [current]
LEFT JOIN
sourceTable AS [next]
ON [next].rowInt = (SELECT MIN(rowInt) FROM sourceTable WHERE rowInt > [current].rowInt)
EDIT:
Thinking about it, using a subquery in the select (ala Quassnoi’s answer) may be more efficient. I would trial different versions, and look at the execution plans to see which would perform best on the size of data set that you have…
EDIT2:
I still see this garnering votes, though it’s unlikely many people still use SQL Server 2005.
If you have access to Windowed Functions such as LEAD(), then use that instead…
SELECT
RowInt,
Value,
LEAD(Value, 1, 0) OVER (ORDER BY RowInt) - Value
FROM
sourceTable
answered Mar 11, 2009 at 13:50
MatBailieMatBailie
82.7k18 gold badges102 silver badges137 bronze badges
2
SELECT rowInt, Value,
COALESCE(
(
SELECT TOP 1 Value
FROM myTable mi
WHERE mi.rowInt > m.rowInt
ORDER BY
rowInt
), 0) - Value AS diff
FROM myTable m
ORDER BY
rowInt
answered Mar 11, 2009 at 13:50
0
SQL Server 2012 and up support LAG / LEAD functions to access the previous or subsequent row. SQL Server 2005 does not support this (in SQL2005 you need a join or something else).
A SQL 2012 example on this data
/* Prepare */
select * into #tmp
from
(
select 2 as rowint, 23 as Value
union select 3, 45
union select 17, 10
union select 9, 0
) x
/* The SQL 2012 query */
select rowInt, Value, LEAD(value) over (order by rowInt) - Value
from #tmp
LEAD(value) will return the value of the next row in respect to the given order in “over” clause.
ChrisW
9,1331 gold badge20 silver badges33 bronze badges
answered Oct 4, 2016 at 19:50
AnsonmusAnsonmus
3252 silver badges9 bronze badges
3
If you really want to be sure of orders, use “Row_Number()” and compare next record of current record (take a close look at “on” clause)
T1.ID + 1 = T2.ID
You are basically joining next row with current row, without specifying “min” or doing “top”. If you have a small number of records, other solutions by “Dems” or “Quassanoi” will work fine.
with T2 as (
select ID = ROW_NUMBER() over (order by rowInt),
rowInt, Value
from myTable
)
select T1.RowInt, T1.Value, Diff = IsNull(T2.Value, 0) - T1.Value
from ( SELECT ID = ROW_NUMBER() over (order by rowInt), *
FROM myTable ) T1
left join T2 on T1.ID + 1 = T2.ID
ORDER BY T1.ID
answered Mar 12, 2009 at 1:34
dance2diedance2die
35.5k38 gold badges130 silver badges193 bronze badges
Does SQL Server support analytic functions?
select rowint,
value,
value - lag(value) over (order by rowint) diff
from myTable
order by rowint
/
answered Mar 12, 2009 at 1:39
David AldridgeDavid Aldridge
51.3k8 gold badges68 silver badges95 bronze badges
3
select t1.rowInt,t1.Value,t2.Value-t1.Value as diff
from (select * from myTable) as t1,
(select * from myTable where rowInt!=1
union all select top 1 rowInt=COUNT(*)+1,Value=0 from myTable) as t2
where t1.rowInt=t2.rowInt-1
LPL
16.8k6 gold badges50 silver badges95 bronze badges
answered Nov 23, 2009 at 12:02
Query to Find the date difference between 2 rows of a single column
SELECT
Column name,
DATEDIFF(
(SELECT MAX(date) FROM table name WHERE Column name < b. Column name),
Column name) AS days_since_last
FROM table name AS b
Thomas G
9,8017 gold badges27 silver badges40 bronze badges
answered May 27, 2016 at 17:09
1
I’d just make a little function for that. Toss in the two values you need to know the difference between and have it subtract the smaller from the larger value. Something like:
CREATE FUNCTION [dbo].[NumDifference]
( @p1 FLOAT,
@p2 FLOAT )
RETURNS FLOAT
AS
BEGIN
DECLARE @Diff FLOAT
IF @p1 > @p2 SET @Diff = @p1 - @p2 ELSE SET @Diff = @p2 - @p1
RETURN @Diff
END
In a query to get the difference between column a and b:
SELECT a, b, dbo.NumDifference(a, b) FROM YourTable
answered Jul 22, 2019 at 11:38
I have 2 queries in MS SQL that return a number of results using the COUNT function.
I can run the the first query and get the first result and then run the other one to get the other result, subtract them and find the results; however is there a way to combine all 3 functions and get 1 overall result
As in: run sql1 run sql2 run SQL3 (sql1-sql2)?….
I tried them with xxxx as a function but no luck.
asked Oct 19, 2009 at 14:41
2
You should be able to use subqueries for that:
SELECT
(SELECT COUNT(*) FROM ... WHERE ...)
- (SELECT COUNT(*) FROM ... WHERE ...) AS Difference
Just tested it:
Difference
-----------
45
(1 row(s) affected)
answered Oct 19, 2009 at 14:44
JoeyJoey
342k85 gold badges686 silver badges683 bronze badges
4
SELECT (SELECT COUNT(*) FROM t1) - (SELECT COUNT(*) FROM t2)
answered Oct 19, 2009 at 14:46
Gary McGillGary McGill
26.1k25 gold badges118 silver badges201 bronze badges
3
This will return the difference
SELECT COUNT(Attribute) - COUNT(DISTINCT Attribute) FROM table_name;
Dharman♦
30.3k22 gold badges84 silver badges132 bronze badges
answered Nov 26, 2019 at 21:41
1
I know this is an old post but here is another solution that fit best to my needs (tested on firebird)
SELECT c1-c2 from (select count(*) c1 from t1), (SELECT COUNT(*) c2 from t2);
answered Dec 9, 2016 at 8:12
Sven M.Sven M.
591 silver badge5 bronze badges
The query is like below :
SELECT (select COUNT(FIRSTNAME) FROM TRMDW.EMPLOYEE1) - (SELECT COUNT(DISTINCT FIRSTNAME) FROM TRMDW.EMPLOYEE1) as difference from dual;
answered Oct 3, 2018 at 13:42
0
This can be done in a single query:
SELECT COUNT(col_name) - COUNT(DISTINCT col_name) as Difference from table_name;
TT.
15.7k6 gold badges46 silver badges87 bronze badges
answered Mar 24, 2020 at 9:01
1
Just create an inline function with your query logic, and have it return the result. Pass in parameters as needed.
answered Oct 19, 2009 at 14:44
John LechowiczJohn Lechowicz
2,5733 gold badges21 silver badges34 bronze badges
select @result = (select count(0) from table1) - (select count(0) from table2)
answered Oct 19, 2009 at 14:45
Justin NiessnerJustin Niessner
241k40 gold badges406 silver badges536 bronze badges
SELECT
t1.HowManyInTable1
,t2.HowManyInTable2
,t1.HowManyInTable1 = t2.HowManyInTable2 Table1_minus_Table2
from (select count(*) HowManyInTable1 from Table1) t1
cross join (select count(*) HowManyInTable2 from Table2) t2
answered Oct 19, 2009 at 14:51
Philip KelleyPhilip Kelley
39.2k11 gold badges57 silver badges92 bronze badges
SELECT (count(*) from t1) - (count(*) from t2);
this worked for me.
Also if there is only one table you can also do:
SELECT (count(column1)) - count(column2)) from table;
marc_s
727k174 gold badges1325 silver badges1454 bronze badges
answered Mar 28, 2018 at 0:43
CsaxenaCsaxena
9016 silver badges3 bronze badges
The query is like below :
((SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(m,'/',2),'/',-1)
FROM ms WHERE ms.id=t.m_id)-(SELECT COUNT(id) FROM t AS tr WHERE tr.m_id=t.m_id)) AS remaining
answered Oct 22, 2017 at 7:40
