有这样两张表,新闻表news、新闻追踪表tarcing_news(记录访问量)
CREATE TABLE news
(news_id
int(11) NOT NULL AUTO_INCREMENT,title
varchar(255) NOT NULL,content
text NOT NULL,
PRIMARY KEY (news_id
)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE tracing_news
(tracing_id
int(11) NOT NULL AUTO_INCREMENT,news_id
int(11) NOT NULL,
PRIMARY KEY (tracing_id
)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
现使用spring data jpa实现查询新闻内容和其对应的阅读量,sql文如下:
SELECT n.*, tt.count FROM news n,
(SELECT t.news_id,COUNT(1) AS 'count' FROM tracing_news t GROUP BY news_id) tt
WHERE n.news_id = tt.news_id
请问,如果是这样,实体和repository应该怎么写?