如何使用mysql从查询输出中排除记录(基于某些特定条件)？

piv4azn7 于 2021-06-20 发布在 Mysql

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

我不太喜欢sql，我有以下问题：如果某个特定条件被验证，我必须从查询输出中排除特定记录。
我有一个可以返回0，1，>1条记录的查询：

SELECT
    LS.id                                                                                                       AS livestock_id,
    LS.parent_livestock_species_id                                                                              AS parent_livestock_species_id,
    LS.livestock_species_name_en                                                                                AS livestock_species_name_en,
    IFNULL(LSN.livestock_species_name, LS.livestock_species_name_en)                                            AS livestock_species_name,        
    LSN.description                                                                                             AS description,
    LS.image_link                                                                                               AS image_link,
    (
        select count(*) from LivestockSpecies ls2 where ls2.id = 1 or ls2.parent_livestock_species_id = 1
    ) as total_number
FROM LivestockSpecies                                                                                          AS LS
LEFT JOIN LivestockSpeciesName                                                                                 AS LSN
      ON LSN.livestock_species_id = LS.id AND LSN.language_id = 1
WHERE
    LS.id = 1
OR
    LS.parent_livestock_species_id = 1

“总数”字段统计返回的记录总数并显示在每行中。
如果这个total\u number字段包含一个大于1的值，并且parent\u vestocs\u species\u id字段的值为**null*则从查询输出中排除该记录。
我可以用sql做这样的事情吗？

sql mysql Database rdbms

来源：https://stackoverflow.com/questions/50449058/how-can-i-exclude-a-record-from-the-query-output-basing-on-some-specific-condit

3条答案

按热度按时间

au9on6nz1#

SELECT
    LS.id AS livestock_id,
    LS.parent_livestock_species_id AS parent_livestock_species_id,
    LS.livestock_species_name_en AS livestock_species_name_en,
    IFNULL(LSN.livestock_species_name, LS.livestock_species_name_en) AS livestock_species_name,        
    LSN.description AS description,
    LS.image_link AS image_link,
    ls2.total_number as total_number
FROM LivestockSpecies AS LS
INNER JOIN (
   SELECT count(*) AS total_number 
   FROM LivestockSpecies ls2 
   WHERE ls2.id = 1 OR ls2.parent_livestock_species_id = 1
    ) ls2
  ON 1=1 AND ls2.total_number>0
LEFT JOIN LivestockSpeciesName AS LSN
      ON LSN.livestock_species_id = LS.id AND LSN.language_id = 1
WHERE
    LS.id = 1

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

niknxzdl2#

在mysql中，可以添加 having 条款：

having total_number = 1 or parent_livestock_species_id is not null

这回答了你的具体问题。也许有更合适的方法来完成你真正想完成的事情。

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

ftf50wuq3#

添加 HAVING 条款

SELECT
          LS.id                                                            AS livestock_id
        , LS.parent_livestock_species_id                                   AS parent_livestock_species_id
        , LS.livestock_species_name_en                                     AS livestock_species_name_en
        , IsNULL(LSN.livestock_species_name, LS.livestock_species_name_en) AS livestock_species_name
        , LSN.description                                                  AS description
        , LS.image_link                                                    AS image_link
        , (
                 select
                        count(*)
                 from
                        LivestockSpecies ls2
                 where
                        ls2.id                             = 1
                        or ls2.parent_livestock_species_id = 1
          )
          as total_number
FROM
          LivestockSpecies AS LS
          LEFT JOIN
                    LivestockSpeciesName AS LSN
                    ON
                              LSN.livestock_species_id = LS.id
                              AND LSN.language_id      = 1
WHERE
          LS.id                             = 1
          OR LS.parent_livestock_species_id = 1

GROUP BY
          LS.id
        , LS.parent_livestock_species_id
        , LS.livestock_species_name_en
        , IsNULL(LSN.livestock_species_name, LS.livestock_species_name_en)
        , LSN.description
        , LS.image_link

HAVING
          (
                 select
                        count(*)
                 from
                        LivestockSpecies ls2
                 where
                        ls2.id                             = 1
                        or ls2.parent_livestock_species_id = 1
          )
          > 1

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

我来回答

如何使用mysql从查询输出中排除记录(基于某些特定条件)？

3条答案

相关问题

热门标签

最新问答