一对多表的行转列问题!!

wxf87541163 2007-12-07 09:19:41
表1:ProductPrice(产品价格)
ID | UserID | ProductID | WriteTime
----|--------|-----------|---------
1 | 1 | 2 | 2007-12-6
2 | 1 | 2 | 2007-12-7
3 | 2 | 1 | 2007-12-6
4 | 2 | 3 | 2007-12-1

表2:ProductPriceInfo(价格详情)

ProductPriceID | Area | Price
----------------|------|--------
1 | 杭州 | 30
1 | 上海 | 130
2 | 杭州 | 20
2 | 上海 | 13
3 | 武汉 | 34

表3:UserArea(用户地区)
ID | UserID | Area
----|--------|-----
1 | 1 | 杭州
2 | 1 | 上海
3 | 2 | 武汉

我现在想写个存储过程通过UserID和ProductID查询出这样的一张表:
UserID=1 and ProductID=2

WriteTime | 杭州 | 上海
-----------|-----|------
2007-12-6 | 30 | 130
2007-12-7 | 20 | 13

请问各位高手们应该怎么写这个存储过程啊??

...全文
132 6 打赏 收藏 举报
写回复
6 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
wxf87541163 2007-12-07
可以了!真的非常感谢!!
  • 打赏
  • 举报
回复
晓风残月0110 2007-12-07

create procedure Gettest
@UserID int,
@ProductID int
as
begin
select a.WriteTime,b.Area,b.Price into #
from ProductPrice a left join ProductPriceInfo b on a.ProductID = b.ProductPriceID
where a.UserID=@UserID and a.ProductID = @ProductID
declare @sql varchar(8000)
set @sql = 'select WriteTime'
select @sql = @sql + ',' + Area+'=sum(case area when ''' + Area+ ''' then Price else 0 end )'
from # group by Area order by Area
set @sql = @sql + ' from # group by WriteTime'
exec(@sql)
end

go
--测试
exec Gettest 1,2

go
--删除测试
drop table ProductPrice
drop table ProductPriceInfo
drop proc Gettest
WriteTime 杭州 上海
----------------------- ----------- -----------
2007-12-05 00:00:00.000 40 20
  • 打赏
  • 举报
回复
wxf87541163 2007-12-07
不好意思啊 不知道为什么查不出来啊。。这是表的数据,你能帮我试下么?
create table ProductPrice (ProductPriceID int,UserID int,ProductID int, WriteTime datetime)
insert into ProductPrice select 1,1,1,'2007-12-6'
insert into ProductPrice select 2,1,1,'2007-12-7'
insert into ProductPrice select 3,2,1,'2007-12-7'
insert into ProductPrice select 4,1,2,'2007-12-5'
create table ProductPriceInfo (ProductPriceID int,Area nvarchar(10),Price int)
insert into ProductPriceInfo select 1,'杭州',10
insert into ProductPriceInfo select 1,'上海',20
insert into ProductPriceInfo select 2,'杭州',40
insert into ProductPriceInfo select 2,'上海',20
  • 打赏
  • 举报
回复
晓风残月0110 2007-12-07

create procedure Gettest
@UserID int
@ProductID int
as
begin
select a.writetime,b.Area,b,Price into #
from t1 a left join t2 b on a.ProdeuctID = b.ProductPriceID
where UserID=@UserID and ProductID = @ProductID
declare @sql varchar(8000)
set @sql = 'select writetime'
select @sql = @sql + ',' + area+'=sum(case area when ''' + area+ ''' then price else 0 end )'
from # group by area order by area
set @sql = @sql + ' from # group by writetime'
end
  • 打赏
  • 举报
回复
wxf87541163 2007-12-07
请问(晓风残月)大哥 能不能写个存储过程啊 因为我是通过ProductPrice表里面的UserID和ProductID作为条件来查询的,比如:Where UserID = 1 and ProductID = 2 就可以查询出这样的结果
WriteTime ¦ 杭州 ¦ 上海
2007-12-6 ¦ 30 ¦ 130
2007-12-7 ¦ 20 ¦ 13
谢谢了。
  • 打赏
  • 举报
回复
晓风残月0110 2007-12-07

create table pro(writetime datetime,area nvarchar(10),price int)
insert into pro select '2007-12-6','杭州',30
insert into pro select '2007-12-6','杭州',130
insert into pro select '2007-12-7','上海',20
insert into pro select '2007-12-7','上海',13

declare @sql varchar(8000)
set @sql = 'select writetime'
select @sql = @sql + ',' + area+ '=sum(case area when ''' + area+ ''' then price else 0 end)'
from pro group by area order by area
set @sql = @sql + ' from pro group by writetime'
exec(@sql)
writetime 杭州 上海
----------------------- ----------- -----------
2007-12-06 00:00:00.000 160 0
2007-12-07 00:00:00.000 0 33
  • 打赏
  • 举报
回复
相关推荐
发帖
MS-SQL Server

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
帖子事件
创建了帖子
2007-12-07 09:19
社区公告
暂无公告