SQL group by issue

Question

这是我有个问题。下面是一个例子：

DateTime               Campaign
2011-06-01 08:55:00    Campaign1
2011-06-01 08:56:00    Campaign1
2011-06-01 08:57:00    Campaign2
2011-06-01 08:58:00    Campaign1
2011-06-01 08:59:00    Campaign1

我喜欢按活动分组，但只要是同一个就行。我期望的结果是：

DateTime               Campaign    Count
2011-06-01 08:55:00    Campaign1       2
2011-06-01 08:57:00    Campaign2       1
2011-06-01 08:58:00    Campaign1       2

每当我在campaign上分组时，我只得到一行Campaign1

Select *, Count(Campaign) as Count from myTable group by Campaign

结果：

DateTime               Campaign    Count
2011-06-01 08:55:00    Campaign1       4
2011-06-01 08:57:00    Campaign2       1

你知道我怎样才能得到想要的结果吗？

谢谢!

Answer 1

没有一种方法可以在标准ANSI SQL语句中执行您想要的操作。上面提到的关于存储过程或使用游标的内容是可以完成的-但您也可以使用游标将语句写入T-SQL或PL/SQL，以确定从开始开始时间和计数开始的每个语句的数量-在T-SQL (Microsoft SQL服务器)中类似这样：

USE [<database name here>]
DECLARE @tbl_result TABLE (Result_DT DATETIME, Campaign VARCHAR(25), unit_count int);
DECLARE @start_dt DATETIME,
        @current_campaign VARCHAR(25), --This can be any length needed - if your campaigns are over 25 chars long
        @record_dt DATETIME,
        @campaign VARCHAR(25),
        @cur_records CURSOR,
        @Record_Count INT;
SET @current_campaign = 'Start'
SET @cur_records = CURSOR
FOR SELECT DateTime, Campaign FROM myTable order by 1,2

OPEN @cur_records

FETCH NEXT FROM @cur_records INTO @record_dt, @campaign
WHILE @@FETCH_STATUS = 0
    BEGIN
       -- CHECK THE record to the Current Campaign for grouping
        IF @campaign <> @current_campaign
            BEGIN
              If @current_campaign <> 'Start'
                BEGIN
                    INSERT INTO @tbl_result 
                    VALUES (@start_dt, @current_campaign, @Record_Count)
                END
            SET @current_campaign = @campaign
            SET @start_dt = @record_dt
            SET @Record_Count = 1
            END
        ELSE
            BEGIN
                SET @Record_Count = @Record_Count + 1
            END
    END
    INSERT INTO @tbl_result  -- Inserting the last of the Records
    VALUES (@start_dt, @current_campaign, @Record_Count)
-- Now to display the results
SELECT Result_DT, Campaign, unit_count FROM @tbl_result order by 1, 2

现在请原谅我这个错误-但是如果它是Microsoft Box，它应该是您所需的基本结构

Answer 2

这是一个在SQL中很难解决的问题，而用其他方法很容易解决。我甚至不认为这可以在一个原始的SQL查询中完成。问题是group by不是单个列的属性的聚合，而是也依赖于其他列。您最好使用游标编写存储过程，或者在其他地方以编程方式进行存储过程。

Answer 3

您似乎希望按日期分组，然后进行竞选，如下所示：

SELECT DateTime, Campaign, Count(Campaign) as Count 
FROM myTable 
GROUP BY DateTime, Campaign