use database
SELECT *
FROM info a
WHERE bsort IN ('分类1', '分类2', '分类3', '分类4') AND bsort IN
(SELECT TOP 3 bsort
FROM bizinfo
WHERE bsort =a.bsort
ORDER BY id DESC)
order by id desc
SELECT *
FROM info a
WHERE bsort IN ('分类1', '分类2', '分类3', '分类4') AND bsort IN
(SELECT TOP 3 bsort
FROM bizinfo
WHERE bsort =a.bsort
ORDER BY id DESC)
order by id desc
要不然bsort in (select top 3 * from...)怎么定位bsort对应哪一列呢??
SELECT *
FROM info a
WHERE bsort IN ('分类1', '分类2', '分类3', '分类4') AND bsort IN
(SELECT TOP 3 bsort
FROM bizinfo
WHERE bsort =a.bsort
ORDER BY id DESC)
order by id desc
按上面改了后,返回所有的记录,而不是每个分类取3条记录,麻烦再看下哪有问题
SELECT *
FROM info a
WHERE bsort IN ('分类1', '分类2', '分类3', '分类4') AND bsort IN
(SELECT TOP 3 bsort
FROM bizinfo
ORDER BY id DESC)
order by id desc
SELECT *
FROM info a
WHERE bsort IN ('分类1', '分类2', '分类3', '分类4') AND bsort IN
(SELECT TOP 3 bsort
FROM info
ORDER BY id DESC)
order by id desc还是返回所有记录呀
use database
SELECT *
FROM info a
WHERE bsort IN ('分类1', '分类2', '分类3', '分类4') AND bsort IN
(SELECT TOP 3 bsort
FROM bizinfo
where id=a.id
ORDER BY id DESC)
order by id desc
id int 自动编号
bsort char 分类
.
.
.
"AND bsort IN
(SELECT TOP 3 bsort
FROM bizinfo
where id=a.id
ORDER BY id DESC) "
这些语句好像没执行呀,如何改呀,真是头痛的事.麻烦再看看呢 感谢!
use database
go
SELECT top 3 * FROM info WHERE bsort IN ('分类1')
union all
SELECT top 3 * FROM info WHERE bsort IN ('分类2')
union all
SELECT top 3 * FROM info WHERE bsort IN ('分类3')
union all
SELECT top 3 * FROM info WHERE bsort IN ('分类4')
用union只能在最后加上order by id desc用这种办法返回的并不是每个分类最新的3条记录。
GO
SELECT *
FROM info a
WHERE bsort IN ('分类1', '分类2', '分类3', '分类4') AND id IN
(SELECT TOP 3 id
FROM info
where bsort=a.bsort
ORDER BY id DESC)
order by id desc
GO
SELECT *
FROM info a
WHERE bsort IN ('分类1', '分类2', '分类3', '分类4') AND id IN
(SELECT TOP 3 id
FROM info
where bsort=a.bsort
ORDER BY id DESC)
order by id desc