数据库查询求助

xlinux 发表于 2023-9-22 13:17

【新手】
表结构如下：

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

zhantianxia 发表于 2023-9-22 13:40

sql联表查询了解下很简单

0Ling0 发表于 2023-9-22 13:57

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

layuai 发表于 2023-9-22 14:13

课程作业？

hushxh 发表于 2023-9-22 14:36

很简单的联合查询

dzpos 发表于 2023-9-22 14:48

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

cnorchid 发表于 2023-9-22 15:15

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

dzwanser 发表于 2023-9-22 15:45

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 发表于 2023-9-23 14:22

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 发表于 2023-9-24 15:44

谢谢各位支持！{:1_893:}

页: [1]

吾爱破解 - 52pojie.cn's Archiver

数据库查询求助