SQL 语句
在一个表中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
这语句怎么写？ --------------------编程问答-------------------- select max(QTY),ID,Name from table1 group by Name --------------------编程问答-------------------- select ID,Name,max(QTY) from table1 group by ID, Name --------------------编程问答--------------------
--------------------编程问答-------------------- SELECT * FROM test WHERE QTY IN(SELECT MAX(QTY) AS QTY FROM test WHERE NAME=HU) --------------------编程问答--------------------
--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 行受影响)
--------------------编程问答--------------------
1楼的SQL会报错，3楼的方法支持
--------------------编程问答--------------------
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
--------------------编程问答-------------------- 楼主看看这样行不？
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 --------------------编程问答-------------------- select ID,[Name],QTY from cs1
where QTY in (
select max(qty) from cs1
group by name) --------------------编程问答-------------------- select * from [test] a where
not exists (select * from [test] b where a.name=b.name and b.qty > a.qty)
补充：.NET技术 , C#
