产品表
产品表ID（主键） 货名 金额
1 电视 100
2 手机 150
3 衣服 50库存表
库存表ID（主键） 产品表ID 打折 货存 <-----货存表只有三种状态 0，1，2
1 1 0.2 1
2 1 0.3 1
3 2 0.8 0
4 2 0.5 1
5 1 0.7 1
6 3 0.2 2
6 3 0.7 1
6 3 0.1 0我要得到以下结果统计
产品表货名 赢亏 <----计算 是否打折 <-----只有三种状态 0，1，2
电视 100*(1+0.2)*(1+0.3)*(1+0.7)-100=165.2 1 <---当 库存表 的 [货存] 字段全部等于 1 时，那么 [是否打折]=1
手机 150-(150+150)=-150 0 <---当 库存表 的 [货存] 字段有一个为 0, 那么 [是否打折]=0
衣服 0 2 <---当 库存表 的 [货存] 字段有一个为 2, 那么 [是否打折]=2
-------------------------------------------------------------
也就是说 [货存] 有三种状态 0 、 1 、 2，
当对应的这列 全部 为 1 时，那么 [是否打折]才等于1，（注意：是全部等于1，那么 [是否打折] 才等于2）
当对应的这列 有一行 为 0 时，那么 [是否打折]才等于0 ，（如果这列有一行 等于2，那么 [是否打折] 要等于2 。也就是2的优先级比0和1都高）
当对应的这列 有一行 为 2 时，那么 [是否打折]才等于2 ，（不管有0还是1，只要有一行等于 2，那么[是否打折]就等于2）
产品表ID（主键） 货名 金额
1 电视 100
2 手机 150
3 衣服 50库存表
库存表ID（主键） 产品表ID 打折 货存 <-----货存表只有三种状态 0，1，2
1 1 0.2 1
2 1 0.3 1
3 2 0.8 0
4 2 0.5 1
5 1 0.7 1
6 3 0.2 2
6 3 0.7 1
6 3 0.1 0我要得到以下结果统计
产品表货名 赢亏 <----计算 是否打折 <-----只有三种状态 0，1，2
电视 100*(1+0.2)*(1+0.3)*(1+0.7)-100=165.2 1 <---当 库存表 的 [货存] 字段全部等于 1 时，那么 [是否打折]=1
手机 150-(150+150)=-150 0 <---当 库存表 的 [货存] 字段有一个为 0, 那么 [是否打折]=0
衣服 0 2 <---当 库存表 的 [货存] 字段有一个为 2, 那么 [是否打折]=2
-------------------------------------------------------------
也就是说 [货存] 有三种状态 0 、 1 、 2，
当对应的这列 全部 为 1 时，那么 [是否打折]才等于1，（注意：是全部等于1，那么 [是否打折] 才等于2）
当对应的这列 有一行 为 0 时，那么 [是否打折]才等于0 ，（如果这列有一行 等于2，那么 [是否打折] 要等于2 。也就是2的优先级比0和1都高）
当对应的这列 有一行 为 2 时，那么 [是否打折]才等于2 ，（不管有0还是1，只要有一行等于 2，那么[是否打折]就等于2）
1 1 0.2 1
2 1 0.3 1
3 2 0.8 0
4 2 0.5 1
5 1 0.7 1
6 3 0.2 2
6 3 0.7 1
6 3 0.1 0100*(1+0.2)*(1+0.3)*(1+0.7)-100
3 2 0.8 0
4 2 0.5 1
5 2 0.7 1
已知产品表ID=2其中 [货存] 有一行等于 0，那么 [是打折]=0=====================================================库存表ID（主键） 产品表ID 打折 货存
3 2 0.8 2
4 2 0.5 1
5 2 0.7 0 其中 [货存] 有一行等于 2，那么 [是打折]=2
insert into 产品表 select
1 ,'电视', 100 union select
2 ,'手机', 150 union select
3 ,'衣服', 50
create table 库存表(ID int,PID int ,打折 numeric(12,2),货存 int)
insert into 库存表 select
1 , 1 , 0.2 , 1 union select
2 , 1 , 0.3 , 1 union select
3 , 2 , 0.8 , 0 union select
4 , 2 , 0.5 , 1 union select
5 , 1 , 0.7 , 1 union select
6 , 3 , 0.2 , 2 union select
6 , 3 , 0.7 , 1 union select
6 , 3 , 0.1 , 0
go
select a.货名,是否打折 = c.bz
from 产品表 a,
(select pid,sum(打折) as dz,case when exists(select 1 from 库存表 where pid = a.pid and 货存 = 2) then 2
else case when exists(select 1 from 库存表 where pid = a.pid and 货存 = 0 ) then 0
else 1 end end as bz
from 库存表 a
group by pid) c
where a.id = c.piddrop table 产品表,库存表/*货名 是否打折
---------- -----------
电视 1
手机 0
衣服 2（所影响的行数为 3 行）
*/
--> 测试数据: #P
if object_id('tempdb.dbo.#P') is not null drop table #P
create table #P (ID int,货名 varchar(4),金额 int)
insert into #P
select 1,'电视',100 union all
select 2,'手机',150 union all
select 3,'衣服',50
--> 测试数据: #S
if object_id('tempdb.dbo.#S') is not null drop table #S
create table #S (ID int,产品表ID int,打折 numeric(2,1),货存 int)
insert into #S
select 1,1,0.2,1 union all
select 2,1,0.3,1 union all
select 3,2,0.8,0 union all
select 4,2,0.5,1 union all
select 5,1,0.7,1 union all
select 6,3,0.2,2 union all
select 6,3,0.7,1 union all
select 6,3,0.1,0select
货名 as 产品表货名,
--盈亏=case when max(b.货存)=2 then 0 when min(b.货存)=0 then min(a.金额)-sum(a.金额) else sum(a.金额*(1+b.打折))-min(a.金额) end,
盈亏=case when max(b.货存)=2 then 0 when min(b.货存)=0 then min(a.金额)-sum(a.金额) else min(a.金额)*exp(sum(log(1+b.打折)))-min(a.金额) end,
是否打折=case when max(b.货存)=2 then 2 when min(b.货存)=0 then 0 else 1 end
from
#P a
inner join
#S b
on a.ID=b.产品表ID
group by
a.货名/*
产品表货名 盈亏 是否打折
---------- ---------- ----------
电视 165.2 1
手机 -150 0
衣服 0 2
*/
insert into 产品表 select
1 ,'电视', 100 union select
2 ,'手机', 150 union select
3 ,'衣服', 50
create table 库存表(ID int,PID int ,打折 numeric(12,2),货存 int)
insert into 库存表 select
1 , 1 , 0.2 , 1 union select
2 , 1 , 0.3 , 1 union select
3 , 2 , 0.8 , 0 union select
4 , 2 , 0.5 , 1 union select
5 , 1 , 0.7 , 1 union select
6 , 3 , 0.2 , 2 union select
6 , 3 , 0.7 , 1 union select
6 , 3 , 0.1 , 0
goselect b.货名,case when exists(select 1 from 库存表 where pid = a.pid and 货存 = 2) then 2
else case when exists(select 1 from 库存表 where pid = a.pid and 货存 = 0 ) then 0
else 1 end end as bz
from 库存表 a,产品表 b
where b.id = a.pid
group by b.货名,a.pid
drop table 产品表,库存表/*货名 是否打折
---------- -----------
电视 1
手机 0
衣服 2（所影响的行数为 3 行）
*/
/*回复：代码20080513003 总：00000000010 */
/*主题：A表 查询B表列的全部行 */
/*作者：二等草 */
/******************************************//************例子数据 begin****************/
declare @p table(id int,name varchar(10),money int)
insert @p select 1, '电视', 100
insert @p select 2, '手机', 150
insert @p select 3, '衣服', 50
declare @s table(id int,pid int,discount dec(4,2),status int)
insert @s select 1, 1, 0.2, 1
insert @s select 2, 1, 0.3, 1
insert @s select 3, 2, 0.8, 0
insert @s select 4, 2, 0.5, 1
insert @s select 5, 1, 0.7, 1
insert @s select 6, 3, 0.2, 2
insert @s select 7, 3, 0.7, 1
insert @s select 8, 3, 0.1, 0/************例子数据 end******************//************代码 begin***************/
select name,money,discount
,status= case when exists(select 1 from @s where pid =a.id and status = 2) then 2
when exists(select 1 from @s where pid =a.id and status = 0) then 0
else 1 end
from @p a,@s b where a.id =pid
/************代码 end*****************//************结果 begin***************
name money discount status
---------- ----------- -------- -----------
电视 100 .20 1
电视 100 .30 1
电视 100 .70 1
手机 150 .80 0
手机 150 .50 0
衣服 50 .20 2
衣服 50 .70 2
衣服 50 .10 2
************结果 end*****************//************清除*************************/
insert into 产品表 select
1 ,'电视', 100 union select
2 ,'手机', 150 union select
3 ,'衣服', 50
create table 库存表(ID int,PID int ,打折 numeric(12,2),货存 int)
insert into 库存表 select
1 , 1 , 0.2 , 1 union select
2 , 1 , 0.3 , 1 union select
3 , 2 , 0.8 , 0 union select
4 , 2 , 0.5 , 1 union select
5 , 1 , 0.7 , 1 union select
6 , 3 , 0.2 , 2 union select
6 , 3 , 0.7 , 1 union select
6 , 3 , 0.1 , 0
go
select 货名,case bz when 2 then 0 when 1 then 金额*dz - 金额 else (1- cn) *金额 end as 盈亏,bz
from(select b.货名,b.金额,exp(sum(log(1+打折))) as dz,count(1) as cn,
case when exists(select 1 from 库存表 where pid = a.pid and 货存 = 2) then 2
when exists(select 1 from 库存表 where pid = a.pid and 货存 = 0) then 0
else 1 end as bz
from 库存表 a,产品表 b
where b.id = a.pid
group by b.货名,a.pid,b.金额
) adrop table 产品表,库存表/*货名 是否打折
---------- -----------
电视 1
手机 0
衣服 2（所影响的行数为 3 行）
*/
insert into 产品表 select
1 ,'电视', 100 union select
2 ,'手机', 150 union select
3 ,'衣服', 50
create table 库存表(ID int,PID int ,打折 numeric(12,2),货存 int)
insert into 库存表 select
1 , 1 , 0.2 , 1 union select
2 , 1 , 0.3 , 1 union select
3 , 2 , 0.8 , 0 union select
4 , 2 , 0.5 , 1 union select
5 , 1 , 0.7 , 1 union select
6 , 3 , 0.2 , 2 union select
6 , 3 , 0.7 , 1 union select
6 , 3 , 0.1 , 0
go
select 货名,case bz when 2 then 0 when 1 then 金额*dz - 金额 else (1- cn) *金额 end as 盈亏,bz
from(select b.货名,b.金额,exp(sum(log(1+打折))) as dz,count(1) as cn,
case when exists(select 1 from 库存表 where pid = a.pid and 货存 = 2) then 2
when exists(select 1 from 库存表 where pid = a.pid and 货存 = 0) then 0
else 1 end as bz
from 库存表 a,产品表 b
where b.id = a.pid
group by b.货名,a.pid,b.金额
) adrop table 产品表,库存表/*货名 盈亏 bz
---------- ----------------------------------------------------- -----------
电视 165.19999999999999 1
手机 -150.0 0
衣服 0.0 2（所影响的行数为 3 行）
*/