现在在学数据库,用的是sql sever2000里的northwind数据库,遇到一个问题(估计应该很简单把)
订单号 订单金额
orderid price
10248 100
10248 200
10248 300
10249 100
10249 800
10250 500
10250 600
10250 700
10251 600
10251 400
怎么计算每个订单的总金额?就是10248=100+200+300,10490=100+800
哪位知道帮我一下,不胜感激~~
订单号 订单金额
orderid price
10248 100
10248 200
10248 300
10249 100
10249 800
10250 500
10250 600
10250 700
10251 600
10251 400
怎么计算每个订单的总金额?就是10248=100+200+300,10490=100+800
哪位知道帮我一下,不胜感激~~
insert into tb values('10248', 100)
insert into tb values('10248', 200)
insert into tb values('10248', 300)
insert into tb values('10249', 100)
insert into tb values('10249', 800)
insert into tb values('10250', 500)
insert into tb values('10250', 600)
insert into tb values('10250', 700)
insert into tb values('10251', 600)
insert into tb values('10251', 400)
goselect orderid , sum(price) price from tb group by orderiddrop table tb/*
orderid price
---------- -----------
10248 600
10249 900
10250 1800
10251 1000(所影响的行数为 4 行)
*/
-- Author : htl258(Tony)
-- Date : 2010-04-28 23:38:30
-- Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
-- Blog : http://blog.csdn.net/htl258
--------------------------------------------------------------------------
--> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([orderid] INT,[price] INT)
INSERT [tb]
SELECT 10248,100 UNION ALL
SELECT 10248,200 UNION ALL
SELECT 10248,300 UNION ALL
SELECT 10249,100 UNION ALL
SELECT 10249,800 UNION ALL
SELECT 10250,500 UNION ALL
SELECT 10250,600 UNION ALL
SELECT 10250,700 UNION ALL
SELECT 10251,600 UNION ALL
SELECT 10251,400
GO
--SELECT * FROM [tb]-->SQL查询如下:
select orderid,sum(price) price from tb group by orderid
/*
orderid price
----------- -----------
10248 600
10249 900
10250 1800
10251 1000(4 行受影响)
*/