22,206
社区成员
发帖
与我相关
我的任务
分享
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2014-07-28 00:25:16
-- Version:
-- Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86)
-- Feb 10 2012 19:13:17
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[T1]
if object_id('[T1]') is not null drop table [T1]
go
create table [T1]([ID] int,[CKMC] varchar(10))
insert [T1]
select 4,'报废品' union all
select 3,'不可重工品' union all
select 2,'可重工品' union all
select 1,'良品'
--> 测试数据:[T2]
if object_id('[T2]') is not null drop table [T2]
go
create table [T2]([sclh] varchar(16),[ckmc] varchar(10),[sl] int)
insert [T2]
select '1B3394000-600-GA','良品',500 union all
select '1B3394000-600-GA','可重工品',800 union all
select '1B3394000-600-GA','不可重工品',600 union all
select '1B3394000-600-GA','报废品',400
--> 测试数据:[T3]
if object_id('[T3]') is not null drop table [T3]
go
create table [T3]([khmc] varchar(4),[sclh] varchar(16),[cplh] varchar(16),[ckmc] varchar(10),[ys] varchar(4),[sl] int)
insert [T3]
select '中信','1B3394000-600-GA','1B089G100-600-GA','良品','银色',800 union all
select '中信','1B3394000-600-GA','1B089G100-600-GA','可重工品','银色',200 union all
select '中信','1B3394000-600-GA','1B089G100-600-GA','不可重工品','银色',100 union all
select '中信','1B3394000-600-GA','1B089G100-600-GA','报废品','银色',100 union all
select '中信','1B3394000-600-GA','1B089G100-600-GA','良品','灰色',100
--> 测试数据:[T4]
if object_id('[T4]') is not null drop table [T4]
go
create table [T4]([khmc] varchar(4),[wlfl] varchar(4),[wllh] varchar(16),[ckmc] varchar(8),[ys] varchar(4),[sl] int)
insert [T4]
select '中信','成品','1B089G100-600-GA','良品','银色',1000 union all
select '中信','素材','1B3394000-600-GA','可重工品',null,300
--> 测试数据:[T5]
if object_id('[T5]') is not null drop table [T5]
go
create table [T5]([khmc] varchar(4),[wlfl] varchar(4),[wllh] varchar(16),[ckmc] varchar(8),[ys] varchar(4),[sl] int)
insert [T5]
select '中信','成品','1B089G100-600-GA','良品','银色',600 union all
select '中信','素材','1B3394000-600-GA','可重工品',null,200 union all
select '中信','成品','1B089G100-600-GA','良品','灰色',50
--------------开始查询--------------------------
--select * from [T1]
--select * from [T2]
--select * from [T3]
--select * from [T4]
--select * from [T5]
select b.khmc,a.ckmc,b.wlfl,b.wllh,b.ys,a.sl from t2 as a cross join (select * from t4 where wlfl='素材')as b
union all
select b.khmc,a.ckmc,b.wlfl,b.wllh,a.ys,a.sl from t3 as a cross join (select * from t4 where wlfl='成品')as b
----------------结果----------------------------
/* khmc ckmc wlfl wllh ys sl
---- ---------- ---- ---------------- ---- -----------
中信 良品 素材 1B3394000-600-GA NULL 500
中信 可重工品 素材 1B3394000-600-GA NULL 800
中信 不可重工品 素材 1B3394000-600-GA NULL 600
中信 报废品 素材 1B3394000-600-GA NULL 400
中信 良品 成品 1B089G100-600-GA 银色 800
中信 可重工品 成品 1B089G100-600-GA 银色 200
中信 不可重工品 成品 1B089G100-600-GA 银色 100
中信 报废品 成品 1B089G100-600-GA 银色 100
中信 良品 成品 1B089G100-600-GA 灰色 100
(9 行受影响)
*/