主表A
prodid qty reportid
pd0011 5 jb0001
pd0012 4 jb0002
pd0013 3 jb0003
pd0014 2 jb0004从表B
reportid subid subqty
jb001 1 4
jb001 2 6
jb003 1 5
jb003 2 8通过关联A与B 要得出结果
prodid qty reportid subid subqty
pd0011 5 jb0001 1 4
pd0011 5 jb0001 2 6
pd0012 4 jb0002
pd0013 5 jb0003 1 5
pd0013 5 jb0003 2 8
pd0014 2 jb0004怎么样写呢?
prodid qty reportid
pd0011 5 jb0001
pd0012 4 jb0002
pd0013 3 jb0003
pd0014 2 jb0004从表B
reportid subid subqty
jb001 1 4
jb001 2 6
jb003 1 5
jb003 2 8通过关联A与B 要得出结果
prodid qty reportid subid subqty
pd0011 5 jb0001 1 4
pd0011 5 jb0001 2 6
pd0012 4 jb0002
pd0013 5 jb0003 1 5
pd0013 5 jb0003 2 8
pd0014 2 jb0004怎么样写呢?
解决方案 »
- sql 2000 数据库定期备份不成功
- 取且仅取一条记录
- SQL中怎么查询出这样的结果显示方式 ?
- 一个实例里放多个数据库性能如何?
- 请问SQL server数据库的数据表能否加密?
- 咨询myeclipse+tomcat+spring+sql server进行链接服务器出错,急需要高手指点下迷津!!!小弟穷,暂时没分送,5555
- 在Sql Server中,创建临时表时,字符集是根据什么定的?是操作系统还是数据库字符集?
- 关于文件加载错误、索引与表不相匹配?
- 我用sql query登陆服务器,并执行了一个建表的CREATE语句,运行,返回说正常执行,但就是看不到新建的表,为什么??
- SQL Server 2000 的 Log 问题
- 有没有一个工具可以把sql server里的数据转移到sqlite
- 产生负数数据的问题
FROM A LEFT JOIN B ON A.reportid=b.reportid
FROM A
LEFT JOIN B
ON A.reportid=b.reportid
from a left join b
on a.reportid = b.reportid
IF OBJECT_ID('[A]') IS NOT NULL
DROP TABLE [A]
GO
CREATE TABLE [A] ([prodid] [nvarchar](10),[qty] [int],[reportid] [nvarchar](10))
INSERT INTO [A]
SELECT 'pd0011','5','jb0001' UNION ALL
SELECT 'pd0012','4','jb0002' UNION ALL
SELECT 'pd0013','3','jb0003' UNION ALL
SELECT 'pd0014','2','jb0004'--> 生成测试数据表: [B]
IF OBJECT_ID('[B]') IS NOT NULL
DROP TABLE [B]
GO
CREATE TABLE [B] ([reportid] [nvarchar](10),[subid] [int],[subqty] [int])
INSERT INTO [B]
SELECT 'jb0001','1','4' UNION ALL
SELECT 'jb0001','2','6' UNION ALL
SELECT 'jb0003','1','5' UNION ALL
SELECT 'jb0003','2','8'--SELECT * FROM [A]
--SELECT * FROM [B]-->SQL查询如下:
SELECT A.*,B.subid,B.subqty
FROM A
LEFT JOIN B
ON A.reportid=b.reportid
--SQL2005以上:
SELECT *
FROM A
OUTER APPLY(SELECT subid,subqty FROM B WHERE A.reportid=B.reportid) B
/*
prodid qty reportid subid subqty
---------- ----------- ---------- ----------- -----------
pd0011 5 jb0001 1 4
pd0011 5 jb0001 2 6
pd0012 4 jb0002 NULL NULL
pd0013 3 jb0003 1 5
pd0013 3 jb0003 2 8
pd0014 2 jb0004 NULL NULL(6 行受影响)
*/
insert into a values('pd0011', 5 ,'jb0001')
insert into a values('pd0012', 4 ,'jb0002')
insert into a values('pd0013', 3 ,'jb0003')
insert into a values('pd0014', 2 ,'jb0004')
create table B(reportid varchar(10),subid int,subqty int)
insert into b values('jb0001', 1 ,4)
insert into b values('jb0001', 2 ,6)
insert into b values('jb0003', 1 ,5)
insert into b values('jb0003', 2 ,8)
goselect a.* , isnull(ltrim(b.subid),'') subid , isnull(ltrim(b.subqty),'') subqty
from a left join b
on a.reportid = b.reportid drop table a , b/*
prodid qty reportid subid subqty
---------- ----------- ---------- ------------ ------------
pd0011 5 jb0001 1 4
pd0011 5 jb0001 2 6
pd0012 4 jb0002
pd0013 3 jb0003 1 5
pd0013 3 jb0003 2 8
pd0014 2 jb0004 (所影响的行数为 6 行)
*/
use PracticeDB
if exists (select 1 from sysobjects where name='tb_a')
drop table tb_a
go
create table tb_a (prodid varchar(10),qty int,reportid varchar(10))
go
insert into tb_a
select 'pd0011' ,5, 'jb0001' union all
select 'pd0012' ,4, 'jb0002' union all
select 'pd0013' ,3, 'jb0003' union all
select 'pd0014' ,2, 'jb0004'use PracticeDB
if exists (select 1 from sysobjects where name='tb_b')
drop table tb_b
go
create table tb_b (reportid varchar(10),subid int,subqty int)
go
insert into tb_b
select 'jb0001' ,1, 4 union all
select 'jb0001' ,2, 6 union all
select 'jb0003' ,1, 5 union all
select 'jb0003' ,2, 8select * from tb_b
select * from tb_a
select tb_a.prodid,tb_a.qty,tb_b.subid,tb_b.subqty
from tb_a left join tb_b on tb_a.reportid=tb_b.reportid --prodid qty reportid subid subqty
--pd0011 5 1 4
--pd0011 5 2 6
--pd0012 4 NULL NULL
--pd0013 3 1 5
--pd0013 3 2 8
--pd0014 2 NULL NULL