select name,type,money,[month],sum(money) as sum_money,sum(number) as sum_number from product group by name,type;
试试下语句。没测试。 select distinct name,type ,aa.money,aa.number,bb.sum_money,bb.sum_number from product p left outer join (select name,type,first(money) as money,first(number) as number) aa on p.name=aa.name and p.type=aa.type left outer join (select name,type,sum(money) as sum_money,sum(number) as sum_number from product group by name,type) bb on p.name=bb.name and p.type=bb.type
如果还要按这些求和排序呢?sum_money大的排在最前,怎么写?
select name,type,MAX(money),MAX(month),sum(money) as sum_money,sum(number) as sum_number from product group by name,type
SELECT NAME,TYPE,MAX(MONEY),MAX(NUMBER),SUM(MONEY),SUM(NUMBER) FROM PRODUCT GROUP BY NAME,TYPE ORDER BY NAME A1 123 10 3 22 9 A2 258 4 4 6 8 A3 147 1 1 1 1 A4 256 7 1 7 1
弄混了,SQL没有FIRST。参考5楼吧,再排序。 select name,type,MAX(money),MAX(month),sum(money) as sum_money,sum(number) as sum_number from product group by name,type order by sum(money) desc
--字段名最好加個[],你的字段名都是系統關鍵字,容易混淆! SELECT [name],[type],MAX([money]) AS [money],MAX([month]) AS [month],SUM([money]) AS sum_money,SUM([number]) AS sum_number FROM product GROUP BY [name],[type] ORDER BY SUM([money])
--字段名最好加個[],你的字段名都是系統關鍵字,容易混淆! SELECT [name],[type],MAX([money]) AS [money],MAX([number]) AS [number],SUM([money]) AS sum_money,SUM([number]) AS sum_number FROM product GROUP BY [name],[type] ORDER BY SUM([money])
select name,type,MAX(money),MAX(month),sum(money) as sum_money,sum(number) as sum_number from product group by name,type order by sum_money desc
--字段名最好加個[],你的字段名都是系統關鍵字,容易混淆! SELECT [name],[type],MAX([money]) AS [money],MAX([number]) AS [number],SUM([money]) AS sum_money,SUM([number]) AS sum_number FROM product GROUP BY [name],[type] ORDER BY SUM([money]) DESC
SELECT a,f1,max(f4),max(f5),sum(f4)as Fmoney,sum(f5)as FF FROM TB group by a,f1 order by Fmoney desc
select distinct name,type ,aa.money,aa.number,bb.sum_money,bb.sum_number
from product p
left outer join
(select name,type,first(money) as money,first(number) as number) aa on p.name=aa.name and p.type=aa.type
left outer join
(select name,type,sum(money) as sum_money,sum(number) as sum_number from product group by name,type) bb on p.name=bb.name and p.type=bb.type
SELECT NAME,TYPE,MAX(MONEY),MAX(NUMBER),SUM(MONEY),SUM(NUMBER) FROM
PRODUCT GROUP BY NAME,TYPE ORDER BY NAME
A1 123 10 3 22 9
A2 258 4 4 6 8
A3 147 1 1 1 1
A4 256 7 1 7 1
select name,type,MAX(money),MAX(month),sum(money) as sum_money,sum(number) as sum_number from product group by name,type order by sum(money) desc
SELECT [name],[type],MAX([money]) AS [money],MAX([month]) AS [month],SUM([money]) AS sum_money,SUM([number]) AS sum_number
FROM product
GROUP BY [name],[type]
ORDER BY SUM([money])
--字段名最好加個[],你的字段名都是系統關鍵字,容易混淆!
SELECT [name],[type],MAX([money]) AS [money],MAX([number]) AS [number],SUM([money]) AS sum_money,SUM([number]) AS sum_number
FROM product
GROUP BY [name],[type]
ORDER BY SUM([money])
--字段名最好加個[],你的字段名都是系統關鍵字,容易混淆!
SELECT [name],[type],MAX([money]) AS [money],MAX([number]) AS [number],SUM([money]) AS sum_money,SUM([number]) AS sum_number
FROM product
GROUP BY [name],[type]
ORDER BY SUM([money]) DESC
SELECT a,f1,max(f4),max(f5),sum(f4)as Fmoney,sum(f5)as FF FROM TB group by a,f1 order by Fmoney desc