All geek questions in one place

How to count sql quantity in an extra table inside sql stored procedure?

I have a stored proc (called sprocGetArticles) that returns a list of articles from an article table. This saved proc has no parameters.

Users can leave comments for each article, and I store these comments in the comment table associated with the article identifier.

Is there a way to make a comment counter for each articleid in the returned list from the sprocGetArticles stored procedure, so I only need to make one call to the database?

My problem is that I need an article id to make an account, which I seem to be unable to declare.

Is this a better approach?

+3
source share
7 answers

, , , . , , , !

SQL Server 2005 , , , , . - , !

CREATE FUNCTION dbo.CountComments(@ArticleID INT)
RETURNS INT 
WITH SCHEMABINDING
AS BEGIN
    DECLARE @ArticleCommentCount INT

    SELECT @ArticleCommentCount = COUNT(*)
    FROM dbo.ArticleComments
    WHERE ArticleID = @ArticleID

    RETURN @ArticleCommentCount
END
GO

:

ALTER TABLE dbo.Articles
    ADD CommentCount AS dbo.CountComments(ArticleID)

:

SELECT ArticleID, ArticleTitle, ArticlePostDate, CommentCount 
FROM dbo.Articles

, , !: -)

ALTER TABLE dbo.Articles
    ADD CommentCount AS dbo.CountComments(ArticleID) PERSISTED

, , ! , , XML, , INT - .

! SQL Server.

+1

SQL . . , :

SELECT a.*, (
  SELECT COUNT(*)
  FROM Comments c
  WHERE c.article_id = a.article_id) AS CountComments
  FROM Articles a;

, , .

+5

, , , ( , SQL Server 2005:

WITH CommentCounts AS
(
   SELECT COUNT(*) CommentCount, ac.ArticleID
   FROM Articles a
   INNER JOIN ArticleComments ac
      ON ac.ArticleID = a.ID
   GROUP BY ac.ArticleID
)

SELECT a.*,
       c.CommentCount
FROM Articles a
INNER JOIN CommentCounts c
   ON a.ID = c.ArticleID

CTE. : http://msdn.microsoft.com/en-us/library/ms190766.aspx

+2

, - , ? , :

  SELECT a.ArticleId
       , a.ArticleName
       , (other a columns)
       , COUNT(*)
    FROM Articles a
         LEFT JOIN Comments c
                ON c.ArticleId = a.ArticleId
GROUP BY a.ArticleId
       , a.ArticleName
       , (other a columns);
+2

SQL Server 2005+ Oracle 9i +:

WITH COMMENT_COUNT AS (
      SELECT ac.article_id
             COUNT(ac.*) 'numComments'
        FROM ARTICLE_COMMENTS ac
    GROUP BY ac.article_id)
SELECT t.description,
       cc.numComments
  FROM ARTICLES t
  JOIN COMMENT_COUNT cc ON cc.article_id = t.article_id

SQL Server (CTE); Oracle Subquery.

:

SELECT t.description,
       cc.numComments
  FROM ARTICLES t
  JOIN (SELECT ac.article_id
               COUNT(ac.*) 'numComments'
          FROM ARTICLE_COMMENTS ac
      GROUP BY ac.article_id) cc ON cc.article_id = t.article_id

SELECT , , .

+1

, , , ( , SQL Server). , , , - , . SQL Server, , - :

CREATE TABLE smb_header (keycol INTEGER NOT NULL
                        , name1 VARCHAR2(255)
                        , name2 VARCHAR2(255));

INSERT INTO smb_header
  VALUES (1
        , 'This is column 1'
        , 'This is column 2'
         );

INSERT INTO smb_header
   SELECT (SELECT MAX(keycol)
             FROM smb_header
          ) + keycol
        , name1
        , name2
     FROM smb_header;
REM (repeat 20 times to generate ~1 million rows)

ALTER TABLE smb_header ADD PRIMARY KEY (keycol);

CREATE TABLE smb_detail (keycol INTEGER
                        , commentno INTEGER
                        , commenttext VARCHAR2(255));

INSERT INTO smb_detail
   SELECT keycol
        , 1
        , 'A comment that describes this issue'
     FROM smb_header;

ALTER TABLE smb_detail ADD PRIMARY KEY (keycol, commentno);

ALTER TABLE smb_detail ADD FOREIGN KEY (keycol) 
                           REFERENCES smb_header (keycol);

INSERT INTO smb_detail
   SELECT keycol
        , (SELECT MAX(commentno)
             FROM smb_detail sd2
            WHERE sd2.keycol = sd1.keycol
          ) + commentno
        , 'A comment that follows comment number ' 
          + CAST(sd1.commentno AS VARCHAR(32))
     FROM smb_detail sd1
    WHERE MOD(keycol, 31) = 0;

REM repeat 5 times, to create some records that have 64 comments
REM where others have one.

1 , - 1 64 .

( , , ) :

alter table dbo.smb_header add CommentCountPersist as dbo.CountComments(keycol)

, PERSISTED , - SQL Server , , , PERSISTED :

Msg 4934, Level 16, State 3, Line 1
Computed column 'CommentCountPersist' in table 'smb_header' cannot be 
persisted because the column does user or system data access.

- , SQL Server , , , , , , .

. #holder : , , , , Mgmt Studio.

  SELECT h.keycol
       , h.name1
       , CommentCount
    INTO #holder
    FROM smb_header h
   WHERE h.keycol < 0

. :

  INSERT
    INTO #holder
  SELECT h.keycol
       , h.name1
       , CommentCount
    FROM smb_header h
   WHERE h.keycol between 5000 and 10000

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
Table 'Worktable'. Scan count 1, logical reads 10160, physical reads 0, 
                   read-ahead  reads 0, lob logical reads 0, 
                   lob physical reads 0, lob read-ahead reads 0.
Table 'smb_header'. Scan count 1, logical reads 44, physical reads 0, 
                    read-ahead reads 0, lob logical reads 0, 
                    lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 265 ms,  elapsed time = 458 ms.

(5001 row(s) affected)
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

GROUP BY, :

  INSERT
    INTO #holder
  SELECT h.keycol
       , h.name1
       , COUNT(*)
    FROM smb_header h
       , smb_detail d 
   WHERE h.keycol between 5000 and 10000
     AND h.keycol = d.keycol 
GROUP BY h.keycol, h.name1

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
Table 'smb_header'. Scan count 1, logical reads 44, physical reads 0, 
                    read-ahead reads 0, lob logical reads 0, 
                    lob physical reads 0, lob read-ahead reads 0.
Table 'smb_detail'. Scan count 1, logical reads 366, physical reads 0, 
                    read-ahead reads 0, lob logical reads 0, 
                    lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 15 ms,  elapsed time = 13 ms.

(5001 row(s) affected)
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

SELECT, Remus, , GROUP BY ( ).

, . , (*) , .

, - . , marc_s .

0

. ( , ), , - , , , , , .

 SELECT h.keycol
       , h.name1
       , COUNT(*)
    FROM smb_header h
       , smb_detail d 
   WHERE h.keycol between 5000 and 10000
     AND h.keycol = d.keycol 
GROUP BY h.keycol, h.name1

 SELECT h.keycol
       , h.name1
       , CommentCount
    FROM smb_header h
   WHERE h.keycol between 5000 and 10000

: 25% , 75% , . , , 3 .

SQL Server 2008 http://i29.tinypic.com/140cl79.jpg

Dell Desktop, Vista Business x64 1 (SP1), 4 , SQL Server 2008 Developer Edition.

: SQL Server, , : , SQL Server . , SQL Server , "keycol" . , ( , , JOIN ), SQL- -, . /?

0
source

Source: https://habr.com/ru/post/1712969/

More articles:


All Articles