--> 生成测试数据: @tb1 DECLARE @tb1 TABLE (品号 INT,品名 VARCHAR(1),数量 INT) INSERT INTO @tb1 SELECT 111,'A',100 UNION ALL SELECT 222,'B',50 UNION ALL SELECT 333,'C',125 UNION ALL SELECT 222,'B',70
--> 生成测试数据: @tb2 DECLARE @tb2 TABLE (品号 INT,单价 NUMERIC(2,1)) INSERT INTO @tb2 SELECT 111,0.3 UNION ALL SELECT 222,0.5 UNION ALL SELECT 111,0.1 UNION ALL SELECT 333,1.5--SQL查询如下:SELECT A.品号, A.品名, SUM(A.数量) AS 数量, MIN(B.单价) AS 最低单价 FROM @tb1 AS A JOIN @tb2 AS B ON A.品号=B.品号 GROUP BY A.品号,A.品名/* 品号 品名 数量 最低单价 ----------- ---- ----------- --------------------------------------- 111 A 200 0.1 222 B 120 0.5 333 C 125 1.5(3 row(s) affected) */
SELECT T.品号,T.品名,产量,最低单价 FROM (SELECT 品号,品名,SUM(数量)AS产量 FROM A GROUP BY 品号,品名)AS T] JOIN (SELECT 品号,MIN(单价)AS 最低单价 FROM B GROUP BY 品号)AS T1 ON T.品号=T1.品号
--> 生成测试数据: @tb1 DECLARE @tb1 TABLE (品号 INT,品名 VARCHAR(1),数量 INT) INSERT INTO @tb1 SELECT 111,'A',100 UNION ALL SELECT 222,'B',50 UNION ALL SELECT 333,'C',125 UNION ALL SELECT 222,'B',70
--> 生成测试数据: @tb2 DECLARE @tb2 TABLE (品号 INT,单价 NUMERIC(2,1)) INSERT INTO @tb2 SELECT 111,0.3 UNION ALL SELECT 222,0.5 UNION ALL SELECT 111,0.1 UNION ALL SELECT 333,1.5--SQL查询如下:select a.品号, a.品名, sum(a.数量) as 产量, min(b.单价) as 最低单价, sum(a.数量) * min(b.单价) as 售价 from @tb1 a join @tb2 b on a.品号 = b.品号 group by a.品号, a.品名/* 品号 品名 产量 最低单价 售价 ----------- ---- ----------- ---- --------------- 111 A 200 .1 20.0 222 B 120 .5 60.0 333 C 125 1.5 187.5 */
晕,为什么我测试了不行呢?我还有一些where的判断条件要放在哪里呢?
哇真快!!借数据用用,谢谢,跟你学习 --> 生成测试数据: @tb1 DECLARE @tb1 TABLE (品号 INT,品名 VARCHAR(1),数量 INT) INSERT INTO @tb1 SELECT 111,'A',100 UNION ALL SELECT 222,'B',50 UNION ALL SELECT 333,'C',125 UNION ALL SELECT 222,'B',70
--> 生成测试数据: @tb2 DECLARE @tb2 TABLE (品号 INT,单价 NUMERIC(3,1)) INSERT INTO @tb2 SELECT 111,0.3 UNION ALL SELECT 222,0.5 UNION ALL SELECT 111,0.1 UNION ALL SELECT 333,1.5SELECT T.品号,T.品名,产量,最低单价 FROM (SELECT 品号,品名,SUM(数量)AS 产量 FROM @tb1 GROUP BY 品号,品名)AS T JOIN (SELECT 品号,MIN(单价) AS '最低单价' FROM @tb2 GROUP BY 品号)AS T1 ON T.品号=T1.品号(所影响的行数为 4 行) (所影响的行数为 4 行)品号 品名 产量 最低单价 ----------- ---- ----------- ----- 111 A 100 .1 222 B 120 .5 333 C 125 1.5(所影响的行数为 3 行)
奇怪,为什么跟我原来的语句查询结果一样,是错的 我这边是这样的.SELECT A.品号, A.品名, SUM(A.数量) AS 数量, MIN(B.单价) AS 最低单价 FROM A,B where A.品号=B.品号,(....这里还有若干条件) GROUP BY A.品号,A.品名 order by A.品号
-- Author: liangCK 小梁
-- Date : 2009-05-19 15:43:32
---------------------------------
--> 生成测试数据: @tb1
DECLARE @tb1 TABLE (品号 INT,品名 VARCHAR(1),数量 INT)
INSERT INTO @tb1
SELECT 111,'A',100 UNION ALL
SELECT 222,'B',50 UNION ALL
SELECT 333,'C',125 UNION ALL
SELECT 222,'B',70
--> 生成测试数据: @tb2
DECLARE @tb2 TABLE (品号 INT,单价 NUMERIC(2,1))
INSERT INTO @tb2
SELECT 111,0.3 UNION ALL
SELECT 222,0.5 UNION ALL
SELECT 111,0.1 UNION ALL
SELECT 333,1.5--SQL查询如下:SELECT
A.品号,
A.品名,
SUM(A.数量) AS 数量,
MIN(B.单价) AS 最低单价
FROM @tb1 AS A
JOIN @tb2 AS B
ON A.品号=B.品号
GROUP BY A.品号,A.品名/*
品号 品名 数量 最低单价
----------- ---- ----------- ---------------------------------------
111 A 200 0.1
222 B 120 0.5
333 C 125 1.5(3 row(s) affected)
*/
SELECT T.品号,T.品名,产量,最低单价
FROM
(SELECT 品号,品名,SUM(数量)AS产量 FROM A GROUP BY 品号,品名)AS T]
JOIN
(SELECT 品号,MIN(单价)AS 最低单价 FROM B GROUP BY 品号)AS T1
ON T.品号=T1.品号
---------------------------------
-- Author: liangCK 小梁
-- Date : 2009-05-19 15:43:32
---------------------------------
--> 生成测试数据: @tb1
DECLARE @tb1 TABLE (品号 INT,品名 VARCHAR(1),数量 INT)
INSERT INTO @tb1
SELECT 111,'A',100 UNION ALL
SELECT 222,'B',50 UNION ALL
SELECT 333,'C',125 UNION ALL
SELECT 222,'B',70
--> 生成测试数据: @tb2
DECLARE @tb2 TABLE (品号 INT,单价 NUMERIC(2,1))
INSERT INTO @tb2
SELECT 111,0.3 UNION ALL
SELECT 222,0.5 UNION ALL
SELECT 111,0.1 UNION ALL
SELECT 333,1.5--SQL查询如下:select a.品号, a.品名, sum(a.数量) as 产量, min(b.单价) as 最低单价, sum(a.数量) * min(b.单价) as 售价
from @tb1 a join @tb2 b on a.品号 = b.品号
group by a.品号, a.品名/*
品号 品名 产量 最低单价 售价
----------- ---- ----------- ---- ---------------
111 A 200 .1 20.0
222 B 120 .5 60.0
333 C 125 1.5 187.5
*/
哇真快!!借数据用用,谢谢,跟你学习
--> 生成测试数据: @tb1
DECLARE @tb1 TABLE (品号 INT,品名 VARCHAR(1),数量 INT)
INSERT INTO @tb1
SELECT 111,'A',100 UNION ALL
SELECT 222,'B',50 UNION ALL
SELECT 333,'C',125 UNION ALL
SELECT 222,'B',70
--> 生成测试数据: @tb2
DECLARE @tb2 TABLE (品号 INT,单价 NUMERIC(3,1))
INSERT INTO @tb2
SELECT 111,0.3 UNION ALL
SELECT 222,0.5 UNION ALL
SELECT 111,0.1 UNION ALL
SELECT 333,1.5SELECT T.品号,T.品名,产量,最低单价
FROM
(SELECT 品号,品名,SUM(数量)AS 产量 FROM @tb1 GROUP BY 品号,品名)AS T
JOIN
(SELECT 品号,MIN(单价) AS '最低单价' FROM @tb2 GROUP BY 品号)AS T1
ON T.品号=T1.品号(所影响的行数为 4 行)
(所影响的行数为 4 行)品号 品名 产量 最低单价
----------- ---- ----------- -----
111 A 100 .1
222 B 120 .5
333 C 125 1.5(所影响的行数为 3 行)
我这边是这样的.SELECT
A.品号,
A.品名,
SUM(A.数量) AS 数量,
MIN(B.单价) AS 最低单价
FROM A,B
where A.品号=B.品号,(....这里还有若干条件)
GROUP BY A.品号,A.品名 order by A.品号