table1
Sname varchar(50)（产品名称）
Stime datetime （销售时间）
SQty int （销售数量）

table2
Sname varchar(50)（产品名称）
Unitprice money（单价）

上面是表结构，现在要查询每个产品每天的销售数量及销售总额，SQL怎么写啊？

用这两个表，创建个视图。。我已经试了。可以实现。
视图如下：
SELECT dbo.table1.Sname, dbo.table1.Stime, SUM(dbo.table1.SQty) AS 日销售量,
SUM(dbo.table1.SQty) * dbo.table2.Unitprice AS 日销售额
FROM dbo.table1 INNER JOIN
dbo.table2 ON dbo.table1.Sname = dbo.table2.Sname
GROUP BY dbo.table1.Sname, dbo.table1.Stime, dbo.table2.Unitprice

每个产品每天的销售数量及销售总额

我觉得有一点问题，如果同一种商品在同一天内有有多次销售的话，好像就不对了。
001 2007-05-05 10：56：26 60
001 2007-05-05 13：15：52 70
如果这样的话，查出来的结果就是2条了。

SELECT dbo.table1.Sname, dbo.table1.Stime, SUM(dbo.table1.SQty) AS 日销售量,
SUM(dbo.table1.SQty) * dbo.table2.Unitprice AS 日销售额
FROM dbo.table1 INNER JOIN
dbo.table2 ON dbo.table1.Sname = dbo.table2.Sname
GROUP BY dbo.table1.Stime

DECLARE @tb1 TABLE(sname VARCHAR(50),stime DATETIME, sqty INT)
INSERT INTO @tb1 SELECT 'A','2003-05-07 12:30',5
UNION ALL SELECT 'B','2002-11-17 8:15',30
UNION ALL SELECT 'C','2003-05-07 21:33',25
UNION ALL SELECT 'D','2004-08-23 13:40',12
UNION ALL SELECT 'E','2001-07-19 18:42',50
UNION ALL SELECT 'D','2004-08-23 07:32',12
UNION ALL SELECT 'B','2002-11-17 19:25',17
UNION ALL SELECT 'A','2002-11-19 17:31',16
UNION ALL SELECT 'F','2004-08-23 07:32',19

DECLARE @tb2 TABLE(sname VARCHAR(50),Unitprice MONEY)
INSERT INTO @tb2 VALUES( 'A',$10)
INSERT INTO @tb2 VALUES( 'B',$3)
INSERT INTO @tb2 VALUES( 'C',$12)
INSERT INTO @tb2 VALUES( 'D',$8)
INSERT INTO @tb2 VALUES( 'E',$5)
INSERT INTO @tb2 VALUES( 'F',$7)

SELECT t1.sname AS '产品名称',LEFT(CONVERT(CHAR(30),t1.stime,120),10) AS '销售日期',
SUM(sqty) AS '每天销售总量',SUM(sqty*Unitprice) AS '每天销售总额'
FROM @tb1 AS t1 INNER JOIN @tb2 AS t2
ON t1.sname=t2.sname
GROUP BY t1.sname,LEFT(CONVERT(CHAR(30),t1.stime,120),10)
ORDER BY t1.sname,2


或者建立视图

CREATE TABLE tb1
(
sname VARCHAR(50),
stime DATETIME,
sqty INT
)
GO
INSERT INTO tb1 SELECT 'A','2003-05-07 12:30',5
UNION ALL SELECT 'B','2002-11-17 8:15',30
UNION ALL SELECT 'C','2003-05-07 21:33',25
UNION ALL SELECT 'D','2004-08-23 13:40',12
UNION ALL SELECT 'E','2001-07-19 18:42',50
UNION ALL SELECT 'D','2004-08-23 07:32',12
UNION ALL SELECT 'B','2002-11-17 19:25',17
UNION ALL SELECT 'A','2002-11-19 17:31',16
UNION ALL SELECT 'F','2004-08-23 07:32',19
GO

CREATE TABLE tb2
(
sname VARCHAR(50),
Unitprice MONEY
)
GO

INSERT INTO tb2 VALUES( 'A',$10)
INSERT INTO tb2 VALUES( 'B',$3)
INSERT INTO tb2 VALUES( 'C',$12)
INSERT INTO tb2 VALUES( 'D',$8)
INSERT INTO tb2 VALUES( 'E',$5)
INSERT INTO tb2 VALUES( 'F',$7)
GO

CREATE VIEW view_productsales(产品名称,销售日期,每天销售总量,每天销售总额)
AS
SELECT t1.sname , LEFT(CONVERT(CHAR(30),t1.stime,120),10),
SUM(sqty) ,SUM(sqty*Unitprice)
FROM tb1 AS t1 INNER JOIN tb2 AS t2
ON t1.sname=t2.sname
GROUP BY t1.sname,LEFT(CONVERT(CHAR(30),t1.stime,120),10)


1.查看所有产品的销售情况

SELECT *
FROM view_productsales
ORDER BY 1,2

2.查看某个产品的销售情况
SELECT *
FROM view_productsales
WHERE 产品名称='A'
ORDER BY 1,2

3.查看某个产品某个销售日期的销售情况
SELECT *
FROM view_productsales
WHERE 产品名称='A' AND 销售日期='2002-11-19'
ORDER BY 1,2

[此贴子已经被作者于2007-8-1 7:48:47编辑过]