T-SQL问题

Question

DROP PROC countVipUserData
CREATE PROC countVipUserData
@time1 varchar(10),
@time2 varchar(10),
@findByDay int, --1表示按日计算，2表示按月计算
@orderby int, --1:表示按照新增用户 2：表示按照总用户数，3：表示按照最高在线用户数
@order int --1:表示升序，2：表示降序
AS
DECLARE @SQL VARCHAR(8000)
DECLARE @WHERE VARCHAR(500)
DECLARE @WHERE2 VARCHAR(500)
--得到传入的年份
select * into #temp from  (select @time1 + t.number [year] from master..spt_values t where t.type='P' and @time1 + t.number <= @time2)tb
--组装成日期，必须按每个月的天数计算 比如闰年与平年的二月份天数
if(@findByDay = 1)
begin
select 
[time] ,
count(visit_time) '访问次数',
'老用户免费下载' = ( --在user_info_personal表中查询, 日期在今天之前的用户，都统称老用户.  
select count(user_act_consumeLog.user_id) from user_info_personal inner join User_act_consumeLog on (user_info_personal.id = user_id  and user_info_personal.reg_date < getdate()) inner join chl_resource on (product_id = chl_resource.id and chl_resource.price = 0)  where convert(varchar(10),down_time,120) = datetimetable.[time]
),
'老用户收费下载' = (
select count(user_act_consumeLog.user_id) from user_info_personal inner join User_act_consumeLog on (user_info_personal.id = user_id and user_info_personal.reg_date < getdate())  inner join chl_resource on (product_id = chl_resource.id and chl_resource.price > 0)  where  convert(varchar(10),down_time,120) = datetimetable.[time]
),
'新用户免费下载' = (
select count(user_act_consumeLog.user_id) from user_info_personal inner join User_act_consumeLog on (user_info_personal.id = user_id  and user_info_personal.reg_date = getdate())  inner join chl_resource on (product_id = chl_resource.id and chl_resource.price = 0) where convert(varchar(10),down_time,120) = datetimetable.[time]
),
'新用户收费下载' = (
select count(user_act_consumeLog.user_id) from user_info_personal inner join User_act_consumeLog on (user_info_personal.id = user_id and user_info_personal.reg_date = getdate())  inner join chl_resource on (product_id = chl_resource.id and chl_resource.price > 0) where convert(varchar(10),down_time,120) = datetimetable.[time]
),
'老用户数' = (
select count(id) from user_info_personal where convert(varchar(10),reg_date,120) = datetimetable.[time] and user_info_personal.reg_date < getdate()
),
'新增用户数' = (
select count(id)-(select count(id) from user_info_personal where convert(varchar(10),reg_date,120) = datetimetable.[time] and user_info_personal.reg_date < getdate()) from user_info_personal where convert(varchar(10),reg_date,120) = datetimetable.[time] and user_info_personal.reg_date < getdate()
)
from 
( select convert(varchar(10),c.[year])+'-'+(case when len(a.number) = 1 then '0'+convert(varchar(10),a.number) else convert(varchar(10),a.number) end)+'-'+convert(varchar(10),b.number) [time] from master..spt_values a,master..spt_values
 b, #temp c where a.name is null and a.number between 1 and 12 and b.name is null and b.number between 1 and
(case when a.number+1=13 then 31 else  (
(select right(convert(varchar(5),(select dateadd(day,-1,convert(char(10),c.[year])+'-'+convert(char(10),(a.number+1))+'-01'))),2)) 
)end)
) datetimetable
left outer join user_act_clicklog  on (convert(varchar(10),visit_time,120) = datetimetable.[time])
group by [time] order by year(time),month(time),day(time)
end
else
begin
select 
[time] ,
count(visit_time) '访问次数',
'老用户免费下载' = ( --在user_info_personal表中查询, 日期在今天之前的用户，都统称老用户.  
select count(user_act_consumeLog.user_id) from user_info_personal inner join User_act_consumeLog on (user_info_personal.id = user_id  and user_info_personal.reg_date < getdate())  inner join chl_resource on (product_id = chl_resource.id and chl_resource.price = 0) where convert(varchar(7),down_time,120) = datetimetable.[time]
),
'老用户收费下载' = (
select count(user_act_consumeLog.user_id) from user_info_personal inner join User_act_consumeLog on (user_info_personal.id = user_id and user_info_personal.reg_date < getdate())  inner join chl_resource on (product_id = chl_resource.id and chl_resource.price > 0)  where  convert(varchar(7),down_time,120) = datetimetable.[time]
),
'新用户免费下载' = (
select count(user_act_consumeLog.user_id) from user_info_personal inner join User_act_consumeLog on (user_info_personal.id = user_id  and user_info_personal.reg_date = getdate())  inner join chl_resource on (product_id = chl_resource.id and chl_resource.price = 0) where convert(varchar(7),down_time,120) = datetimetable.[time]
),
'新用户收费下载' = (
select count(user_act_consumeLog.user_id) from user_info_personal inner join User_act_consumeLog on (user_info_personal.id = user_id and user_info_personal.reg_date = getdate())  inner join chl_resource on (product_id = chl_resource.id and chl_resource.price > 0) where convert(varchar(7),down_time,120) = datetimetable.[time]
),
'老用户数' = (
select count(id) from user_info_personal where convert(varchar(7),reg_date,120) = datetimetable.[time] and user_info_personal.reg_date < getdate()
),
'新增用户数' = (
select count(id)-(select count(id) from user_info_personal where convert(varchar(7),reg_date,120) = datetimetable.[time] and user_info_personal.reg_date < getdate()) from user_info_personal where convert(varchar(7),reg_date,120) = datetimetable.[time] and user_info_personal.reg_date < getdate()
)
from 
(
select convert(varchar(10),c.[year])+'-'+(case when len(a.number) = 1 then '0'+convert(varchar(10),a.number) else convert(varchar(10),a.number) end) [time] from master..spt_values a, #temp c where a.name is null and a.number between 1 and 12
) datetimetable
left outer join user_act_clicklog  on (convert(varchar(7),visit_time,120) = datetimetable.[time])
group by [time]
endGO
countVipUserData '2009','2010',1,1,1
现在我想大家第一给我这个sql优化优化，看哪里有没有需要再更正的地方，再就是order by的问题，由于是存储过程，在程序中给出排序方向，在这里我希望能写一个动态的sql，不想再多些重复的代码了。 【也就是两条sql最后的order by 应该如何动态写？ 参数我已经定义好了。】

Answer 1

这一堆的子查询尽量整合到一个查询中处理，子查询太多，十分影响性能。
        select 
            [time] ,
            count(visit_time) '访问次数',
            '老用户免费下载' = ( --在user_info_personal表中查询, 日期在今天之前的用户，都统称老用户.  
                select count(user_act_consumeLog.user_id) from user_info_personal inner join User_act_consumeLog on (user_info_personal.id = user_id  and user_info_personal.reg_date < getdate()) inner join chl_resource on (product_id = chl_resource.id and chl_resource.price = 0)  where convert(varchar(10),down_time,120) = datetimetable.[time]
            ),
            '老用户收费下载' = (
                select count(user_act_consumeLog.user_id) from user_info_personal inner join User_act_consumeLog on (user_info_personal.id = user_id and user_info_personal.reg_date < getdate())  inner join chl_resource on (product_id = chl_resource.id and chl_resource.price > 0)  where  convert(varchar(10),down_time,120) = datetimetable.[time]
            ),
            '新用户免费下载' = (
                select count(user_act_consumeLog.user_id) from user_info_personal inner join User_act_consumeLog on (user_info_personal.id = user_id  and user_info_personal.reg_date = getdate())  inner join chl_resource on (product_id = chl_resource.id and chl_resource.price = 0) where convert(varchar(10),down_time,120) = datetimetable.[time]
            ),
            '新用户收费下载' = (
                select count(user_act_consumeLog.user_id) from user_info_personal inner join User_act_consumeLog on (user_info_personal.id = user_id and user_info_personal.reg_date = getdate())  inner join chl_resource on (product_id = chl_resource.id and chl_resource.price > 0) where convert(varchar(10),down_time,120) = datetimetable.[time]
            ),
            '老用户数' = (
                select count(id) from user_info_personal where convert(varchar(10),reg_date,120) = datetimetable.[time] and user_info_personal.reg_date < getdate()
            ),
            '新增用户数' = (
                select count(id)-(select count(id) from user_info_personal where convert(varchar(10),reg_date,120) = datetimetable.[time] and user_info_personal.reg_date < getdate()) from user_info_personal where convert(varchar(10),reg_date,120) = datetimetable.[time] and user_info_personal.reg_date < getdate()
            )
        from  .....

Answer 2

order by year(time),month(time),day(time)
=====
上面这个order by应该跟 order by time一样的吧，有差别么？

Answer 3

优化优化再优化我爱精炼sql。大家是在写呢？还是在关注着呢？

Answer 4

把子查询提出来，变成几个表 
有些表重复率很高，               ( select convert(varchar(10),down_time,120) down_time,count(user_act_consumeLog.user_id) from user_info_personal inner join User_act_consumeLog on (user_info_personal.id = user_id  and user_info_personal.reg_date < getdate()) inner join chl_resource on (product_id = chl_resource.id and chl_resource.price = 0)  group by convert(varchar(10),down_time,120)
            ) 老用户免费下载,
             (
                select convert(varchar(10),down_time,120) down_time , count(user_act_consumeLog.user_id) from user_info_personal inner join User_act_consumeLog on (user_info_personal.id = user_id and user_info_personal.reg_date < getdate())  inner join chl_resource on (product_id = chl_resource.id and chl_resource.price > 0)  group by convert(varchar(10),down_time,120) 
            ) 老用户收费下载,
             (
                select convert(varchar(10),down_time,120) down_time,count(user_act_consumeLog.user_id) from user_info_personal inner join User_act_consumeLog on (user_info_personal.id = user_id  and user_info_personal.reg_date = getdate())  inner join chl_resource on (product_id = chl_resource.id and chl_resource.price = 0) group by convert(varchar(10),down_time,120)
            ) '新用户免费下载',             (
                select convert(varchar(10),down_time,120) down_time ,count(user_act_consumeLog.user_id) from user_info_personal inner join User_act_consumeLog on (user_info_personal.id = user_id and user_info_personal.reg_date = getdate())  inner join chl_resource on (product_id = chl_resource.id and chl_resource.price > 0) group by convert(varchar(10),down_time,120)
) 新用户收费下载
--再与datetimetable连接 ，convert(varchar(10),down_time,120)=datetimetable.time即 down_time＝datetimetable.time

Answer 5

#9的这个条SQL和我的到底有啥区别呢？我实在没看明白，能举个浅显的例子不咯？

Answer 6

首先条件就不对--在user_info_personal表中查询, 日期在今天之前的用户，都统称老用户.  
user_info_personal.reg_date < getdate()
user_info_personal.reg_date = getdate()这样有可能有新用户吗，穿越？