我有下面三个表:
table1
id type1 type2
1 a b
2 c d
3 e ftable2
id type1 qty
1 a 200
2 c 400 table3
id type2 qty
1 b 300
3 f 600 需要连接成下面这样的视图:
id type1 type2 qty1 qty2
1 a b 100 200
2 c 400
3 f 600请问各位精通SQL语句的大大,有什么办法?
谢谢!
table1
id type1 type2
1 a b
2 c d
3 e ftable2
id type1 qty
1 a 200
2 c 400 table3
id type2 qty
1 b 300
3 f 600 需要连接成下面这样的视图:
id type1 type2 qty1 qty2
1 a b 100 200
2 c 400
3 f 600请问各位精通SQL语句的大大,有什么办法?
谢谢!
isnull(t2.id,t3.id) as id,
isnull(t2,type,'') as type1,
isnull(t3.type,'') as type2,
isnull(ltrim(t2.qty),'') as qty1,
isnull(ltrim(t3.qty),''_ as qty2
from
table2 t2
full join
table2 t3
on
t2.id=t3.id
select table1.id,table2.type1,table3.type2,table2.qty1,table3.qty2
from table1
left join table2 on table1.type1=table2.type1
left join table3 on table1.type2=table2.type2
table1
id type1 type2
1 a b
2 c d
3 e f table2
id type1 qty
1 a 200
2 c 400 table3
id type2 qty
1 b 300
3 f 600 需要连接成下面这样的视图:
id type1 type2 qty1 qty2
1 a b 200 300
2 c 400
3 f 600 请问各位精通SQL语句的大大,有什么办法?
谢谢!
select table1.id,table2.type1,table3.type2,table2.qty1,table3.qty2
from table1
left join table2 on table1.type1=table2.type1
left join table3 on table1.type2=table3.type2
if object_id('[table2]') is not null drop table [table2]
go
create table [table2]([id] int,[type1] varchar(1),[qty] int)
insert [table2]
select 1,'a',200 union all
select 2,'c',400
if object_id('[table3]') is not null drop table [table3]
go
create table [table3]([id] int,[type2] varchar(1),[qty] int)
insert [table3]
select 1,'b',300 union all
select 3,'f',600
select
isnull(t2.id,t3.id) as id,
isnull(t2.type1,'') as type1,
isnull(t3.type2,'') as type2,
isnull(ltrim(t2.qty),'') as qty1,
isnull(ltrim(t3.qty),'') as qty2
from
table2 t2
full join
table3 t3
on
t2.id=t3.id
--测试结果:
/*
id type1 type2 qty1 qty2
----------- ----- ----- ------------ ------------
1 a b 200 300
3 f 600
2 c 400 (所影响的行数为 3 行)*/
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-10-23 22:31:40
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[table1]
if object_id('[table1]') is not null drop table [table1]
go
create table [table1]([id] int,[type1] varchar(1),[type2] varchar(1))
insert [table1]
select 1,'a','b' union all
select 2,'c','d' union all
select 3,'e','f'
--> 测试数据:[table2]
if object_id('[table2]') is not null drop table [table2]
go
create table [table2]([id] int,[type1] varchar(1),[qty] int)
insert [table2]
select 1,'a',200 union all
select 2,'c',400
--> 测试数据:[table3]
if object_id('[table3]') is not null drop table [table3]
go
create table [table3]([id] int,[type2] varchar(1),[qty] int)
insert [table3]
select 1,'b',300 union all
select 3,'f',600
--------------开始查询--------------------------
select
a.id,
isnull(a.type1,'') as type1,
isnull(c.type2,'') as type2,
isnull(b.qty,'') as qty1,
isnull(c.qty,'') as qty2
from
table1 a
left join
table2 b
on
a.id=b.id and a.type1=b.type1
left join
table3 c on
a.id=c.id and a.type2=c.type2----------------结果----------------------------
/* id type1 type2 qty1 qty2
----------- ----- ----- ----------- -----------
1 a b 200 300
2 c 400 0
3 e f 0 600(3 行受影响)
*/
但可能我表达错误,其实还有一个是table1的空行不要.
我有下面三个表:
table1
id type1 type2
1 a b
2 c d
3 e f
4 g htable2
id type1 qty
1 a 200
2 c 400 table3
id type2 qty
1 b 300
3 f 600 需要连接成下面这样的视图:
id type1 type2 qty1 qty2
1 a b 200 300
2 c 400
3 f 600
而不是这样
id type1 type2 qty1 qty2
1 a b 200 300
2 c 400
3 f 600
4 g h null null根据5楼的方法,第4条记录也会出现.可不可以剔除记录4?
即不在table2,和table3中的记录不出现.谢谢~~
isnull(t2.id,t3.id) as id,
isnull(t2.type1,'') as type1,
isnull(t3.type2,'') as type2,
isnull(ltrim(t2.qty),'') as qty1,
isnull(ltrim(t3.qty),'') as qty2
from
table2 t2
full join
table3 t3
on
t2.id=t3.id7楼 树哥的是对的
一定要出现在table1中的type1和type2.
例如:
table2
id type1 qty
1 a 200
2 c 400
3 X 800
第3条记录,不是需要的.
1 没在tabl2和table3中配备的table1记录要剔除。
2 没出现在table1的type1和type2的类型,不要选择进去。
if object_id('[table1]') is not null drop table [table1]
go
create table [table1]([id] int,[type1] varchar(1),[type2] varchar(1))
insert [table1]
select 1,'a','b' union all
select 2,'c','d' union all
select 3,'e','f' union all
select 4,'g','h'
--> 测试数据:[table2]
if object_id('[table2]') is not null drop table [table2]
go
create table [table2]([id] int,[type1] varchar(1),[qty] int)
insert [table2]
select 1,'a',200 union all
select 2,'c',400
--> 测试数据:[table3]
if object_id('[table3]') is not null drop table [table3]
go
create table [table3]([id] int,[type2] varchar(1),[qty] int)
insert [table3]
select 1,'b',300 union all
select 3,'f',600
select t2.*
from
table1 t1
inner join
(
select
isnull(t2.id,t3.id) as id,
isnull(t2.type1,'') as type1,
isnull(t3.type2,'') as type2,
isnull(ltrim(t2.qty),'') as qty1,
isnull(ltrim(t3.qty),'') as qty2
from
table2 t2
full join
table3 t3
on
t2.id=t3.id) t2 on t1.id=t2.id
/*id type1 type2 qty1 qty2
----------- ----- ----- ------------ ------------
1 a b 200 300
2 c 400
3 f 600*/
go
create table [table1]([id] int,[type1] varchar(1),[type2] varchar(1))
insert [table1]
select 1,'a','b' union all
select 2,'c','d' union all
select 3,'e','f'
if object_id('[table2]') is not null drop table [table2]
go
create table [table2]([id] int,[type1] varchar(1),[qty] int)
insert [table2]
select 1,'a',200 union all
select 2,'c',400 union all
select 3,'X',800
if object_id('[table3]') is not null drop table [table3]
go
create table [table3]([id] int,[type2] varchar(1),[qty] int)
insert [table3]
select 1,'b',300 union all
select 3,'f',600-->查询
select
isnull(t2.id,t3.id) as id,
isnull(t2.type1,'') as type1,
isnull(t3.type2,'') as type2,
isnull(ltrim(t2.qty),'') as qty1,
isnull(ltrim(t3.qty),'') as qty2
from
(select t2.* from table2 t2 join table1 t1 on t1.id=t2.id and t1.type1=t2.type1) t2
full join
(select t3.* from table3 t3 join table1 t1 on t1.id=t3.id and t1.type2=t3.type2) t3
on
t2.id=t3.id
order by
id/**
id type1 type2 qty1 qty2
----------- ----- ----- ------------ ------------
1 a b 200 300
2 c 400
3 f 600(3 行受影响)
**/
---用type关联
--> 测试数据:[table1]
if object_id('[table1]') is not null drop table [table1]
go
create table [table1]([id] int,[type1] varchar(1),[type2] varchar(1))
insert [table1]
select 1,'a','b' union all
select 2,'c','d' union all
select 3,'e','f' union all
select 4,'g','h'
--> 测试数据:[table2]
if object_id('[table2]') is not null drop table [table2]
go
create table [table2]([id] int,[type1] varchar(1),[qty] int)
insert [table2]
select 1,'a',200 union all
select 2,'c',400
--> 测试数据:[table3]
if object_id('[table3]') is not null drop table [table3]
go
create table [table3]([id] int,[type2] varchar(1),[qty] int)
insert [table3]
select 1,'b',300 union all
select 3,'f',600select table1.id,table2.type1,table3.type2,qty1=table2.qty,qty2=table3.qty
from table1
left join table2 on table1.type1=table2.type1
left join table3 on table1.type2=table3.type2
where (isnull(table2.type1,'') <>'' or isnull(table3.type2,'') <>'')/*
id type1 type2 qty1 qty2
----------- ----- ----- ----------- -----------
1 a b 200 300
2 c NULL 400 NULL
3 NULL f NULL 600(所影响的行数为 3 行)*/
table3.qty as qty2 from table1
left join table2 on table1.type1=table2.type1
left join table3 on table1.type2=table3.type2
go
create Table table1 (id int,type1 varchar(10),type2 varchar(10))
Create Table Table2 (id int,type1 varchar(10),qty int)
Create Table Table3 (id int,Type2 varchar(10),qty int)
insert into Table1 select 1,'a','b' Union All select 2,'c','d' Union All select 3,'e','f'
insert into Table2 select 1,'a',200 Union All select 2,'c',400
insert into Table3 select 1,'b',300 Union All select 3,'f',600select table1.id,table2.type1,table3.type2,qty1 = (case table1.id when 1 then table2.qty when 2 then table2.qty end),
qty2 = (case table1.id when 1 then table3.qty when 3 then table3.qty end)
from table1 left outer join table2 ON table1.id = table2.id and table1.type1 = table2.type1
left outer join table3 ON table1.id = table3.id and table1.type2 = table3.type2
--------------------------------
id type1 type2 qty1 qty2
1 a b 200 300
2 c 400
3 f 600