表T_Item
Id Name Color Number
1 桌子 黄 16
2 桌子 红 20
3 桌子 黄 5
4 桌子 红 6
5 椅子 黄 10
6 椅子 黄 7
7 椅子 红 10
8 椅子 红 5要求使用SQL查询语句求出每种物品当中数量最多的颜色。
Id Name Color Number
1 桌子 黄 16
2 桌子 红 20
3 桌子 黄 5
4 桌子 红 6
5 椅子 黄 10
6 椅子 黄 7
7 椅子 红 10
8 椅子 红 5要求使用SQL查询语句求出每种物品当中数量最多的颜色。
from tb t
where not exists(select 1 from tb where name=t.name and number>t.number)
if object_id('[T_Item]') is not null drop table [T_Item]
go
create table [T_Item]([Id] int,[Name] varchar(4),[Color] varchar(2),[Number] int)
insert [T_Item]
select 1,'桌子','黄',16 union all
select 2,'桌子','红',20 union all
select 3,'桌子','黄',5 union all
select 4,'桌子','红',6 union all
select 5,'椅子','黄',10 union all
select 6,'椅子','黄',7 union all
select 7,'椅子','红',10 union all
select 8,'椅子','红',5
---查询---
select *
from T_Item t
where not exists(select 1 from T_Item where name=t.name and number>t.number)---结果---
Id Name Color Number
----------- ---- ----- -----------
2 桌子 红 20
5 椅子 黄 10
7 椅子 红 10(3 行受影响)
from
T_Item mt,
(select Name ,max(Number) as Number from T_Item group by Name) tt
where
mt.Number = tt.Number
select 1,'桌子','黄',16 union all
select 2,'桌子','红',20 union all
select 3,'桌子','黄',5 union all
select 4,'桌子','红',6 union all
select 5,'椅子','黄',10 union all
select 6,'椅子','黄',7 union all
select 7,'椅子','红',10 union all
select 8,'椅子','红',5SELECT * FROM (
SELECT t.name,t.color,SUM(t.number) AS number FROM t_item AS t GROUP BY t.name,t.color) AS tt
WHERE NOT EXISTS(SELECT 1 FROM (SELECT t.name,t.color,SUM(t.number) AS number FROM t_item AS t GROUP BY t.name,t.color) AS ttt WHERE tt.NAME=ttt.NAME AND tt.number < ttt.number)SELECT ti.*
FROM (SELECT t.name,t.color,SUM(t.number) AS number FROM t_item AS t GROUP BY t.name,t.color ) AS ti,(
SELECT tt.name,MAX(tt.number) AS number
FROM (SELECT t.name,t.color,SUM(t.number) AS number FROM t_item AS t GROUP BY t.name,t.color ) AS tt
GROUP BY tt.name ) AS ttt
WHERE ti.name = ttt.name AND ti.number = ttt.number
/*
name color number
-------------------- ---------- -----------
椅子 黄 17
桌子 红 26(所影响的行数为 2 行)name color number
-------------------- ---------- -----------
桌子 红 26
椅子 黄 17
*/
(
select item, color, sum(amount) num
from t_item
group by item, color
)
select t1.item, t1.color, t1.num
from sta t1, (select sta.item, max(sta.num) ma
from sta
group by sta.item
)t2
where t1.item = t2.item
and t1.num =t2.ma;执行结果:桌子 红 26
椅子 黄 17
FROM (SELECT t.name,t.color,SUM(t.number) AS number FROM t_item AS t GROUP BY t.name,t.color ) AS ti,(
SELECT tt.name,MAX(tt.number) AS number
FROM (SELECT t.name,t.color,SUM(t.number) AS number FROM t_item AS t GROUP BY t.name,t.color ) AS tt
GROUP BY tt.name ) AS ttt
WHERE ti.name = ttt.name AND ti.number = ttt.number
这个对了!