Sql查询获取每行更改行值的次数 [英] Sql query to get how many times row values changed for each row

问题描述

我在SQLserver数据库中有以下表格

我想获得RAM更改的时间

   -    ---------------------  
 sr Machinename RAM 
   -    ------------------ -   
  1 。 Comp-1  20  
  2 。 Comp-2  40  
  3 。 Comp-1  15  
  4 。 Comp-1  10  
  5 。 Comp-2  40  
  6 。 Comp-2  30  
   -     - ------------------  

我想要关注输出

< pre lang =SQL> - ----------------- ----------------------
sr <机器名号 span class =code-keyword> time RAM已更改
- ---- -----------------------------------
1 。 Comp-1 3
2 。 Comp-2 2
- - -------------------------------------

注意：Comp-2有3行，但1行具有相同的RAM值，因此不计入更改值



我有什么试过：

 WITH CTE AS（
 SELECT ROW_NUMBER（）OVER（按名称分区ORDER BY RAM desc） as rowid，Compname，RAM 
 FROM tblA）
 select * from cte 

解决方案

取决于你的意思通过获取RAM已更改的时间...



试试这个：

  SELECT  Machinename，COUNT（RAM） AS  CountOfRAM 
  FROM  YourTable 
  GROUP   BY 计算机名

或：

  SELECT  Machinename，COUNT（ DISTINCT  RAM）< span class =code-keyword> AS  CountOfRAM 
  FROM  YourTable 
  GROUP   BY 计算机名

I have following table in SQLserver database
I want to get number of time RAM has changed

-----------------------
sr  Machinename   RAM
----------------------
1.  Comp-1        20
2.  Comp-2        40
3.  Comp-1        15
4.  Comp-1        10
5.  Comp-2        40
6.  Comp-2        30 
----------------------

I want following output

-----------------------------------------
sr  Machinename   No.of time RAM changed
-----------------------------------------
1.  Comp-1        3
2.  Comp-2        2 
-----------------------------------------

NOTE:Comp-2 has 3 rows but 1 row having same RAM value so its not counted as changed value

What I have tried:

WITH CTE AS(
  SELECT ROW_NUMBER() OVER(PARTITION BY Compname ORDER BY RAM desc) as rowid,Compname, RAM
	FROM tblA) 
	select * from cte

解决方案

Depending on what you mean by "to get number of time RAM has changed"...

Try this:

SELECT Machinename,  COUNT(RAM) AS CountOfRAM
FROM YourTable
GROUP BY Machinename

or:

SELECT Machinename, COUNT(DISTINCT RAM) AS CountOfRAM
FROM YourTable
GROUP BY Machinename

这篇关于Sql查询获取每行更改行值的次数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！