27,579
社区成员
发帖
与我相关
我的任务
分享
select t1.品名,
t2.数量,
case when s3.min编号=s3.max编号 then
cast(s3.min编号 as varchar)
else
cast(s3.min编号 as varchar)+'~'+cast(s3.max编号 as varchar)
end as 编号
,t1.功能
from t2
inner join t1 on t1.ID=t2.品名id
inner join ( select 销售表id,
min(编号) min编号,
max(编号) max编号
from t3
group by 销售表id
) s3
on s3.销售表id=t2.id
order by s3.min编号 --非返回的字段也可以用来排序
品名 数量 编号 功能
----- ----------- ------------------------- -----
品名1 1 1 功能1
品名3 1 5 功能3
品名2 3 102~104 功能2
t3(id,销售表id,编号) as
(select '1','1','1'union all
select '2','2','2'union all
select '3','2','3'union all
select '4','2','99'union all
select '5','3','100')
品名 数量 编号 功能
----- ---- -------------------------------------------------------------- -----
品名1 1 1 功能1
品名3 1 100 功能3
品名2 3 2~99 功能2
显示的顺序是这样 不对是吧。,我觉得要是你的表2保证编号是按照品名递增的 直接ORDER by 品名。 select t1.品名,t2.数量,
(select case when max(convert (int,t3.编号))=min(convert (int,t3.编号))
then max (t3.编号)
else
cast(min(convert(int,t3.编号)) as varchar)
+'~'+cast(max(convert(int,t3.编号)) as varchar) end as bh from t3
where t3.销售表id=t2.id) as 编号,t1.功能 from t2
inner join t1 on t1.ID=t2.id
order by 编号
品名 数量 编号 功能
----- ---- -------------------------------------------------------------- -----
品名1 1 1 功能1
品名2 3 2~103 功能2
品名3 1 5 功能3
(3 行受影响)
这个不行吗 品名 数量 编号 功能
----- ---- -------------------------------------------------------------- -----
品名1 1 1 功能1
品名2 3 103~4 功能2
品名3 1 5 功能3
(3 行受影响)
改成103是这个结果。你对于这情况要显示成 2~103吗
select t1.品名,t2.数量,
(select case when max(convert (int,t3.编号))=min(convert (int,t3.编号))
then max (t3.编号)
else
cast(min(convert(int,t3.编号)) as varchar)
+'~'+cast(max(convert(int,t3.编号)) as varchar) end as bh from t3
where t3.销售表id=t2.id) as 编号,t1.功能 from t2
inner join t1 on t1.ID=t2.id
order by 编号
with t1(ID,品名,规格,功能,厂家) as
(select '1','品名1','规格1','功能1','厂家1' union all
select '2','品名2','规格2','功能2','厂家2' union all
select '3','品名3','规格3','功能3','厂家3') ,
t2(id,品名id,数量,价格,销售情况) as
(select '1','品名ID1','1','价格1','销售情况1' union all
select '2','品名ID2','3','价格2','销售情况2' union all
select '3','品名ID3','1','价格3','销售情况3' ),
t3(id,销售表id,编号) as
(select '1','1','1'union all
select '2','2','2'union all
select '3','2','3'union all
select '4','2','4'union all
select '5','3','5')
select t1.品名,t2.数量,
(select case when max(t3.编号)=min(t3.编号)
then max(t3.编号)
else
cast(min(t3.编号) as varchar)
+'~'+cast(max(t3.编号) as varchar) end as bh from t3
where t3.销售表id=t2.id) as 编号,t1.功能 from t2
inner join t1 on t1.ID=t2.id
order by 编号
测试代码品名 数量 编号 功能
----- ---- -------------------------------------------------------------- -----
品名1 1 1 功能1
品名2 3 2~4 功能2
品名3 1 5 功能3
(3 行受影响)
select t1.品名,t2.数量,
(select case when max(t3.编号)=min(t3.编号)
then max(t3.编号)
else
cast(min(t3.编号) as varchar)
+'~'+cast(max(t3.编号) as varchar) end as bh from t3
where t3.销售表id=t2.id) as 编号,t1.功能 from t2
inner join t1 on t1.ID=t2.id
order by 编号
有问题吗