sql语句，在线等

[xuxiaomei2707]

一个主表，TA
id 主键
title 标题
content 内容

一个子表 TB 这个表存的是对TA表的评论
id 主键
comment 评论


我想查TA表中所有的数据，在加上每个标题的评论数

标题 内容 评论条数

[qxyywy]

你的TB表应该有个TAID对应TA的主键

select title ,content,replaySum=(select count(*) from tb where tb.TAID=ta.id)
from Ta

[夜色镇歌]

select  a.title,a.content,replaySum=(select count(*) from TB b where b.taid=a.id) from TA a

[华生]

[Quote=引用 1 楼 qxyywy 的回复:]
你的TB表应该有个TAID对应TA的主键

select title ,content,replaySum=(select count(*) from tb where tb.TAID=ta.id)
from Ta
[/Quote]+++++

[xuxiaomei2707]

[Quote=引用 1 楼 qxyywy 的回复:]
你的TB表应该有个TAID对应TA的主键

select title ,content,replaySum=(select count(*) from tb where tb.TAID=ta.id)
from Ta
[/Quote]

是的，TB表少写了一个，这上面忘了写了，谢谢了

[猿序程]

你的TB表差一个与TA表关联的外键Id啊，比如叫taid

select a.title,a.content,replaySum=(select count(*) from TB b where b.taid=a.id) from TA a

[EnForGrass]

CREATE TABLE TA
(
   tid INT PRIMARY KEY,
   title VARCHAR(50),
   tcontent TEXT
)
INSERT INTO TA VALUES(1,'C#','ab')
INSERT INTO TA VALUES(2,'sql','cd')
INSERT INTO TA VALUES(3,'java','ef')
CREATE TABLE TB
(
   tid INT REFERENCES TA(tid),
   comment TEXT
)
DROP TABLE TA
INSERT INTO TB VALUES(1,'hi')
INSERT INTO TB VALUES(1,'rr')
INSERT INTO TB VALUES(2,'tt')
INSERT INTO TB VALUES(2,'yy')

SELECT *,num=(SELECT COUNT(*) FROM TB where TA.tid=TB.tid) FROM TA 
/*
tid         title                                              tcontent                                                                                                                                                                                                                                                         num
----------- -------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -----------
1           C#                                                 ab                                                                                                                                                                                                                                                               2
2           sql                                                cd                                                                                                                                                                                                                                                               2
3           java                                               ef                                                                                                                                                                                                                                                               0

(3 行受影响)
*/

[EnForGrass]

调整一下

CREATE TABLE TA
(
   tid INT PRIMARY KEY,
   title VARCHAR(50),
   tcontent TEXT
)
INSERT INTO TA VALUES(1,'C#','ab')
INSERT INTO TA VALUES(2,'sql','cd')
INSERT INTO TA VALUES(3,'java','ef')
CREATE TABLE TB
(
   tid INT REFERENCES TA(tid),
   comment TEXT
)
DROP TABLE TA
INSERT INTO TB VALUES(1,'hi')
INSERT INTO TB VALUES(1,'rr')
INSERT INTO TB VALUES(2,'tt')
INSERT INTO TB VALUES(2,'yy')
INSERT INTO TB VALUES(3,'yu')
SELECT *,num=(SELECT COUNT(*) FROM TB where TA.tid=TB.tid) FROM TA 
/*
CREATE TABLE TA
(
   tid INT PRIMARY KEY,
   title VARCHAR(50),
   tcontent TEXT
)
INSERT INTO TA VALUES(1,'C#','ab')
INSERT INTO TA VALUES(2,'sql','cd')
INSERT INTO TA VALUES(3,'java','ef')
CREATE TABLE TB
(
   tid INT REFERENCES TA(tid),
   comment TEXT
)
DROP TABLE TA
INSERT INTO TB VALUES(1,'hi')
INSERT INTO TB VALUES(1,'rr')
INSERT INTO TB VALUES(2,'tt')
INSERT INTO TB VALUES(2,'yy')
INSERT INTO TB VALUES(3,'yu')
SELECT *,num=(SELECT COUNT(*) FROM TB where TA.tid=TB.tid) FROM TA 
/*
tid         title         tcontent      num
----------- ---------- -------------  ------
1           C#             ab            2
2           sql            cd            2
3           java           ef            1

(3 行受影响)
*/
*/

[EnForGrass]

越调越错

CREATE TABLE TA
(
   tid INT PRIMARY KEY,
   title VARCHAR(50),
   tcontent TEXT
)
INSERT INTO TA VALUES(1,'C#','ab')
INSERT INTO TA VALUES(2,'sql','cd')
INSERT INTO TA VALUES(3,'java','ef')
CREATE TABLE TB
(
   tid INT REFERENCES TA(tid),
   comment TEXT
)
INSERT INTO TB VALUES(1,'hi')
INSERT INTO TB VALUES(1,'rr')
INSERT INTO TB VALUES(2,'tt')
INSERT INTO TB VALUES(2,'yy')
INSERT INTO TB VALUES(3,'yu')
SELECT *,num=(SELECT COUNT(*) FROM TB where TA.tid=TB.tid) FROM TA 
/*
tid         title         tcontent      num
----------- ---------- -------------  ------
1           C#             ab            2
2           sql            cd            2
3           java           ef            1

(3 行受影响)
*/

[CodingR]

TB这个表应该设个外键和TA表关联一下


select T1.*,replaySum=(select count(*)
from T2 TB where T2.id=T1.id) from TA T1

[haozhibing45]

搞什么啊，两个表关系都没有