有两个表,表结构和数据如下:
1.合同表(合同ID , 合同编号, 合同总金额,合同回款总金额)
1 H001 2000 0
2 H002 5000 2000
3 H003 3000 1500
2.合同明细表(合同ID,产品ID, 单价, 数量)
1 P01 50 20
1 P03 20 50
2 P02 100 50
3 P01 50 20
3 P02 25 40
3 P03 100 10
希望得到的查询结果如下:
查询结果:
合同ID 合同编号 合同总金额 合同回款总金额 产品ID 单价 数量
1 H001 2000 0
1 H001 P01 50 20
1 H001 P03 20 50
2 H002 5000 2000
2 H002 P02 100 50
3 H003 3000 1500
3 H003 P01 50 20
3 H003 P02 25 40
3 H003 P03 100 10谢谢!
1.合同表(合同ID , 合同编号, 合同总金额,合同回款总金额)
1 H001 2000 0
2 H002 5000 2000
3 H003 3000 1500
2.合同明细表(合同ID,产品ID, 单价, 数量)
1 P01 50 20
1 P03 20 50
2 P02 100 50
3 P01 50 20
3 P02 25 40
3 P03 100 10
希望得到的查询结果如下:
查询结果:
合同ID 合同编号 合同总金额 合同回款总金额 产品ID 单价 数量
1 H001 2000 0
1 H001 P01 50 20
1 H001 P03 20 50
2 H002 5000 2000
2 H002 P02 100 50
3 H003 3000 1500
3 H003 P01 50 20
3 H003 P02 25 40
3 H003 P03 100 10谢谢!
from 合同表
union all
select a.合同ID,a.合同编号,a.合同总金额,a.合同回款总金额, b.产品ID ,b.单价 ,b.数量
from 合同表 a right join 合同明细表 b on a.合同ID = b.合同ID
select * from ((select 合同ID,合同编号,合同总金额,合同回款总金额,'' as 产品ID ,'' as 单价,'' as 数量 from 合同表)
union all (select * from 合同表 a inner join 合同明细表 b on a.合同ID=b.合同ID)) c order by 合同编号,合同总金额
If object_id('合同表') is not null
Drop table 合同表
Go
Create table 合同表(合同ID int,合同编号 varchar(4),合同总金额 int,合同回款总金额 int)
Go
Insert into 合同表
select 1,'H001','2000',0 union all
select 2,'H002','5000',2000 union all
select 3,'H003','3000',1500
Go
-- Test Data: 合同明细表
If object_id('合同明细表') is not null
Drop table 合同明细表
Go
Create table 合同明细表(合同ID int,产品ID varchar(3),单价 int,数量 int)
Go
Insert into 合同明细表
select 1,'P01',50,20 union all
select 1,'P03',20,50 union all
select 2,'P02',100,50 union all
select 3,'P01',50,20 union all
select 3,'P02',25,40 union all
select 3,'P03',100,10
Go
--Startselect *,产品ID=' ' ,0 as 单价,0 as 数量
from 合同表
union all
select a.合同ID,a.合同编号,0,0, b.产品ID ,b.单价 ,b.数量
from 合同表 a right join 合同明细表 b on a.合同ID = b.合同ID
order by 合同编号
--Result:
/*合同ID 合同编号 合同总金额 合同回款总金额 产品ID 单价 数量
----------- ---- ----------- ----------- ---- ----------- -----------
1 H001 2000 0 0 0
1 H001 0 0 P01 50 20
1 H001 0 0 P03 20 50
2 H002 0 0 P02 100 50
2 H002 5000 2000 0 0
3 H003 3000 1500 0 0
3 H003 0 0 P01 50 20
3 H003 0 0 P02 25 40
3 H003 0 0 P03 100 10(所影响的行数为 9 行)
*/
--End
CREATE TABLE TB1(ID INT,HIDNO VARCHAR(10),AMONEY INT,TOTALMONEY INT)
INSERT INTO TB1 SELECT 1,'H001',2000,0
UNION ALL SELECT 2,'H002',5000,2000
UNION ALL SELECT 3,'H003',3000,1500 CREATE TABLE TB2(ID INT,CIDNO VARCHAR(10),PRICE INT,QUANTITY INT)
INSERT INTO TB2 SELECT 1,'P01',50,20
UNION ALL SELECT 1,'P03',20,50
UNION ALL SELECT 2,'P02',100,50
UNION ALL SELECT 3,'P01',50,20
UNION ALL SELECT 3,'P02',25,40
UNION ALL SELECT 3,'P03',100,10 SELECT ID,HIDNO,AMONEY,TOTALMONEY,CIDNO='',PRICE='',QUANTITY='' FROM TB1
UNION ALL
SELECT B.ID,B.HIDNO,AMONEY='',TOTALMONEY='',A.CIDNO,A.PRICE,A.QUANTITY
FROM TB2 A
INNER JOIN TB1 B
ON A.ID=B.ID
ORDER BY ID,AMONEY DESC/*
ID HIDNO AMONEY TOTALMONEY CIDNO PRICE QUANTITY
----------- ---------- ----------- ----------- ---------- ----------- -----------
1 H001 2000 0 0 0
1 H001 0 0 P01 50 20
1 H001 0 0 P03 20 50
2 H002 5000 2000 0 0
2 H002 0 0 P02 100 50
3 H003 3000 1500 0 0
3 H003 0 0 P01 50 20
3 H003 0 0 P02 25 40
3 H003 0 0 P03 100 10(所影响的行数为 9 行)
*/
From 合同表
Union all
Select 合同ID,'' As 合同编号, '' As 合同总金额,'' As 合同回款总金额,产品ID,单价, 数量
From 合同明细表
Order by 1,2 Desc
运行结果:
合同ID 合同编号 合同总金额 合同回款总金额 产品ID 单价 数量
-----------------------------------------------------------
1 H001 2000 0 0 0
1 0 0 P01 50 20
1 0 0 P03 20 50
2 H002 5000 2000 0 0
2 0 0 P02 100 50
3 H003 3000 1500 0 0
3 0 0 P01 50 20
3 0 0 P02 25 40
3 0 0 P03 100 10
select
合同ID,合同编号, 合同总金额,合同回款总金额,0 产品ID , 0 单价,0 数量
from 合同表
union all
select
合同ID,合同编号,0 合同总金额,0 合同回款总金额, 产品ID,单价,数量
from 合同明细表
order by 合同ID,合同编号
From 合同表
Union all
Select D.合同ID, 合同编号, '' As 合同总金额,'' As 合同回款总金额,产品ID,单价, 数量
From
合同明细表 D
Inner join
合同表 T
On T.合同ID=D.合同ID
Order by 1,3 Desc
INSERT INTO TB1 SELECT 1,'H001',2000,0
UNION ALL SELECT 2,'H002',5000,2000
UNION ALL SELECT 3,'H003',3000,1500 CREATE TABLE TB2(ID INT,CIDNO VARCHAR(10),PRICE INT,QUANTITY INT)
INSERT INTO TB2 SELECT 1,'P01',50,20
UNION ALL SELECT 1,'P03',20,50
UNION ALL SELECT 2,'P02',100,50
UNION ALL SELECT 3,'P01',50,20
UNION ALL SELECT 3,'P02',25,40
UNION ALL SELECT 3,'P03',100,10 SELECT ID,HIDNO,AMONEY,TOTALMONEY,CIDNO='',PRICE='',QUANTITY='' FROM TB1
UNION ALL
SELECT B.ID,B.HIDNO,AMONEY='',TOTALMONEY='',A.CIDNO,A.PRICE,A.QUANTITY
FROM TB2 A
INNER JOIN TB1 B
ON A.ID=B.ID
ORDER BY ID,AMONEY DESC
如果通过如下语句创建了视图V_Test
Create View V_Test
as
select *,产品ID=' ' ,0 as 单价,0 as 数量
from 合同表
union all
select a.合同ID,a.合同编号,0,0, b.产品ID ,b.单价 ,b.数量
from 合同表 a right join 合同明细表 b on a.合同ID = b.合同ID 在视图V_Test中查询 "产品ID=P02" 希望得到
合同ID 合同编号 合同总金额 合同回款总金额 产品ID 单价 数量
2 H002 5000 2000
2 H002 P02 100 50
3 H003 3000 1500
3 H003 P01 50 20
3 H003 P02 25 40
3 H003 P03 100 10
即含有P02的两个合同及明细均显示出来,请问该如何写SQL语句,有合适结果立即结贴
select c.* from (select *,产品ID=' ' ,0 as 单价,0 as 数量
from 合同表
union all
select a.合同ID,a.合同编号,0,0, b.产品ID ,b.单价 ,b.数量
from 合同表 a right join 合同明细表 b on a.合同ID = b.合同ID ) c where 合同ID in (select 合同ID from 合同明细表 where 产品ID ='p02') order by 合同ID
select c.* from (select *,产品ID=' ' ,0 as 单价,0 as 数量
from 合同表
union all
select a.合同ID,a.合同编号,0,0, b.产品ID ,b.单价 ,b.数量
from 合同表 a right join 合同明细表 b on a.合同ID = b.合同ID ) c where c.合同ID in (select distinct 合同ID from 合同明细表 where 产品ID ='p02') order by c.合同ID
SELECT * FROM V_Test
WHERE 合同编号 IN (SELECT 合同编号FROM V_Test WHERE 产品ID= 'P02')
ORDR BY 合同编号,合同总金额 DESC
不过我一开始用连接语句,后来发现union all更方便。
drop table [合同表]
create table [合同表] ([合同ID] int,[合同编号] varchar(10),[合同总金额] int,[合同回款总金额] int)
insert [合同表]
select 1,'H001',2000,0
union all
select 2,'H002',5000,2000
union all
select 3,'H003',3000,1500if object_id('[合同明细表]') is not null
drop table [合同明细表]
create table [合同明细表]([合同ID] int ,[产品ID] varchar(10), [单价] int ,[数量] int)
insert [合同明细表]
select 1,'P01',50,20
union all
select 1,'P03',20,50
union all
select 2,'P02',100,50
union all
select 3,'P01',50,50
union all
select 3,'P02',25,40
union all
select 3,'P03',100,10select * from [合同表]
select * from [合同明细表]
select [合同表].[合同ID],[合同编号],[合同总金额]=0,[合同回款总金额]=0,[产品ID],[单价],[数量] from [合同表],[合同明细表] where [合同表].[合同ID]=[合同明细表].[合同ID]
union all
select [合同ID],[合同编号],[合同总金额],[合同回款总金额],'',0,0 from [合同表] order by [合同表].[合同ID] ,[数量]asc
select *,产品ID=' ' ,0 as 单价,0 as 数量
from 合同表
union all
select a.合同ID,a.合同编号,0,0, b.产品ID ,b.单价 ,b.数量
from 合同表 a right join 合同明细表 b on a.合同ID = b.合同ID
order by 合同编号
第二个 in
select * from V_Test where [合同ID] in
(select [合同ID] from V_Test where [产品ID]= 'P02')
order by [合同ID],[产品ID]
select * from V_Test where 产品ID=‘P02’