create table ta (id int,name varchar(50),info varchar(50))
insert ta select 1 ,'产品a' ,'测试产品'
insert ta select 2 ,'产品b', '测试产品2'
insert ta select 3 ,'产品c', '测试产品3'

create table td (id int,pid int,purl varchar(max))
insert td select 1 ,1 ,'/upload/p1.rar?sn=a223'
insert td select 2 ,2 ,'/upload/p2.rar?sn=b221&from=1'
insert td select 3 ,3 ,'/upload/p3.rar?sn=a223'



create table tb (date varchar(50),url varchar(max),prams varchar(max) )
insert tb select '20120421', '/upload/p1.rar', 'sn=a223'
insert tb select '20120422', '/upload/p2.rar', 'sn=b221&from=1'
insert tb select '20120422', '/upload/p3.rar', 'sn=a223'
insert tb select '20120422', '/upload/p3.rar', 'sn=a223'
insert tb select '20120423', '/upload/p2.rar', 'sn=b221&from=1'
insert tb select '20120423', '/upload/p3.rar' ,'sn=a223'


create table tc(id int,name varchar(50),sn varchar(50))
insert tc select 1 ,'公司1', 'a223'
insert tc select 2 ,'公司2', 'b221'


select pid,ta.name,tc.name ,tc.id,count(substring(prams,4,4))num, date from tb join tc on substring(prams,4,4)=sn join td on td.purl=tb.url+'?'+tb.prams 
join ta on ta.id=td.pid  group by pid,ta.name,tc.name ,tc.id,date order by date
/*
pid    name    name    id    num    date
1    产品a    公司1    1    1    20120421
2    产品b    公司2    2    1    20120422
3    产品c    公司1    1    2    20120422
2    产品b    公司2    2    1    20120423
3    产品c    公司1    1    1    20120423

*/
drop table ta
drop table tb
drop table tc
drop table td