27,579
社区成员
发帖
与我相关
我的任务
分享
create table tk3(spbh varchar(20),spmch nvarchar(30),price int,qty int,je as price*qty )
select identity(int,1,1) as id,* into #tk3 from tk3
select a.spbh,a.spmch,a.price,(select price from #tk3 where id>a.id and spmch=a.spmch) price2 from #tk3 a
where (select price from #tk3 where id>a.id and spmch=a.spmch) is not null
drop table #tk3
哥们你这个表结构是有问题的,实际的设计比如会员就和售价,总有个字段能标示,这样行转列就有判断的依据,你这样的。只能自己增加定义咧了。
create table A表
(产品编号 varchar(10),名称 varchar(10),
单价 int,数量 int,金额 int)
insert into A表
select '001', '记事本', 12, 10, 120 union all
select '002', '计算机', 20, 42, 840 union all
select '001', '记事本', 25, 33, 825 union all
select '002', '计算机', 13, 12, 156
declare @tsql varchar(6000)
select @tsql=isnull(@tsql+',','')
+'max(case when 产品编号=t.产品编号 and rn='+rtrim(number)+' then 单价 else 0 end) ''单价'+rtrim(number)+''' '
from master.dbo.spt_values
where type='P' and number>=1
and number<=
(select max(qty)
from (select count(单价) 'qty' from A表 group by 产品编号) t)
select @tsql='select 产品编号,名称,'+@tsql+
' from (select 产品编号,名称,单价,
row_number() over(partition by 产品编号 order by rn0) ''rn''
from (select *,row_number() over(order by getdate()) ''rn0'' from A表) u) t
group by 产品编号,名称
order by 产品编号 '
exec(@tsql)
/*
产品编号 名称 单价1 单价2
---------- ---------- ----------- -----------
001 记事本 12 25
002 计算机 20 13
(2 row(s) affected)
*/
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2014-01-14 07:54:57
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
-- Dec 28 2012 20:23:12
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([产品编号] varchar(3),[名称] varchar(6),[单价] int,[数量] int,[金额] int)
insert [huang]
select '001','记事本',12,10,120 union all
select '002','计算机',20,42,840 union all
select '001','记事本',25,33,825 union all
select '002','计算机',13,12,156
--------------开始查询--------------------------
SELECT [产品编号],[名称],MAX(CASE WHEN id=1 THEN 单价 ELSE NULL END)[单价1] ,MAX(CASE WHEN id=2 THEN 单价 ELSE NULL END )[单价2]
FROM (
select * ,ROW_NUMBER()OVER(PARTITION BY [产品编号] ORDER BY [产品编号])id
from [huang])a
GROUP BY [产品编号],[名称]
----------------结果----------------------------
/*
产品编号 名称 单价1 单价2
---- ------ ----------- -----------
001 记事本 12 25
002 计算机 13 20
*/