100分求一超难SQL语句,与行列转换有关. 真正高手帮个忙!该怎么处理
100分求一超难SQL语句,与行列转换有关.. 真正高手帮个忙!!!
原表:
商品ID 用户ID 商品名称 添加日期
0001 user1 名称1 getDate()
0002 user2 名称2 getDate()
0003 user2 名称3 getDate()
0004 user2 名称4 getDate()
0005 user1 名称5 getDate()
想得到的结果:
用户ID 商品一 商品二
user1 名称1 名称5
user2 名称4 名称3
说明: 想把商品名 由列 转为 行, 但是只要top 2的商品名称,order by 日期 desc,也就是说
只把每个用户最新发布的两个商品名 转成 列 ,返回
(全分送上了,只为解决这个棘手的问题,结果正确,马上结贴给分)
------解决方案--------------------
CREATE TABLE t
(
商品ID VARCHAR(20),
用户ID VARCHAR(20),
商品名称 VARCHAR(20),
添加日期 DATETIME
)
INSERT INTO t
SELECT '0001','user1','名称1','2007-10-18 10:11:50.483' UNION ALL
SELECT '0002','user2','名称2','2007-10-18 10:21:50.483' UNION ALL
SELECT '0003','user2','名称3','2007-10-18 10:31:50.483' UNION ALL
SELECT '0004','user2','名称4','2007-10-18 10:41:50.483' UNION ALL
SELECT '0005','user1','名称5','2007-10-18 10:51:50.483'
DECLARE @sql VARCHAR(8000),@i INT
SELECT @sql = 'SELECT 用户ID',@i = 1
WHILE @i<3
SELECT @sql = @sql + ',[商品'+CAST(@i AS VARCHAR)+'] = MAX(CASE WHEN id = '+CAST(@i AS VARCHAR)+'
THEN 商品名称 END)',
@i = @i + 1
SELECT @sql = @sql + ' FROM (
SELECT id=(SELECT COUNT(1) FROM t WHERE A.用户ID = 用户ID AND A.添加日期 <= 添加日期),*
FROM t A WHERE (SELECT COUNT(1) FROM t WHERE A.用户ID=用户ID AND A.添加日期 < 添加日期)<2
) A GROUP BY 用户ID'
EXEC(@sql)
DROP TABLE t
用户ID 商品1 商品2
-------------------- -------------------- --------------------
user1 名称5 名称1
user2 名称4 名称3
警告: 聚合或其它 SET 操作消除了空值。
------解决方案--------------------
原表:
商品ID 用户ID 商品名称 添加日期
0001 user1 名称1 getDate()
0002 user2 名称2 getDate()
0003 user2 名称3 getDate()
0004 user2 名称4 getDate()
0005 user1 名称5 getDate()
想得到的结果:
用户ID 商品一 商品二
user1 名称1 名称5
user2 名称4 名称3
说明: 想把商品名 由列 转为 行, 但是只要top 2的商品名称,order by 日期 desc,也就是说
只把每个用户最新发布的两个商品名 转成 列 ,返回
(全分送上了,只为解决这个棘手的问题,结果正确,马上结贴给分)
------解决方案--------------------
CREATE TABLE t
(
商品ID VARCHAR(20),
用户ID VARCHAR(20),
商品名称 VARCHAR(20),
添加日期 DATETIME
)
INSERT INTO t
SELECT '0001','user1','名称1','2007-10-18 10:11:50.483' UNION ALL
SELECT '0002','user2','名称2','2007-10-18 10:21:50.483' UNION ALL
SELECT '0003','user2','名称3','2007-10-18 10:31:50.483' UNION ALL
SELECT '0004','user2','名称4','2007-10-18 10:41:50.483' UNION ALL
SELECT '0005','user1','名称5','2007-10-18 10:51:50.483'
DECLARE @sql VARCHAR(8000),@i INT
SELECT @sql = 'SELECT 用户ID',@i = 1
WHILE @i<3
SELECT @sql = @sql + ',[商品'+CAST(@i AS VARCHAR)+'] = MAX(CASE WHEN id = '+CAST(@i AS VARCHAR)+'
THEN 商品名称 END)',
@i = @i + 1
SELECT @sql = @sql + ' FROM (
SELECT id=(SELECT COUNT(1) FROM t WHERE A.用户ID = 用户ID AND A.添加日期 <= 添加日期),*
FROM t A WHERE (SELECT COUNT(1) FROM t WHERE A.用户ID=用户ID AND A.添加日期 < 添加日期)<2
) A GROUP BY 用户ID'
EXEC(@sql)
DROP TABLE t
用户ID 商品1 商品2
-------------------- -------------------- --------------------
user1 名称5 名称1
user2 名称4 名称3
警告: 聚合或其它 SET 操作消除了空值。
------解决方案--------------------
- SQL code
--result /*用户id 商品一 商品二 ------------------------------ ------------------------------ ------------------------------ user1 名称1 名称5 user2 名称4 名称3 */
------解决方案--------------------
- SQL code
create table tb(商品ID varchar(10),用户ID varchar(10),商品名称 varchar(10),添加日期 datetime) insert into tb values('0001','user1','名称1','2007-10-10') insert into tb values('0002','user2','名称2','2007-10-11') insert into tb values('0003','user2','名称3','2007-10-12') insert into tb values('0004','user2','名称4','2007-10-13') insert into tb values('0005','user1','名称5','2007-10-14') go --只显示前两个(1,2) select 用户ID, max(case px when 1 then 商品名称 else '' end) '商品一', max(case px when 2 then 商品名称 else '' end) '商品二' from ( select px=(select count(1) from tb where 用户ID=a.用户ID and 添加日期>a.添加日期)+1 , 用户ID,商品名称 from tb a ) t group by 用户ID /* 用户ID 商品一 商品二 ---------- ---------- ---------- user1 名称5 名称1 user2 名称4 名称3 (所影响的行数为 2 行) */ --如果都要显示,得用动态SQL declare @sql varchar(8000) set @sql = 'select 用户ID' select @sql = @sql + ' , max(case px when ''' + cast(px as varchar) + ''' then 商品名称 else '' '' end) [商品' + cast(px as varchar) + ']' from (select distinct px from (select px=(select count(1) from tb where 用户ID=a.用户ID and 添加日期>a.添加日期)+1 , 用户ID,商品名称 from tb a) t ) as a set @sql = @sql + ' from (select px=(select count(1) from tb where 用户ID=a.用户ID and 添加日期>a.添加日期)+1 , 用户ID,商品名称 from tb a) t group by 用户ID' exec(@sql) /* 用户ID 商品1 商品2 商品3 ---------- ---------- ---------- ---------- user1 名称5 名称1 user2 名称4 名称3 名称2 */ drop table tb