请教:价格分析 原贴在以下地址: http://topic.csdn.net/u/20090906/11/E1E4EA61-84C3-4496-9C09-C4EBA4A38B8D.html如果某货品当天有两次采购单价相同的为什么是4行显示??
IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TB(
BD DATETIME,
VENDORNO VARCHAR(50),
GOODS VARCHAR(50),
QTY NUMERIC(19,2),
PRICE NUMERIC(19,6)
)
INSERT INTO TB
SELECT '2009-08-01','G001','A001', 100, 2 UNION ALL
SELECT '2009-08-10','G001','A001', 90, 2
DECLARE @BDATE DATETIME,@EDATE DATETIME
SELECT @BDATE='2009-08-01',@EDATE='2009-08-31'
SELECT T.GOODS '商品'
,T1.PRICE '最低价',T1.VENDORNO '供应商',T1.BD '时间'
,T2.PRICE '最高价',T2.VENDORNO '供应商',T2.BD '时间'
,T3.PRICE '最近价',T2.VENDORNO '供应商',T3.BD '时间'
FROM
(SELECT DISTINCT GOODS FROM TB
WHERE BD >= @BDATE AND BD<=@EDATE) T
INNER JOIN (
SELECT GOODS,VENDORNO,PRICE,BD FROM TB T1
WHERE BD >= @BDATE AND BD<=@EDATE and NOT EXISTS(SELECT 1 FROM TB T2
WHERE T2.BD >= @BDATE AND T2.BD<=@EDATE AND T2.GOODS=T1.GOODS AND T2.PRICE<T1.PRICE)
) T1 ON T.GOODS=T1.GOODSINNER JOIN (
SELECT GOODS,VENDORNO,PRICE,BD FROM TB T1
WHERE BD >= @BDATE AND BD<=@EDATE and NOT EXISTS(SELECT 1 FROM TB T2
WHERE T2.BD >= @BDATE AND T2.BD<=@EDATE AND T2.GOODS=T1.GOODS AND T2.PRICE>T1.PRICE)
) T2 ON T.GOODS=T2.GOODSINNER JOIN (
SELECT GOODS,VENDORNO,PRICE,BD FROM TB T1
WHERE BD >= @BDATE AND BD<=@EDATE and NOT EXISTS(SELECT 1 FROM TB T2
WHERE T2.BD >= @BDATE AND T2.BD<=@EDATE AND T2.GOODS=T1.GOODS AND T2.BD>T1.BD)
) T3 ON T.GOODS=T3.GOODS
查询如果如下,请问有没有办法查询结课只有一行显示?谢谢!!
/**A001 2.000000 G001 2009-08-01 00:00:00.000 2.000000 G001 2009-08-01 00:00:00.000 2.000000 G001 2009-08-10 00:00:00.000
A001 2.000000 G001 2009-08-10 00:00:00.000 2.000000 G001 2009-08-01 00:00:00.000 2.000000 G001 2009-08-10 00:00:00.000
A001 2.000000 G001 2009-08-01 00:00:00.000 2.000000 G001 2009-08-10 00:00:00.000 2.000000 G001 2009-08-10 00:00:00.000
A001 2.000000 G001 2009-08-10 00:00:00.000 2.000000 G001 2009-08-10 00:00:00.000 2.000000 G001 2009-08-10 00:00:00.000**/
IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TB(
BD DATETIME,
VENDORNO VARCHAR(50),
GOODS VARCHAR(50),
QTY NUMERIC(19,2),
PRICE NUMERIC(19,6)
)
INSERT INTO TB
SELECT '2009-08-01','G001','A001', 100, 2 UNION ALL
SELECT '2009-08-10','G001','A001', 90, 2
DECLARE @BDATE DATETIME,@EDATE DATETIME
SELECT @BDATE='2009-08-01',@EDATE='2009-08-31'
SELECT T.GOODS '商品'
,T1.PRICE '最低价',T1.VENDORNO '供应商',T1.BD '时间'
,T2.PRICE '最高价',T2.VENDORNO '供应商',T2.BD '时间'
,T3.PRICE '最近价',T2.VENDORNO '供应商',T3.BD '时间'
FROM
(SELECT DISTINCT GOODS FROM TB
WHERE BD >= @BDATE AND BD<=@EDATE) T
INNER JOIN (
SELECT GOODS,VENDORNO,PRICE,BD FROM TB T1
WHERE BD >= @BDATE AND BD<=@EDATE and NOT EXISTS(SELECT 1 FROM TB T2
WHERE T2.BD >= @BDATE AND T2.BD<=@EDATE AND T2.GOODS=T1.GOODS AND T2.PRICE<T1.PRICE)
) T1 ON T.GOODS=T1.GOODSINNER JOIN (
SELECT GOODS,VENDORNO,PRICE,BD FROM TB T1
WHERE BD >= @BDATE AND BD<=@EDATE and NOT EXISTS(SELECT 1 FROM TB T2
WHERE T2.BD >= @BDATE AND T2.BD<=@EDATE AND T2.GOODS=T1.GOODS AND T2.PRICE>T1.PRICE)
) T2 ON T.GOODS=T2.GOODSINNER JOIN (
SELECT GOODS,VENDORNO,PRICE,BD FROM TB T1
WHERE BD >= @BDATE AND BD<=@EDATE and NOT EXISTS(SELECT 1 FROM TB T2
WHERE T2.BD >= @BDATE AND T2.BD<=@EDATE AND T2.GOODS=T1.GOODS AND T2.BD>T1.BD)
) T3 ON T.GOODS=T3.GOODS
查询如果如下,请问有没有办法查询结课只有一行显示?谢谢!!
/**A001 2.000000 G001 2009-08-01 00:00:00.000 2.000000 G001 2009-08-01 00:00:00.000 2.000000 G001 2009-08-10 00:00:00.000
A001 2.000000 G001 2009-08-10 00:00:00.000 2.000000 G001 2009-08-01 00:00:00.000 2.000000 G001 2009-08-10 00:00:00.000
A001 2.000000 G001 2009-08-01 00:00:00.000 2.000000 G001 2009-08-10 00:00:00.000 2.000000 G001 2009-08-10 00:00:00.000
A001 2.000000 G001 2009-08-10 00:00:00.000 2.000000 G001 2009-08-10 00:00:00.000 2.000000 G001 2009-08-10 00:00:00.000**/
1/如果是同一供应商VENDORNO 时只取其中一个就行了
2/如果不是同一供应商VENDORNO时则需要取二个,分行显示
IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TB(
BD DATETIME,
VENDORNO VARCHAR(50),
GOODS VARCHAR(50),
QTY NUMERIC(19,2),
PRICE NUMERIC(19,6)
)
INSERT INTO TB
SELECT '2009-08-01','G001','A001', 100, 2 UNION ALL
SELECT '2009-08-10','G001','A001', 90, 2
DECLARE @BDATE DATETIME,@EDATE DATETIME
SELECT @BDATE='2009-08-01',@EDATE='2009-08-31'
SELECT DISTINCT T.GOODS '商品'
,T1.PRICE '最低价',T1.VENDORNO '供应商',MAX(T1.BD) '时间'
,T2.PRICE '最高价',T2.VENDORNO '供应商',MAX(T2.BD) '时间'
,T3.PRICE '最近价',T2.VENDORNO '供应商',T3.BD '时间'
FROM
(SELECT DISTINCT GOODS FROM TB
WHERE BD >= @BDATE AND BD <=@EDATE) T
INNER JOIN (
SELECT GOODS,VENDORNO,PRICE,BD FROM TB T1
WHERE BD >= @BDATE AND BD <=@EDATE and NOT EXISTS(SELECT 1 FROM TB T2
WHERE T2.BD >= @BDATE AND T2.BD <=@EDATE AND T2.GOODS=T1.GOODS AND T2.PRICE <T1.PRICE)
) T1 ON T.GOODS=T1.GOODS INNER JOIN (
SELECT GOODS,VENDORNO,PRICE,BD FROM TB T1
WHERE BD >= @BDATE AND BD <=@EDATE and NOT EXISTS(SELECT 1 FROM TB T2
WHERE T2.BD >= @BDATE AND T2.BD <=@EDATE AND T2.GOODS=T1.GOODS AND T2.PRICE>T1.PRICE)
) T2 ON T.GOODS=T2.GOODS INNER JOIN (
SELECT GOODS,VENDORNO,PRICE,BD FROM TB T1
WHERE BD >= @BDATE AND BD <=@EDATE and NOT EXISTS(SELECT 1 FROM TB T2
WHERE T2.BD >= @BDATE AND T2.BD <=@EDATE AND T2.GOODS=T1.GOODS AND T2.BD>T1.BD)
) T3 ON T.GOODS=T3.GOODS
GROUP BY T.GOODS ,T1.PRICE,T1.VENDORNO ,T2.PRICE ,T2.VENDORNO ,T3.PRICE ,T2.VENDORNO ,T3.BD