-- ==================求一个最后售价的查询================--
- SQL code
-- Sql2000--有一个表如下SELECT * FROM(select 'dw_a' as 单位,'sp_1' as 商品,'2012-02-03' as 销售日期,'17.0' as 单价 unionselect 'dw_a' as 单位,'sp_1' as 商品,'2012-04-13' as 销售日期,'21.0' as 单价 unionselect 'dw_a' as 单位,'sp_1' as 商品,'2012-05-16' as 销售日期,'16.0' as 单价 unionselect 'dw_a' as 单位,'sp_2' as 商品,'2012-01-03' as 销售日期,'11.0' as 单价 unionselect 'dw_a' as 单位,'sp_2' as 商品,'2012-02-13' as 销售日期,'12.0' as 单价 unionselect 'dw_a' as 单位,'sp_2' as 商品,'2012-03-16' as 销售日期,'15.0' as 单价 unionselect 'dw_b' as 单位,'sp_1' as 商品,'2011-02-03' as 销售日期,'12.0' as 单价 unionselect 'dw_b' as 单位,'sp_1' as 商品,'2012-04-13' as 销售日期,'121.0' as 单价 unionselect 'dw_b' as 单位,'sp_1' as 商品,'2012-05-16' as 销售日期,'136.0' as 单价 unionselect 'dw_b' as 单位,'sp_2' as 商品,'2012-01-03' as 销售日期,'101.0' as 单价 unionselect 'dw_b' as 单位,'sp_2' as 商品,'2012-02-13' as 销售日期,'102.0' as 单价 unionselect 'dw_b' as 单位,'sp_2' as 商品,'2012-03-16' as 销售日期,'105.0' as 单价 ) A--求一个查询，得到各单位，各商品的最后日期的售价
[解决办法]
- SQL code
declare @A table(单位 varchar(20),商品 varchar(20),销售日期 varchar(20),单价 float)insert into @ASELECT * FROM(select 'dw_a' as 单位,'sp_1' as 商品,'2012-02-03' as 销售日期,'17.0' as 单价 unionselect 'dw_a' as 单位,'sp_1' as 商品,'2012-04-13' as 销售日期,'21.0' as 单价 unionselect 'dw_a' as 单位,'sp_1' as 商品,'2012-05-16' as 销售日期,'16.0' as 单价 unionselect 'dw_a' as 单位,'sp_2' as 商品,'2012-01-03' as 销售日期,'11.0' as 单价 unionselect 'dw_a' as 单位,'sp_2' as 商品,'2012-02-13' as 销售日期,'12.0' as 单价 unionselect 'dw_a' as 单位,'sp_2' as 商品,'2012-03-16' as 销售日期,'15.0' as 单价 unionselect 'dw_b' as 单位,'sp_1' as 商品,'2011-02-03' as 销售日期,'12.0' as 单价 unionselect 'dw_b' as 单位,'sp_1' as 商品,'2012-04-13' as 销售日期,'121.0' as 单价 unionselect 'dw_b' as 单位,'sp_1' as 商品,'2012-05-16' as 销售日期,'136.0' as 单价 unionselect 'dw_b' as 单位,'sp_2' as 商品,'2012-01-03' as 销售日期,'101.0' as 单价 unionselect 'dw_b' as 单位,'sp_2' as 商品,'2012-02-13' as 销售日期,'102.0' as 单价 unionselect 'dw_b' as 单位,'sp_2' as 商品,'2012-03-16' as 销售日期,'105.0' as 单价 ) Aselect * 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 16dw_a sp_2 2012-03-16 15dw_b sp_1 2012-05-16 136dw_b sp_2 2012-03-16 105(4 row(s) affected)*/
[解决办法]
- SQL code
SELECT * INTO #t FROM(select 'dw_a' as 单位,'sp_1' as 商品,'2012-02-03' as 销售日期,'17.0' as 单价 unionselect 'dw_a' as 单位,'sp_1' as 商品,'2012-04-13' as 销售日期,'21.0' as 单价 unionselect 'dw_a' as 单位,'sp_1' as 商品,'2012-05-16' as 销售日期,'16.0' as 单价 unionselect 'dw_a' as 单位,'sp_2' as 商品,'2012-01-03' as 销售日期,'11.0' as 单价 unionselect 'dw_a' as 单位,'sp_2' as 商品,'2012-02-13' as 销售日期,'12.0' as 单价 unionselect 'dw_a' as 单位,'sp_2' as 商品,'2012-03-16' as 销售日期,'15.0' as 单价 unionselect 'dw_b' as 单位,'sp_1' as 商品,'2011-02-03' as 销售日期,'12.0' as 单价 unionselect 'dw_b' as 单位,'sp_1' as 商品,'2012-04-13' as 销售日期,'121.0' as 单价 unionselect 'dw_b' as 单位,'sp_1' as 商品,'2012-05-16' as 销售日期,'136.0' as 单价 unionselect 'dw_b' as 单位,'sp_2' as 商品,'2012-01-03' as 销售日期,'101.0' as 单价 unionselect 'dw_b' as 单位,'sp_2' as 商品,'2012-02-13' as 销售日期,'102.0' as 单价 unionselect 'dw_b' as 单位,'sp_2' as 商品,'2012-03-16' as 销售日期,'105.0' as 单价 ) ASELECT * FROM #t tWHERE 销售日期=(SELECT MAX(销售日期) FROM #t WHERE 单位=t.单位 AND 商品=t.商品)/*单位 商品 销售日期 单价---- ---- ---------- -----dw_b sp_2 2012-03-16 105.0dw_b sp_1 2012-05-16 136.0dw_a sp_2 2012-03-16 15.0dw_a sp_1 2012-05-16 16.0*/DROP TABLE #t
[解决办法]
- SQL code
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 单价 ) BWHERE 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.0dw_a sp_2 2012-03-16 15.0dw_b sp_1 2012-05-16 136.0dw_b sp_2 2012-03-16 105.0*/
[解决办法]
- SQL code
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'tba')BEGIN DROP TABLE tbaENDGOCREATE TABLE tba( 单位 VARCHAR(100), 商品 VARCHAR(10), 销售日期 VARCHAR(10), 单价 VARCHAR(10))GOINSERT INTO tbaselect 'dw_a' as 单位,'sp_1' as 商品,'2012-02-03' as 销售日期,'17.0' as 单价 unionselect 'dw_a' as 单位,'sp_1' as 商品,'2012-04-13' as 销售日期,'21.0' as 单价 unionselect 'dw_a' as 单位,'sp_1' as 商品,'2012-05-16' as 销售日期,'16.0' as 单价 unionselect 'dw_a' as 单位,'sp_2' as 商品,'2012-01-03' as 销售日期,'11.0' as 单价 unionselect 'dw_a' as 单位,'sp_2' as 商品,'2012-02-13' as 销售日期,'12.0' as 单价 unionselect 'dw_a' as 单位,'sp_2' as 商品,'2012-03-16' as 销售日期,'15.0' as 单价 unionselect 'dw_b' as 单位,'sp_1' as 商品,'2011-02-03' as 销售日期,'12.0' as 单价 unionselect 'dw_b' as 单位,'sp_1' as 商品,'2012-04-13' as 销售日期,'121.0' as 单价 unionselect 'dw_b' as 单位,'sp_1' as 商品,'2012-05-17' as 销售日期,'136.0' as 单价 unionselect 'dw_b' as 单位,'sp_2' as 商品,'2012-01-03' as 销售日期,'101.0' as 单价 unionselect 'dw_b' as 单位,'sp_2' as 商品,'2012-02-16' as 销售日期,'102.0' as 单价 unionselect 'dw_b' as 单位,'sp_2' as 商品,'2012-03-17' as 销售日期,'105.0' as 单价SELECT * FROM tba AS AWHERE 销售日期 = (SELECT TOP 1 销售日期 FROM tba WHERE A.单位 = 单位 AND A.商品 = 商品 ORDER BY 销售日期 DESC)单位 商品 销售日期 单价dw_a sp_1 2012-05-16 16.0dw_a sp_2 2012-03-16 15.0dw_b sp_1 2012-05-17 136.0dw_b sp_2 2012-03-17 105.0
[解决办法]
http://topic.csdn.net/u/20080626/00/43d0d10c-28f1-418d-a05b-663880da278a.html