请教个SQL写法，请赐教，谢谢！！！！！！

Question

本帖最后由 aspfeng9527 于 2011-06-28 17:08:45 编辑

Answer 1

;WITH  cte AS (
 SELECT ROW_NUMBER()OVER(ORDER BY  TIME ) AS rowid ,qudao_name,url,ref_url,flag,[TIME] FROM  Test_qudao
)SELECT *  INTO  Test_qudao_1 FROM cte 
GO
CREATE    FUNCTION fn_getname ( @time DATETIME )
RETURNS NVARCHAR(20)
AS BEGIN  
    RETURN ( SELECT qudao_name
             FROM   Test_qudao_1
             WHERE  rowid = ( SELECT    MAX(rowid) AS rowid
                              FROM      ( SELECT    MAX([rowid]) AS [rowid],
                                                    qudao_name
                                          FROM      Test_qudao_1
                                          WHERE     time <= @time
                                                    AND flag = 1
                                          GROUP BY  qudao_name
                                        ) a
                            )
           )
   END 
 
GO 
SELECT  NAME,
        SUM(pvcount) AS pvcount,
        SUM(toalsum) AS toalsum
FROM    ( SELECT    NAME,
                    COUNT(flag) AS pvcount,
                    ( SELECT    COUNT(rowid)
                      FROM      Test_qudao_1 b
                      WHERE     a.rowid = b.rowid
                                AND b.flag = 1
                    ) AS toalsum
          FROM      ( SELECT    *,
                                dbo.fn_getname(time) AS NAME
                      FROM      Test_qudao_1 a
                    ) a
          GROUP BY  a.NAME,
                    a.rowid
        ) a
GROUP BY NAME 
/*
NAME pvcount toalsum
百度 4 2
本网站 2 1
谷歌 2 1
*/
 求高手解答 。。

Answer 2

declare @qudao_name varchar(100),@ls varchar(100)
select @ls=qudao_name from (select top 1 qudao_name from  test_qudao where qudao_name<>'') as tb 
update Test_qudao set @qudao_name=@ls,
                 @ls=case when qudao_name='' then @ls else qudao_name end,
                 qudao_name=case when qudao_name='' then @qudao_name else qudao_name endselect qudao_name,count(*) pvcount,
       sum(case flag when 0 then 1 else 0 end) toalsum 
from Test_qudao group by qudao_name/*
qudao_name           pvcount     toalsum     
-------------------- ----------- ----------- 
百度                   4           2
本网站                  2           1
谷歌                   2           1（所影响的行数为 3 行）
*/