有表如下:
0ID DishName Rank
1 肉1 1
2 肉2 1
3 肉2 2
4 肉2 3
想得到结果如下
0ID DishName Rank
1 肉1 1
4 肉2 3请问SQL语句怎么写啊?
0ID DishName Rank
1 肉1 1
2 肉2 1
3 肉2 2
4 肉2 3
想得到结果如下
0ID DishName Rank
1 肉1 1
4 肉2 3请问SQL语句怎么写啊?
调试欢乐多
select top 1 0ID , DishName,Rank from t order by 0ID union all
select top 1 0ID , DishName,Rank from t order by 0ID desc
INSERT INTO tb(oid,dishName,rank) VALUES (1,'肉1',1)
INSERT INTO tb(oid,dishName,rank) VALUES (2,'肉2',1)
INSERT INTO tb(oid,dishName,rank) VALUES (3,'肉2',2)
INSERT INTO tb(oid,dishName,rank) VALUES (4,'肉2',3)
SELECT * FROM tb WHERE cast(rank AS varchar(200))+','+dishName IN (SELECT cast(max(rank) AS varchar(200))+','+dishName FROM tb GROUP BY dishName)
DROP table tb
CREATE TABLE tb (OID int,dishName varchar(200),Rank int)
INSERT INTO tb(oid,dishName,rank) VALUES (1,'肉1',1)
INSERT INTO tb(oid,dishName,rank) VALUES (2,'肉2',1)
INSERT INTO tb(oid,dishName,rank) VALUES (3,'肉2',2)
INSERT INTO tb(oid,dishName,rank) VALUES (4,'肉2',3)
SELECT * FROM tb WHERE cast(rank AS varchar(200))+','+dishName IN (SELECT cast(max(rank) AS varchar(200))+','+dishName FROM tb GROUP BY dishName)
DROP table tb
结果:
0ID DishName Rank
1 肉1 1
4 肉2 3
(
OID int identity(1,1) not null,
DishName varchar(20),
Rank int
)
insert into t
select '肉1',1 union all
select '肉2',1 union all
select '肉2',2 union all
select '肉2',3 select
OID,t.DishName,t.Rank
from
t
join
(select DishName ,max(Rank) as Rank from t group by DishName) t1
on
t.DishName=t1.DishName and t.Rank=t1.Rank
order by OIDOID DishName Rank
----------- -------------------- -----------
1 肉1 1
4 肉2 3(2 行受影响)
declare @tb table (OID int,dishName varchar(200),Rank int)
INSERT INTO @tb(oid,dishName,rank) VALUES (1,'肉1',1)
INSERT INTO @tb(oid,dishName,rank) VALUES (2,'肉2',1)
INSERT INTO @tb(oid,dishName,rank) VALUES (3,'肉2',2)
INSERT INTO @tb(oid,dishName,rank) VALUES (4,'肉2',3)select a.* from @tb a
join (
select dishName,max(Rank)as Rank from @tb group by dishName
)b on a.dishName=b.dishName and a.Rank=b.Rank
order by oid/*
1 肉1 1
4 肉2 3
*/
where Rank=(select max(Rank) from table1 b where b.DishName=a.DishName and b.0ID=a.0ID)