34,576
社区成员
发帖
与我相关
我的任务
分享
-- Sql2000
--有一个表如下
SELECT * FROM
(
select 'dw_a' as 单位,'sp_1' as 商品,'2012-02-03' as 销售日期,'17.0' as 单价 union
select 'dw_a' as 单位,'sp_1' as 商品,'2012-04-13' as 销售日期,'21.0' as 单价 union
select 'dw_a' as 单位,'sp_1' as 商品,'2012-05-16' as 销售日期,'16.0' as 单价 union
select 'dw_a' as 单位,'sp_2' as 商品,'2012-01-03' as 销售日期,'11.0' as 单价 union
select 'dw_a' as 单位,'sp_2' as 商品,'2012-02-13' as 销售日期,'12.0' as 单价 union
select 'dw_a' as 单位,'sp_2' as 商品,'2012-03-16' as 销售日期,'15.0' as 单价 union
select 'dw_b' as 单位,'sp_1' as 商品,'2011-02-03' as 销售日期,'12.0' as 单价 union
select 'dw_b' as 单位,'sp_1' as 商品,'2012-04-13' as 销售日期,'121.0' as 单价 union
select 'dw_b' as 单位,'sp_1' as 商品,'2012-05-16' as 销售日期,'136.0' as 单价 union
select 'dw_b' as 单位,'sp_2' as 商品,'2012-01-03' as 销售日期,'101.0' as 单价 union
select 'dw_b' as 单位,'sp_2' as 商品,'2012-02-13' as 销售日期,'102.0' as 单价 union
select 'dw_b' as 单位,'sp_2' as 商品,'2012-03-16' as 销售日期,'105.0' as 单价 ) A
--求一个查询,得到各单位,各商品的最后日期的售价
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'tba')
BEGIN
DROP TABLE tba
END
GO
CREATE TABLE tba
(
单位 VARCHAR(100),
商品 VARCHAR(10),
销售日期 VARCHAR(10),
单价 VARCHAR(10)
)
GO
INSERT INTO tba
select 'dw_a' as 单位,'sp_1' as 商品,'2012-02-03' as 销售日期,'17.0' as 单价 union
select 'dw_a' as 单位,'sp_1' as 商品,'2012-04-13' as 销售日期,'21.0' as 单价 union
select 'dw_a' as 单位,'sp_1' as 商品,'2012-05-16' as 销售日期,'16.0' as 单价 union
select 'dw_a' as 单位,'sp_2' as 商品,'2012-01-03' as 销售日期,'11.0' as 单价 union
select 'dw_a' as 单位,'sp_2' as 商品,'2012-02-13' as 销售日期,'12.0' as 单价 union
select 'dw_a' as 单位,'sp_2' as 商品,'2012-03-16' as 销售日期,'15.0' as 单价 union
select 'dw_b' as 单位,'sp_1' as 商品,'2011-02-03' as 销售日期,'12.0' as 单价 union
select 'dw_b' as 单位,'sp_1' as 商品,'2012-04-13' as 销售日期,'121.0' as 单价 union
select 'dw_b' as 单位,'sp_1' as 商品,'2012-05-17' as 销售日期,'136.0' as 单价 union
select 'dw_b' as 单位,'sp_2' as 商品,'2012-01-03' as 销售日期,'101.0' as 单价 union
select 'dw_b' as 单位,'sp_2' as 商品,'2012-02-16' as 销售日期,'102.0' as 单价 union
select 'dw_b' as 单位,'sp_2' as 商品,'2012-03-17' as 销售日期,'105.0' as 单价
SELECT * FROM tba AS A
WHERE 销售日期 = (SELECT TOP 1 销售日期 FROM tba WHERE A.单位 = 单位 AND A.商品 = 商品 ORDER BY 销售日期 DESC)
单位 商品 销售日期 单价
dw_a sp_1 2012-05-16 16.0
dw_a sp_2 2012-03-16 15.0
dw_b sp_1 2012-05-17 136.0
dw_b sp_2 2012-03-17 105.0
SELECT B.单位 ,
B.商品 ,
B.销售日期 ,
B.单价
FROM ( SELECT 'dw_a' AS 单位 ,
'sp_1' AS 商品 ,
'2012-02-03' AS 销售日期 ,
'17.0' AS 单价
UNION
SELECT 'dw_a' AS 单位 ,
'sp_1' AS 商品 ,
'2012-04-13' AS 销售日期 ,
'21.0' AS 单价
UNION
SELECT 'dw_a' AS 单位 ,
'sp_1' AS 商品 ,
'2012-05-16' AS 销售日期 ,
'16.0' AS 单价
UNION
SELECT 'dw_a' AS 单位 ,
'sp_2' AS 商品 ,
'2012-01-03' AS 销售日期 ,
'11.0' AS 单价
UNION
SELECT 'dw_a' AS 单位 ,
'sp_2' AS 商品 ,
'2012-02-13' AS 销售日期 ,
'12.0' AS 单价
UNION
SELECT 'dw_a' AS 单位 ,
'sp_2' AS 商品 ,
'2012-03-16' AS 销售日期 ,
'15.0' AS 单价
UNION
SELECT 'dw_b' AS 单位 ,
'sp_1' AS 商品 ,
'2011-02-03' AS 销售日期 ,
'12.0' AS 单价
UNION
SELECT 'dw_b' AS 单位 ,
'sp_1' AS 商品 ,
'2012-04-13' AS 销售日期 ,
'121.0' AS 单价
UNION
SELECT 'dw_b' AS 单位 ,
'sp_1' AS 商品 ,
'2012-05-16' AS 销售日期 ,
'136.0' AS 单价
UNION
SELECT 'dw_b' AS 单位 ,
'sp_2' AS 商品 ,
'2012-01-03' AS 销售日期 ,
'101.0' AS 单价
UNION
SELECT 'dw_b' AS 单位 ,
'sp_2' AS 商品 ,
'2012-02-13' AS 销售日期 ,
'102.0' AS 单价
UNION
SELECT 'dw_b' AS 单位 ,
'sp_2' AS 商品 ,
'2012-03-16' AS 销售日期 ,
'105.0' AS 单价
) B
WHERE B.销售日期 >= ( SELECT MAX(销售日期)
FROM ( SELECT 'dw_a' AS 单位 ,
'sp_1' AS 商品 ,
'2012-02-03' AS 销售日期 ,
'17.0' AS 单价
UNION
SELECT 'dw_a' AS 单位 ,
'sp_1' AS 商品 ,
'2012-04-13' AS 销售日期 ,
'21.0' AS 单价
UNION
SELECT 'dw_a' AS 单位 ,
'sp_1' AS 商品 ,
'2012-05-16' AS 销售日期 ,
'16.0' AS 单价
UNION
SELECT 'dw_a' AS 单位 ,
'sp_2' AS 商品 ,
'2012-01-03' AS 销售日期 ,
'11.0' AS 单价
UNION
SELECT 'dw_a' AS 单位 ,
'sp_2' AS 商品 ,
'2012-02-13' AS 销售日期 ,
'12.0' AS 单价
UNION
SELECT 'dw_a' AS 单位 ,
'sp_2' AS 商品 ,
'2012-03-16' AS 销售日期 ,
'15.0' AS 单价
UNION
SELECT 'dw_b' AS 单位 ,
'sp_1' AS 商品 ,
'2011-02-03' AS 销售日期 ,
'12.0' AS 单价
UNION
SELECT 'dw_b' AS 单位 ,
'sp_1' AS 商品 ,
'2012-04-13' AS 销售日期 ,
'121.0' AS 单价
UNION
SELECT 'dw_b' AS 单位 ,
'sp_1' AS 商品 ,
'2012-05-16' AS 销售日期 ,
'136.0' AS 单价
UNION
SELECT 'dw_b' AS 单位 ,
'sp_2' AS 商品 ,
'2012-01-03' AS 销售日期 ,
'101.0' AS 单价
UNION
SELECT 'dw_b' AS 单位 ,
'sp_2' AS 商品 ,
'2012-02-13' AS 销售日期 ,
'102.0' AS 单价
UNION
SELECT 'dw_b' AS 单位 ,
'sp_2' AS 商品 ,
'2012-03-16' AS 销售日期 ,
'105.0' AS 单价
) C
WHERE C.单位 = B.单位
AND C.商品 = B.商品
)
/*
单位 商品 销售日期 单价
dw_a sp_1 2012-05-16 16.0
dw_a sp_2 2012-03-16 15.0
dw_b sp_1 2012-05-16 136.0
dw_b sp_2 2012-03-16 105.0
*/
SELECT * INTO #t FROM
(
select 'dw_a' as 单位,'sp_1' as 商品,'2012-02-03' as 销售日期,'17.0' as 单价 union
select 'dw_a' as 单位,'sp_1' as 商品,'2012-04-13' as 销售日期,'21.0' as 单价 union
select 'dw_a' as 单位,'sp_1' as 商品,'2012-05-16' as 销售日期,'16.0' as 单价 union
select 'dw_a' as 单位,'sp_2' as 商品,'2012-01-03' as 销售日期,'11.0' as 单价 union
select 'dw_a' as 单位,'sp_2' as 商品,'2012-02-13' as 销售日期,'12.0' as 单价 union
select 'dw_a' as 单位,'sp_2' as 商品,'2012-03-16' as 销售日期,'15.0' as 单价 union
select 'dw_b' as 单位,'sp_1' as 商品,'2011-02-03' as 销售日期,'12.0' as 单价 union
select 'dw_b' as 单位,'sp_1' as 商品,'2012-04-13' as 销售日期,'121.0' as 单价 union
select 'dw_b' as 单位,'sp_1' as 商品,'2012-05-16' as 销售日期,'136.0' as 单价 union
select 'dw_b' as 单位,'sp_2' as 商品,'2012-01-03' as 销售日期,'101.0' as 单价 union
select 'dw_b' as 单位,'sp_2' as 商品,'2012-02-13' as 销售日期,'102.0' as 单价 union
select 'dw_b' as 单位,'sp_2' as 商品,'2012-03-16' as 销售日期,'105.0' as 单价
) A
SELECT * FROM #t t
WHERE 销售日期=(SELECT MAX(销售日期) FROM #t WHERE 单位=t.单位 AND 商品=t.商品)
/*
单位 商品 销售日期 单价
---- ---- ---------- -----
dw_b sp_2 2012-03-16 105.0
dw_b sp_1 2012-05-16 136.0
dw_a sp_2 2012-03-16 15.0
dw_a sp_1 2012-05-16 16.0
*/
DROP TABLE #t
declare @A table(单位 varchar(20),商品 varchar(20),销售日期 varchar(20),单价 float)
insert into @A
SELECT * FROM
(
select 'dw_a' as 单位,'sp_1' as 商品,'2012-02-03' as 销售日期,'17.0' as 单价 union
select 'dw_a' as 单位,'sp_1' as 商品,'2012-04-13' as 销售日期,'21.0' as 单价 union
select 'dw_a' as 单位,'sp_1' as 商品,'2012-05-16' as 销售日期,'16.0' as 单价 union
select 'dw_a' as 单位,'sp_2' as 商品,'2012-01-03' as 销售日期,'11.0' as 单价 union
select 'dw_a' as 单位,'sp_2' as 商品,'2012-02-13' as 销售日期,'12.0' as 单价 union
select 'dw_a' as 单位,'sp_2' as 商品,'2012-03-16' as 销售日期,'15.0' as 单价 union
select 'dw_b' as 单位,'sp_1' as 商品,'2011-02-03' as 销售日期,'12.0' as 单价 union
select 'dw_b' as 单位,'sp_1' as 商品,'2012-04-13' as 销售日期,'121.0' as 单价 union
select 'dw_b' as 单位,'sp_1' as 商品,'2012-05-16' as 销售日期,'136.0' as 单价 union
select 'dw_b' as 单位,'sp_2' as 商品,'2012-01-03' as 销售日期,'101.0' as 单价 union
select 'dw_b' as 单位,'sp_2' as 商品,'2012-02-13' as 销售日期,'102.0' as 单价 union
select 'dw_b' as 单位,'sp_2' as 商品,'2012-03-16' as 销售日期,'105.0' as 单价 ) A
select * from @A a1 where a1.销售日期=(select max(销售日期) from @A a2
where a1.单位=a2.单位 and a1.商品=a2.商品)
order by 单位,商品
/*
单位 商品 销售日期 单价
-------------------- -------------------- -------------------- ----------------------
dw_a sp_1 2012-05-16 16
dw_a sp_2 2012-03-16 15
dw_b sp_1 2012-05-16 136
dw_b sp_2 2012-03-16 105
(4 row(s) affected)
*/