Sql как найти разность - Сайт, где вы сможете решить свои вопросы

Для нахождения разницы между двумя значениями в 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

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

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

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

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

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

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

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

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

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

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

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

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

Источник

Вам также может понравиться

Как вконтакте найти понравившиеся записи с телефона

Как исправить aeyrc dll

E127 continuation line over indented for visual indent как исправить

Добавить комментарий Отменить ответ