我想实现这样的效果:
lb name date
11 222 2007-9-19
11 333 2007-9-18
11 444 2007-9-17
22 555 2007-9-20
22 666 2007-9-18
22 777 2007-9-16
33 888 2007-9-21
33 999 2007-9-19
33 000 2007-9-18
........按照类别(lb)分组,在各组内日期从大到小排序,而且每一组我只要8条数据。SQL怎么写?不知道我说明白了没?
lb name date
11 222 2007-9-19
11 333 2007-9-18
11 444 2007-9-17
22 555 2007-9-20
22 666 2007-9-18
22 777 2007-9-16
33 888 2007-9-21
33 999 2007-9-19
33 000 2007-9-18
........按照类别(lb)分组,在各组内日期从大到小排序,而且每一组我只要8条数据。SQL怎么写?不知道我说明白了没?
where (select count(*) from table where lb = a.lb) <=8
union all
select top 8 * from table b group by lb order by date
where (select count(*) from table where lb = b.lb) >8
update table set flag=0;
update table set flag=1 from (select max(date), lb from table where flag=0 group by lb) table 2 where table.lb=table2.lb and table.date=table2.date;
.....执行8编
select lb,name,date from table where flag=1 order by lb,date
AVG(price) AS AveragePrice
FROM pubs.dbo.titles
WHERE pub_id > '0800'
AND price >= $5
GROUP BY pub_id
HAVING SUM(advance) > $15000
AND AVG(price) < $20
ORDER BY pub_id DESC
(注: asc 表示升序 , desc表示降序 , 未明确写明排序方式时默认是升序 )
http://tech.techweb.com.cn/thread-196877-1-1.html
where name in
(select top 2 name from tb b where a.lb = b.lb order by lb, date )
order by lb, date要保证名字唯一
好像有语法错误....
该列没有包含在聚合函数或 GROUP BY 子句中。
如果你没有使用聚合函数(如:avg,sum等)当然不能用!你要的效果可以这样写:
select top 8 from table order by date desc
where p.date in (select top 8 mytable.date from mytable where mytable.lb=p.lb)如果有id列
select p.id, p.lb, p.name, p.date from mytable p
where p.id in (select top 8 mytable.id from mytable where mytable.lb=p.lb order by mytable.date)
where p.date in (select top 8 mytable.date from mytable where mytable.lb=p.lb
order by mytable.date desc) order by p.lb, p.date desc
FROM table AS p
WHERE (p.date IN
(SELECT TOP (8) date
FROM table
where p.lb=lb
ORDER BY date DESC))
ORDER BY lb, date DESC