sql

declare @a table(code int, class varchar(10), price  int)
----------- ------------ -----------
insert @a select 1 ,'A', 123
union all select 2 ,'B', 1563
union all select 3 ,'C', 1543
union all select 4 ,'A', 456
union all select 5 ,'B', 4122
union all select 6 ,'C', 411
union all select 7 ,'A', 48882
union all select 8 ,'B',417722
union all select 9 ,'C', 854
union all select 10 ,'A', 4122

select class,price from @a a where code in (select top 2 code from @a where class=a.class order by price desc)
order by class 

其它方法:
declare @t table(code int, class varchar(10), price  int)
insert @t select 1 ,'A', 123
union all select 2 ,'B', 1563
union all select 3 ,'C', 1543
union all select 4 ,'A', 456
union all select 5 ,'B', 4122
union all select 6 ,'C', 411
union all select 7 ,'A', 48882
union all select 8 ,'B',417722
union all select 9 ,'C', 854
union all select 10 ,'A', 4122

----方法1:
select * from @t as a
where (select count(*) from @t where class = a.class and price > a.price ) < 2
order by class,price DESC
----方法2:
select * from @t as a
where not exists(select 1 from @t where class = a.class and price > a.price group by class having count(*) > 1)
order by class,price DESC

统计一周的点击数

SELECT isnull(t.number,0) AS number, wks.id AS weekdays, case   wks.id
when   1   then   '星期天 ' 
when   2   then   '星期一 ' 
when   3   then   '星期二 ' 
when   4   then   '星期三 ' 
when   5   then   '星期四 ' 
when   6   then   '星期五 ' 
when   7   then   '星期六 ' 
 END AS weekdaystring
 FROM
(
select count(*) AS number ,DATEPART(weekday,Convert(varchar(10),建立时间,120)) as wd from hrtoolfiles
where 建立时间 between '2012-11-12 00:00:00'  and  '2012-11-18 00:00:00' 
group by Convert(varchar(10),建立时间,120)
) AS t
RIGHT JOIN 
(
 SELECT '1' AS id UNION SELECT '2' AS id UNION SELECT '3' AS id UNION SELECT '4' AS id
 UNION SELECT '5' AS id UNION SELECT '6' AS id UNION  SELECT '7' AS id 
) AS wks
ON t.wd = wks.id
ORDER BY wks.id

多行合并到一列

IF OBJECT_ID('[tb]') IS NOT NULL
    DROP TABLE [tb]
GO
CREATE TABLE [tb] ([用户账号] [nvarchar](10),[用户姓名] [nvarchar](10),[所属角色] [nvarchar](10))
INSERT INTO [tb]
SELECT 'a','name1','A' UNION ALL
SELECT 'a','name1','B' UNION ALL
SELECT 'a','name1','C' UNION ALL
SELECT 'b','name2','B' UNION ALL
SELECT 'b','name2','C' UNION ALL
SELECT 'C','name3','A' UNION ALL
SELECT 'C','name3','D'
 
--SELECT * FROM [tb]
 
-->SQL查询如下:
;WITH t AS 
(
    SELECT 用户账号, 用户姓名, 所属角色 
    FROM tb
)
SELECT DISTINCT 用户账号,用户姓名,
       所属角色=LTRIM((SELECT ' '+所属角色 FROM t a WHERE 用户账号=t.用户账号 AND 用户姓名=t.用户姓名 FOR XML PATH('')))
FROM t