我现在有两个表 iqcproduct 和restorepnrpack
iqcproduct表
字段prodnr metal_weight(重量)
restorepnrpack表
字段
prodnr查询得到如下结果:
在表iqcproduct查询 结果只显示metal_weight的重量,这个字段的重量是不固定的,两个表之间prodnr的编号是相同,但restorepnrpack表里的prodnr同一编号可能有好几个,因为不同客户,只需要随便取一个就可以,如果没有就显示0 应该如何写。
iqcproduct表
字段prodnr metal_weight(重量)
restorepnrpack表
字段
prodnr查询得到如下结果:
在表iqcproduct查询 结果只显示metal_weight的重量,这个字段的重量是不固定的,两个表之间prodnr的编号是相同,但restorepnrpack表里的prodnr同一编号可能有好几个,因为不同客户,只需要随便取一个就可以,如果没有就显示0 应该如何写。
from restorepnrpack A
inner join iqcproduct B on A.prodnr = B.prodnr
group by A.prodnr
???
需求没明确
他要iqcproduct.prodnr和restorepnrpack.metal_weight(随机值)
通过prodnr关联 是不?
两张表都有prodnr metal_weight 就是这样写的了PS:rand()这个随机函数可用否 没试过
*FROM iqcproduct AS a
LEFT JOIN restorepnrpack AS b ON a.prodnr=b.prodnr AND CustomerID=(SELECT TOP 1 CustomerID FROM restorepnrpack WHERE prodnr=b.prodnr)
SELECT
*
FROM iqcproduct AS a
CROSS APPLY
(SELECT TOP 1 * FROM restorepnrpack WHERE a.prodnr=prodnr ORDER BY NEWID()) AS b
insert into restorepnrpack select 1 union all select 2 union all select 3
create table iqcproduct(prodnr int,metal_weight decimal(10,2))
insert into iqcproduct select 1,15.2
insert into iqcproduct select 1,22.8
insert into iqcproduct select 1,17.5
insert into iqcproduct select 2,371.1
go
select a.prodnr,isnull(sum(b.metal_weight),0)metal_weight
from restorepnrpack a left join iqcproduct b on a.prodnr=b.prodnr
group by a.prodnr
/*
prodnr metal_weight
----------- ---------------------------------------
1 55.50
2 371.10
3 0.00
警告: 聚合或其他 SET 操作消除了 Null 值。(3 行受影响)*/
go
drop table restorepnrpack,iqcproduct
if object_id('iqcproduct','U') is not null
drop table iqcproduct
go
create table iqcproduct
(
prodnr varchar(10),
metal_weight int
)
go
insert into iqcproduct
select '001',20 union all
select '002',30 union all
select '003',40 union all
select '004',50
if object_id('restorepnrpack','U') is not null
drop table restorepnrpack
go
create table restorepnrpack
(
prodnr varchar(10)
)
go
insert into restorepnrpack
select '001' union all
select '001' union all
select '001' union all
select '002' union all
select '003' union all
select '004' union all
select '005'
go
select a.prodnr,metal_weight=ISNULL(b.metal_weight,0) from restorepnrpack a left join iqcproduct b on a.prodnr=b.prodnr group by a.prodnr,metal_weight
/*
prodnr metal_weight
---------- ------------
001 20
002 30
003 40
004 50
005 0(5 行受影响)
*/
drop table iqcproduct
Go
Create table iqcproduct([prodnr] nvarchar(10),[metal_weight] int,)
Insert iqcproduct
select 'aa',100 union all
select 'bb',20 union all
select 'cc',12 if not object_id('Tempdb..restorepnrpack ') is null
drop table restorepnrpack
Go
Create table restorepnrpack([prodnr] nvarchar(10),xx int)
Insert restorepnrpack
select 'aa',1 union all
select 'aa',2 union all
select 'bb',2 select A.prodnr,case when max(B.xx) is null then 0 else max(B.xx) end from iqcproduct A left join restorepnrpack B on A.prodnr=B.prodnr
group by A.prodnr
/*
prodnr
---------- -----------
aa 2
bb 2
cc 0
(3 行受影响)
*/