从“log”表获取数据到mysql查询

u5rb5r59 于 2021-06-24 发布在 Mysql

关注(0)|答案(3)|浏览(188)

我需要检查日志表中的数据，看看用户以前是否读过帖子。我的日志表名为foretag\u kontaktervisadbrf2017，它包含关于谁读过一篇文章的信息。我如何编辑我的查询，使我得到的信息告诉我，如果我读了一篇文章或没有？
这是我目前的疑问：

SELECT 
    brfbolagsverket.BrfNamn
    , brfextra.BrfId
    , brfextra.Organisationsnr
    , brfextra.Gatuadress
    , brfextra.Ort
    , brfextra.Lagenheter
    , brfextra.ByggAr
    , BrfHarFastighet.Fangdatum    
FROM 
    (((((brfextra LEFT JOIN BrfHarFastighet ON brfextra.Organisationsnr = BrfHarFastighet.Organisationsnr)     
    LEFT JOIN brfkonkurs ON brfextra.Organisationsnr = brfkonkurs.Organisationsnr) 
    LEFT JOIN brfavford ON brfextra.Organisationsnr = brfavford.Organisationsnr) 
    LEFT JOIN brffusion ON brfextra.Organisationsnr = brffusion.Organisationsnr) 
    LEFT JOIN brflikvidation ON brfextra.Organisationsnr = brflikvidation.Organisationsnr) 
    LEFT JOIN brfbolagsverket ON brfextra.Organisationsnr = brfbolagsverket.Organisationsnr  
WHERE 
    (((brfkonkurs.BolagetsStatusKod) IS NULL) 
    AND ((brfavford.BolagetsStatusKod) IS NULL) 
    AND ((brffusion.BolagetsStatusKod) IS NULL) 
    AND ((brflikvidation.BolagetsStatusKod) IS NULL OR (brflikvidation.BolagetsStatusKod) = 34))

在结果中，我需要日志表中的以下附加列：

BrfId, BrfNamn, Organisationsnr, LogEntry
1,     Billy,   Organisationsnr, You have shown this post before
2,     Carl,    Organisationsnr, NULL
3,     Kent,    Organisationsnr, NULL
4,     Sara,    Organisationsnr, You have shown this post before

请帮助我的代码需要添加到我的查询。我缺乏编码经验。

sql mysql left-join

来源：https://stackoverflow.com/questions/49749696/get-data-from-a-log-table-to-a-mysql-query

3条答案

按热度按时间

mbskvtky1#

这里是另一种不使用subselect的方法，它假设表foretag\u kontaktervisadbrf2017每个用户包含0或1行。

SELECT 
    brfbolagsverket.BrfNamn
    , CASE WHEN L.Foretag_kontaktID IS NOT NULL THEN 'Lest' ELSE 'Inte lest' END AS NotificationRead
    , brfextra.BrfId
    , brfextra.Organisationsnr
    , brfextra.Gatuadress
    , brfextra.Ort
    , brfextra.Lagenheter
    , brfextra.ByggAr
    , BrfHarFastighet.Fangdatum

FROM (((((
    brfextra AS B
    LEFT JOIN BrfHarFastighet ON brfextra.Organisationsnr = BrfHarFastighet.Organisationsnr) 
    LEFT JOIN brfkonkurs ON brfextra.Organisationsnr = brfkonkurs.Organisationsnr) 
    LEFT JOIN brfavford ON brfextra.Organisationsnr = brfavford.Organisationsnr) 
    LEFT JOIN brffusion ON brfextra.Organisationsnr = brffusion.Organisationsnr) 
    LEFT JOIN brflikvidation ON brfextra.Organisationsnr = brflikvidation.Organisationsnr) 
    LEFT JOIN brfbolagsverket ON brfextra.Organisationsnr = brfbolagsverket.Organisationsnr 
    LEFT JOIN foretag_kontaktervisadbrf2017 AS L ON L.Organisationsnr = B.Organisationsnr AND L.Foretag_kontaktID = 1247 -- This id probably needs to come from somewhere else, join in the users etc..

WHERE (((brfkonkurs.BolagetsStatusKod) Is Null) AND ((brfavford.BolagetsStatusKod) Is Null) AND ((brffusion.BolagetsStatusKod) Is Null) AND ((brflikvidation.BolagetsStatusKod) Is Null Or (brflikvidation.BolagetsStatusKod)=34))

赞(0）回复(0）举报 2021-06-24

kpbpu0082#

为此，我将添加一个子选择，如下面的示例所示。希望它能帮你找到正确的方向。

SELECT 
    brfbolagsverket.BrfNamn
    , CASE WHEN (SELECT COUNT(*) FROM foretag_kontaktervisadbrf2017 AS L WHERE L.Organisationsnr = B.Organisationsnr AND L.Foretag_kontaktID = 1247) > 0 THEN 'Lest' ELSE 'Inte lest' END AS NotificationRead
    , brfextra.BrfId
    , brfextra.Organisationsnr
    , brfextra.Gatuadress
    , brfextra.Ort
    , brfextra.Lagenheter
    , brfextra.ByggAr
    , BrfHarFastighet.Fangdatum

FROM (((((
    brfextra AS B
    LEFT JOIN BrfHarFastighet ON brfextra.Organisationsnr = BrfHarFastighet.Organisationsnr) 
    LEFT JOIN brfkonkurs ON brfextra.Organisationsnr = brfkonkurs.Organisationsnr) 
    LEFT JOIN brfavford ON brfextra.Organisationsnr = brfavford.Organisationsnr) 
    LEFT JOIN brffusion ON brfextra.Organisationsnr = brffusion.Organisationsnr) 
    LEFT JOIN brflikvidation ON brfextra.Organisationsnr = brflikvidation.Organisationsnr) 
    LEFT JOIN brfbolagsverket ON brfextra.Organisationsnr = brfbolagsverket.Organisationsnr 

WHERE (((brfkonkurs.BolagetsStatusKod) Is Null) AND ((brfavford.BolagetsStatusKod) Is Null) AND ((brffusion.BolagetsStatusKod) Is Null) AND ((brflikvidation.BolagetsStatusKod) Is Null Or (brflikvidation.BolagetsStatusKod)=34))

赞(0）回复(0）举报 2021-06-24

oxalkeyp3#

我很抱歉，如果我张贴错误-不知道如何正确地回答沃纳瓦格的答案。
当搜索得到超过50-70条记录时，查询速度很慢-我试图更改为选择like (SELECT Foretag_kontaktervisadBrfID FROM foretag_kontaktervisadbrf2017 AS L WHERE L.Organisationsnr = B.Organisationsnr AND L.Foretag_kontaktID = '1247') AS NotificationRead 但它的速度很慢。
我的日志表foretag\u kontaktervisadbrf2017有这样独特的帖子

Foretag_kontaktervisadBrfID, Foretag_kontaktID, Organisationsnr, Date001
1,  Billy01,   Acme_01,  2018-01-01
2,  Carl01,    Acme_02,  2018-01-02
3,  Billy01,   Acme_02,  2018-04-15
4,  Carl01,    Acme_03,  2018-14-14

有没有办法让我以前的速度回到新的查询？我最初的sql查询工作得很快，即使结果是1000条记录/帖子。

赞(0）回复(0）举报 2021-06-24

我来回答

从“log”表获取数据到mysql查询

3条答案

相关问题

热门标签

最新问答