今天面试的两道sql题,回来发现都做错了

作者:Rwing 日期:2009-03-24

两道原题见这里
http://www.cnblogs.com/springwind/archive/2008/06/28/1231527.html
第一题很简单只不过当时题意理解错了不多说
第二题整理一下思路:先获取每个时间点即每天,然后按照广告位置和每天进行分组,得到每天每个广告位的数量,然后基本就出来了,附我的sql,未整理,其中t我写死的数据,用个临时表替换一下就可以了

引用内容

with t as(
    select '2006-11-01' as 'date'
    union all
    select '2006-11-02'
    union all
    select '2006-11-03'
    union all
    select '2006-11-04'
    union all
    select '2006-11-05'
)
select * from (
    select count(*) total, positioncode, t.date
    from orders a
    left join t on t.date between startdate and enddate
    group by positioncode,t.date
) a
left join Product b on b.positioncode = a.positioncode
inner join orders c on c.positioncode = a.positioncode
where total > showcount
order by date, a.positioncode

[本日志由 Rwing 于 2009-03-24 03:43 PM 编辑]