SQL 按问题分组

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(微软 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，它应该是你需要的基本结构