sql一对多关系查询
答案:3 悬赏:0
解决时间 2021-02-15 17:45
- 提问者网友:娇妻失忆
- 2021-02-14 22:59
sql一对多关系查询
最佳答案
- 二级知识专家网友:啵啵桃汀
- 2021-02-14 23:06
CREATE TABLE #C (cid int, id int,value varchar(2))
INSERT #C SELECt 1, 1,'a'
UNIOn ALL SELECT 2, 1,'d'
UNIOn ALL SELECT 3, 2,'a'
UNIOn ALL SELECT 4, 2,'b'
UNIOn ALL SELECT 5, 3,'c'
CREATE TABLE #A (id int, name varchar(2),age int)
INSERT #A SELECT 1, 'de',33
UNIOn ALL SELECT 2, 'ac',34
UNIOn ALL SELECT 3, 'ea',35
-- 查询处理
SELECT #A.*,T1.cid,T1.value FROM #A INNER JOIN (
SELECt *
FROM(
SELECt DISTINCT
id
FROM #C
)A
OUTER APPLY(
SELECt
[cid]= STUFF(REPLACE(REPLACE(
(
SELECT cid FROM #C N
WHERe id = A.id
FOR XML AUTO
), ' ', ''), 1, 1, '')
)N
OUTER APPLY(
SELECt
[value]= STUFF(REPLACE(REPLACE(
(
SELECT value FROM #C N
WHERe id = A.id
FOR XML AUTO
), ' ', ''), 1, 1, '')
)d
)T1 ON #A.id=T1.id
DROP TABLE #A
drop table #C
-------------------------
id name age cid value
1 de 33 1,2 a,d
2 ac 34 3,4 a,b
3 ea 35 5 c
INSERT #C SELECt 1, 1,'a'
UNIOn ALL SELECT 2, 1,'d'
UNIOn ALL SELECT 3, 2,'a'
UNIOn ALL SELECT 4, 2,'b'
UNIOn ALL SELECT 5, 3,'c'
CREATE TABLE #A (id int, name varchar(2),age int)
INSERT #A SELECT 1, 'de',33
UNIOn ALL SELECT 2, 'ac',34
UNIOn ALL SELECT 3, 'ea',35
-- 查询处理
SELECT #A.*,T1.cid,T1.value FROM #A INNER JOIN (
SELECt *
FROM(
SELECt DISTINCT
id
FROM #C
)A
OUTER APPLY(
SELECt
[cid]= STUFF(REPLACE(REPLACE(
(
SELECT cid FROM #C N
WHERe id = A.id
FOR XML AUTO
), '
)N
OUTER APPLY(
SELECt
[value]= STUFF(REPLACE(REPLACE(
(
SELECT value FROM #C N
WHERe id = A.id
FOR XML AUTO
), '
)d
)T1 ON #A.id=T1.id
DROP TABLE #A
drop table #C
-------------------------
id name age cid value
1 de 33 1,2 a,d
2 ac 34 3,4 a,b
3 ea 35 5 c
全部回答
- 1楼网友:荒唐后生
- 2021-02-15 01:00
数据插入
insert into b(aid,bvalue) select 3,'bvalue' from a where a.id=3
解释:select 3,'bvalue' from a where a.id=3
3=我们向B表中插入的aid的值
bvalue=我们向B表中插入的bvalue的值
使用where
语句判断主表(A表)是否拥有ID为3的数据
数据查询
select a.avalue,b.aid ,b.bvalue
from a left join b on b.aid=a.id
SELECt
t1.id,t2.id,t3.id
FROM t1,( t2 LEFT JOIN t3 ON (t3.id=t1.id) )
WHERe
t1.id=t2.id;
- 2楼网友:woshuo
- 2021-02-14 23:36
select a.id, a.name, a.age, wm_concat(c.cid) cid, wm_concat(c.value) value
from a
left join c
on a.id = c.id
group by a.id, a.name, a.age
我要举报
如以上问答内容为低俗、色情、不良、暴力、侵权、涉及违法等信息,可以点下面链接进行举报!
大家都在看
推荐资讯
• 手机登qq时,显示手机磁盘不足,清理后重新登 |
• 刺客的套装怎么选啊? |