数据库查询求助

xlinux

【新手】
表结构如下：

需求：
通过查询，可以把需求表和关联表 之间的关系组合成一张新表。SQL语句怎么写，或者有什么优化的，谢谢！

zhantianxia

sql  联表查询  了解下  很简单

0Ling0

[SQL] 纯文本查看 复制代码

select a.*,b.* from a left join b on a.id=b.id left join c on a.id=b.id where …；

大概这样

layuai

课程作业？

hushxh

很简单的联合查询

dzpos

使用sql中的联结查询，即可 把多个表的字段组合成一个临时表中。

cnorchid

学习一下，请各位高手们，不要说简单，把查询语句写出来呗

dzwanser

SELECT
d.pg_id,d.lz_id,d.xj_id,d.pt_id,r.`add`,r.num,r.phone
FROM
-- 关联表
demand d
LEFT JOIN
-- 需求表
relevance r on d.xu_id =r.id

lwb514

1、用UNION将供应合并成一张总表
2、将关联表增加两列：表名和行号（苹果id、梨子id、香蕉id、葡萄id四项只有一项有效的情况）
select c1.*,b1.* from 需求表 c1
inner join (select 苹果 as 表名,a1.id as 行号,a1.bj as bj,a1.mj as mj,null as dx,null as sl,null as cd,null as kd,null as xj,null as dw from 苹果表 a1
UNION
select 梨子 as 表名,a2.id as 行号,null as bj,null as mj,a2.dx as dx,a2.sl as sl,null as cd,null as kd,null as xj,null as dw from 苹果表 a2
UNION
select 香蕉 as 表名,a3.id as 行号,null as bj,null as mj,null as dx,null as sl,a3.cd as cd,a3.kd as kd,null as xj,null as dw from 苹果表 a3
UNION
select 葡萄 as 表名,a4.id as 行号,null as bj,null as mj,null as dx,null as sl,null as cd,null as kd,a4.xj as xj,a4.dw as dw from 苹果表 a4) b1 on b1.表名 = c1.add
inner join (select d1.*,
case when isnull(d1.pg_id,0) > 0 then 苹果            
          when isnull(d1.lz_id,0) > 0 then 梨子
          when isnull(d1.xj_id,0) > 0 then 香蕉
          when isnull(d1.pt_id,0) > 0 then 葡萄
          else null end as 表名,
case when isnull(d1.pg_id,0) > 0 then d1.pg_id            
          when isnull(d1.lz_id,0) > 0 then d1.lz_id
          when isnull(d1.xj_id,0) > 0 then d1.xj_id
          when isnull(d1.pt_id,0) > 0 then d1.pt_id
          else null end as 行号    --此项如果也可以改成d1.pg_id + d1.lz_id + d1.xj_id + d1.pt_id as 行号
from 关联表 d1) d2 on d2.xu_id = c1.id and d2.表名=b1.表名 and d2.行号=b1.行号   --and d2.表名 = c1.add如果需求表中的ID会重复则需加上此项，不重复的话可不需要

xlinux

谢谢各位支持！