从SQL表获取记录计数 [英] Get Record Count From SQL table

问题描述

您好，



我有一张表，其中包含各种字段，如id（自动增量），SKU，标题，ImageName等等。我想要一个结果，它给出了与保存SKU匹配的记录总数，并且只返回一行该结果，包含任何1个ID，标题，相同SKU和ImageName的计数



请帮帮我我尝试了基本计数和分组条款，但它没有给我我想要的确切结果



我无法得到查询。如何构建它以便得到如下结果



计算SKU标题ID ImageName

解决方案

如果你有一个像这样的样本表...

 create table table1（
 id int identity（1,1），
 SKU varchar（10），
 Title varchar（ 30），
 ImageName varchar（30）
）
 
插入table1值
（'part1'，'apart'，'image11'），
（'part2'，'apart'，'image21'），
（'part1'，'apart'，'image12'），
（'part1'，'apart'，'image13'） 

您可以执行以下任何操作以获得相同的结果

 选择  top   1  id，SKU，Title，ImageName，（ select  count（*） 来自 table1 其中 SKU = '  part1'）
 来自 table1 
 其中 SKU = '  part1' 

  select   TOP   1  A.id，A.SKU，Title，ImageName，B.countofsku 
 来自（ SELECT  SKU，COUNT（*） AS  countofsku  FROM  table1  GROUP   BY  SKU）b 
  INNER   JOIN  table1 A  ON  A.SKU = B.SKU 
  WHERE  A.SKU = ' < span class =code-string> part1' 

;  WITH  CTE  AS  
（
  SELECT  SKU，COUNT（*） AS  countofsku 
  FROM  table1  GROUP   BY  SKU 
）
  SELECT   TOP   1  A.id，A.SKU，Title，ImageName，CTE.countofsku 
  FROM  CTE 
  INNER   JOIN  table1  ON  A.SKU = CTE.SKU 
 < span class =code-keyword> WHERE  A.SKU = '  part1' 

（编辑 - 错过了V1上最后一个查询的WHERE子句）



：< blockquote class =quote>

引用：

非常感谢它的答案，但现在我有一个更复杂的我想要的是我只想得到一些记录只说15条记录但是以相同的方式。



例如我想要前15条记录当我按下加载更多15和15等等......但是使用上面的输出

有一个很好的讨论这里 [ ^ ]关于返回的各种寻呼结果的方法。以下是基于前一次调用的最后一条记录可以完成的一种方式

 USE [沙盒] 
 GO 
 
 SET ANSI_NULLS ON 
 GO 
 
 SET QUOTED_IDENTIFIER ON 
 GO 
 
 CREATE PROCEDURE [dbo]。[spPageResults] 
 @ LinesPer INT，
 @SKU varchar（10）， 
 @PrevID INT OUTPUT 
 AS 
 BEGIN 
; WITH CTE AS 
（
 SELECT SKU，COUNT（*）AS countofsku 
 FROM table1 GROUP BY SKU 
）
 SELECT TOP（@RowsPer）A.​​id，A.SKU，Title，ImageName，CTE.countofsku 
 FROM CTE 
 INNER JOIN table1 A ON A. SKU = CTE.SKU 
 WHERE A.SKU = @SKU 
和A.id> @PrevID 
 ORDER BY A.id 
 
 SELECT TOP（@ LinesPer）@PrevID = id 
 FROM table1 
 WHERE SKU = @SKU 
和id > @PrevID 
 ORDER BY id 
 
 END 
 
 GO 

你可以像这样叫

  DECLARE   @ RowsPer   AS   INT ; 
  SET   @ RowsPer  =  15 ; 
 
  DECLARE   @ PrevID   INT  
  SET   @ PrevID  =  0  
 
  EXECUTE  dbo.spPageResults  5 ，'  part1'， @ PrevID   OUTPUT  
  SELECT   @ PrevID  
 < span class =code-keyword> EXECUTE  dbo.spPageResults  5 ，'  part1'， @ PrevID   OUTPUT  
  SELECT   @ PrevID  

@PrevID将在t上显示7他是第一个例子，第二个是15个。



另一种方法可能是（效率较低）：

  - 传递给存储过程的参数
 DECLARE @Page AS INT; 
 DECLARE @RowsPer AS INT; 
 SET @Page = 2; 
 SET @RowsPer = 15; 
 
 
 DECLARE @Start AS INT =（（@ page  -  1）* @RowsPer）+ 1 
 DECLARE @End AS INT = @Start + @RowsPer  -  1 
 
打印@Start 
打印@End 
 
 
 
; WITH CTE AS 
（
 SELECT SKU，COUNT（ *）AS countofsku 
 FROM table1 GROUP BY SKU 
），
 CTE2 AS 
（
 SELECT id，SKU，Title，ImageName 
，ROW_NUMBER（） OVER（ORDER BY ID）AS RowNum 
 FROM table1 A 
 WHERE SKU ='part1'
）
 SELECT CTE2.id，CTE2.SKU，CTE2.Title，CTE2.ImageName ，CTE.countofsku，RowNum 
来自CTE2 
 INNER JOIN CTE ON CTE2.SKU = CTE.SKU 
 AND RowNum BETWEEN @Start AND @End 

 选择  top   1  t1.id，t1.Title，t1.ImageName，COUNT（t2.SKU）
 来自 table1 t1 < span class =code-keyword> inner   join  table1 t2 
  ON  t1.SKU = t2.SKU 
 其中 t2.SKU = '  part1' 
  group   by  t1.id，t1.Title，t1.ImageName 

尝试使用top 1子句获取单个记录。

Hello,

I have a table with various fields like id(Auto Increment),SKU,Title,ImageName and many others. I want a result which gives me Total Number of Records which matches With Save SKU and Return only one row for that result with any 1 ID,Title,Count of Same SKU and ImageName

Please Help Me I tried Basic Count and Group by clause but its not giving me exact result as i want

I am unable to get the query. how to frame it so i get the result as below

Count SKU Title ID ImageName

解决方案

If you have a sample table like this ...

create table table1(
	id int identity(1,1),
	SKU varchar (10),
	Title varchar(30),
	ImageName varchar(30)
)

insert into table1 values
('part1','apart','image11'),
('part2','apart','image21'),
('part1','apart','image12'),
('part1','apart','image13')

You can do any of the following to get the same results

select top 1 id, SKU, Title, ImageName, (select count(*) from table1 where SKU = 'part1')
from table1
where SKU = 'part1'

select TOP 1 A.id, A.SKU, Title, ImageName, B.countofsku
from (SELECT SKU, COUNT(*) AS countofsku FROM table1 GROUP BY SKU) b
INNER JOIN table1 A ON A.SKU = B.SKU
WHERE A.SKU = 'part1'

;WITH CTE AS
(
    SELECT SKU, COUNT(*) AS countofsku
    FROM table1 GROUP BY SKU
)
SELECT TOP 1 A.id, A.SKU, Title, ImageName, CTE.countofsku
FROM CTE
INNER JOIN table1 A ON A.SKU = CTE.SKU
WHERE A.SKU = 'part1'

(Edit - missed the WHERE clause on the last query on V1)

[EDIT - in response to further OP question in comments]:

Quote:

Thanks alot for the answer it worked but now i have one more complexity here what i want is i only want to get some records say only 15 records but in the same manner.

For example i want first 15 records and when i press load more next 15 and old 15 and so on... but with the above output

There is an excellent discussion here[^] on the various ways of paging results returned. Here is one way it can be done based on the last record of the previous call

USE [sandbox]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[spPageResults]
@RowsPer INT,
@SKU varchar(10),
@PrevID INT OUTPUT
AS
BEGIN
	;WITH CTE AS
	(
		SELECT SKU, COUNT(*) AS countofsku
		FROM table1 GROUP BY SKU
	)
	SELECT TOP (@RowsPer) A.id, A.SKU, Title, ImageName, CTE.countofsku
	FROM CTE
	INNER JOIN table1 A ON A.SKU = CTE.SKU
	WHERE A.SKU = @SKU
	and A.id > @PrevID
	ORDER BY A.id

	SELECT TOP (@RowsPer) @PrevID = id       
	FROM table1
	WHERE SKU = @SKU
	and id > @PrevID
	ORDER BY id

END

GO

You can call it like this

DECLARE @RowsPer AS INT;
SET @RowsPer = 15;

DECLARE @PrevID INT
SET @PrevID = 0

EXECUTE dbo.spPageResults 5, 'part1', @PrevID OUTPUT
SELECT @PrevID
EXECUTE dbo.spPageResults 5, 'part1', @PrevID OUTPUT
SELECT @PrevID

@PrevID will show 7 on the first instance and 15 on the 2nd.

An alternative approach could be (less efficient):

-- Parameters to pass into the Stored Procedure
DECLARE @Page AS INT;
DECLARE @RowsPer AS INT;
SET @Page = 2;
SET @RowsPer = 15;


DECLARE @Start AS INT = ((@Page - 1) * @RowsPer) + 1
DECLARE @End AS INT = @Start + @RowsPer - 1

print @Start
print @End



;WITH CTE AS
(
    SELECT SKU, COUNT(*) AS countofsku
    FROM table1 GROUP BY SKU
),
CTE2 AS 
(
	SELECT id, SKU, Title, ImageName
	,ROW_NUMBER() OVER(ORDER BY ID) AS RowNum
	FROM table1 A
	WHERE SKU = 'part1'
)
SELECT CTE2.id, CTE2.SKU, CTE2.Title, CTE2.ImageName, CTE.countofsku, RowNum
FROM CTE2
INNER JOIN CTE ON CTE2.SKU = CTE.SKU
AND RowNum BETWEEN @Start AND @End

select top 1 t1.id,t1.Title, t1.ImageName, COUNT(t2.SKU)  
from table1 t1 inner join table1 t2
ON t1.SKU= t2.SKU 
where t2.SKU = 'part1' 
group by t1.id,t1.Title, t1.ImageName

Try to use the top 1 clause to get the single record.

这篇关于从SQL表获取记录计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！