SQL 语句

[jackhuyi]

在一个表中
ID NAME QTY
1 HU 20
2 HU 40
3 HY 40
1 JACK 30

怎么样取出不同ID，同NAME，数量大的一条记录
比如上表中ID中有1，2，并且同NAME，取出2，因为2的QTY比1的大；
然后得到下面的表：
ID NAME QTY
2 HU 40
3 HY 40
1 JACK 30

这语句怎么写？

[gongsun]

select max(QTY),ID,Name from table1 group by Name

[苏州牛恋歌]

select ID,Name,max(QTY) from table1 group by ID, Name

[q107770540]

--CREATE TABLE test(
-- ID INT,
-- NAME nvarchar(20),
-- QTY int
--)
--
--INSERT INTO test VALUES(1,'HU',20)
--INSERT INTO test VALUES(2,'HU',40)
--INSERT INTO test VALUES(3,'HY',40)
--INSERT INTO test VALUES(1,'JACK',30)

SELECT * FROM test WHERE QTY IN(
SELECT MAX(QTY) AS QTY FROM test GROUP BY NAME)

ID          NAME                 QTY
----------- -------------------- -----------
2           HU                   40
3           HY                   40
1           JACK                 30

(3 行受影响)

[完美算法]

SELECT * FROM test WHERE QTY IN(SELECT MAX(QTY) AS QTY FROM test WHERE NAME=HU)

[大明湖畔_帅锅]

[Quote=引用 3 楼 q107770540 的回复:]

SQL code


--CREATE TABLE test(
-- ID INT,
-- NAME nvarchar(20),
-- QTY int
--)
--
--INSERT INTO test VALUES(1,'HU',20)
--INSERT INTO test VALUES(2,'HU',40)
--INSERT INTO test VALUES(3,'HY',40)
--IN……
[/Quote]

[苏州牛恋歌]

select ID,[Name],QTY from cs1
where QTY in (
select max(qty) from cs1
group by name)

[jshi123]

select * from [test] a where
not exists (select * from [test] b where a.name=b.name and b.qty > a.qty)

[tashiwoweiyi]

1楼的SQL会报错，3楼的方法支持

[huangweizhao]

3楼的方法不行，不信试试
INSERT INTO test VALUES(5,'HY',50)

SELECT * FROM test WHERE QTY IN(
SELECT MAX(QTY) AS QTY FROM test GROUP BY NAME);

2 HU 40
3 HY 40
1 JACK 30
5 HY 50

[huangweizhao]

楼主看看这样行不？

select a.* from test a,(
select [name] , max(qty) as qty from test
group by [name]) b
where a.[name] = b.[name]
and a.qty = b.qty