特例的处理:select
a.dishid,
a.dishname,
unit1 = a.unit,
price1 = a.price,
unit2 = max(case b.unit when '中' then b.unit end),
price2 = max(case b.unit when '中' then b.price end),
unit3 = max(case b.unit when '大' then b.unit end),
price3 = max(case b.unit when '大' then b.price end)
from
dish a
left join
dish_unit b
group by
a.dishid,a.dishname,a.unit,a.price
a.dishid,
a.dishname,
unit1 = a.unit,
price1 = a.price,
unit2 = max(case b.unit when '中' then b.unit end),
price2 = max(case b.unit when '中' then b.price end),
unit3 = max(case b.unit when '大' then b.unit end),
price3 = max(case b.unit when '大' then b.price end)
from
dish a
left join
dish_unit b
group by
a.dishid,a.dishname,a.unit,a.price
dish表中记录:
dishid,dishname,unit,price。
1001 碟子 小 5.00
1002 碗 个 3.00
1003 勺子 把 5.00
1004 纸巾 包 2.00
1005 啤酒 瓶 4.00dish_unit表中记录:
dishid,unit,price
1001 中 8.00
1001 大 12.00
1004 盒 12.00
1005 打 48.00
1005 半打 24.00
1005 听 2.00
a.dishid,
a.dishname,
unit1 = a.unit,
price1 = a.price,
unit2 = max(case b.unit when '中' then b.unit end),
price2 = max(case b.unit when '中' then b.price end),
unit3 = max(case b.unit when '大' then b.unit end),
price3 = max(case b.unit when '大' then b.price end)
from
dish a
left join
dish_unit b
on
a.dishid = b.dishid
group by
a.dishid,a.dishname,a.unit,a.price
create table dish(dishid int,dishname varchar(20),unit varchar(10),price money)
insert into dish select 1001,rtrim('碟子'),'小',5.00
insert into dish select 1002,rtrim('碗 '),'个',3.00
insert into dish select 1003,rtrim('勺子'),'把',5.00create table dish_unit(dishid int,unit varchar(10),price money)
insert into dish_unit select 1001,'中', 8.00
insert into dish_unit select 1001,'大',12.00
--执行动态查询
select identity(int,1,1) as id,0 as nid,* into #t from dish_unit
update c set nid=d.cnt from #t c,(select a.id,cnt=count(b.id) from #t a,#t b where a.dishid=b.dishid and a.id>=b.id group by a.id) d where c.id = d.id
select * from #tdeclare @s varchar(8000),@i int
select @i = max(nid)+1 from #tset @s = ''
while @i>1
begin
set @s = ',unit' +rtrim(@i)+'=max(case b.nid when '+rtrim(@i-1)+' then b.unit end)'
+',price'+rtrim(@i)+'=max(case b.nid when '+rtrim(@i-1)+' then b.price end)'
+@s
set @i = @i - 1
end
set @s = 'select a.dishid,a.dishname,unit1 = a.unit,price1 = a.price'+@s+' from dish a left join #t b on a.dishid=b.dishid group by a.dishid,a.dishname,a.unit,a.price'exec(@s)--输出查询结果
dishid dishname unit1 price1 unit2 price2 unit3 price3
------ -------- ----- ------ ----- ------ ----- ------
1001 碟子 小 5.00 中 8.00 大 12.00
1002 碗 个 3.00
1003 勺子 把 5.00
create table dish(dishid int,dishname varchar(20),unit varchar(10),price numeric(5,2))
insert into dish select 1001,rtrim('碟子'),'小',5.00
insert into dish select 1002,rtrim('碗 '),'个',3.00
insert into dish select 1003,rtrim('勺子'),'把',5.00create table dish_unit(dishid int,unit varchar(10),price numeric(5,2))
insert into dish_unit select 1001,'中' ,8.00
insert into dish_unit select 1001,'大' ,12.00
insert into dish_unit select 1002,'盒' ,12.00
insert into dish_unit select 1003,'打' ,48.00
insert into dish_unit select 1003,'半打',24.00
insert into dish_unit select 1003,'听' ,2.00
go
--执行动态查询
select identity(int,1,1) as id,0 as nid,* into #t from dish_unit
update c set nid=d.cnt from #t c,(select a.id,cnt=count(b.id) from #t a,#t b where a.dishid=b.dishid and a.id>=b.id group by a.id) d where c.id = d.id
select * from #tdeclare @s varchar(8000),@i int
select @i = max(nid)+1 from #tset @s = ''
while @i>1
begin
set @s = ',unit' +rtrim(@i)+'=max(case b.nid when '+rtrim(@i-1)+' then b.unit end)'
+',price'+rtrim(@i)+'=max(case b.nid when '+rtrim(@i-1)+' then b.price end)'
+@s
set @i = @i - 1
end
set @s = 'select a.dishid,a.dishname,unit1 = a.unit,price1 = a.price'+@s+' from dish a left join #t b on a.dishid=b.dishid group by a.dishid,a.dishname,a.unit,a.price'exec(@s)
--输出查询结果
dishid dishname unit1 price1 unit2 price2 unit3 price3 unit3 price3
------ -------- ----- ------ ----- ------ ----- ------ ----- ------
1001 碟子 小 5.00 中 8.00 大 12.00 NULL NULL
1002 碗 个 3.00 盒 12.00 NULL NULL NULL NULL
1003 勺子 把 5.00 打 48.00 半打 24.00 听 2.00
create table dish(dishid int,dishname varchar(20),unit varchar(10),price numeric(5,2))
insert into dish select 1001,rtrim('碟子'),'小',5.00
insert into dish select 1002,rtrim('碗 '),'个',3.00
insert into dish select 1003,rtrim('勺子'),'把',5.00
insert into dish select 1004,rtrim('纸巾'),'包',2.00
insert into dish select 1005,rtrim('啤酒'),'瓶',4.00
create table dish_unit(dishid int,unit varchar(10),price numeric(5,2))
insert into dish_unit select 1001,'中' ,8.00
insert into dish_unit select 1001,'大' ,12.00
insert into dish_unit select 1004,'盒' ,12.00
insert into dish_unit select 1005,'打' ,48.00
insert into dish_unit select 1005,'半打',24.00
insert into dish_unit select 1005,'听' ,2.00
go
--执行动态查询
select identity(int,1,1) as id,0 as nid,* into #t from dish_unit
update c set nid=d.cnt from #t c,(select a.id,cnt=count(b.id) from #t a,#t b where a.dishid=b.dishid and a.id>=b.id group by a.id) d where c.id = d.id
select * from #tdeclare @s varchar(8000),@i int
select @i = max(nid)+1 from #tset @s = ''
while @i>1
begin
set @s = ',unit' +rtrim(@i)+'=max(case b.nid when '+rtrim(@i-1)+' then b.unit end)'
+',price'+rtrim(@i)+'=max(case b.nid when '+rtrim(@i-1)+' then b.price end)'
+@s
set @i = @i - 1
end
set @s = 'select a.dishid,a.dishname,unit1 = a.unit,price1 = a.price'+@s+' from dish a left join #t b on a.dishid=b.dishid group by a.dishid,a.dishname,a.unit,a.price'exec(@s)
--输出查询结果
dishid dishname unit1 price1 unit2 price2 unit3 price3 unit3 price3
------ -------- ----- ------ ----- ------ ----- ------ ----- ------
1001 碟子 小 5.00 中 8.00 大 12.00 NULL NULL
1002 碗 个 3.00 NULL NULL NULL NULL NULL NULL
1003 勺子 把 5.00 NULL NULL NULL NULL NULL NULL
1004 纸巾 包 2.00 盒 12.00 NULL NULL NULL NULL
1005 啤酒 瓶 4.00 打 48.00 半打 24.00 听 2.00
非常感谢,又可以多学点东西了。
数据库中已存在名为 '#t' 的对象。
上面的语句好像只能运行一次,我再次运行的时候就提示上面这个错误了。
请libin_ftsafe(子陌红尘) 再帮忙看看