如何计算复杂sql查询中受影响的行？ [英] How to count the affected rows in complex sql queries?

问题描述

我必须在我的c＃Form应用程序上显示一些条目。我有一个SQL查询，给我结果。但是这个SQL查询正在加入另外两个表。以下是查询：

  SELECT  
 People.FirstName，
 People。 MiddleName，
 People.LastName，
 People.Class，
 LogonStats。 DateTime ，
 LogonStats.IP 
 
  FROM  
 LogonStats  INNER  加入人 ON  LogonStats.Logon = People.ID 
 
  WHERE   DateTime > = DATEADD（day，-7，GETDATE（））
 
  ORDER   BY   DateTime   DESC  

那么基本上这个查询是什么让我在过去1周访问该网站的人。我想知道有多少人参观过。我可以为一个表使用Count（）但是我无法使它在这个查询上工作。反正有没有让它工作？

解决方案

这将为您的查询添加唯一用户数

  WITH  stats  AS （
  SELECT  
 LogonStats。 DateTime ，
 LogonStats.IP 
 LogonStats.Logon 
  FROM  LogonStats 
  WHERE   DateTime > = DATEADD（day， -7，GETDATE（））
）
，cnt  AS （
  SELECT  Count（ DISTINCT  LogonStats.Logon）Unique_users 
  FROM  stats 
） 
  SELECT  
 People.FirstName，
 People.MiddleName， 
 People.LastName，
 People.Class，
 Stats。 DateTime ，
 Stats.IP，
 Unique_users 
  FROM 统计
 加入人 ON  LogonStats.Logon = People.ID 
  JOIN  cnt 
  ORDER   BY   DateTime   DESC  

I have to display a number of entries on my c# Form application. I have a sql query that gives me the result. However this sql query is joining two other tables. Here is the query:

SELECT  
People.FirstName, 
People.MiddleName,      
People.LastName, 
People.Class, 
LogonStats.DateTime, 
LogonStats.IP

FROM         
LogonStats INNER JOIN People ON LogonStats.Logon = People.ID

WHERE DateTime >= DATEADD(day,-7, GETDATE())

ORDER BY DateTime DESC

So what basically does this query is that gives me the people who visit the website in last 1 week. I'd like to know how many people visited. I can user Count() for one table however I couldn't make it work on this query. Is there anyway to make it work?

解决方案

This will add the number of unique users to your query

WITH stats AS (
    SELECT  
            LogonStats.DateTime, 
            LogonStats.IP
            LogonStats.Logon
    FROM    LogonStats
    WHERE   DateTime >= DATEADD(day,-7, GETDATE())
    )
,cnt AS (
    SELECT  Count(DISTINCT LogonStats.Logon) Unique_users
    FROM    stats
    )
SELECT  
        People.FirstName, 
        People.MiddleName,      
        People.LastName, 
        People.Class, 
        Stats.DateTime, 
        Stats.IP,
        Unique_users
FROM    Stats
JOIN    People ON LogonStats.Logon = People.ID
JOIN    cnt
ORDER BY DateTime DESC

这篇关于如何计算复杂sql查询中受影响的行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！