sql去除重复以及列转行sql语句

工作中非常实用的sql语句

数据：

userid  sid
sexgo	2145796903
sexgo	2145802359
sexgo	2145794277
sexgo	2145802357
sexgo	2145815136
sexgo	2145810994
uuwap	2145874581
uuwap	2145872602
uuwap	2145847304
uuwap	2145800783
uuwap	2145797838

需要获取的数据是每一个userid就一个sid，像上面的数据就只有2条是想要的，sql语句如下：

sqlserver中的sql语句：

select   *   from   unionsite   a   
where   not   exists
(select   1   from   unionsite   where   a.userid   =   userid   and   a.sid   >   sid)
and (userid='uuwap' or userid='sexgo')

mysql中的sql语句：

select *,count(distinct userid) from unionsite group by userid

最后执行sql语句后的数据为：
2145794277 sexgo
2145797838 uuwap

数据：
sid 数据(validtotal) 类型(searchType)

	
435466064	17100			c
2145900222	25699			c
2145900226	18505698		c
2145908186	798			c
2145909778	170			c
2145909800	4071			c
2145904011	2967164			p
2145874581	23			p
2145836931	13064841		p
2145800783	593122			p
2145900226	582085			p
2145907505	583524			p
2145847304	142			p
2145847304	0			e
2145907505	0			e
2145874581	0			e
2145900226	0			e
2145800783	0			e
2145836931	0			e
2145904011	0			e
2145874581	0			t
2145900226	45905			t
2145909800	6920			t
2145800783	42692			t
2145847304	7			t
2145797748	5750			t
2145805071	9534			t
435466064	17100			c
2145900222	25699			c
2145900226	18505698		c
2145908186	798			c
2145909778	170			c
2145909800	4071			c

需要获取的是把同一个sid而不同的类型整合成一行，sql语句如下：
mysql和sqlserver都可以：

select statDate,a.sid,searchValue,themeValue,
clientValue,adValue,searchPrice,themePrice,clientPrice,adPrice,userid as username,u.domainAddr 
from  (  Select sid,statDate,   
case when searchtype='p' then validTotal else 0 end as searchValue, 
case when searchtype='t' then validTotal else 0 end as themeValue,  
case when searchtype='c' then validTotal else 0 end as clientValue,  
case when searchtype='a' then validTotal else 0 end as adValue,  
case when searchtype='p' then price else 0 end as searchPrice, 
case when searchtype='t' then price else 0 end as themePrice,  
case when searchtype='c' then price else 0 end as clientPrice,  
case when searchtype='a' then price else 0 end as adPrice  
from unionsitemonthreportdetail   where statdate='2008-08'   ) as  a , unionsite u 
where a.sid=u.sid order by searchValue+themeValue+clientValue+adValue DESC

最后的数据：

2008-11	2145900226	0	0	18505698	0	0.00	0.00	1.50	0.00	moabcyicha	wap.moabc.com
2008-11	2145900226	0	0	18505698	0	0.00	0.00	1.50	0.00	moabcyicha	wap.moabc.com
2008-11	2145836931	13064841	0	0	0	1.30	0.00	0.00	0.00	andjiang	wap.ucfly.com
2008-11	2145904011	2967164	0	0	0	1.30	0.00	0.00	0.00	mmbcn	wap.mmb.cn
2008-11	2145800783	593122	0	0	0	1.20	0.00	0.00	0.00	uuwap	wapwz.cn
2008-11	2145907505	583524	0	0	0	1.30	0.00	0.00	0.00	yichaishd	yicha.cn
2008-11	2145900226	582085	0	0	0	1.20	0.00	0.00	0.00	moabcyicha	wap.moabc.com
2008-11	2145900226	0	45905	0	0	0.00	NULL	0.00	0.00	moabcyicha	wap.moabc.com
2008-11	2145800783	0	42692	0	0	0.00	NULL	0.00	0.00	uuwap	wapwz.cn
2008-11	2145900222	0	0	25699	0	0.00	0.00	0.50	0.00	moabcyicha	moabc.com
2008-11	2145900222	0	0	25699	0	0.00	0.00	0.50	0.00	moabcyicha	moabc.com
2008-11	435466064	0	0	17100	0	0.00	0.00	0.00	0.00	user84573	wap.wapsai.com
2008-11	435466064	0	0	17100	0	0.00	0.00	0.00	0.00	user84573	wap.wapsai.com
2008-11	2145805071	0	9534	0	0	0.00	NULL	0.00	0.00	181860	181860.com
2008-11	2145909800	0	6920	0	0	0.00	NULL	0.00	0.00	3gpp	3g.pp.cn
2008-11	2145797748	0	5750	0	0	0.00	NULL	0.00	0.00	tkwap	tkwap.com
2008-11	2145909800	0	0	4071	0	0.00	0.00	15.00	0.00	3gpp	3g.pp.cn
2008-11	2145909800	0	0	4071	0	0.00	0.00	15.00	0.00	3gpp	3g.pp.cn
2008-11	2145908186	0	0	798	0	0.00	0.00	0.16	0.00	hypei@easou.com	http://3gwawa.com
2008-11	2145908186	0	0	798	0	0.00	0.00	0.16	0.00	hypei@easou.com	http://3gwawa.com
2008-11	2145909778	0	0	170	0	0.00	0.00	5.00	0.00	hypei@easou.com	ltmp3.cn
2008-11	2145909778	0	0	170	0	0.00	0.00	5.00	0.00	hypei@easou.com	ltmp3.cn
2008-11	2145847304	142	0	0	0	1.00	0.00	0.00	0.00	uuwap	wap.wapwz.cn
2008-11	2145874581	23	0	0	0	1.00	0.00	0.00	0.00	uuwap	6.3nt.cn
2008-11	2145847304	0	7	0	0	0.00	NULL	0.00	0.00	uuwap	wap.wapwz.cn
2008-11	2145800783	0	0	0	0	0.00	0.00	0.00	0.00	uuwap	wapwz.cn
2008-11	2145874581	0	0	0	0	0.00	0.00	0.00	0.00	uuwap	6.3nt.cn
2008-11	2145874581	0	0	0	0	0.00	NULL	0.00	0.00	uuwap	6.3nt.cn
2008-11	2145847304	0	0	0	0	0.00	0.00	0.00	0.00	uuwap	wap.wapwz.cn
2008-11	2145836931	0	0	0	0	0.00	0.00	0.00	0.00	andjiang	wap.ucfly.com
2008-11	2145907505	0	0	0	0	0.00	0.00	0.00	0.00	yichaishd	yicha.cn
2008-11	2145904011	0	0	0	0	0.00	0.00	0.00	0.00	mmbcn	wap.mmb.cn
2008-11	2145900226	0	0	0	0	0.00	0.00	0.00	0.00	moabcyicha	wap.moabc.com

另外的一个sql语句：

select case when price is null then 0 else price end as adPrice from t_client_stat