将两个 select 语句中的值合并为一个

发布于 2024-12-05 12:17:37 字数 796 浏览 2 评论 0原文

我有一个用于记录方法调用的表。它有 LogId、MethodId、DateTime 列。

我需要编写一条 select 语句，对特定时间段内特定方法 ID 的所有日志进行计数，并显示不同时间段内特定方法的日志数量。

第一点很简单：

select
    l.[MethodId],
    count(l.[LogId]) as [Count]
from
    [Log] l (nolock)
where
    l.[DateTime] between @from and @to
    and l.[MethodId] in @methodIds
group by
    l.[MethodId]

但现在我需要该表中的第二列，Previous，如果它在单独的语句中，它看起来像这样：

select
    l.[MethodId],
    count(l.[LogId]) as [Previous]
from
    [Log] l (nolock)
where
    l.[DateTime] between @before and @from 
    and l.[MethodId] in @methodIds
group by
    l.[MethodId]

并非所有方法都会有两个时间段的日志，所以如果在这些情况下，连接将在 count/previous 列中插入 0，而不是它们为空。如果一个方法在这两个时期都没有任何日志也没关系。

我想看到的是一个表中的 MethodId, Count, Previous 。我怎样才能做到这一点？

原文

I have a table for logging method calls. It has LogId, MethodId, DateTime columns.

I need to write a select statement that counts all logs for specific method IDs over a specific time period and also show the number of logs for the specific methods over a different time period.

The first bit is simple:

select
    l.[MethodId],
    count(l.[LogId]) as [Count]
from
    [Log] l (nolock)
where
    l.[DateTime] between @from and @to
    and l.[MethodId] in @methodIds
group by
    l.[MethodId]

But now I need a second column in that table, Previous, which would look like this if it was in a separate statement:

select
    l.[MethodId],
    count(l.[LogId]) as [Previous]
from
    [Log] l (nolock)
where
    l.[DateTime] between @before and @from 
    and l.[MethodId] in @methodIds
group by
    l.[MethodId]

Not all methods will will have logs for the two time periods, so would be nice if the join would insert 0 in the count/previous columns in those cases instead of them being null. It's ok if a method doesn't have any logs in either periods.

What I want to see is MethodId, Count, Previous in one table. How do I make this happen?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

栀子花开つ 2024-12-12 12:17:37

类似于：

select 
    l.[MethodId], 
    sum(case when datetime between @from and @to then 1 else 0 end) as count,
    sum(case when datetime between @before and @from then 1 else 0 end) as previous
from 
    [Log] l
where 
    l.[DateTime] between @before and @to 
    and l.[MethodId] in @methodIds 
group by 
    l.[MethodId]

where 中的 BETWEEN 子句不会影响输出，但如果您在日期时间上有索引，则可能会影响性能。如果这个表可以变得很大，你可能应该有这样的索引。

Something like:

select 
    l.[MethodId], 
    sum(case when datetime between @from and @to then 1 else 0 end) as count,
    sum(case when datetime between @before and @from then 1 else 0 end) as previous
from 
    [Log] l
where 
    l.[DateTime] between @before and @to 
    and l.[MethodId] in @methodIds 
group by 
    l.[MethodId]

The BETWEEN clause in the where doesn't affect the output then, but it might affect performance if you have an index on datetime. And if this table can get big, you probably should have such an index.

回复收藏 0 原文

半透明的墙 2024-12-12 12:17:37

试试这个：

select
    l.[MethodId],
    count(isnull(l.[LogId],0)) as [Previous]
from
    [Log] l (nolock)
where
    l.[DateTime] between @before and @from 
    and l.[MethodId] in @methodIds
group by
    l.[MethodId]

Try this:

select
    l.[MethodId],
    count(isnull(l.[LogId],0)) as [Previous]
from
    [Log] l (nolock)
where
    l.[DateTime] between @before and @from 
    and l.[MethodId] in @methodIds
group by
    l.[MethodId]

回复收藏 0 原文

~没有更多了~