--> liangCK小梁 于2008-10-08
--> 生成测试数据: #T
IF OBJECT_ID('tempdb.dbo.#T') IS NOT NULL DROP TABLE #T
CREATE TABLE #T (日期 DATETIME,编号 INT,数量 INT,单价 INT,订单号 INT,采购数量 INT,采购价格 INT)
INSERT INTO #T
SELECT '2008-05-05','20','5','50','1234','2','30' UNION ALL
SELECT '2008-05-08','20','5','50','1234','3','35'--SQL查询如下:SELECT 日期,CASE WHEN rid=1 THEN RTRIM(编号) ELSE '' END AS 编号,
CASE WHEN rid=1 THEN RTRIM(数量) ELSE '' END AS 数量,
CASE WHEN rid=1 THEN RTRIM(单价) ELSE '' END AS 单价,
CASE WHEN rid=1 THEN RTRIM(订单号) ELSE '' END AS 订单号,
采购数量,采购价格
FROM
(
SELECT *,rid=ROW_NUMBER() OVER(PARTITION BY 编号,数量,单价,订单号 ORDER BY 日期)
FROM #T
) AS t/*
日期 编号 数量 单价 订单号 采购数量 采购价格
----------------------- ------------ ------------ ------------ ------------ ----------- -----------
2008-05-05 00:00:00.000 20 5 50 1234 2 30
2008-05-08 00:00:00.000 3 35(2 行受影响)*/
--> 生成测试数据: #T
IF OBJECT_ID('tempdb.dbo.#T') IS NOT NULL DROP TABLE #T
CREATE TABLE #T (日期 DATETIME,编号 INT,数量 INT,单价 INT,订单号 INT,采购数量 INT,采购价格 INT)
INSERT INTO #T
SELECT '2008-05-05','20','5','50','1234','2','30' UNION ALL
SELECT '2008-05-08','20','5','50','1234','3','35'--SQL查询如下:SELECT 日期,CASE WHEN rid=1 THEN RTRIM(编号) ELSE '' END AS 编号,
CASE WHEN rid=1 THEN RTRIM(数量) ELSE '' END AS 数量,
CASE WHEN rid=1 THEN RTRIM(单价) ELSE '' END AS 单价,
CASE WHEN rid=1 THEN RTRIM(订单号) ELSE '' END AS 订单号,
采购数量,采购价格
FROM
(
SELECT *,rid=ROW_NUMBER() OVER(PARTITION BY 编号,数量,单价,订单号 ORDER BY 日期)
FROM #T
) AS t/*
日期 编号 数量 单价 订单号 采购数量 采购价格
----------------------- ------------ ------------ ------------ ------------ ----------- -----------
2008-05-05 00:00:00.000 20 5 50 1234 2 30
2008-05-08 00:00:00.000 3 35(2 行受影响)*/
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货