select zy_in.id,zy_in.name,zy_cost.date,zy_cost.code,zy_in.name2
from zy_in join zy_cost on zy_in.id=zy_cost.id
join zy_hj on zy_hj.id=by_in.id
from zy_in join zy_cost on zy_in.id=zy_cost.id
join zy_hj on zy_hj.id=by_in.id
解决方案 »
- 想通过一个函数或sql语句,随机生成任意6个字母组成的字符!
- 我想我已经被气死了,这样的 SQL 语句(在线等)
- 求一SQL语句,请大家帮忙看看!
- 请问 为什么 这个 分页的 贮存过程 在 adodataset 中不能 显示数据 ??????????????????
- 求行列转换,mysql数据库
- 菜鸟问题:Sql server 2008 R2 (64bit),使用混合登录后, 查询字段和表都显示错误。结贴马上给分!
- sql 2000服务器端怎么获取用户ip?
- 求助!
- 如何打印出1-100间的数?
- DELPHI里有没有好的办法实现对SQL server 数据库的备份?
- 连接占线导致另一个hstmt(100分)
- 内容添加不上,急!!
left join zy_cost b on a.id=b.id
left join zy_hj c on b.code=c.code
from zy_in a
left join zy_cost b on a.id=b.id
left join zy_hj c on a.id=c.code
go
insert zy_in select 1 , 'xd'
gocreate table zy_cost([date] varchar(5), id int, code int, name1 varchar(20))
goinsert zy_cost select'4-29' , 1 , 1, '西药'
insert zy_cost select'4-29' , 1 , 1 , '中药'
insert zy_cost select'4-29' , 1 , 1 , '中成药'
go
create table zy_hj(code int, name2 varchar(10) )
go
insert zy_hj select 1 , 'lwdh'
insert zy_hj select 1 , 'sdf'
insert zy_hj select 1 , 'sdfg'
go
select distinct a.id,a.name,b.date,b.code,c.name2 from zy_in a
join zy_cost b on a.id=b.id
join zy_hj c on b.code=c.code
go
/*
id name date code name2
----------- ---------- ----- ----------- ----------
1 xd 4-29 1 lwdh
1 xd 4-29 1 sdf
1 xd 4-29 1 sdfg(所影响的行数为 3 行)*/
from zy_in join zy_cost on zy_in.id=zy_cost.id
join zy_hj on zy_hj.code=zy_cost.code
create table zy_in(id int, name varchar(10))
go
insert zy_in select 1 , 'xd'
go
create table zy_cost([date] varchar(5), id int, code int, name1 varchar(20))
go
insert zy_cost select'4-29' , 1 , 3, '西药'
insert zy_cost select'4-29' , 1 , 3 , '中药'
insert zy_cost select'4-29' , 1 , 3 , '中成药'
go
create table zy_hj(code int, name2 varchar(10) )
go
insert zy_hj select 3 , 'lwdh'
insert zy_hj select 3 , 'sdf'
insert zy_hj select 3 , 'sdfg'
go
select distinct zy_in.id,zy_in.name,zy_cost.date,zy_cost.code,zy_hj.name2
from zy_in,zy_cost,zy_hj
where zy_in.id = zy_cost.id and zy_cost.code = zy_hj.code
/*
1 xd 4-29 3 lwdh
1 xd 4-29 3 sdf
1 xd 4-29 3 sdfg
*/
if object_id('[zy_in]') is not null drop table [zy_in]
go
create table [zy_in]([id] int,[name] varchar(2))
insert [zy_in]
select 1,'xd'
if object_id('[zy_cost]') is not null drop table [zy_cost]
go
create table [zy_cost]([date] varchar(4),[id] int,[code] int,[name1] varchar(6))
insert [zy_cost]
select '4-29',1,1,'西药' union all
select '4-29',1,1,'中药' union all
select '4-29',1,1,'中成药'
if object_id('[zy_hj]') is not null drop table [zy_hj]
go
create table [zy_hj]([code] int,[name2] varchar(4))
insert [zy_hj]
select 1,'lwdh' union all
select 1,'sdf' union all
select 1,'sdfg'
---查询---
select
distinct
zy_in.id as [zy_in.id],
zy_in.name as [zy_in.name],
zy_cost.[date] as [zy_cost.date],
zy_cost.code as [zy_cost.code],
zy_hj.name2 as [zy_in.name2]
from
[zy_cost]
left join
[zy_in]
on
[zy_cost].id=[zy_in].id
left join
[zy_hj]
on
[zy_hj].code=[zy_cost].code---结果---
zy_in.id zy_in.name zy_cost.date zy_cost.code zy_in.name2
----------- ---------- ------------ ------------ -----------
1 xd 4-29 1 lwdh
1 xd 4-29 1 sdf
1 xd 4-29 1 sdfg(所影响的行数为 3 行)
from zy_in a
left join zy_cost b on a.id=b.id
left join zy_hj c on a.id=c.code
create table zy_in(id int, name varchar(10))
go
insert zy_in select 1 , 'xd'
gocreate table zy_cost([date] varchar(5), id int, code int, name1 varchar(20))
goinsert zy_cost select'4-29' , 1 , 1, '西药'
insert zy_cost select'4-29' , 1 , 1 , '中药'
insert zy_cost select'4-29' , 1 , 1 , '中成药'
go
create table zy_hj(code int, name2 varchar(10) )
go
insert zy_hj select 1 , 'lwdh'
insert zy_hj select 1 , 'sdf'
insert zy_hj select 1 , 'sdfg'
go
select distinct a.id,a.name,b.date,b.code,c.name2 from zy_in a
join zy_cost b on a.id=b.id
join zy_hj c on b.code=c.code
go
/*
id name date code name2
----------- ---------- ----- ----------- ----------
1 xd 4-29 1 lwdh
1 xd 4-29 1 sdf
1 xd 4-29 1 sdfg(所影响的行数为 3 行)*/
试一下
where a.id= b.id and b.code=c.code
zy_in:
******************************
id name
1 xd zy_cost:
******************************
date id code name1
4-29 1 3 西药
4-29 1 3 中药
4-29 1 3 中成药 zy_hj:
*******************************
code name2
3 lwdh
3 sdf
3 sdfg 现在已知道zy_in的id和zy_cost里的date
现在需要如下结果:
zy_in.id zy_in.name zy_cost.date zy_cost.code zy_in.name2 zy_cost.name1
1 xd 4-29 3 lwdh 西药
1 xd 4-29 3 sdf 中药
1 xd 4-29 3 sdfg 中成药
go
insert zy_in select 1 , 'xd'
gocreate table zy_cost([date] varchar(5), id int, code int, name1 varchar(20))
goinsert zy_cost select'4-29' , 1 , 1, '西药'
insert zy_cost select'4-29' , 1 , 1 , '中药'
insert zy_cost select'4-29' , 1 , 1 , '中成药'
go
create table zy_hj(code int, name2 varchar(10) )
go
insert zy_hj select 1 , 'lwdh'
insert zy_hj select 1 , 'sdf'
insert zy_hj select 1 , 'sdfg'
go
select distinct a.id,a.name,b.date,b.code,c.name2,b.name1 from zy_in a
join zy_cost b on a.id=b.id
join zy_hj c on b.code=c.code
go
/*
id name date code name2 name1
----------- ---------- ----- ----------- ---------- --------------------
1 xd 4-29 1 lwdh 西药
1 xd 4-29 1 lwdh 中成药
1 xd 4-29 1 lwdh 中药
1 xd 4-29 1 sdf 西药
1 xd 4-29 1 sdf 中成药
1 xd 4-29 1 sdf 中药
1 xd 4-29 1 sdfg 西药
1 xd 4-29 1 sdfg 中成药
1 xd 4-29 1 sdfg 中药(所影响的行数为 9 行)*/
go
insert zy_in select 1 , 'xd'
gocreate table zy_cost([date] varchar(5), id int, code int, name1 varchar(20))
goinsert zy_cost select'4-29' , 1 , 1, '西药'
insert zy_cost select'4-29' , 1 , 1 , '中药'
insert zy_cost select'4-29' , 1 , 1 , '中成药'
go
create table zy_hj(code int, name2 varchar(10) )
go
insert zy_hj select 1 , 'lwdh'
insert zy_hj select 1 , 'sdf'
insert zy_hj select 1 , 'sdfg'
go
select distinct a.id,a.name,b.date,b.code,c.name2,b.name1
from zy_in a
join (select *,rid= (select count(1) from zy_cost where d.name1 >name1) from zy_cost d) b on a.id=b.id
join (select *,rid= (select count(1) from zy_hj where e.name2 >name2) from zy_hj e)c on b.code=c.code
where b.rid = c.rid
go
/*
id name date code name2 name1
----------- ---------- ----- ----------- ---------- --------------------
1 xd 4-29 1 lwdh 西药
1 xd 4-29 1 sdf 中成药
1 xd 4-29 1 sdfg 中药
*/
drop table zy_in,zy_cost,zy_hj